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.

GuidoT
  • 280
  • 1
  • 12
  • Possible duplicate of [Pass array to ParamArray](https://stackoverflow.com/q/20783170/11683) – GSerg Feb 12 '20 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. – GuidoT Feb 12 '20 at 13:27
  • What kind of native function are we talking about? – GSerg Feb 12 '20 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. – Rory Feb 12 '20 at 13:31
  • @GSerg One very specific example is the CallByName-Function. Example: `CallbyName ComboBox1, "Column", vbLet, 1, 1, "Word"` All parameters after vbLet are part of a paramarray. The last parameter in this array will be the new value for the column-array of my ComboBox1 because column is a 2D-Array. But `CallbyName ComboBox1, "Column", vbLet, Array(1, 1, "Word")` will cause a totally different result. If I use a different array with only 1D in the CallByName function there will be 1 parameter less. `CallbyName AnotherObject, "Some1DArray", vbLet, 1, "Word"`. – GuidoT Feb 12 '20 at 14:01
  • 1
    Possible duplicate of [Passing an array of Arguments to CallByName VBA](https://stackoverflow.com/q/36313575/11683) – GSerg Feb 12 '20 at 14:02
  • Thanks, this greatly helped me with the CallByName-Function. But it's still no generic answer to my question. – GuidoT Feb 12 '20 at 16:04
  • There is, https://stackoverflow.com/questions/60189210/pass-array-as-list-to-paramarray#comment106459129_60189210. – GSerg Feb 12 '20 at 16:35

0 Answers0