pass undefined number of arguments to a VBA function

Question

I need to call an excel function from VBA, and at the and of the function.

I don't know how many variable arguments I have (var1 to...).

Is there a way to convert an array, and apply it as parameters... ? :

Application.Run("MyFunct", fixParam1, fixParam2, var1, var2 ... varx)

I though eventually there is something like kwargs in python...

Application.run("myFunct", fixParam1, fixParam2, kwargs(myArray))

Perhaps something with paramarray ?

mySub( fixParam1, fixParam2, ParamArray var() as Variant)
    Application.run([here I'm not sure...??])

Since you can't change `myFunct` (as you said in comments) please give us the defined argument list for `myFunct`. If you can't change it then you must match it. — D_Bester, Apr 01 '16 at 10:11
myFunct is defined with two fixed arguments, and after that 1 to N optional arguments... thats the difficulty I try to solve. So I will call it one time with fix1 fix2 var1 for example, and after that with fix1,fix2,var1,var2,var3 — OpenStove, Apr 01 '16 at 10:27

gizlmo · Answer 1 · 2016-04-01T10:01:50.490

2

Define a Function that takes an array as parameter like this:

Function testFunction(args())

Then you can pass an array to the Function:

testFunction myArray

edited Apr 01 '16 at 10:01

answered Apr 01 '16 at 09:53

gizlmo

1,882
1
14
14

score 0 · Answer 2 · edited May 08 '18 at 21:49

0

You can pass an array if you can change myFunct to handle it. http://www.cpearson.com/excel/passingandreturningarrays.htm

Application.run("myFunct", fixParam1, fixParam2, myArray)

So I'm inferring that myFunct accepts arguments like this: fixParam1, fixParam2, [var1], [var2], ... Brackets indicate optional arguments. So you might have a number of optional arguments.

OPTION 1 So create an array with all arguments and call it like this:

Application.Run("MyFunct", fixParam1, fixParam2, myArray(0), myArray(1), _
    myArray(2), myArray(3), myArray(4), myArray(5))

Hopefully the called function can handle blank input.

OPTION 2 If myFunct can't handle extra arguments you may have to do something like this:

Select Case argCount
    Case 1
        Call Application.Run("MyFunct", fixParam1, fixParam2, myArray(0))
    Case 2
        Call Application.Run("MyFunct", fixParam1, fixParam2, myArray(0), myArray(1))
    Case 3
        Call Application.Run("MyFunct", fixParam1, fixParam2, myArray(0), myArray(1), myArray(2))
    Case 4
        Call Application.Run("MyFunct", fixParam1, fixParam2, myArray(0), myArray(1), myArray(2), myArray(3))
End Select

Not pretty but works.

edited May 08 '18 at 21:49

Community

1
1

answered Apr 01 '16 at 09:51

D_Bester

5,723
5
35
77

In fact, I doesn't explained it well, "myFunct" is a given Function, I can't change... You mean that the called function, will understand an array automatically as new parameters ? It that case it wouldn't be possible to pass arrays as arguments... seems strange – OpenStove Apr 01 '16 at 10:06
An argument is simply data. So if you need to pass an unknown number of values, use an array. – D_Bester Apr 01 '16 at 10:07
Since myFunct is not changeable then you need to match it. You cannot pass more or less arguments than is specified. Some of the arguments may be optional so that allows some flexibility. – D_Bester Apr 01 '16 at 10:08
What you're trying to do isn't possible in VBA. You have to have your code count the arguments and pass exactly the right amount of arguments. – D_Bester Apr 01 '16 at 10:10
the givenFunction, is defined to have 2 Fixed arguments, and after that 1 to n... So I need a way to pass, maybe all arguments as an array, I would have for example : array=(1,2,3) Application.run("givenFunct",array) array=(1,2,3,4) Application.run("givenFunct",array) Would something like this be possible ? – OpenStove Apr 01 '16 at 10:14
So will `myFunct` complain if you pass a Null argument? – D_Bester Apr 01 '16 at 10:16
What data type are the optional arguments? – D_Bester Apr 01 '16 at 10:20

Łukasz Małarzewski · Answer 3 · 2022-05-12T09:15:42.490

So it is easy.Try this Function and Sub to test:

Function OneLine(ByVal f As Variant, ParamArray V() As Variant) As Variant
    Dim i As Long, ValToReturn As Variant
    For i = 0 To UBound(V, 1)
        V(i) = f
        If IsNumeric(V(i)) Then ValToReturn = ValToReturn + V(I)
    Next I
    OneLine = ValToReturn
End Function

Sub TestOnLineFunction()
    Dim X, a, b, c, d, e, f, g, h, i, j, k, TMP
    Dim Y(1 To 7, 8 To 12)

    x = 125
    Y(5, 10) = "Anything you want"
    Call OneLine(X, a, b, c, d, e)
    MsgBox d ' will show 125
    Call OneLine(Y, a, b, c, d, e, f, g, h, i, j, k)
    MsgBox h(5, 10) ' will show string:"Anything you want"
    'if You want to use Application.Run, do the same, but in this
    'case will not take V variables ByRef because Run method is always taking ByVal
    'So Sub do what is it doing but Function should return something in this case
    X = 888
    TMP = Application.Run("OneLine", X, a, b, c, d, e, f, g, h, i)
    MsgBox TMP
End Sub

Function OneLine will set all passed after F variables value to be equal to F variable. This F variable can be any value or even a multidimensional array. You can pass after F available as many variables as you want, but always as a Variant type. The V will always be passed ByRef. As You see OneLine function is can not return anything so you need Call it, and it will change all variables from this ParamArray V. If you want to use Application.Run method do the same, but in that way function should return something because this Run method is always taking variables ByVal. So as you see it does not make sense to use Run method in this case. If you run Subroutine the behavior will be the same. Sub will do what is it doing and it will take as many variables as you pass but you should remember that Application.Run has a limit of 30 variables.

How does that help to call a `ParamArray` function if the caller does not know the number of parameters at compile time? Here you have harcoded 5 and 11 parameters at the caller side. — GSerg, May 12 '22 at 07:43
ParamArray can take as many parameters as you want. I don't have 5 parameters hardcoded it is only an example of how to use OneLine Function which I wrote. If you pass there 5 parameters is OK but if you pass 55 or 255 parameters that also will be OK. — Łukasz Małarzewski, May 12 '22 at 07:49
You have 5 parameters hardcoded at `Call OneLine(X, a, b, c, d, e)`. The point of the original question is to call `OneLine` in such fashion that you don't know the number of parameters at compile time in `TestOnLineFunction`. Make your `Sub TestOnLineFunction()` to be `Sub TestOnLineFunction(ByVal NumberOfParameters As Long)`, then call it from outside like `Call TestOnLineFunction(5) or TestOnLineFunction(11)`, so that it, in turn, calls `OneLine` with the number of parameters passed that it received in `NumberOfParameters`. — GSerg, May 12 '22 at 07:50
As far as you can not pass any object (only values of variables) to Application.Run it is not possible. In this case Transforming code into a function calling Run method depending on number of parameter will be the best option. — Łukasz Małarzewski, May 12 '22 at 10:12
You can pass any object to `Application.Run`. There is no difference between "an object" and "a variable". All this has nothing to do with calling with unknown number of arguments - see the duplicate above for how to do that. — GSerg, May 12 '22 at 11:08

pass undefined number of arguments to a VBA function

3 Answers3