return data from called sub in another workbook

mwc0914

New Member
Joined
Sep 24, 2015
Messages
42
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top