I frequently need to use variables in a program €“ could be words or numbers €“
which are available for as long as the programme is running.
The programme may contain several modules some or all of which may be called
during execution.
To achieve this, I define a named range and store the variable value in it.
This is tedious. I believe I can define an enduring variable as something like
"Public MyVariable", but have had no success.
If Public is the right approach,
How is it written?
Where do I put that declaration?
Can it go in "This Workbook" or must it go in a module?
If I use "Public" do all my modules have to be "Sub Public" ?
Some enlightenment would be much appreciated.
--
donwb

Don,

It must go in a standard code module, and it should be declared before any
subs or functions in that module.

Do not put it in ThisWorkbook, else you will need to use
Thisworkbook.MyVariable.

It has no bearing on the declaration of the subs, so they can be public or
private depending on their usage.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"donbowyer" wrote in message
...
I frequently need to use variables in a program - could be words or
numbers -
which are available for as long as the programme is running.
The programme may contain several modules some or all of which may be
called
during execution.
To achieve this, I define a named range and store the variable value in
it.
This is tedious. I believe I can define an enduring variable as something
like
"Public MyVariable", but have had no success.
If Public is the right approach,
How is it written?
Where do I put that declaration?
Can it go in "This Workbook" or must it go in a module?
If I use "Public" do all my modules have to be "Sub Public" ?
Some enlightenment would be much appreciated.
--
donwb

Bob,

I usually create a module for all my public variables,
and include comments to describe them.

But one thing I am not sure of - do Public variables hold there
value even when the code ends?

And if so - what is the best way to reset them to 0, or Nothing.

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...
Don,

It must go in a standard code module, and it should be declared before any
subs or functions in that module.

Do not put it in ThisWorkbook, else you will need to use
Thisworkbook.MyVariable.

It has no bearing on the declaration of the subs, so they can be public or
private depending on their usage.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"donbowyer" wrote in message
...
I frequently need to use variables in a program - could be words or
numbers -
which are available for as long as the programme is running.
The programme may contain several modules some or all of which may be
called
during execution.
To achieve this, I define a named range and store the variable value in
it.
This is tedious. I believe I can define an enduring variable as something
like
"Public MyVariable", but have had no success.
If Public is the right approach,
How is it written?
Where do I put that declaration?
Can it go in "This Workbook" or must it go in a module?
If I use "Public" do all my modules have to be "Sub Public" ?
Some enlightenment would be much appreciated.
--
donwb

Hi Steve,

I do the same for bigger projects. I store all my Public variables and
Public constants in there.

As for holding their value, yes they will maintain throughout the Excel
session. To re-initialise them, you will need specific code to do so. Where
you run that code will be a design decision, but it may be well worth having
a button to do so. Personally, even with Public variables I can't think of
many situations where I use that to hold persistent data, it tends to be to
have a value that will be used in many macros, across modules, but will
always be initialised within one of them.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"STEVE BELL" wrote in message
news:MDuIe.43397$MW5.20588@trnddc08...
Bob,

I usually create a module for all my public variables,
and include comments to describe them.

But one thing I am not sure of - do Public variables hold there
value even when the code ends?

And if so - what is the best way to reset them to 0, or Nothing.

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...
Don,

It must go in a standard code module, and it should be declared before
any
subs or functions in that module.

Do not put it in ThisWorkbook, else you will need to use
Thisworkbook.MyVariable.

It has no bearing on the declaration of the subs, so they can be public
or
private depending on their usage.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"donbowyer" wrote in message
...
I frequently need to use variables in a program - could be words or
numbers -
which are available for as long as the programme is running.
The programme may contain several modules some or all of which may be
called
during execution.
To achieve this, I define a named range and store the variable value in
it.
This is tedious. I believe I can define an enduring variable as
something
like
"Public MyVariable", but have had no success.
If Public is the right approach,
How is it written?
Where do I put that declaration?
Can it go in "This Workbook" or must it go in a module?
If I use "Public" do all my modules have to be "Sub Public" ?
Some enlightenment would be much appreciated.
--
donwb

Bob,

Thanks for confirming what I suspected. And you are right - in most
situations the individual modules shouldn't be sensitive to any existing
value held by the variable. If you suspect that it will, then initialize
the value in the code....

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...
Hi Steve,

I do the same for bigger projects. I store all my Public variables and
Public constants in there.

As for holding their value, yes they will maintain throughout the Excel
session. To re-initialise them, you will need specific code to do so.
Where
you run that code will be a design decision, but it may be well worth
having
a button to do so. Personally, even with Public variables I can't think of
many situations where I use that to hold persistent data, it tends to be
to
have a value that will be used in many macros, across modules, but will
always be initialised within one of them.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"STEVE BELL" wrote in message
news:MDuIe.43397$MW5.20588@trnddc08...
Bob,

I usually create a module for all my public variables,
and include comments to describe them.

But one thing I am not sure of - do Public variables hold there
value even when the code ends?

And if so - what is the best way to reset them to 0, or Nothing.

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...
Don,

It must go in a standard code module, and it should be declared before
any
subs or functions in that module.

Do not put it in ThisWorkbook, else you will need to use
Thisworkbook.MyVariable.

It has no bearing on the declaration of the subs, so they can be public
or
private depending on their usage.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"donbowyer" wrote in message
...
I frequently need to use variables in a program - could be words or
numbers -
which are available for as long as the programme is running.
The programme may contain several modules some or all of which may be
called
during execution.
To achieve this, I define a named range and store the variable value
in
it.
This is tedious. I believe I can define an enduring variable as
something
like
"Public MyVariable", but have had no success.
If Public is the right approach,
How is it written?
Where do I put that declaration?
Can it go in "This Workbook" or must it go in a module?
If I use "Public" do all my modules have to be "Sub Public" ?
Some enlightenment would be much appreciated.
--
donwb

Bob
Many thanks. It worked fine
--
donwb


"Bob Phillips" wrote:

Don,

It must go in a standard code module, and it should be declared before any
subs or functions in that module.

Do not put it in ThisWorkbook, else you will need to use
Thisworkbook.MyVariable.

It has no bearing on the declaration of the subs, so they can be public or
private depending on their usage.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"donbowyer" wrote in message
...
I frequently need to use variables in a program - could be words or
numbers -
which are available for as long as the programme is running.
The programme may contain several modules some or all of which may be
called
during execution.
To achieve this, I define a named range and store the variable value in
it.
This is tedious. I believe I can define an enduring variable as something
like
"Public MyVariable", but have had no success.
If Public is the right approach,
How is it written?
Where do I put that declaration?
Can it go in "This Workbook" or must it go in a module?
If I use "Public" do all my modules have to be "Sub Public" ?
Some enlightenment would be much appreciated.
--
donwb