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

higgsch

New Member
Joined
Jun 22, 2012
Messages
13
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:
Code:
For Each ********> in <container> 
  ...
Next

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

Any ideas?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
If your UDF returns an array or a collection, you can loop through it.

Code:
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
 
Upvote 0
My UDF contains several variables and several arrays. How would I have it return a collection?

Here's my UDF:
Code:
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
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
Much obliged, worked like a charm. I never thought of a UDF to return all properties in an array... Thanks again!
 
Upvote 0

Forum statistics

Threads
1,211,888
Messages
6,104,587
Members
447,917
Latest member
cbacba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top