VBA - For Each variable/object in User-Defined Type

[higgsch]

Is there a way to loop through each variable or object (with a variant, of course) contained in a UDT? For the purpose of saving a UDT created to hold the user's chosen preferences. If it exists, the loop should loop through each item in the same order for the purpose of reloading the user's chosen preferences.

Using this pattern:

For Each ********> in <container> 
  ...
Next

I receive the error:
For Each may only iterate over a collection object or an array

Any ideas?

 

[mikerickson]

If your UDF returns an array or a collection, you can loop through it.

Sub test()
    Dim oneElement as Variant

    For Each oneElement in OneTo(10)
        MsgBox oneElement
    Next oneElement
End Sub

Function OneTo(N as Long) as Variant
    Dim i As Long
    Dim result as Variant
    ReDim result(1 to N)

    For i = 1 to N
        result(i) = i
    Next i

    OneTo = result
End Function

 

[higgsch]

My UDF contains several variables and several arrays. How would I have it return a collection?

Here's my UDF:

Public Type WSheets
    WName As String
    hVal As String
    hName As String
    hWgt As String
    hDate As String
    colHeadings() As String
    colVal As Integer
    colName As Integer
    colWgt As Integer
    colDate As Integer
    LastCol As Integer
    LastRow As Integer
    SortOrder() As String
    NumericCols() As String
End Type

I have an array of WSheets, one index for each Worksheet in the Workbook

 

[mikerickson]

I misread the OP.
You want to loop through all of the properties of a User Defined Type.

The only way that I can think of would be to create a UDF that puts them into an array.

Public Type WSheets
    WName As String
    hVal As String
    hName As String
    hWgt As String
    hDate As String
    colHeadings() As String
    colVal As Integer
    colName As Integer
    colWgt As Integer
    colDate As Integer
    LastCol As Integer
    LastRow As Integer
    SortOrder() As String
    NumericCols() As String
End Type

Function PropertiesOf(aType As WSheets, Optional index As Variant) As Variant
    Dim Result() As Variant
    Dim temp As Variant, i As Long
    ReDim Result(1 To 14)
    With aType
        Result(1) = .WName
        Result(2) = .hVal
        Result(3) = .hName
        Result(4) = .hWgt
        Result(5) = .hDate
        
        On Error GoTo InitializeColHeadings
        ReDim temp(LBound(.colHeadings) To UBound(.colHeadings))
        On Error GoTo 0
        For i = LBound(.colHeadings) To UBound(.colHeadings)
            temp(i) = .colHeadings(i)
        Next i
        Result(6) = temp
        '...

        On Error GoTo InitializeNumericCols
        ReDim temp(LBound(.colHeadings) To UBound(.colHeadings))
        On Error GoTo 0
        For i = LBound(.NumericCols) To UBound(.NumericCols)
            temp(i) = .NumericCols(i)
        Next i
        Result(14) = temp
    
        If IsMissing(index) Then
            PropertiesOf = Result
        Else
            Select Case TypeName(index)
                Case "Integer", "Long", "Double", "Single", "Byte"
                    PropertiesOf = Result(index)
                Case "String"
                    Select Case LCase(index)
                        Case "wname": PropertiesOf = Result(1)
                        Case "hval": PropertiesOf = Result(2)
                        Case "hname": PropertiesOf = Result(3)
                        ' ...
                    End Select
            End Select
        End If
    End With
    Exit Function
InitializeColHeadings:
    ReDim aType.colHeadings(-1 To -1)
    Resume
'...
InitializeNumericCols:
    ReDim aType.NumericCols(-1 To -1)
    Resume
End Function

 

[higgsch]

Much obliged, worked like a charm. I never thought of a UDF to return all properties in an array... Thanks again!