MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Public variables

  Asked By: Adelisa    Date: Sep 01    Category: MS Office    Views: 911

I have a program that i have created that has a number of separate
subroutines in it. I also have a couple of different modules in the
program as well. I have a number of variables set up as public in
the first module and, in general they seem to work ok. I have a
problem with some variables however, I call one "ResponseQPI" and it
is a byte variable that is used to determine if the user has access
to a particular drive and the responses are either VBYes or VBNo.

As long as I use it in one subroutine it works Ok. If I try to use
the results in a different subroutine within the same module the
results always seem to be VBYes even when the initial setting was
VBNo. It is possible that sometimes I go to one of the other modules
and then try to use the results previously set but that is not always
the case. It is certainly true that I run other subroutines without
using that value in between requests for the value of the variable.
Should I eliminate the other modules and put all the subroutines into
one module?

My question is whether setting a variable as Public will retain the
results once they are set. I have read that the results are saved,
and it seems to work most of the time, but this one and others don't
retain thier values throughout the program.

I could set a value in a cell to represent the value of the variable,
but I didn't think I had to do that.

Any comments on my problem? Do I have to declare the variable as
Public in each module to retain the value? I would think that if I
redeclare the variable in another module it would be reset. Is the
value retained only while a series of subroutines are running?

Let me know what you think.



4 Answers Found

Answer #1    Answered By: Ray Lawrence     Answered On: Sep 01

If you declare  the variable  in more than one module  you will get two
separate variables, and confusion will reign. :-)

Global variables  are only declared once. If they are declared in a normal
module, they will retain  their values  during execution of the VBA program.
However, they are not saved - they are simply global variables.

It is extremely important that you have Option Explicit at the top of every
module to ensure that you are not inadvertently creating more than one
variable with very similar names.

You should also use "dot" notation to refer to public  variables in other
modules, to ensure that both you and VBA are in agreement on what you're
talking about. E.g. if ResponseQPI is declared in module Fred, then you
should refer to it as Fred.ResponseQPI.

Within the module that has ResponseQPI declared in it, it is optional
whether you use dot notation or not. I do not.

You need to make sure that you really are declaring public variables too.
I.e. the declaration has the word Public in it, and the declaration is at
the top of the module - after the Option Explicit, but before any
subroutines or functions.

Last, but definitely not least ... look for a logic error. vbYes has a
value of 6 and vbNo has a value of 7. When created, your byte  variable will
have a value of zero. The only way it can become vbYes is if it is set  that

But look at how you're checking the variables. A check for <> vbNo is
incorrect as a check for "yes". This is not a Boolean variable.

Answer #2    Answered By: Chad Bradley     Answered On: Sep 01

Thanks for the information. I thought that I should only declare  it in one
place because I expected that if I declared it in more than one place it
would cause confusion and we both agree there.

I have the variables  in the top of the module  and declare them as "Public"
after the top line of "Option Explicit". I always tend to use "Option
Explicit" because it is so easy to misspell variable  names or create new
ones when it is not intended to do so. I have done that many times in the
past, before I found and used the Option Explicit command, and created  many
problems that I had a hard time  finding.

I did find one logic problem  in the program  and that was with the variable
ResponseQPI. I found that I either set  it as VbYes or never set it at all
in one area of the program. I skipped over the section where I set it as
VbNo and therefore had a problem if I tried to use it later. One of the
times I used the program and it failed at the point of using that particular
variable I noted that the value of the variable was neither yes or no but
just blank. That was when I started looking for the logic error in a
different manor and finally found a problem.

I have never heard of the dot notation, but I will keep that in mind as I
have to continually change this program for various reasons.

One additional point you brought up, you say that ".they will retain  their
values during execution of the VBA program. However, they are not saved -
they are simply Global Variables". Does this mean that you set a variable
value Globally and place a value in it. If you go back to the spreadsheet
and do a series  of other operations and then restart more procedures the
values you placed in the variables are lost unless you reset  them again? I
have found this to be the case  for some things and have to constantly rerun
a particular procedure to reset variables that I used before. I don't
always have to do this, but I have found that some of the variables lose
their values.

Answer #3    Answered By: Laurel Collins     Answered On: Sep 01

Option Explicit can be put  in automatically by Excel (for new modules).
It's in Tools/Options/Require variable  Declaration.

Never trust global variables  to retain  a value between separate "runs" of
the code while the spreadsheet is open. On occasions, Excel will "reset
your project". Sometimes it warns you and other times it doesn't.

If you want a value to have some permanence, you're better off putting the
value into a cell  - possibly on a hidden sheet.

I will also tend to put calls to an "Initialise" routine into every
subroutine on occasions. The call  checks an "Initialised" Boolean
variable - if that is false, it does the initialisation then sets it to

Answer #4    Answered By: Orville Rodriguez     Answered On: Sep 01

Thanks or the information. It looks like I am doing just what you said
except for a few variables  that burn me once in a while if I don't reset
them when I run  another routine after leaving and coming back to another
subroutine. I will have to do what you suggested more, that is, place
variable values  that I want to keep on a sheet somewhere and call  that to
either use or reset  the variable  as needed. I don't usually check the
variable values, I just set  them and forget them in an initialize routine
when I am sure I will need them.

I guess I will have to go over this program  once more and set an area for
variables that I want set to a particular value to keep from getting
unwanted and unexpected surprises.

Didn't find what you were looking for? Find more on Public variables Or get search suggestion and latest updates.