用户定义的数组/集合类型以及每个循环的类型 [英] User defined types in arrays/collections and for each loops

问题描述

VBA在弹出窗口中显示不允许我遍历具有用户定义类型的数组. 我写了一些代码，想知道如何解决此问题. 这是一个迷你示例，专注于我想做的事.

VBA shows in a popup that i am not allowed to iterate through an array with user defined types. I wrote a bit of code and wonder how i can work around this. Here is a mini example that focusses on what i want to be able to do.

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数组? 由于我无法编译此文件，因此这里可能存在多个问题. 我不确定我设置AllTargets列表的方式是否是有效的语法.

How can i iterate through my AllTargets array? Since i am unable to compile this there may be more than one problem here. I am not entirely sure if the way i set up the AllTargets list is a valid syntax.

我重新制作了示例以缩小代码中的问题:

I reworked the example to narrow down the problems in the code:

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

代码从Array到Collection-即便如此，仍然存在我现在无法解决的问题.

The code went from Array to Collection - never the less there are still issues in it that i can not solve now.

我认为根本原因保持不变:使用用户定义的类型. 我将问题所在的位置标记为评论.

I think the root cause stayed the same: using user defined types. I marked as a comment where i think the problems are located.

推荐答案

您不能将UDT添加到集合或词典中.我不知道为什么，但这是语言固有的.您可以创建一个简单的自定义类，其功能与UDT相同.我不再使用UDT，而只是创建一个类来避免这些奇怪的限制.

You can't add UDTs to collections or dictionaries. I don't know why, but it's inherent in the language. You can make a simple custom class that does the same thing as the UDT. I never use UDTs any more and just create a class to avoid these strange limitations.

创建一个新的类模块(插入-模块).转到属性表(F4)，然后将name属性更改为CInfo.

Create a new class module (Insert - Module). Go to the properties sheet (F4) and change the name property to 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

这篇关于用户定义的数组/集合类型以及每个循环的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！