Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-02
4
Medium Priority
?
435 Views
Last Modified: 2013-11-27
Hi,

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?  

Thanks!
0
Comment
Question by:JC_Lives
  • 2
4 Comments
 
LVL 13

Assisted Solution

by:ioane
ioane earned 400 total points
ID: 24053794
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.
0
 

Author Comment

by:JC_Lives
ID: 24054217
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!?!?
Book1.xls
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 1600 total points
ID: 24054287
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....
0
 

Author Closing Comment

by:JC_Lives
ID: 31565958
Ok great, thanks guys!  
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question