Unpacking Array into ParamArray in VBA

Question

Given an array of unknown size and a procedure that accepts a Parameter Array, how can one pass the array as the parameter array without modifying the procedure?

Sub Example(sequence() As String)
    UnModifiableSub sequence
End Sub

Sub UnModifiableSub(ParamArray bar())
    ' bar(0) = sequence not bar = sequence
End Sub

I'm looking behavior similar to Python's unpacking

def foo(*args):  # identical to ParamArray
    print(args)

x = [1,2,3,4]
foo(*x)  # what VBA can't do

I know there is no built-in solution like there is in Python but any cruel implementation is acceptable other than a long switch statement.

Does `UnModifiableSub Array(sequence)` do the trick? – David Zemens Sep 19 '14 at 01:37 — David Zemens, Sep 19 '14 at 01:37
No that nests it further. Then `bar(0)(0) = sequence` – cheezsteak Sep 19 '14 at 14:31 — cheezsteak, Sep 19 '14 at 14:31

score 2 · Accepted Answer · edited May 23 '17 at 12:29

The giant switch statement sounds kind of cruel to me, but that's what you're in for if the routine you must call is truly unmodifiable. In VBA, that's a Select Case statement. And don't forget that VBA arrays can have arbitrary indices, so you have to test both LBound and UBound unless you're sure where your sequence argument is coming from.

If you can write your own routine, there is a a way to mostly do what you want. You can assign an array to a variable of type Variant, like so:

Sub tryThis(v)
    Debug.Assert IsArray(v)
    Debug.Print v(LBound(v))
End Sub

Sub Example(sequence() As String)
    tryThis sequence
End Sub

Sub test()
    Dim s() As String

    ReDim s(1 To 2)
    s(1) = "a"
    s(2) = "b"

    Call Example(s)
End Sub

tryThis() takes the place of your UnModifiableSub. If you run test() in the Immediate window you get this output:

call test
a

I think that's the behavior you want. (Somewhat, anyway. Nobody wants arbitrary array indices.) Of course, this is limited compared to Python. Notably, if you want to call tryThis(), you must put your "parameters" into an array yourself. Some of the tradeoffs related to doing that are discussed in this answer and the parent question:

What is the benefit of using ParamArray (vs a Variant array)?

There are some other issues as well. For example, you can't just pass the contents of a ParamArray to tryThis():

'Doesn't work...
Sub gotcha(ParamArray pa())

    'Can't do it this way! "Invalid ParamArray use"
    Call tryThis(pa)
End Sub

You have to make the transfer to a variable explicitly:

Sub gotchaFixed(ParamArray pa())
    Dim v
    v = pa

    Call tryThis(v)
End Sub

Sub test2()
    Call gotchaFixed("a", "b")
End Sub

...

call test2
a

score 1 · Answer 2 · answered Sep 19 '14 at 15:14

I don't believe it is possible to pass an argument as the ParamArray, by its nature you are not assigning to the ParamArray itself, but mapping to elements of the ParamArray.

Each argument passed by the caller maps to an element in the ParamArray variable. Consequently, the ParamArray variable will have as many elements as the arguments passed by the calling statement.

http://www.tushar-mehta.com/publish_train/xl_vba_cases/1005%20ParamArray.shtml

Unpacking Array into ParamArray in VBA

2 Answers2