pass undefined number of arguments to a VBA function [duplicate]

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

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.

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.