Passing a Variable between Workbooks

[TukaranXL]

Hi,

I have a number of workbooks - one main file and several templates. In the main file a variable is assigned 'poolID'. Each of the templates is then opened in turn and macros within these templates are run (with Application.Run), however each template uses the same variable. I currently have to put the variable in a cell on the template once it has opened and then reassign the variable in the template based on that cell, then clear the cell contents.

Is there a way to pass the value of the variable which has been assigned in the main file to the variable in the template file in a sort of ByRef or ByVal way?

Sorry if that's not particularly clear - basically I just want poolID in Workbook B to be populated with the value of poolID in Workbook A.

Cheers

 

[Sektor]

Can you write code, please?

 

[MrKowz]

Can you please post your existing code? If I understand the question correctly, you will need to temporarily store the variable into a public variable that is defined outside of the procedure level, or create your second sub to be something like:

Public Sub WorksheetB(poolIDa)
'code for WorksheetB
End Sub

Public Sub WorksheetA()
'code for WorksheetA
Call WorksheetB(poolIDb)
End Sub

 

[TukaranXL]

Hi,

I can't post the exact code due to it's nature (work related), but this is a similar example:

Workbook A (main file):

Global poolID as String
Sub mainRoutine()

poolID = Range("A1").Value
Workbooks.Open("C:\Workbook B.xls"), ReadOnly:=True
Application.Run "'Workbook B.xls'!templateRoutine"

End Sub

Workbook B (template):

Sub templateRoutine()

Sheets(poolID).Select

End Sub

Obviously in the above example I could just put in Workbook A
Windows("Workbook B").Activate
Sheets(poolID).Select
and have the same effect but it is more complicated than that and I want to pass the contents of poolID in Workbook A to a variable of the same name in Workbook B.

Cheers.

 

[MrKowz]

Define the variable poolID as a Public variable instead of Global:

Public poolID as String

 

[TukaranXL]

Define the variable poolID as a Public variable instead of Global:

Public poolID as String

Would that allow it to be read by code in another workbook? How would I refer to that in the second workbook - do I need to define it in the 2nd workbook as well?

i.e. If I was to do the following:

Workbook A (main file):

Public poolID as String
Sub mainRoutine()

poolID = Range("A1").Value ' Assume Range A1 contains the text "Hello World"
Workbooks.Open("C:\Workbook B.xls"), ReadOnly:=True
Application.Run "'Workbook B.xls'!templateRoutine"

End Sub

Workbook B (template):

Sub templateRoutine()

MsgBox poolID

End Sub

The message box shown by Workbook B is just blank as the variable poolID has no value in Workbook B even though it is defined as Public and assigned a value prior to Workbook B being opened and the macro called?

 

[Norie]

Why don't you pass the variable value as a parameter (ByRef or ByVal) to the sub(s) you are calling?

Option Explicit
 
Sub test()
 
Dim poolID As String
 
poolID = "Sheet3"
 
Workbooks.Open ("C:\TestFile.xls")
 
Application.Run "'TestFile.xls'!templateRoutine", poolID
 
End Sub

Sub templateRoutine(poolID As String)
 
Sheets(poolID).Select
 
End Sub

 

[TukaranXL]

Perfect - exactly what I was looking for! Thanks!

 

[notnotvba]

Why don't you pass the variable value as a parameter (ByRef or ByVal) to the sub(s) you are calling?

Option Explicit
 
Sub test()
 
Dim poolID As String
 
poolID = "Sheet3"
 
Workbooks.Open ("C:\TestFile.xls")
 
Application.Run "'TestFile.xls'!templateRoutine", poolID
 
End Sub

___________________________________________________________________________-

Sub templateRoutine(poolID As String)
 
Sheets(poolID).Select
 
End Sub

Would the two code sections (before and after the dividing line) be in their two own respective workbooks? So subtest would be in one workbook macro and templateRoutine would be in another workbook macro?

Thanks

 

[Norie]

The sub templateRoutine would be in the workbook named TestFile.xls, declared in a standard module.