Global Variable

[dhstein]

How can I define a global variable that is accessible to any subroutine in
any module of the workbook? Thanks for any help on this.

 

[Chip Pearson]

Above and outside of any procedure in the module, declare the variable
with a declaration like:

Public MyVar As Long

This variable will be accessible from any procedure in any module of
the VBA project (and to other VBA projects that reference this one)
and will retain its value after a procedure terminates.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[dhstein]

Thanks Chip.

David

Above and outside of any procedure in the module, declare the variable
with a declaration like:

Public MyVar As Long

This variable will be accessible from any procedure in any module of
the VBA project (and to other VBA projects that reference this one)
and will retain its value after a procedure terminates.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Rick Rothstein]

To make the variable global to the entire workbook, declare it as Public in
the variable as Public in the (General)(Declarations) section of any normal
(BAS) Module. If you don't have any modules in your project, click
Insert/Module on the VB editor's Menu Bar and use that one. Here is an
example of what a declaration would look like (in this case, for a String
variable, but any valid data type may be specified)...

Public MyGlobalVariable As String

 

[Bob Phillips]

You can also call it Global, works the same as Public, maybe more
informative.