excel - 私有(private)函数跳过 For 循环

Question

我现在正在尝试学习字典在 VBA 中是如何工作的，所以我创建了一个简单的类模块、一个函数，然后是两个子模块，但出于我之外的原因 For循环在函数内被完全跳过。以下是上述所有项目的代码。我确实在工具 > 引用中检查了 Microsoft 脚本运行时。我不太熟悉如何使用后期和早期绑定(bind)，所以我想知道这是否是问题之一。

目前Set rg = LoanData.Range("AH2")在表格中，我已经尝试将该范围内的数据作为表格和范围，但是 For如果数据在表中，则跳过函数中的循环。



名为 clsCounty 的类模块

Public CountyID As Long
Public County As String

名为 ReadCounty 的函数

Private Function ReadCounty() As Dictionary
    Dim dict As New Dictionary

    Dim rg As Range
    Set rg = LoanData.Range("AH2")

    Dim oCounty As clsCounty, i As Long


    For i = 2 To rg.Rows.Count

        Set oCounty = New clsCounty

        oCounty.CountyID = rg.Cells(i, 1).Value
        oCounty.County = rg.Cells(i, 2).Value

        dict.Add oCounty.CountyID, oCounty
    Next i

    Set ReadCounty = dict


End Function

要写入即时窗口的两个潜艇

Private Sub WriteToImmediate(dict As Dictionary)
    Dim key As Variant, oCounty As clsCounty

    For Each key In dict.Keys
        Set oCounty = dict(key)
        With oCounty
            Debug.Print .CountyID, .County
        End With
    Next key

End Sub

Sub Main()
    Dim dict As Dictionary

    Set dict = ReadCounty

    WriteToImmediate dict

End Sub

Answer 1

您已将您的范围声明为 Set rg = LoanData.Range("AH2")然后使用 For i = 2 To rg.Rows.Count在你的循环中。 rg.Rows.Count将是 1因为您的范围内只有 1 个单元格。这是您的 For 的起始值之前。循环( 2 )所以它不会做任何事情。

即For i = 2 to 1
声明您的rg变量与全范围。我会猜测类似的东西

With LoanData
    Set rg = .Range(.Cells(1,"AH"), .Cells(.Cells(.Rows.Count, "AH").End(xlUp).Row, "AH"))
End With