Excel VBA - declaring a constant as a value that is subject to change


I am having a really funny issue with a variable changing by itself.  I think it happens when I modify code near the declaration of that variable: then it resets to zero because VBA thinks I just declared it or something.  I wanted to declare a constant as a value that could change later, but I want the constant to stay the same.  I want to use a counta function on a range to do that... but I can't seem to use counta in the constant declaration section.  Is there any way to freeze a variable's value, even if something attempts to change it later?  

Who is Participating?
Saurabh Singh TeotiaConnect With a Mentor Commented:
Okay this is why it happens, When you call the vbopen macro it stores the value for testerVariable in it, But when you delete the trythis variable command, all the values stored in gets reseted since vb resets all the values which are there so far if you delete any declaration or change or add any declartion, now if you run the workbook open macro again and then run the macro testervariablevalue,You will see the value again in there since you have to store the value back to see what the value you are looking for....
ioaneConnect With a Mentor Planning & Analytics ManagerCommented:
Hi JC_Lives,

There are some fairly fundamental rules regarding constants and variables.

By definition, you cannot change a constant, and hence you cannot set it equal to a function. In the same way, you can always change the value of a variable.

I suggest you insert an IF statement into your code to prevent the variable changing when you don't want it to.

Another option is to create a new variable and set it equal to the value of the variable you don't want changed.
JC_LivesAuthor Commented:
You know, what I really want to know is, why is it that when you open this attached document, delete the trythis variable in the workbook code, and then run Sub testerVariableValue(), you find the testerVariable value change to zero.  In what other cases do variables change by themselves!?!?
JC_LivesAuthor Commented:
Ok great, thanks guys!  
All Courses

From novice to tech pro — start learning today.