Excel VBA - Why use Constants versus a Variables

What does using a CONST declaration do versus using a variable?
I just want to store a numeric value.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CONST is a variable that doesn't change.  You can't change its value later.  It's useful, for example, for applications such as calculating the circumference of a circle, when PI is constant and never changes.  If you declared PI as a regular variable, you may change it's value by mistake if writing a large amount of code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Const is nice because you can set it to a fixed value at the module level, so any routine there can use it without having to send it to subroutines. It is also easier to read within the code, and thus easier to debug when its not familiar to you.

My primary reasons for using constants is when writing for users who are not fluent in VBA, but may need to alter the code. For example, a server address, network path, or intranet site address; if I have a database connection and I suspect the internal address may change down the road, I'll make it a constant. If I'm not nearby when it changes (breaking the code), it is much simpler to walk them through the update if I don't have access; telling them to double click on a module and having the const line be at the very top makes it simple.
Another nice thing about constants is that if you have a value that you want to use multiple times within a program, but always use the same value then make it a constant, then you only need to change it once, rather than having to find everywhere in the program where this value is hardcoded.

If you try and change a value of a constant at runtime then you will get an error
Jacques Bourgeois (James Burger)PresidentCommented:
CONST is a variable that doesn't change

Very strange way of putting it. If it doesn't change, its not a variable.

You can't change its value later

Yes, you can. But you need to recompile the application. And that is one of the main purposes of constants.

The main difference is that in order to change a constant, you need to recompile the application. It cannot be done through code, while a variable is intended to be set and eventually changed through code.

To resume everything that has been said, with examples, a constant is a fixed value that you want to reused in many places in the code, presented in a way that makes it easier to understand. What do you think of a Form that has its BackColor set to 97. Nice color isn't it? Now, if I tell you that it is LightPink... Using DaysInWeek instead of 7 is better, because 7 could also mean LuckyNumber. When you see the word DaysInWeek, you understand what the command is about. When you see 7, it can be anything.

But it also has the advantage that if you need to change it for some reason, let's say that the work week goes from 7 days to 5 days, it's easier to change the value of the DaysInWeek constant than go through the code for all the occurrences of 7 and try to determine which ones needs to be changed to 5. Not only is it easier, but you are sure that you won't miss one or two by mistake, or change a 7 that was intended to be a lucky number.

It also prevents errors. It's easy to hit 6 instead of 7 while typing and not detect the problem. But type DayInWeek instead of DaysInWeek and the compiler will flag you because it does not recognize that word without the "s" after Day (that if is you are careful enough to work with Option Explicit On).

These are all things that you could do with variables, but with more chances of making mistakes. If you have a bug because variable MyVar takes the value 10, you have to look everywhere to try to find out where it takes that value. And this can be hard if it comes from running an expression, because you never see 10 as is in the code. Now, if constant MyVar takes the value of 10, you know precisely where to look for the problem, because the constant value can be defined in only one place.

They can also be used for a lot of little tricks when you create modules specifically for constants.

For instance, I know programmers who control multilingual application that way. They have one module that defines the value of the constants for one language, and another that defines the same constants, but in another language (Const Hello="Hello in English, Const Hello="Allo" in French). Simply by switching modules, they change the language of the application. Resources files usually play that role in .NET, but some programmers find they cumbersome and would rather work with modules of constants.

Another one. If you sell the same application to different customers, they might all need specific adjustments, such as paths to common files, specific messages that are labelled differently, one that prefers Quit buttons instead of Close buttons. Once again, having a module for each customer and using constants in the code where needed enables you to share the same code but simply switch modules of constants to make the difference between 2 versions of the same application.
And there's something not yet metioned:

Since the compiler can handle constants much easier than variables (no boundary checks at runtime, could be hardcoded into formula calculations instead of fetching it from somewhere, etc.), it has better chances for optimizing the code written around 'em. That way constants are more performant than variables - but usually the effect could only be measured by profiling software instead of being clearly observed by human beings.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.