Variables sent/received using Application.Run

[FractalWalk]

I think I know the answer to this question but I hoping someone here can confirm or deny. I have an Excel add-in that stores macros that I am using to call from other open workbooks. I am using Application.Run to call them and they work in general with the exception being when I pass a variable and need the modified result returned. Hypothetical example:

Global n as integer

Sub Test()
    n = 1
    Application.Run "Test_Math", n
    MsgBox (n)
End Sub

Sub Test_Math(x)
    x = x * 2
End Sub

If I place both macros in the same workbook and run "Test", I get the result I expect: a message box displays the value 2. But when I place the Test_Math routine in the Add-in and reference it with Application run, the value displayed is 1. When I de-bug the add-in code, I have confirmed that it is receiving the variable and it is indeed converting it to a value of 2, it just doesn't pass it back. I know that if I convert the code to a function that it works, but due to the manner and number of times I have already utilized the routine, it would be quite cumbersome to do that.

So my question is, is there a way to pass a variable to a macro in an outside workbook and receive the altered variable back?

 

[strongm]

Change

Sub Test_Math(x)

to

Sub Test_Math(ByRef x as Integer)

 

[combo]

The easiest way is to convert subroutine to function. You can still call it like subroutine, but for such cases like this you will have an extra output:

[pre]Function Test_Math(x)
x = x * 2
Test_Math = x
End Sub[/pre]

You can use it as:
[pre] Test_Math 1[/pre]
and
[pre] z=Test_Math(1)[/pre]

There are alternatives however, you can give an unique name to the vba project, reference it and use as a vba library for instance.

combo

 

[strongm]

combo, the op states: "I know that if I convert the code to a function that it works, but due to the manner and number of times I have already utilized the routine, it would be quite cumbersome to do that.

 

[combo]

Strongm, this change does not require any changes in existing applications that utilize this procedure.
There is no simple solution, Application.Run converts arguments to values, so ByRef does not work too. One can find solutions with applying custom class (

http://www.tushar-mehta.com/publish...ument_with_the_Application_Run_method.shtml).

I was thinking about using pointers and api too. The hint in last line of my post is relatively simple, ByRef works after calling procedure without Application.Run.

combo

 

[strongm]

>Application.Run converts

Fair point

 

[strongm]

Although, on reflection, this is interesting: both macros in the same workbook and run "Test", I get the result I expect: a message box displays the value 2

 

[FractalWalk]

OK, thanks. I will look into the library solution.