Custom types in arrays / collections and for each loop

Question

VBA shows in a popup that I'm not allowed to iterate through an array with user-defined types. I have written some code and am wondering how I can get around this. Here is a mini example that focuses on what I want 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 

How can I AllTargets over an array of AllTargets ? Since I cannot compile this, there may be more than one problem. I'm not quite sure that the way I configured the AllTargets list is 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 

The code has moved from Array to Collection - however, it still has problems that I can’t solve now.

I think the main reason remains the same: the use of custom types. I noted as a comment where I think the problems are.

Answer 1

You cannot add UDTs to collections or dictionaries. I do not know why, but it is inherent in the language. You can create a simple custom class that does the same thing as UDT. I never use UDT again and just create a class to avoid these strange restrictions.

Create a new class module (Insert - Module). Go to the property list (F4) and change the name property to CInfo.

In CInfo Class

 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 

In the standard module

 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