From: Troubled User on 18 Apr 2010 14:50
I have 2 files that are open, File A and File B. I have two Public variables
VariableA and VariableB in Files A & B respectively. How do I assign/pass
the value between the two. I know I can do this by assigning the value to a
range and then from a range back to the variable, but what is the syntax for
assigning from one to another.
Thanks in advance.
From: Dave Peterson on 18 Apr 2010 15:05
You can create a "sending" function in each workbook's project.
In workbook A's project (in a general module):
Public VariableA As Variant
Function ShareVarA() As Variant
ShareVarA = VariableA
VariableA = "hi there" 'some sub that initializes that public variable.
And in Workbook B's project:
Dim myResult As Variant
Dim wkbkA As Workbook
Set wkbkA = Workbooks("abook1.xls")
myResult = Application.Run("'" & wkbkA.Name & "'!ShareVarA")
Another way is to add a reference to workbook B that points at workbook A.
Then you could use that variable just like it's in workbook B.
First, show the project explorer (in the VBE) by hitting ctrl-r
Next, select workbook's A project
(VBAProject is the default project name and we want to change it something
On the General tab, type in the new Project Name. (I used WkbkA, but you should
use something nice (based on the workbook name???).)
Then save this workbook.
Now select Workbook B's project
Tools|References|and select that project that you just renamed (workbook A's
Then your code can look as simple as:
(The function (in the first example) doesn't need to exist.)
Hit F4 to see the properties window
Troubled User wrote:
> I have 2 files that are open, File A and File B. I have two Public variables
> VariableA and VariableB in Files A & B respectively. How do I assign/pass
> the value between the two. I know I can do this by assigning the value to a
> range and then from a range back to the variable, but what is the syntax for
> assigning from one to another.
> Thanks in advance.