Global Variable in Excel VBA
 

Hi!

Please help me on the following issue:

I want to declare a global variable in an Excel workbook.

I used
"Public zIND As Boolean"

in the code for "ThisWorkbook".

But when I try to call its value, that is assigned on the workbook open
event, I can't see it or change it from another sheet code - I want to
change its values when a change occurs in a worksheet, but it's not
possible because it seems that I can't see it from another worksheet.


Thanks in advance,
Chris


--
loopoo
------------------------------------------------------------------------
loopoo's Profile: http://www.excelforum.com/member.php...o&userid=28792
View this thread: http://www.excelforum.com/showthread...hreadid=493738

Global Variable in Excel VBA
 

Hi Chris,

As the "ThisWorkbook" is a pre-built Class module you can refer to a
variable declared as public out side the class like this

ThisWorkbook.zIND = True

msgbox ThisWorkbook.zIND

Or use a Property Let/Get pair.

However it has been recommended by others to put as little as possible in
the ThisWorkbook module, because if anything gets corrupted could mean
re-building from scratch.

So put your public variable in a normal module.

Regards,
Peter T

"loopoo" wrote in
message ...

Hi!

Please help me on the following issue:

I want to declare a global variable in an Excel workbook.

I used
"Public zIND As Boolean"

in the code for "ThisWorkbook".

But when I try to call its value, that is assigned on the workbook open
event, I can't see it or change it from another sheet code - I want to
change its values when a change occurs in a worksheet, but it's not
possible because it seems that I can't see it from another worksheet.


Thanks in advance,
Chris


--
loopoo
------------------------------------------------------------------------
loopoo's Profile:
http://www.excelforum.com/member.php...o&userid=28792
View this thread: http://www.excelforum.com/showthread...hreadid=493738

Global Variable in Excel VBA
 

Don't add it to ThisWorkbook, add it to a standard code module.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"loopoo" wrote in
message ...

Hi!

Please help me on the following issue:

I want to declare a global variable in an Excel workbook.

I used
"Public zIND As Boolean"

in the code for "ThisWorkbook".

But when I try to call its value, that is assigned on the workbook open
event, I can't see it or change it from another sheet code - I want to
change its values when a change occurs in a worksheet, but it's not
possible because it seems that I can't see it from another worksheet.


Thanks in advance,
Chris


--
loopoo
------------------------------------------------------------------------
loopoo's Profile:
http://www.excelforum.com/member.php...o&userid=28792
View this thread: http://www.excelforum.com/showthread...hreadid=493738

Global Variable in Excel VBA
 

Move the variable's declaration to a module
--
Gary's Student


"loopoo" wrote:


Hi!

Please help me on the following issue:

I want to declare a global variable in an Excel workbook.

I used
"Public zIND As Boolean"

in the code for "ThisWorkbook".

But when I try to call its value, that is assigned on the workbook open
event, I can't see it or change it from another sheet code - I want to
change its values when a change occurs in a worksheet, but it's not
possible because it seems that I can't see it from another worksheet.


Thanks in advance,
Chris


--
loopoo
------------------------------------------------------------------------
loopoo's Profile: http://www.excelforum.com/member.php...o&userid=28792
View this thread: http://www.excelforum.com/showthread...hreadid=493738

Global Variable in Excel VBA
 

Thank you all for your help.

It's working fine now.

Chris

--
loopo
-----------------------------------------------------------------------
loopoo's Profile: http://www.excelforum.com/member.php...fo&userid=2879
View this thread: http://www.excelforum.com/showthread.php?threadid=49373