using variable across workbooks

[Kappy]

How can I (if it's possible at all) set the value of a variable being used in one workbook, and have that variable retain its value in a sub in a different work book. eg..

sub code1()
x = 5
application.run("'Path\of\different\workbook\name.xlsm'!code2")
end sub

[then in the other work book]

sub code2()

x = x +1

end sub

...so that at the end of sub code2, x will equal 6

i've tried declaring x using public and global, but this doesn't do the trick..

any help is appreciated

 

[tweedle]

In this case, I believe a master control sheet that did nothing but run the code and possibly house processing logs might be in order; but it would not contain any of the business data.
In this manner, you can control file naming and pass variables around from workbook to workbook because the Master/Parent workbook is always open and housing the code/variables.

 

[Kappy]

tweedle,

thanks for responding to my post.. but I might need a little help understanding your advice

I'm planning to use a single module that calls about 20 different macros in 20 different workbooks..

ideally, I could set the values of a few variables in this "master" macro, and have those variables retain these values when used in the other macros I call from there.. but can't seem to get that to work.

You mention being able to "pass variables around from workbook to workbook"... which sounds like exactly what I need to do..

could you clarify how to accomplish that? Thanks!

 

[tweedle]

There are a few ways.

One way, write the Subs in the subordinate (20) workbooks to accept parameters.

Another way (if you don't wish to re-address existing Subs), would be to add a Sub to each subordinate wb specifically to handle the variable revaluation.

A third method would be to utilize wb.DocumentProperty collection to add and store Document Properties that store variables; this has an added benefit in that the values of the variables can be stored with the workbook and retrieved later.

[I'm sure there have been more than one discussion on the 'best' method.]

Below are examples of the first two methods.
Start with two workbooks in the same directory.
One named Master.xlsm, the other Slave.xlsm.

Apply the code in Modules;
Close Slave and step through MasterFoo from Master.

Slave Code:

'---------------------------------------------------------------------------------------
' Module    : Module1
' Author    : Tweedle 
' Citation  : [URL]http://www.mrexcel.com/forum/showthread.php?p=2800374#post2800374[/URL]
' Date      : Tue 2011-07-26 03:45
' Purpose   : Subordinate module of Subs called from another workbook (Master.xlsm)
'---------------------------------------------------------------------------------------
Public y As Integer
Public name$
Sub FOO()
    'A 'normal' module to call
    MsgBox "FOO "
End Sub
Sub FOOZ(x)
    'Show the argument passed from the Call in Master
    MsgBox "FOOZ " & x
End Sub
Sub SetVarVal(varName$, varVal)
    'a Mechanism to control variables here
    'if the effort to re-code parameters to existing Subs is too daunting
    Select Case varName$
    Case Is = "y"
        y = varVal
    Case Is = "name$"
        name$ = varVal
    End Select
End Sub
Sub showPubVar()
    'Show the variables altered
    'by passing to SetVarVal in earlier steps of Master
    MsgBox y & " " & name$, vbOKOnly, "Public Variable Values"
End Sub

Master Code:

Sub MasterFoo()
'---------------------------------------------------------------------------------------
' Procedure : MasterFoo
' Author    : Tweedle 
' Citation  : [URL]http://www.mrexcel.com/forum/showthread.php?p=2800374#post2800374[/URL]
' Date      : Tue 2011-07-26
' Purpose   : Demonstration of controlliong variables in another workbook's modules
'---------------------------------------------------------------------------------------
'
    Dim wb As Workbook
    'Set reference to the 'external' workbook Slave.xlsm
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\Slave.xlsm")
    'Usual call to Sub with no parameters
    Application.Run "Slave.xlsm!FOO"
 
    'Call to Sub with a parameter
    Application.Run "Slave.xlsm!FOOZ", 5
 
    'Calls to Sub in Slave to set the variables 'over there'
    Application.Run "Slave.xlsm!SetVarVal", "y", 10
    Application.Run "Slave.xlsm!SetVarVal", "name$", Application.UserName
 
    'Call to Sub in Slave to display the variables set in the prior steps
    Application.Run "Slave.xlsm!showPubVar"
    wb.Close False
End Sub

 

[RoryA]

It would be far simpler to just pass the variable directly to the other routine if you can alter it to take a variable.