Hi All,
I need to check the value of a boolean variable in one workbook from
another.
eg. I have two workbooks open, how can i check the value of a variable in
workbook1 from a procedure in workbook2. when workbook 1 initiates a
procedure in workbook2. ( if that makes any sense)

regards
Lee

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)





On Thu, 24 Sep 2009 09:19:05 -0700, leerem
wrote:

Hi All,
I need to check the value of a boolean variable in one workbook from
another.
eg. I have two workbooks open, how can i check the value of a variable in
workbook1 from a procedure in workbook2. when workbook 1 initiates a
procedure in workbook2. ( if that makes any sense)

regards
Lee

many thanks chip,

welcome your assistance in this matter.

once again many thanks

"Chip Pearson" wrote:

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)





On Thu, 24 Sep 2009 09:19:05 -0700, leerem
wrote:

Hi All,
I need to check the value of a boolean variable in one workbook from
another.
eg. I have two workbooks open, how can i check the value of a variable in
workbook1 from a procedure in workbook2. when workbook 1 initiates a
procedure in workbook2. ( if that makes any sense)

regards
Lee