There are a number of ways to do this. Remember, though, that only
publicly scoped variables can be read from one workbook by code in
another workbook. You can't under any circumstances read a variable
that is declared WITHIN a Sub or Function procedure. Only variable
declared at the module level (before and outside of any Sub or
Function procedure) can be read/written across workbook boundaries.
If Book1 contains the variable MyB whose value you want to read from
code in Book2, you can put the following in Book1:
Public Function GetMyB() As Boolean
GetMyB = MyB
End Function
Then, you can call GetMyB from Book2 with code like
Sub AAA()
Dim B As Boolean
B = Application.Run("Book1.xls!GetMyB")
Debug.Print B
End Sub
Another way is to create a Property procedure in the ThisWorkbook
module of Book1:
Public Property Get BB() As Boolean
BB = MyB
End Property
Then, in Book2, use
Sub ZZZ()
Dim B As Boolean
B = CallByName(Workbooks("Book1.xls"), "BB", VbGet)
Debug.Print B
End Sub
Yet another way, if you are so inclined, is to set a reference in
Book2 that points to Book1. In VBA, open Book1's project, go to the
Tools menu, choose "VBA Project Properties" and change the name of the
project from "VBAProject" to something meaningful like "projBook1".
Then open Book2's project, go to the Tools menu in VBA, choose
References, and select "projBook1" or whatever you renamed Book1's
project. Then, in Book2's VBA code, you can simply read the value of
the Boolean variable (assuming it was declared as "Public MyB As
Boolean" rather than "Dim MyB As Boolean") as if it existed in Book2's
code:
Sub SSS()
Dim B As Boolean
B = MyB
Debug.Print B
End Sub
If it is possible that both Book1 and Book2 have a variable named MyB,
you need to prefix the MyB variable with the library name that
contains it:
Sub TTT()
Dim B As Boolean
B = projBook1.MyB
Debug.Print B
End Sub
There are many variations on these themes.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
