Iterate over Type

[jakopak]

Hello Dear Excel Forum

Is it possible to iterate over Type?
I have following method of looping doesn't work:

Public imtype As mtype
Public Type mtype()
Item1 As Long
Item2 As Long
Item3 AS Long
End Type


Sub howtoiter()
Dim el As Variant
With imtype
Item1 =1
Item2 =2
Item3 =3
End With

For each el in imtype
   do something with el
Next el

End Sub

Here is a method for iterating over Enum.
https://msdn.microsoft.com/en-us/library/58hssdz9.aspx

[xlnitwit]

Hi,

The Enum method is VB rather than VBA. There is no way to iterate over a type in VBA. Where would it be useful?

[jakopak]

Hi xlnitwit,
In public Type I have defined items. The items are conditional formulas expressed as string. I would like to do some conversion/operation on those items when condition is met. I can use array but then I would have to reference items like formulas(1) insted of formulas.item1. I would have to remember what formulas(1) is or look it up whenever I need it.

[xlnitwit]

I'm still not really clear as to how iterating over them would help, but I think you would probably require a simple class rather than a UDT, which are a little limited in scope.

[jakopak]

Well, whenever condition is met I would iterate over Type items and replace for example string "Row" with "Column" or something like that.
formulas.Item1=Replace(formulas.Item1,"ROW","COLUMN")

[Norie]

Is formulas(1) much different to formulas.item1?

[jakopak]

Is formulas(1) much different to formulas.item1?

Yes, because item1 can be named for example melons. With formulas(1) I have to remember formulas(1) means melons. I would have to resize array each time I add an item.
Maybe I can patch up something like that in Class with Array and Type, but it will be helluva of code.

I'd suggest you should probably be using a Collection or a Dictionary rather than a UDT.

Collection will do, thanks. Though I will have no intelisense. Still iterating over type would be handy.

[xlnitwit]

I'd suggest you should probably be using a Collection or a Dictionary rather than a UDT.

[Norie]

If you need to do any sort of 'resize' then using an array or collection is probably the right way to go because I don't think you can resize a UDT.

[jakopak]

Yes, collection is right tool to use here, though not convenient to use as Type.