Hey guys,

I always use cells in a sheet to store variables, usualy the active sheet.
However, I am finding that as I create more and more programs and attempt to
expand them for more usability, that I am running into problems where my
variables are getting in the way. In most cases, in order for me to expand
usability/features of my programs, I am having to move my variables so the
code I write does not overwrite them. This takes time because I have to
then go all through the entire code and specifiy the new cell location of
the variable. This increases the probability that I will make a mistake,
and then my code will not work.

There has to be some other way or alternative...
Is there anyway I can store variables in a module or something in the actual
VBA code instead of cells in a worksheet. I still will use some cells in a
worksheet to store variables(theres no way around that), but I would also
like to have them stored in a module or something in my actual VBA code.

For instance, I have a value in cell A1 that changes based on certain
conditions, and I use this value as a reference for other functions in my
VBA code. Is there a way to have this value(variable) stored somewhere in a
module where I can simply reference that module variable instead of always
having to reference the actual cell to find the value? This way if I need
to put something in cell A1, I can without having to move the variable in
cell A1 and then having to go all throughout my code and re-specifiy where
to look for that variable.

I hope this is clear.

Thanx in advance
Todd Huttenstine

I'd recommend adding a worksheet to your workbook and store the
variables there. You can set the worksheet's .Visible property to
xlVeryHidden so that users won't know about it.

Then when your workbook starts, load the variables from the
worksheet using the Workbook_Open() event and write them back before
closing with the Workbook_BeforeClose event (or more frequently).


In article ,
"Todd Huttenstine" wrote:

Hey guys,

I always use cells in a sheet to store variables, usualy the active sheet.
However, I am finding that as I create more and more programs and attempt to
expand them for more usability, that I am running into problems where my
variables are getting in the way. In most cases, in order for me to expand
usability/features of my programs, I am having to move my variables so the
code I write does not overwrite them. This takes time because I have to
then go all through the entire code and specifiy the new cell location of
the variable. This increases the probability that I will make a mistake,
and then my code will not work.

There has to be some other way or alternative...
Is there anyway I can store variables in a module or something in the actual
VBA code instead of cells in a worksheet. I still will use some cells in a
worksheet to store variables(theres no way around that), but I would also
like to have them stored in a module or something in my actual VBA code.

For instance, I have a value in cell A1 that changes based on certain
conditions, and I use this value as a reference for other functions in my
VBA code. Is there a way to have this value(variable) stored somewhere in a
module where I can simply reference that module variable instead of always
having to reference the actual cell to find the value? This way if I need
to put something in cell A1, I can without having to move the variable in
cell A1 and then having to go all throughout my code and re-specifiy where
to look for that variable.

I hope this is clear.

Todd,

I put all the variables I need to store in a worksheet I call "System".
I only use this sheet for variables and (nearly)constants
(like the list of authorised users) that I want to store between sessions.
I keep the sheet hidden, so that prying eyes can't play with it easily.
That way, it never gets overwritten by the data as all the data is on other
sheets.
When you need to change one of these, its much easier to overwrite a cell
than to have to trawl through the program finding out where they are.

HTH
Henry

"Todd Huttenstine" wrote in message
...
Hey guys,

I always use cells in a sheet to store variables, usualy the active sheet.
However, I am finding that as I create more and more programs and attempt
to
expand them for more usability, that I am running into problems where my
variables are getting in the way. In most cases, in order for me to
expand
usability/features of my programs, I am having to move my variables so the
code I write does not overwrite them. This takes time because I have to
then go all through the entire code and specifiy the new cell location of
the variable. This increases the probability that I will make a mistake,
and then my code will not work.

There has to be some other way or alternative...
Is there anyway I can store variables in a module or something in the
actual
VBA code instead of cells in a worksheet. I still will use some cells in
a
worksheet to store variables(theres no way around that), but I would also
like to have them stored in a module or something in my actual VBA code.

For instance, I have a value in cell A1 that changes based on certain
conditions, and I use this value as a reference for other functions in my
VBA code. Is there a way to have this value(variable) stored somewhere in
a
module where I can simply reference that module variable instead of always
having to reference the actual cell to find the value? This way if I need
to put something in cell A1, I can without having to move the variable in
cell A1 and then having to go all throughout my code and re-specifiy where
to look for that variable.

I hope this is clear.

Thanx in advance
Todd Huttenstine

So is there not a way to store them in a module?

"J.E. McGimpsey" wrote in message
...
I'd recommend adding a worksheet to your workbook and store the
variables there. You can set the worksheet's .Visible property to
xlVeryHidden so that users won't know about it.

Then when your workbook starts, load the variables from the
worksheet using the Workbook_Open() event and write them back before
closing with the Workbook_BeforeClose event (or more frequently).


In article ,
"Todd Huttenstine" wrote:

Hey guys,

I always use cells in a sheet to store variables, usualy the active
sheet.
However, I am finding that as I create more and more programs and
attempt to
expand them for more usability, that I am running into problems where my
variables are getting in the way. In most cases, in order for me to
expand
usability/features of my programs, I am having to move my variables so
the
code I write does not overwrite them. This takes time because I have to
then go all through the entire code and specifiy the new cell location
of
the variable. This increases the probability that I will make a
mistake,
and then my code will not work.

There has to be some other way or alternative...
Is there anyway I can store variables in a module or something in the
actual
VBA code instead of cells in a worksheet. I still will use some cells
in a
worksheet to store variables(theres no way around that), but I would
also
like to have them stored in a module or something in my actual VBA code.

For instance, I have a value in cell A1 that changes based on certain
conditions, and I use this value as a reference for other functions in
my
VBA code. Is there a way to have this value(variable) stored somewhere
in a
module where I can simply reference that module variable instead of
always
having to reference the actual cell to find the value? This way if I
need
to put something in cell A1, I can without having to move the variable
in
cell A1 and then having to go all throughout my code and re-specifiy
where
to look for that variable.

I hope this is clear.

Todd,

The first thing you do with a variable in a module is to Dim it.
When the module is reopened, it Dims the variable, setting it to zero or ""
So there is no way that you can store variables in a module.

HTH
Henry


"Todd Huttenstine" wrote in message
...
So is there not a way to store them in a module?

"J.E. McGimpsey" wrote in message
...
I'd recommend adding a worksheet to your workbook and store the
variables there. You can set the worksheet's .Visible property to
xlVeryHidden so that users won't know about it.

Then when your workbook starts, load the variables from the
worksheet using the Workbook_Open() event and write them back before
closing with the Workbook_BeforeClose event (or more frequently).


In article ,
"Todd Huttenstine" wrote:

Hey guys,

I always use cells in a sheet to store variables, usualy the active
sheet.
However, I am finding that as I create more and more programs and
attempt to
expand them for more usability, that I am running into problems where
my
variables are getting in the way. In most cases, in order for me to
expand
usability/features of my programs, I am having to move my variables so
the
code I write does not overwrite them. This takes time because I have
to
then go all through the entire code and specifiy the new cell location
of
the variable. This increases the probability that I will make a
mistake,
and then my code will not work.

There has to be some other way or alternative...
Is there anyway I can store variables in a module or something in the
actual
VBA code instead of cells in a worksheet. I still will use some cells
in a
worksheet to store variables(theres no way around that), but I would
also
like to have them stored in a module or something in my actual VBA
code.

For instance, I have a value in cell A1 that changes based on certain
conditions, and I use this value as a reference for other functions in
my
VBA code. Is there a way to have this value(variable) stored
somewhere
in a
module where I can simply reference that module variable instead of
always
having to reference the actual cell to find the value? This way if I
need
to put something in cell A1, I can without having to move the variable
in
cell A1 and then having to go all throughout my code and re-specifiy
where
to look for that variable.

I hope this is clear.

Alright thanx, I will start doing that.

Todd

"Henry" wrote in message
...
Todd,

I put all the variables I need to store in a worksheet I call "System".
I only use this sheet for variables and (nearly)constants
(like the list of authorised users) that I want to store between sessions.
I keep the sheet hidden, so that prying eyes can't play with it easily.
That way, it never gets overwritten by the data as all the data is on
other
sheets.
When you need to change one of these, its much easier to overwrite a cell
than to have to trawl through the program finding out where they are.

HTH
Henry

"Todd Huttenstine" wrote in message
...
Hey guys,

I always use cells in a sheet to store variables, usualy the active
sheet.
However, I am finding that as I create more and more programs and
attempt
to
expand them for more usability, that I am running into problems where my
variables are getting in the way. In most cases, in order for me to
expand
usability/features of my programs, I am having to move my variables so
the
code I write does not overwrite them. This takes time because I have to
then go all through the entire code and specifiy the new cell location
of
the variable. This increases the probability that I will make a
mistake,
and then my code will not work.

There has to be some other way or alternative...
Is there anyway I can store variables in a module or something in the
actual
VBA code instead of cells in a worksheet. I still will use some cells
in
a
worksheet to store variables(theres no way around that), but I would
also
like to have them stored in a module or something in my actual VBA code.

For instance, I have a value in cell A1 that changes based on certain
conditions, and I use this value as a reference for other functions in
my
VBA code. Is there a way to have this value(variable) stored somewhere
in
a
module where I can simply reference that module variable instead of
always
having to reference the actual cell to find the value? This way if I
need
to put something in cell A1, I can without having to move the variable
in
cell A1 and then having to go all throughout my code and re-specifiy
where
to look for that variable.

I hope this is clear.

Thanx in advance
Todd Huttenstine