UDT Question

[cosmarchy]

Hello all,
I am trying to develop some code in Excel 2003 and need some advide regarding the best way to handle a UDT.
Firstly let me describe the UDT:

Type ExtendedAddress
Address1 as String
Address2 as String
PostCode as String
end Type
Type CustomerDetails
Forename as string
Surname as string
Address as ExtendedAddress
Telephone as String
...
...
...
End Type
Dim Cusomers as CustomerDetails

I'm sure you get the picture. In reality there is much more to the UDT than that hence my question.
What I would like to know is how do you iterate through a UDT in the same way as you would an array?
What I want is an equivalent to a For ... Each but for a UDT so that I do not have to write

Cusomers.Forename = "joe"
Cusomers.Surname = "bloggs"
Cusomers.Address.Address1 = "..."
etc

There are so many elements to the UDT that writing everyone out by hand, as above, would be highly impractable.
Hopefully this makes some sense?
Thanks for any help.

[mikerickson]

I use UDF's to get data in and out of UDTs

Type ExtendedAddress
Address1 As String
Address2 As String
PostCode As String
End Type
Type CustomerDetails
Forename As String
Surname As String
Address As ExtendedAddress
Telephone As String
End Type


Sub test()
Dim myCustomer As CustomerDetails
myCustomer = newCustomerDetails("John", "Smith", "123 First St.", "Wanton, Neb.", "333333", "(530) 555-1212")
MsgBox customerString(myCustomer)
End Sub

Function newCustomerDetails(newFore As String, newSur As String, _
newAddress1 As String, newAddress2 As String, newPostCode As String, _
newTel As String) As CustomerDetails
With newCustomerDetails
.Forename = newFore
.Surname = newSur
.Address = newExAddress(newAddress1, newAddress2, newPostCode)
.Telephone = newTel
End With
End Function

Function newExAddress(newAdd1 As String, newAdd2 As String, newPost As String) As ExtendedAddress
With newExAddress
.Address1 = newAdd1
.Address2 = newAdd2
.PostCode = newPost
End With
End Function

Function customerString(inCust As CustomerDetails) As String
With inCust
customerString = .Forename & " " & .Surname & vbCr
With .Address
customerString = customerString & .Address1 & vbCr & .Address2 & " " & .PostCode
End With
customerString = customerString & vbCr & .Telephone
End With
End Function
Alternalty, one could make the "properties" of the UDT arrays, so looping can be done

Type ExtendAddressV2
AddressLine() As String
End Type

Sub test2()
Dim anAddress As ExtendAddressV2
anAddress = newExtendAddressV2("a", "b", "c")
MsgBox AddressString2(anAddress)
End Sub

Function newExtendAddressV2(ParamArray newLines() As Variant) As ExtendAddressV2
Dim i As Long
With newExtendAddressV2
ReDim .AddressLine(LBound(newLines) To UBound(newLines))
For i = LBound(newLines) To UBound(newLines)
.AddressLine(i) = newLines(i)
Next i
End With
End Function

Function AddressString2(inAddress As ExtendAddressV2) As String
AddressString2 = Join(inAddress.AddressLine, vbCr)
End Function

I hope this helps.

[Oorang]

You can do an array for UDTs by declaring the type as an array:
Dim Customers() As CustomerDetails
Then looping through it using: For i = Lbound(Customers) To UBound(Customers)
Msgbox Customers(i).ForeName
Next
Or you can declare the type to contain an array:
Type ExtendedAddress
Address1() As String
Address2() As String
PostCode() As String
End Type

Then you would use it like so:
Dim udtEA As ExtendedAddress
Redim udtEA.Address1(5)
udtEA.Address1(2)="Foo"
Msgbox udtEA.Address1(2)

But to actually use the For/Each syntax you need to use a class.

[Bob Phillips]

UDTs are a flawed tool in VB/VBA. Use classes instead, much more robust.

[cosmarchy]

Thanks guys for all your replies...

I believe that I can make something that works from your suggestions.

Kind regards