Prev: fractions to decimal
Next: Need help with Workbook_BeforeClose event
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):
Option Explicit
Public VariableA As Variant
Function ShareVarA() As Variant
ShareVarA = VariableA
End Function
Sub SomeSubHere()
VariableA = "hi there" 'some sub that initializes that public variable.
End Sub

And in Workbook B's project:
Option Explicit
Sub GetTheValueFromA()
Dim myResult As Variant
Dim wkbkA As Workbook

Set wkbkA = Workbooks("abook1.xls")

myResult = Application.Run("'" & wkbkA.Name & "'!ShareVarA")

MsgBox myResult
End Sub

=========
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
Tools|VBAProject Properties
(VBAProject is the default project name and we want to change it something
unique)
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
project).

Then your code can look as simple as:
Option Explicit
Sub GetTheValueFromA()
MsgBox VariableA
End Sub

(The function (in the first example) doesn't need to exist.)
Hit F4 to see the properties window
Change the




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.

--

Dave Peterson
 | 
Pages: 1
Prev: fractions to decimal
Next: Need help with Workbook_BeforeClose event