数组/集合和 for Each 循环中的用户定义类型

VBA 在弹出窗口中显示，不允许我迭代具有用户定义类型的数组。 我写了一些代码，想知道如何解决这个问题。 这是一个小例子，重点关注我想要做的事情。

Option Explicit

Type Info
    source As String
    destination As String
End Type

Sub specialCopy()
    Dim target As Variant
    Dim AllTargets() As Info: AllTargets = SetAllTargets()
    For Each target In AllTargets
        CopyValues (target)
    Next
End Sub

Function SetAllTargets() As Info()
    Dim A As Info: A = SetInfo("A1", "B1")
    Dim B As Info: B = SetInfo("A2", "B2")
    Dim AllTargets() As Info
    Set AllTargets = Array(A, B)
End Function

Function SetInfo(source As String, target As String) As Info
    SetInfo.source = source
    SetInfo.destination = destination
End Function

Sub CopyValues(target As Info)
    Range(target.source).Select
    Selection.Copy
    Range(target.destination).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

我如何迭代我的AllTargets大批？ 由于我无法编译此文件，因此这里可能存在多个问题。 我不完全确定我设置的方式是否AllTargetslist 是有效的语法。

我重新编写了示例以缩小代码中的问题范围：

Option Explicit

Type Info
    source As String
    destination As String
End Type

Sub specialCopy()
    Dim target As Variant
    Dim AllTargets As Collection: Set AllTargets = SetAllTargets()
    For Each target In AllTargets
        CopyValues (target) '2. unkown if this is possible
    Next
End Sub

Function SetAllTargets() As Collection
    Dim A As Info: A = SetInfo("A1", "B1")
    Dim B As Info: B = SetInfo("A2", "B2")
    Set SetAllTargets = New Collection
    SetAllTargets.Add (A) '1. problem here when assigning user type
    SetAllTargets.Add (B) '1. problem here when assigning user type
End Function

Function SetInfo(source As String, destination As String) As Info
    SetInfo.source = source
    SetInfo.destination = destination
End Function

Sub CopyValues(target As Info)
    Range(target.source).Select
    Selection.Copy
    Range(target.destination).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

代码从数组变为集合 - 尽管如此，其中仍然存在我现在无法解决的问题。

我认为根本原因保持不变：使用用户定义的类型。 我把我认为问题所在的地方标记为评论。

您无法将 UDT 添加到集合或字典中。我不知道为什么，但这是语言固有的。您可以创建一个简单的自定义类来执行与 UDT 相同的操作。我不再使用 UDT，只是创建一个类来避免这些奇怪的限制。

创建一个新的类模块（插入 - 模块）。转到属性表 (F4) 并将名称属性更改为 CInfo。

在 CInfo 类中

Private mSource As String
Private mDestination As String

Public Property Get Source() As String
    Source = mSource
End Property

Public Property Let Source(rhs As String)
    mSource = rhs
End Property

Public Property Get Destination() As String
    Destination = mDestination
End Property

Public Property Let Destination(rhs As String)
    mDestination = rhs
End Property

在标准模块中

Sub specialCopy()
    Dim target As Variant
    Dim AllTargets As Collection: Set AllTargets = SetAllTargets()
    For Each target In AllTargets
        CopyValues target '2. unkown if this is possible
    Next
End Sub

Function SetAllTargets() As Collection
    Dim A As CInfo: Set A = SetInfo("A1", "B1")
    Dim B As CInfo: Set B = SetInfo("A2", "B2")
    Set SetAllTargets = New Collection
    SetAllTargets.Add A
    SetAllTargets.Add B
End Function

Function SetInfo(Source As String, Destination As String) As CInfo
    Set SetInfo = New CInfo
    SetInfo.Source = Source
    SetInfo.Destination = Destination
End Function

Sub CopyValues(ByRef target As Variant)
    Range(target.Source).Select
    Selection.Copy
    Range(target.Destination).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub