Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-02
4
Medium Priority
?
431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 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