Application.Run and pass variables to Sub

[Barb Reinhardt]

Can this be done? How do I do it.

Let's say my sub is in TestWB.XLS, the Sub name is MySub and I need to pass
two variables, aWB, oWB

Application.Run("TestWB.xls!mySub(aWB,oWB)") ???

Can this be done?

Barb Reinhardt

 

[Peter T]

You can't pass objects, which is what I assume aWB and oWB are, but you can
pass numbers and strings as literals or variables. You'd need to recreate
your object based on the name or index passed as an argument.

Sub test()
Dim n As Long, result
n = 3
result = Application.Run("abc", n, 123, ActiveSheet.Name, "hello")
MsgBox result & " " & ActiveSheet.Name
End Sub

Function abc(num1 As Long, num2 As Long, s1 As String, s2) As Long
ActiveWorkbook.Worksheets(s1).Name = s2
abc = num1 * num2
End Function

Above is just a quick test within the same project. To call in another
project apart from qualifying with the workbook name it's worth including
the module

Application.run "mybook.abs!module1.abc"

Regards,
Peter T

 

[Dave Peterson]

This worked for me:

Option Explicit
Sub testme01()

Dim OtherWkbk As Workbook

Set OtherWkbk = Workbooks("book2.xls")

Application.Run "'" & OtherWkbk.Name & "'!testme", _
Workbooks("book3.xls"), ThisWorkbook

End Sub

And in the other workbook:

Option Explicit
Sub testme(aWB As Workbook, oWB As Workbook)
MsgBox aWB.FullName & vbLf & oWB.FullName
End Sub