Excel VBA函数采用2种不同的数据类型并循环通过类模块的集合

Question

我有以下子工作，但得到#VALUE!将 sub 更改为函数后出错。
工作子如下

Sub Get_Tier2(ByVal myCategory As String, ByVal myMSRP As Double)

Dim ProductTiers As Collection
Dim pt As ProductTier
Dim result As String
Dim CIDTierCount As Integer
CIDTierCount = ThisWorkbook.Worksheets("CIDTier").Range("A:A").CurrentRegion.Rows.Count

Set ProductTiers = New Collection

For i = 2 To CIDTierCount
    Set pt = New ProductTier
    pt.Category = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("B")
    pt.Tier = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("C")
    pt.MSRP_Low = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("D")
    pt.MSRP_High = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("E")
    ProductTiers.Add pt
Next


For Each pt In ProductTiers

    If StrComp(pt.Category, myCategory) = 0 And pt.MSRP_Low < myMSRP And myMSRP < pt.MSRP_High Then
        result = pt.Tier()
        MsgBox result
        Exit Sub
    Else
        'do nothing
    End If
Next

End Sub

当我将 sub 更改为函数并尝试在 excel 中使用时，它会返回 #VALUE!在excel中

Function Get_Tier(ByVal myCategory As String, ByVal myMSRP As Double) As String

Dim ProductTiers As Collection
Dim pt As ProductTier
Dim result As String

Dim CIDTierCount As Integer
CIDTierCount = ThisWorkbook.Worksheets("CIDTier").Range("A:A").CurrentRegion.Rows.Count

Set ProductTiers = New Collection

For i = 2 To CIDTierCount
    Set pt = New ProductTier

    pt.Category = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("B")
    pt.Tier = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("C")
    pt.MSRP_Low = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("D")
    pt.MSRP_High = ThisWorkbook.Worksheets("CIDTier").Rows(i).Columns("E")
    ProductTiers.Add pt
Next

For Each pt In ProductTiers
    If StrComp(pt.Category, myCategory) = 0 And pt.MSRP_Low < myMSRP And myMSRP < pt.MSRP_High Then
        Get_Tier = pt.Tier()
        Exit Function
    Else
        Get_Tier = "no match"
    End If
Next

End Function

类模块ProductTier如下

Private pCategory As String
Private pTier As String
Private pMSRP_Low As Double
Private pMSRP_High As Double

Public Property Get Category() As String
    Category = pCategory
End Property

Public Property Let Category(Value As String)
    pCategory = Value
End Property

Public Property Get Tier() As String
    Tier = pTier
End Property

Public Property Let Tier(Value As String)
    pTier = Value
End Property

Public Property Get MSRP_Low() As String
    MSRP_Low = pMSRP_Low
End Property

Public Property Let MSRP_Low(Value As String)
    pMSRP_Low = Value
End Property

Public Property Get MSRP_High() As String
    MSRP_High = pMSRP_High
End Property

Public Property Let MSRP_High(Value As String)
    pMSRP_High = Value
End Property

以及 CIDTier 选项卡上的数据
B栏:产品A
C 列:第 1 层
D 列:0
E 栏:300

Answer 1

罪魁祸首

CIDTierCount = ThisWorkbook.Worksheets("CIDTier").Range("A:A").CurrentRegion.Rows.Count

如果您在该行上放置一个调试标记并打印什么

ThisWorkbook.Worksheets("CIDTier").Range("A:A").CurrentRegion.Rows.Count

返回，你会得到 1048576 .这将溢出您尝试将其放入的整数。

可能的解决方案

从您正在做的事情来看，您似乎想要行数，您可以尝试将代码更改为以下内容:

CIDTierCount = Worksheets("CIDTier").UsedRange.Rows.Count

为什么这有效

在 Excel 中，每个工作表都有一个定义为 UsedRange 的范围。此范围从 A1 到已格式化或包含数据的最右侧列和已格式化或包含数据的最低行。