Solved

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

Posted on 2009-04-02
4
417 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now