CODEWITHSUNDEEP
exceluser-defined-typesvba
Joseph Benco
Joseph Benco

Reputation: 41

VBA: Use a variable as a reference to a user defined type

How can I reference a user defined type using a local variable without creating a copy of the type instance?

As an example, in the code below what I would ideally like to do is in MySub3 where I create a local variable, MT, and reference a data structure nested inside another struct ... but VBA doesn't allow this. It allows it for objects but not for user defined types (arrggg!) ... and for no apparent reason ... it just doesn't allow it.

MySub1 shows how to reference the nested struct in a long clunky way. MySub2 shows how to do this by passing in the nested struct, but this clutters up the calling routine, and having multiple such nested structs gets ugly.

MySub2 demonstrates that VBA can do what I want, it just doesn't seem to provide a way to do it. I'm hoping there is a method I just haven't stumbled upon.

Note that my actual code is MUCH more complicated than this example, with multiple independent structs providing indices to many arrays as struct elements. Using these local reference variables would make the code much more readable and manageable.

Also Note that I am aware of the "with" statement, and it does help, but can only be used on one struct at a time.

Also Note that I am aware that I could use an actual object class. My code started out using an object but I quickly found out that VBA places limitations on arrays as property members ... a limitation that user defined types don't have.

Type tMyType
    VariableA As Single
End Type

Type tMyOtherType
    MyTypeArray() As tMyType
End Type

Type tOneMoreType
    MyOtherType As tMyOtherType
End Type

Dim GlobalIndex As Integer

Sub TopLevel()
    Dim TopLevelType As tOneMoreType

    ReDim TopLevelType.MyOtherType.MyTypeArray(0 To 10)
    Call MySub1(TopLevelType)
    Call MySub2(TopLevelType.MyOtherType.MyTypeArray(GlobalIndex))
    Call MySub3(TopLevelType)
End Sub

Sub MySub1(OMT As tOneMoreType)
    Dim VarA As Single

    VarA = OMT.MyOtherType.MyTypeArray(GlobalIndex).VariableA
End Sub

Sub MySub2(MT As tMyType)
    Dim VarA As Single

    VarA = MT.VariableA
End Sub

Sub MySub3(OMT As tOneMoreType)
    Dim VarA As Single
    Dim MT

    Set MT = OMT.MyOtherType.MyTypeArray(GlobalIndex)
    VarA = MT.VariableA
End Sub

Upvotes: 4

Views: 4092

Answers (2)

grahamj42
grahamj42

Reputation: 2762

I think here you have hit one of the limitations of VBA. I know of no way round the limitation on partial dereferencing of nested user types.

I think you would be best using classes containing private arrays with getter and setter functions (sadly, VBA doesn't have operator overloading either).

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

From my point of view you have made it vary complicated. But I believe you have the reason for that. The example you submitted generate the error you mentioned. But, when I changed some lines there is no error. I am not sure if my suggestion is the result you expected (while the question isn't fully clear to me) but try this instead of your MySub3:

Sub MySub3(OMT As tOneMoreType)
Dim VarA As Single
Dim MT

MT = OMT.MyOtherType.MyTypeArray(GlobalIndex).VariableA

VarA = MT

End Sub

Generally, this way I'm able to read any element im MySub3 passed from TopLevel. If it is not the answer please clarify more.

Upvotes: 1

Related Questions