特定对象的VBA变体？

Question

我开始在 VBA (Excel AddIn --> xlam) 中使用 Collection，但发现它很难使用。除了变体之外，我无法检索我放入的任何内容。例如，在 Java 中，如果你创建一个 Collection ，你会为它分配一个类型，比如 Collection<MyPerfectObjectThatStoresSomeData> ，所以当我 foreach 它时，我知道我得到了我的对象并且可以像那样使用它。当我想在 VBA 中做同样的事情时，我得到了 Variant 类型，我什至无法将它转换为我的类型。

例子。

假设我想从我这样填写的 Collection 中获取最后一个条目:

Private Function FindTargets(sheet As Worksheet, target As String) As Collection
    Dim result As New Collection
    Dim aCell As range
    Dim i As Long
    For i = 1 To 10456
      Set aCell = sheet.Rows(i).Find(What:=target, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
             MsgBox "Value Found in Cell " & aCell.Address & _
            " and the Cell Column Number is " & aCell.Column
            result.Add aCell
        End If
    Next i

    Set FindTargets = result
End Function

之后，我想遍历结果，但我无法在没有错误的情况下获取最后一个条目:

Dim targetCollection As Collection
Set targetCollection = FindTargets(targetSheet, "some text")
Dim target As range
target = targetCollection.Item(targetCollection.Count - 1)

我得到错误:

Run-time error '91':
Object variable or With block variable not set

如果我尝试

Set target = targetCollection.Item(targetCollection.Count - 1)

我得到:

Run-time error '1004':
Application-defined or object defined error

我只能循环并从 Collection 获取条目到 Variant 类型以外的类型吗？

编辑:更具体地说，此代码需要范围的坐标，但在 Variant 中，我得到了单元格的文本。我考虑过创建一个新类，例如具有文本和坐标属性的 CellProperties 并将其放入 Collection 中，但同样，我无法从 Collection 中检索 Variant 以外的任何内容。

Answer 1

您的错误似乎是由于假设集合的项目是从零开始的，但是 Collection 中的第一项索引为 1

以下是您的 FindTargets 的精简版我用于测试，以及返回 Address 的测试子例程添加到 Collection 的最后一个单元格的:

Private Function FindTargets(sheet As Worksheet, target As String) As Collection
    Dim result As New Collection
    Dim i As Long
    For i = 1 To 100
        result.Add Cells(i, 1)
    Next i

    Set FindTargets = result
End Function

Sub test()
Dim targetCollection As Collection
Set targetCollection = FindTargets(Worksheets("Sheet1"), "some text")
Dim target As Range
Set target = targetCollection.Item(targetCollection.Count)
MsgBox target.Address 'will display $A$100
End Sub