excel - 后期绑定字典 VBA 的问题

Question

我已经使用带有早期绑定的字典编写了一个代码。我对该代码没有任何问题。所以之后，我尝试用字典重写代码，但这次使用的是后期绑定。但是，当使用后期绑定时，我收到以下错误。当我尝试通过这行代码时：

Debug.Print RColDict.Keys(count), RColDict.Items(count)

弹出以下错误：

未定义属性让程序和属性获取程序未返回对象（错误 451）

Option Explicit

Public Sub testsub()
Dim rng1 As Range
Dim count As Long
Dim RColDict As Object
Set RColDict = CreateObject("Scripting.Dictionary")

Set rng1 = Range("A1:A100").SpecialCells(xlCellTypeVisible).Cells

Debug.Print rng1.Address

Set RColDict = ReadColData(rng1)

For count = 0 To RColDict.count - 1
    Debug.Print RColDict.Keys(count), RColDict.Items(count)
Next count

Set rng1 = Range("C1:C100").SpecialCells(xlCellTypeVisible).Cells

Set RColDict = ReadColData(rng1)

For count = 0 To RColDict.count - 1
    Debug.Print RColDict.Keys(count), RColDict.Items(count)
Next count

End Sub

Public Function ReadColData(ColRng As Range) As Object
'ReadColData will read the values on a single column Range, whether it's a filtered column range _
' or a contiguous range

Dim RColDict As Object
Set RColDict = CreateObject("Scripting.Dictionary")
Dim RngAddressArray() As String
Dim NumberOfAreas As Long
Dim RngAddress As Variant
Set ReadColData = CreateObject("Scripting.Dictionary")

NumberOfAreas = ColRng.Areas.count

'A filtered column range address has a structure like this: $A$74:$A$117,$A$126:$A$132,$A$135:$A$136
'So we can use the split function with "," as the separator, split returns an array that starts at index 0

'If the filtered column range contains only 1 contiguous range then it's address will have a structure _
' like this: $A$12:$A$35

'If the filtered column range contains only 1 row then it's address will have a structure like this: $A$12

ReDim RngAddressArray(0 To NumberOfAreas - 1)

RngAddressArray = Split(ColRng.Address, ",")

For Each RngAddress In RngAddressArray
    Set RColDict = ReadColRangeAddress(RColDict, RngAddress)
Next RngAddress

Set ReadColData = RColDict
Set RColDict = Nothing

End Function

Private Function ReadColRangeAddress(RColDict, RngAddress As Variant) As Object

Dim count As Long
Dim StartRow As String, FinalRow As String, ColIndex As String
'RngAddress will be in fact a string with a structure like this: $A$74:$A$117 if it has multiple rows
'                                                   or like this: $A$74 if it has only 1 row
Dim CellValue As Variant
Set ReadColRangeAddress = CreateObject("Scripting.Dictionary")

If UBound(Split(RngAddress, ":")) > 0 Then
    StartRow = Split(RngAddress, ":")(0)
    ColIndex = Split(StartRow, "$")(1) ' Here Split returns an array with 3 elements, the first one being empty
    StartRow = Split(StartRow, "$")(2)
    FinalRow = Split(RngAddress, ":")(1)
    FinalRow = Split(FinalRow, "$")(2)
Else
    StartRow = Split(RngAddress, ":")(0)
    ColIndex = Split(StartRow, "$")(1)
    StartRow = Split(StartRow, "$")(2)
    FinalRow = StartRow
End If

For count = StartRow To FinalRow
    CellValue = Cells(count, ColIndex) 'Cells accepts letters as column indexes
    RColDict.Add count, CellValue
    'Here the row number will be the key of the item, and the item will be the value of the cell at row count and column ColIndex
Next count

Set ReadColRangeAddress = RColDict

End Function

我不知道为什么会弹出这个错误...所有帮助将不胜感激在此先感谢大家

标签： excelvbadictionarylate-binding