excelvbaparametersparameter-passingparamarray

Passing arrays through ParamArray with user-defined functions

New to VBA. I'm having two issues with the following code for user defined functions. I can't figure out how to pass arrays through ParamArray.

1) The printed type from bool() is 8203 as it should be. But the printed type from test() is 8204, so they're null or invalid. Edit: it was pointed out that 8204 is due to the Variant type.

2) I'm not sure if I'm calling elements from nested arrays correctly with "A(0)(1,1)". I'm not able to print or call values from A() the the Test() function.

In a cell formula:

=Test(bool())

In VBA editor:

Function Test(ParamArray A() As Variant)
    Debug.Print VarType(A)
    Debug.Print A(0)(1,1)
    Test = A(0)(1, 1)
End Function

Function bool()
    Dim out() As Boolean
    Dim u As Integer, v As Integer

    ReDim out(1 To 3, 1 To 2)
    For v = 1 To 2
        For u = 1 To 3
            out(u, v) = True
        Next u
    Next v

    Debug.Print VarType(out)
    bool = out
End Function

ParamArray is necessary, I'm just not demonstrating why with this example.

Solution

  • The problem was fixed by removing the Debug.Print A(0). And by fixing another typo that was not present in the original post.