Summary: Are you ready to take your Excel VBA knowledge to the next level? Now that we’ve worked through our first seven Excel VBA tutorials, we’re ready to dive into the exciting world of variables and constants!
Unlike recorded macros, variables and constants allow you to create flexible macros to tackle most real-world Excel challenges. This is where real programming starts… and where you start to profit from what you have learned!
In this tutorial we talk about variable types, their purpose, and how to use them. We also look at how to use constants to define repeat values once and reference them whenever needed.
After an overview of variables and constants, we review examples of VBA code with constants. We’ll use constants to specify file paths and implement a simple Excel payroll system.
In the next tutorial, we explore VBA code with variables.
Download the Sample Workbook
As mentioned in previous articles, variables are essential for programming – without them, programmers wouldn’t have the flexibility to build the complex algorithms necessary to build our most technologically advanced tools. Programmers use them when building software for our smartphones, TVs, cars, and in our “smart” devices.
Variables are one of the building-blocks of coding, as they allow us to store and access information from a computer’s memory.
Since computers can only use the information that programmers or end users provide them, it’s vital that they store that information so it can be used and re-used. This reserved and named memory location – which holds a value that can be changed during program execution – is the variable.
So you now know the components of a variable: a name, a certain amount of memory, and a value. But are there different types of variables?
A variable’s type identifies what kind of information a variable can hold. The type must be specified when declaring a variable, because it defines how much memory will be reserved for the information the variable stores.
⚡ BIG IDEA… Choosing the wrong variable type can cause problems, sometimes known as “bugs,” in execution – this is a quite common mistake in programming. Even worse, a program could run without any error messages and produce an incorrect result. To avoid this, you should become familiar with the variable types!
There are a handful of variable types in VBA. The best type to use depends on the task. There is no such thing as one perfect variable type for every project, just as there isn’t any perfect car type for every person. You have to pick the right type for the job!
Here is a list of the available variable types:
|Data Type||Size in Memory||Description||Range of Values|
|Byte||1 byte||Represents an unsigned (non-negative) number - often used for binary data||0 to 255|
|Boolean||2 bytes||A simple True or False value||True or False|
|Integer||2 bytes||Integer (no decimals)||-32,768 to +32,767|
|Long||4 bytes||Long Integer (no decimals)||
|Single||4 bytes||Single Precision Floating Point Number||-3.4e38 to +3.4e38|
|Double||8 bytes||Double Precision Floating Point Number||-1.8e308 to +1.8e308|
|Currency||8 bytes||A Floating Point Number with a fixed number of decimal places||
|Date||8 bytes||Date & Time - The Date type is represented internally by a floating point number. The integer part of the number represents the date, and the decimal portion represents the time.||
January 100 to|
31st December 9999
|Object||4 bytes||A reference to an object||
|String||varies||Holds a series of characters. The String type can be defined to have a fixed or a variable length, although it is most commonly defined to have a variable length||
- Up to 65,500|
Variable - Up to approx.
2 billion characters
|Variant||varies||Can hold Dates, Floating Point Numbers or Strings of Characters, and should therefore be used when you are not sure what type of data to expect.||
- same as|
String - same as
As I said before, there isn’t any “best” type for every job. However, if you aren’t sure which one you need, choose the “Variant” type. It may use up a bit more memory than necessary. However, it won’t slow the program significantly.
Today’s computers can handle some Variant type variables without performance issues. Do not forget, though, that efficient and professional code uses the appropriate variable types for the job.
If you would like to learn more about variable types, please visit this site:
After the above introduction to variables, you may be wondering why we even need constants.
Although constants work in much the same way as variables, constants can’t be changed during program execution. Like variables, each constant requires a named memory location for its value. However, if you try to change a constant’s value, the application will throw an error message.
If variables are the smartphones of today, constants are the “stupid” phones of yesteryear.
Yet, even if they are “stupid” (i.e., basic), constants are the perfect starting point for us to familiarize ourselves with storing values inside macros. In this tutorial, we will learn how to create and reference constants. You’ll learn in the next tutorial that, in many ways, the way we utilize constants mimics the way we utilize variables.
When declaring (in other words, defining) a constant in VBA, you will need to remember the following:
Once you decide on a name that suits these parameters, you will declare the constant with the following command:
Const [NAME] As [TYPE] = [VALUE]
The command “Const” means that you are going to declare a constant. The word “As” must be between the name and the type. Here are two examples of lines of code that declare constants:
Const MyInteger As Integer = 42
Const myDay As String = "Sunday"
The first example creates an integer (i.e., whole number) constant that is equal to the number 42. The second example creates a string (i.e., text-only) constant that is equal to the word “Sunday.” Note that when setting a constant equal to a number, the number must not be in quotation marks. When setting a constant equal to text, however, the text must be in quotation marks.
These constants will maintain their values throughout the entire macro. You can reference on them (i.e., use them in functions) like Msgbox in the attached Excel workbook to generate output like the message box below:
If you reference the same string or number repeatedly within the same macro, then declaring it once as a constant and referencing that constant may prove less painful that typing the string or number over and over. After initially declaring the constant, you’ll only need to reference only the constant by its name. You won’t need to again indicate its type.
Additionally, if you ever need to change the value of the constant, you can easily change its value in the line of code where you declared it.
As an example of a situation where using a constant makes your job as a coder easier, when you want to open a different workbook using code inside a workbook that you already have open, you can declare its path as a constant via the code as shown below:
Const MyFile As String = "C:\Excel\TestOpen.xlsx"
Set wb = Workbooks.Open(MyFile)
The above code will open workbook “TextOpen.xlsx” from within the “Excel” folder, and then close it while saving the changes (SaveChanges is set via the “1”). In this example, we’re assuming there aren’t any changes in the file. In most cases, however, you’ll want to add code to modify the file while it’s open. In this example, you should add your code between lines 3 and 4.
You can utilize this short code snippet as a template and adapt it to work with other files and paths – just modify the path and name parameters to fit your needs. Just remember that the constant must be written in between the parentheses after the command “Workbooks.Open”!
In our next example, we’ll see that constants can be used to improve the code behind a simple payroll spreadsheet.
I’ve simplified this example to show another way to utilize constants. It’s likely that real-world applications will be more complex.
Since salaries don’t change often – usually once a year – we can use constants to store the salaries of every employee. If we add the tax withholding rate as another constant, we can create a simple weekly payroll system.
As you’ll see in the macro code, we also perform some calculations to determine each employee’s net income.
If salaries rise next year, you can easily adjust the values of the constants at the start of the macro.
Here’s the VBA code for the simple payroll system:
Const SalaryJohn As Long = 1000
Const SalaryBill As Long = 900
Const SalaryMary As Long = 900
Const SalaryAmy As Long = 800
Const SalaryJoseph As Long = 800
Const StateTax As Double = 0.2
Sheet1_Payroll.Cells(2, 1).Value = "John"
Sheet1_Payroll.Cells(3, 1).Value = "Bill"
Sheet1_Payroll.Cells(4, 1).Value = "Mary"
Sheet1_Payroll.Cells(5, 1).Value = "Amy"
Sheet1_Payroll.Cells(6, 1).Value = "Joseph"
Sheet1_Payroll.Range("A1:D1").Value = Array("Name", "Salary", "Tax", "Net income")
Sheet1_Payroll.Cells(2, 2).Value = SalaryJohn
Sheet1_Payroll.Cells(3, 2).Value = SalaryBill
Sheet1_Payroll.Cells(4, 2).Value = SalaryMary
Sheet1_Payroll.Cells(5, 2).Value = SalaryAmy
Sheet1_Payroll.Cells(6, 2).Value = SalaryJoseph
Sheet1_Payroll.Cells(2, 3).Value = SalaryJohn * StateTax
Sheet1_Payroll.Cells(3, 3).Value = SalaryBill * StateTax
Sheet1_Payroll.Cells(4, 3).Value = SalaryMary * StateTax
Sheet1_Payroll.Cells(5, 3).Value = SalaryAmy * StateTax
Sheet1_Payroll.Cells(6, 3).Value = SalaryJoseph * StateTax
'Net income calculated
Sheet1_Payroll.Cells(2, 4).Value = SalaryJohn - (SalaryJohn * StateTax)
Sheet1_Payroll.Cells(3, 4).Value = SalaryBill - (SalaryBill * StateTax)
Sheet1_Payroll.Cells(4, 4).Value = SalaryMary - (SalaryMary * StateTax)
Sheet1_Payroll.Cells(5, 4).Value = SalaryAmy - (SalaryAmy * StateTax)
Sheet1_Payroll.Cells(6, 4).Value = SalaryJoseph - (SalaryJoseph * StateTax)
Notice how the constants are declared at the beginning of the macro. This makes it easier for you to identify and change their values going forward.
Idea: While in the VBA Editor, you can press the F8 key to step into the current VBA project. You can then keep pressing F8 to move through the rest of the lines one at a time. This allows you to observe what each line does, like watching every frame of a stop motion animation. If you run the macro in its entirety – by pressing F5 – it works too quickly to observe.
Remember, these are just examples to demonstrate how constants work. As we continue through the tutorials, we’ll walk through more advanced programming that will make your code even more robust and efficient.
In this tutorial, you have learned about variable types and how to use constants in macros. Now that you know how to use constants, you can increase the efficiency and effectiveness of your programs by referencing constants instead of repeating hard-coded values!
This is where programming starts to get interesting, and you can start profiting from what you have learned!
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.