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.

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)


On Sun, 26 Jul 2009 05:56:01 -0700, dhstein
wrote:

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.

Thanks Chip.

David


"Chip Pearson" wrote:

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)


On Sun, 26 Jul 2009 05:56:01 -0700, dhstein
wrote:

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.

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

--
Rick (MVP - Excel)


"dhstein" wrote in message
...
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.

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

--
__________________________________
HTH

Bob

"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"dhstein" wrote in message
...
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.