Solved

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

Posted on 2009-04-02
4
419 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 100 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 400 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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