0

I want to pass values from an array to a function with a ParamArray in the same way as if I had passed each value from the array seperately.

Simplified example to further explain my question:

Sub DoSomething()
    Dim MyArray(3) As Variant
    MyArray(0) = "A"
    MyArray(1) = "Whatever"
    MyArray(2) = 2
    MyArray(3) = "xyz"

    'Now I want this...
    SomeNativeFunction MyArray

    '...to act the same way like this:
    SomeNativeFunction MyArray(0), MyArray(1), MyArray(2), MyArray(3)

    '... because at this point I don't know how many elements MyArray actually might contain during runtime, so my only chance by now is something like this:
    Select UBound(MyArray)
        Case 1
            SomeNativeFunction MyArray(0)
        Case 2
            SomeNativeFunction MyArray(0), MyArray(1)
        Case 3
            SomeNativeFunction MyArray(0), MyArray(1), MyArray(2)
        Case .....
        Case 99999
            SomeNativeFunction MyArray(0), MyArray(1), MyArray(2), MyArray(...)
    End Select
End Sub

Function SomeNativeFunction (ParamArray args() As Variant)
    'can't touch this...
End Function

The way I call SomeNativeFunction in the example above at first MyArray will be handed over to position 0 in args, so args(0) = MyArray. But Instead, I need args(0) = MyArray(0), args(1) = MyArray(1), etc.. like I did in the second function call. The problem is, MyArray could have a different amount of parameters so hardcoding this would actually not work in all cases or at least would become really messy.

The subs/functions I want to call this way are given by Excel or other sources so changing them is not possible.

8
  • Possible duplicate of Pass array to ParamArray Commented Feb 12, 2020 at 13:24
  • No it's actually not. The basic problem is fully different as this is about calling native excel functions that can not just be altered to just accept the paramarray in whatever way it is given to them. Commented Feb 12, 2020 at 13:27
  • What kind of native function are we talking about? Commented Feb 12, 2020 at 13:29
  • 2
    And how does the solution in the linked thread not apply? The only alternative I can think of would invole using Run and Join to append the relevant parameters. Commented Feb 12, 2020 at 13:31
  • 1
    Possible duplicate of Passing an array of Arguments to CallByName VBA Commented Feb 12, 2020 at 14:02

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.