with more than 30 arguments vba example
vba vs call vba access
vba macro in another workbook private sub vba
objexcel application run
excel run macro with arguments
application run return value

I am using Excel VBA. Does anyone know about a way to overcome the argument length limit of Application.Run()? (Or please suggest other function that can do the same job.)

Specific to my situation some of my constraints are:

  • I need to specify the called function as a string
  • The function is within a standard module
  • It is a function, the return value is needed, so Call() will not work.

In any case, I do not want the parameter list of the called function to be changed (e.g. to variant array or ParamArray) since I have written some other functionalities which depend on the function declaration.

EDIT: In response to some of the comments below I can provide a simplified version of my project here (could be off the original question though). In fact the whole design is established and running smooth except for the 30-arg constrain.

The very ultimate goal is to enable the following spreadsheet function which can be called like =mySpreadSheetFn("calledFn", "para1=abc", "para2=2002", ...). This will invoke the function calledFn() whose declaration may be:

Function calledFn(Optional para1 As String = "P1", _ Optional para2 As Integer = 202, _ Optional para3 As Boolean = True)

and the default argument will be replaced accordingly as specified in the ParamArray in the mySpreadSheetFn() call. Similarly there will be calledFn2() etc which an end user can use. So, there has to be an Application.Run() inside mySpreadSheetFn() .

And here are the function definitions:

Type paramInfo
    Val As Variant
    dataType As String 'can be Enum but let's forget it for this purpose
End Type

Function mySpreadSheetFunction(fnName As String, ParamArray otherParams())

Dim fnParams As Scripting.Dictionary
' getFnDefaultParams(fn): return the defaults and data types of fn's params
'   as a Dictionary. Each item is of type paramInfo (see above)
Set fnParams = getFnParams(fnName)

' For each specified arg check whether it exists for the function.
' If so, replaces the default value with the input value.
' If not exist, then just ignore it
' The problem is really not with this part so just know 
'    we have all the parameters resolved after the for-loop
For i = LBound(otherParams) To UBound(otherParams)
    Dim myKey As String
    myKey = Split(otherParams(i), "=")(0)
    If fnParams.Exists(myKey) Then
        ' parseParam() converts the input string into required data type
        fnParams(myKey).Val = parseParam(Split(otherParams(i), "=", 2)(1), _
                              fnParams(myKey).DataType _
    End If

' Here is the issue since the call cannot go beyond 30 args
Dim lb As Integer: lb = LBound(fnParams)
Select Case UBound(fnParams) - LBound(fnParams) + 1
Case 1: Application.Run fnName, fnParams(lb).Val
Case 2: Application.Run fnName, fnParams(lb).Val, fnParams(lb + 1).Val
' Omitted, goes until Case 30
' What to do with Case 31??
End Select

' Some other operations for each call

End Function

' An example of function that can be called by the above mySpreadSheetFn()
Function calledFn(Optional para1 As String = "P1", _ 
    Optional para2 As Integer = 202, _ 
    Optional para3 As Boolean = True)

' needs to return value
calledFn = para1 & para2 * 1000

 End Function

There is hardly any room to change the front-end since this is how the user interface is desired.

Any thoughts?

Probably a bit late, but if you transfer the method to a class, everything becomes much easier:

Class "c1"

Public Sub IHaveTooManyArguments(ParamArray params())
    Debug.Print "Refactor me!"
End Sub

Module "Main"

Public Sub CallIHaveTooManyArguments(fnName As String, ParamArray params())
    Dim o as new c1
    CallByName o, fnName, VbMethod, params
End Sub

excel vba - with more than 30 arguments, Does anyone know about a way to overcome the argument length limit of Application.Run() ? (Or please suggest other function that can do the  How can I use Application.Run with named arguments? Based on this MS documentation: You cannot use named arguments with this method. Arguments must be passed by position. The solution to force argument passing by position might seem a bit inflexible for my code purpose, which is to create a macro that runs other people's macro by passing some

Pack several arguments into an array. Or pack several arguments into some sort of data document, like an Xml file/string, or a JSON file/string, or an ordinary text file.

Application.Run, Application.Run. This can be used to run a macro written in Visual Basic or the Microsoft the parameters as separate arguments and not as one single string (​for example Application. Run method and specify the filename and folder path of the other file. You can pass up to 30 parameter values to the specified macro​. Trying to use Application.Run to to call a sub called SortSOEDM from a module called SOMacros in an Addin called CDVAddins2010.Xlam that was published to me by a colleague. I've tried dozens of iterations of the following line using the module name and sub name in different positions.

If you're determined to call a procedure with more that 30 parameters via Application.Run, you'll need a trampoline procedure in order to match its function signature. Make a second prodecure that takes the parameters in an array (or some other package), and then pass that to a second procedure that calls the one with too many parameters:

Sub Test()
    Dim args As Variant
    args = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, _
                 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, _
                 31, 32)
    Application.Run "ToManyArgsTrampoline", "fnName", args
End Sub

Sub ToManyArgsTrampoline(fnName As String, args() As Variant)
    If UBound(args) = 31 Then
        IHaveTooManyArguments fnName, args(0), args(1), args(2), args(3), args(4), args(5), _
                              args(6), args(7), args(8), args(9), args(10), args(11), _
                              args(12), args(13), args(14), args(15), args(16), args(17), _
                              args(18), args(19), args(20), args(21), args(22), args(23), _
                              args(24), args(25), args(26), args(27), args(28), args(29), _
                              args(30), args(31)
    End If
End Sub

Sub IHaveTooManyArguments(fnName As String, ParamArray otherparams())
    Debug.Print "Refactor me!"
End Sub

Application.Run method (Excel), More. Explore. Platform Opportunity; Go-to-Market Resources This can be used to run a macro written in Visual Basic or the Microsoft Excel If a string is used, the string will be evaluated in the context of the active sheet. Arg1–Arg30, Optional, Variant, An argument that should be passed to the function. Show the code of your console app, it cannot be more than a few lines. Richard MacCutchan 30-Mar-12 8:25am I have converted your code to C# and run a simple test and it works fine, so there must be something else in your code that is producing the invalid data.

Application.Run method (Access), More. Explore. Platform Opportunity; Go-to-Market Resources You can use the Run method to carry out a specified Microsoft Access or Arg30, Optional, Variant, The arguments that should be passed to the Function or Sub to an individual Access database from any application other than Access. A ByRef argument with the Application.Run method The Application.Run method is a versatile mechanism to call a subroutine particularly in another workbook or add-in. Its one documented limitation, if we want to call it that, is that all arguments are passed by value (ByVal).

Application.Run and ByRef Arguments, So, how does one pass an argument by reference (ByRef)? Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30) Of course, calling a procedure in another workbook requires some more setup. The application is not expected to manually register itself and check if it is the primary instance. Instead, the main() function of a GApplication should do very little more than instantiating the application instance, possibly connecting signal handlers, then calling g_application_run(). All checks for uniqueness are done internally.

Feature request: Please let BERT.Call take more arguments · Issue , Call", "sum", 1, 2, 3, 4, 5, 6, 7, 8) Res2 = Application. I think 32, which is evident from the VBA tooltip when you type . Arg15); For more than 15 arguments use some scheme for "packing in VBA and unpacking in R"; For up to about 30 arguments wait and hope that you enhance BERT. * Application.Run (according to the Help in MS-Access 2003 and MS-Excel 2003) is restricted to a limit of 30 arguments; although, if you have more than 30 arguments, you should be refactoring your code

  • What function requires more than 30 arguments?
  • can you give an example of what the arguments are? Perhaps you can store values in cells and pass a single range. Really going to need more information to give any meaningful suggestions.
  • This sounds more like a design problem.
  • If the parameters are all fairly simple types, you could pack them all into one pipe-delimited string (e.g. Parms = parm1 & "|" & parm2 & "|" & ... & "|" & parm217) and then unpack them in the called function (e.g. ParmList = Split(parms,"|"))
  • This is indeed the method I used. Notice however that the direct use of params in CallByName will not work and I got around it thanks to this question: link
  • This will require IHaveTooManyArguments to be hardcoded in the main, which is not very clean (consider there will be IHaveTooMany2 etc). Can you suggest a way which can avoid that?
  • There isn't a way to avoid it. You can't change Application.Run's function signature (1 required parameter followed by 30 optional parameters) - period.
  • @user90957 - I just saw your edit (see my edit that matches your function signature). This method still works (and AFAIK is the only way to pass a ParamArray through Application.Run.