Passing arrays through ParamArray with user-defined functions

Question

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.

ProfoundlyOblivious · Answer 1 · 2020-01-19T00:45:22.640

1

8204 is expected because ParamArray A() As Variant is a variant array.

vbVariant = 12
vbArray = 8192

8192 + 12 = 8204

edited Jan 19 '20 at 00:45

answered Jan 19 '20 at 00:04

ProfoundlyOblivious

1,455
1
6
12

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/206221/discussion-on-answer-by-profoundlyoblivious-passing-arrays-through-paramarray-wi). – Samuel Liew Jan 19 '20 at 03:53

user11982798 · Answer 2 · 2020-01-19T04:07:40.057

0

You have two issues, first A(0) is param 1 and the content is out(u,v), so debug.print A(0) will give error, second your function test without return value and for running code are as follow:

Function Test(ParamArray A() As Variant)
    msgbox "1" & ubound(A)
    Debug.Print A(0)(1, 1)
    Test = A(0)(1, 1)
    msgbox "2" & ubound(A)
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

edited Jan 19 '20 at 04:07

answered Jan 19 '20 at 00:08

user11982798

1,878
1
6
8

OK for param array you can use the first code i created, but you must remove the debug.print A(0) first, can you please to show the last of your code for function test? In my code I put ' to debug.print A(0) to be indicated as remark, this can be removed – user11982798 Jan 19 '20 at 04:00
and if you need return value, you must give test=A(0)(1,1) as return value to cell caller – user11982798 Jan 19 '20 at 04:04
OK try to change the function test as in my last change, what happen?, would you please inform me – user11982798 Jan 19 '20 at 04:13
what the content of the first message? – user11982798 Jan 19 '20 at 04:17
it means you get 1 param, it was right, and try to change the first msgbox to msgbox (A(0)(1,1)) what happen, any error message, what is it? – user11982798 Jan 19 '20 at 04:26
it's strange, usually if any error it will give error message. OK you try to make a sub, and run from the sub: Sub test2() Dim myArray As Variant myArray = bool() MsgBox myArray(1, 1) MsgBox (Test(bool())) End Sub – user11982798 Jan 19 '20 at 04:42
in mine, if any error, it will give #VALUE! and no message, it's normal. And in my cell formula: = test(bool()) – user11982798 Jan 19 '20 at 04:47

kas · Accepted Answer · 2020-01-24T17:05:23.620

-1

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

edited Jan 24 '20 at 17:05

answered Jan 19 '20 at 16:53

kas

7
3

Passing arrays through ParamArray with user-defined functions

3 Answers3