How do I assign arrays with multiple data types in VBA and then use the data in functions requiring certain datatypes?

Question

I am working on a program which performs calculations on several physical elements. There are about 5 types of elements, each requiring different inputs(with different datatypes) to calculations. There is no limit on the number of elements or order in which they can be calculated.

For example: the first set of data might have an element of type A, and element of type B and a second element of type A.

The second set of data might have an element of type C, an element of type B, and five elements of type A.

Element type A might have a type(string) input and a pressure(double) input. Element type B might have a type(string) input, a length(double) input, number of fins(integer) input. Element type C might have a length(double) input, diameter(double) input and quantity(integer) input

Since I don't know the number or type of elements ahead of time, I have been using a variant array which has a number of rows which can be assigned after elements are selected by the user, and a number of columns equal to the maximum number of data points needed by any element.

The problem is that when I then try to use that data in other functions (by calling Array(1,2)) I get a "byref argument type mismatch" error.

From what I can tell, this means that my program doesn't KNOW that the value contained in the slot of my array can/should be the correct datatype. it just sees "variant" as opposed to "double" or "string" and throws an error.

I have found a couple ways around this. 1)I can put the data in parenthesis when I call the function. Apparent this forces the data to conform to the expected datatype. function(input1, (Array(1,2)), input3)

2) I can go into all my functions and change the expected data-types to variant.

The problem is I am performing an engineering calculation, and I care about maintaining a certain level of accuracy in my numbers. I also DON'T really want my functions to be able to run if the inputs don't make sense. It seems to me that option 1 carries a high risk of loosing important data during the forced conversion. I'm not sure that option 2 is any better, since vba doesn't seem to assign/track these datatypes like I was imagining it did.

Is there any better way for me to assign and transfer data of a variety of types?

Answer 1

It sounds like you should probably create some custom Types or Classes (where each class has members with specific datatypes) for your different element types and then you can add those to a Collection/array and pass around for processing.

For example using classes:

Couple of very basic classes (just public variables, no methods):

'class module clsA
Public MyType As String
Public Pressure As Double

'class module clsB
Public MyType As String
Public Length As Double
Public FinCount As Integer

In a regular code module:

Sub tester()
    Dim colThings As New Collection, e

    colThings.Add MakeTypeAThing("TypeA", 67.92)
    colThings.Add MakeTypeBThing("TypeB", 19.56, 4)
    colThings.Add MakeTypeAThing("TypeA", 0.38)

    For Each e In colThings
        Debug.Print TypeName(e)
    Next e

End Sub


'Couple of "factory" functions to create instances of your classes
'  and set the member fields

Function MakeTypeAThing(typ As String, Pressure As Double) As clsA
    Dim rv As New clsA
    rv.MyType = typ
    rv.Pressure = Pressure
    Set MakeTypeAThing = rv
End Function

Function MakeTypeBThing(typ As String, l As Double, Fins As Integer) As clsB
    Dim rv As New clsB
    rv.MyType = typ
    rv.Length = l
    rv.FinCount = Fins
    Set MakeTypeBThing = rv
End Function