return data from called sub in another workbook

[mwc0914]

I have a xlsm workbook from which I am using application.run and passing a parm to a sub in another .xlsm.
The sub in the second .xlsm is executing and setting a variable to a value. I am trying to return the variable value back to the first .xlsm and set a cell value to it.
I am having trouble getting the variable value returned back to the first .xlsm

What if I also wanted the second sub to set several variable values and have all of them returned to the original sub?

Thanks

 

[Micron]

A sub cannot return a value - you will have to change it to a function and set a variable to the return value of the called function, like

myVar = myOtherFunction(myParameter) myParameter will require quotes if the value is a string.
A function can only return one value, AFAIK. To use several values you could make several calls, but the function will require parameters that you might not use to be Optional. Like
myVar1 = myOtherFunction(param1)
myVar2 = myOtherFunction(,param2) <first comma means the first param is not passed but the second is. Comma is a placeholder for those not passed.

You could also build and array, or a csv string in the called function and parse the values in the calling function. There might be other ways you can learn by researching how to return multiple values from a function.

 

[dmt32]

Hi,
Application.Run passes arguments ByVal and normally would suggest that you look at creating a Function to return your variables value

HOWEVER - your post reminded me that a regular contributor on this forum found a workaround that enabled arguments to be passed ByRef

Have a look at this thread:Application.Run .. (Argument Passed ByRef)
#post 7 & see if the suggestion will do what you want

Dave

 

[mwc0914]

Thank you, so now I have the following code...module2 is in a different workbook.

Module 1
bleh = ThisWorkbook.Sheets(1).Range("b1").value
Workbooks.Open ("C:\Users\Desktop\test\Data.xlsm")
myans = Application.Run("'Data.xlsm'!testit2", bleh)

Module 2
Function testit2(bleh)
if bleh = "1" then
bleh = Range("e1"i).Value
end if
End Function

Range("e1").value in data.xlsm has a value of 4.
myans is coming back Empty.

 

[Micron]

I don't understand the need for all that complexity. The second post in the linked thread seems to have a simple answer. However, if you must use Application.Run, then it is enough to wrap the Run method in parentheses:

Sub Caller1()
    Dim X As Long

    X = 1
    X = Application.Run("Callee", (X))
    MsgBox X 'returns 2
 
End Sub

Function Callee(ByRef arg As Long) As Long
    Callee = arg * 2
End Function

While drafting this I noticed this was done in the subsequent example. To troubleshoot your latest code, walk through the code and check the variable values as you go.