Return array from Run() function

[twilsonco]

Hi All,

I have an ODE solver, and I'd like to be able to have the user enter the name of the function to use by name when calling the macro, so I want to use the Run(function_name,arg1,arg2,...,arg_n) function to call the function and pass arguments to it in a more flexible way.

This works fine, but it seems that the Run() function can only return a single (scalar) value. I've written the code, however, so that an arbitrary of variables can be solved for simultaneously, so function_name() returns an array. When I call function_name() using Run() such that an array is expected, Excel crashes.

The code is functional if I replace

Run(function_name,arg1,arg2())

with

function_name(arg1,arg2())

and returns correct values for all variables, but I hate that I have to rewrite the code every time I want to use a different function.

How can I get an array from Run()? Or how else can I make it so the function to be called is variable?

Thanks,

Tim
******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;">******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[Jerry Sullivan]

Hi Tim,

The Application.Run method can return arrays.
In your example, would function_name be the actual name of the function or a variable that holds the name of the function?

If it's the literal name, which is implied because you say a call to: function_name(arg1,arg2()) works, then the syntax should be

    Application.Run("function_name", arg1, arg2())

If function name is a variable holding the function name then this syntax should work...

    Dim function_name As String
    function_name = "MyFunction"  
    vReturn = Application.Run(function_name, arg1, arg2())

 

[twilsonco]

Thanks Jerry, but this yields the same result as plain old Run(), which is that excel crashes. I replace the like where the array result of my function is stored to an undimensioned array with an identical line using application.run using a string variable with the name of the function stored and the exact same arguments, but it causes an instant crash.

I haven't seen if this is the case with windows, but using excel 2011 for mac (which is all but equivalent now, except that modeless user forms don't work) it crashes instantly. Calling the function explicitly works perfect, however. Is Application.Run() different from just Run()

?******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;">******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[Jerry Sullivan]

To clarify, are you saying you're able to run this code and return an array....

    vReturn = Application.Run("MyFunction" , arg1, arg2())

...but not this code on a Mac using Excel 2011?

    Dim function_name As String
    function_name = "MyFunction"  
    vReturn = Application.Run(function_name, arg1, arg2())

That's interesting if that's the case. Both those snippets work on Windows xl2010.
It's a good idea for all users to note what version of Excel they are using and that's especially true for Mac xl2011 users.

My understanding is that there are more differences between the systems that just modal UserForms.

Ron deBruin keeps a log of differences between MAC and Windows that you might find helpful.
VBA code in Excel 2011 for the Mac

 

[twilsonco]

******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;">

No, this works:

result = function(arg1,arg2())

and these two don't:

result = Run("function",arg1,arg2())

result = Application.Run("function",arg1,arg2())

I've made some really extensive workbooks with and without extensive macros that work perfectly when run in excel 2010, often to my surprise and amazement.

I'll check out that link though.
******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>