Pass and return Variable in different Workbook Module

March 17, 2022

I can’t return a changed Variable from a differenten Module in another Workbook.

Workbook A:

Dim A As String
Dim B As String
Dim C As String
Dim D As String

A = "A"
B = "B"
C = "C"

 Workbooks.Open FileName:= _
        "C:\Users\two.xlsm"

Application.Run "'two.xlsm'!test_her", A, B, C, D

Debug.Print A, B, C, D

End Sub

Workbook B:

Public B
Public D

Public Sub test_her(ByRef A As String, ByVal B As String, C As String, ByRef D As String)


A = A & "TEST"
B = B & "TEST"
C = C & "TEST"
D = "CHANGED"

End Sub

It works that the variables can be passed to "test_her" and processed. But I haven’t figured out how to pass them back modified?

If I work in the same workbook it works with the public variables, only in different workbooks I can’t pass them.

>Solution :

After doing a quick search, it looks like the solution is to assign the Application object to an object variable, and then call the Run method from that variable.

Sub test()

Dim A As String
Dim B As String
Dim C As String
Dim D As String

A = "A"
B = "B"
C = "C"

 Workbooks.Open Filename:= _
        "C:\Users\two.xlsm"
        
Dim xlApp As Object
Set xlApp = Application

xlApp.Run "'two.xlsm'!test_her", A, B, C, D

End Sub