Summary: Are you ready to create truly flexible macros?
We’re now ready to dig into one of the most interesting topics in programming. Coding with variables can be a challenge at first, but learning how to use them is essential if you want to build programs that eliminate time-wasting manual processes!
Variables will help you write custom functions and get the most out of macros!
Download the Sample Workbook
In the previous tutorial, you became familiar with variable types, and you used those types to declare constants. This gives you a head start as we dive into the world of variables!
You probably remember from tutorial #8 that constants cannot change during program execution. They are assigned their values at the start of the macro, and they retain their values throughout the program.
Unlike constants, however, the values of variables can vary while a program runs, hence the name “variable.” As we build programs using VBA code, we will use this ability to our advantage.
Let’s look at the function I showed you in a previous tutorial. This function finds the last used row of a sheet:
Dim lrow As Long
lrow = Range("I1048576").End(xlUp).Row
This command stores the index number of the last row in a variable named “lrow”.
Even if the program with this command changes a sheet (e.g., copies and pastes, adds formulas, changes formats), the variable “lrow” will still keep the number of the last row while the program is running. At any point in the program, you can refer to it easily.
Alright, you say. But what if you add some new rows or activate another sheet? The variable will still hold the information about the last row’s number. However, since the index number of the last row has changed, the variable will no longer be correct!
In this case, the variable will need to be refreshed. To do so, you’ll need to run the above command again. After doing so, you’ll get the correct number of your last row. This is what makes variables critically important – they maintain their values until the program ends or until a command changes them. It is the programmer’s task to know when, in a program, the value needs to be changed. Changing constants mid-program is impossible – that’s why you need variables!
In the above example, I declared the module-level variable using the ‘Dim’ keyword. However, it is possible to declare variables that can be shared with other modules. You can accomplish this by using the keyword ‘Public’ in the declaration instead of ‘Dim’.
Note that, for a module-level variable, the keyword ‘Dim’ could be replaced with the keyword ‘Private’ to indicate that the scope of the variable is limited to the current module.
Let’s look at two different scenarios:
Constants can also use the ‘Public’ and ‘Private’ keywords, but the ‘Public’ or ‘Private’ keyword must be added before (not used instead of) the ‘Const’ keyword. Like this ‘Public Const’ or ‘Private Const’.
In a surprise twist, you don’t really need to declare your variables at the start of your code! Instead, you can start using a variable at any point in the program and the VBA will guess its type.
Just to be clear, though, I recommend you don’t do it this way! Allowing VBA to guess the variable type can cause bugs and lots of headaches.
To avoid these headaches, you should use Option Explicit!
BIG IDEA… ‘Option Explicit’ forces you to declare all variables that you use in your VBA code. It highlights undeclared variables as errors during compilation before the code runs. To use this option, simply type the following before the rest of your VBA code:
Option Explicit also prevents you from introducing bugs into your code by accidentally typing variable names incorrectly.
For example, you might be using a variable called “sVAT_Rate” and, when assigning a value to this variable, you might accidentally type “VATRate = 0.175”. Although you expect “sVAT_Rate” to have the value 0.175, it doesn’t. However, if you had been using Option Explicit, the VBA compiler would alert you that the variable “VATRate” wasn’t declared.
It’s exciting that before your program even runs, you can learn which variables you still need to declare. Without this knowledge, VBA might choose the wrong types for your variables, leading to incorrect results. By the time you notice those results – if you can identify them at all – it’ll be much more difficult to detect their cause.
Therefore, it’s best to detect undeclared variables at an early stage and add code to do so.
If you always want to include Option Explicit at the top of each new VBA module, you can do so automatically via the ‘Require Variable Declaration’ option of your VBA editor.
To activate this option:
Once you complete the steps above, the VBA Editor will automatically insert Option Explicit at the top of all new modules.
Bonus: You can hear from 8 Excel experts what they think of Option Explicit:
“Excel VBA Pro Tip… a simple secret that 7 out of 8 Excel experts use”
Now it’s time to start working with variables!
Let’s consider our payroll macro from tutorial #8. I’m going to show you how to change the constants we used in that macro to variables.
I updated the program to make it more professional and easier to use, all with less code. Much of the code needs to be typed only once – later it will take only seconds to adjust it to account for new salaries or tax rates.
As you can see, I used the Option Explicit method in my code to make sure that I declare all variables. This ensures that I use the correct variable names throughout the subroutine.
After using Option Explicit, I declared the “MaxSalary” and “StateTax” variables publicly to make them available to all Subs.
I then declared the variables representing the salaries of each employee with the keyword ‘Dim’ as opposed to ‘Const’. This transformed them from constants to variables.
Although I set variable “MaxSalary” equal to 1200, it can easily be adjusted in the last line of this code snippet or later in the program.
Users of the macro will input the tax rate (i.e., “StateTax”) via an InputBox, as it needs to be typed in every time a user runs the program. InputBoxes are handy when working with variables, particularly to capture dates or rates that change each time the program runs. Here’s the code to accept the “StateTax” value via an InputBox:
'Getting value for Tax
StateTax = InputBox("Please, type in the value of the state tax for salaries!", _
Finally, I changed the calculation of each employee’s salary to utilize variables. Salaries are now calculated using the “MaxSalary” variable.
As you can see, changing constants to variables dramatically improves the efficiency and functionality of the macro.
Here’s the VBA code for the simple payroll system using variables:
'Declaring variables for employees
Dim SalaryJohn As Long
Dim SalaryBill As Long
Dim SalaryMary As Long
Dim SalaryAmy As Long
Dim SalaryJoseph As Long
'Giving value to variables
MaxSalary = 1200
'Getting value for Tax
StateTax = InputBox("Please, type in the value of the state tax for salaries!", _
Sheets(2).Cells(2, 1).Value = "John"
Sheets(2).Cells(3, 1).Value = "Bill"
Sheets(2).Cells(4, 1).Value = "Mary"
Sheets(2).Cells(5, 1).Value = "Amy"
Sheets(2).Cells(6, 1).Value = "Joseph"
Sheets(2).Range("A1:D1").Value = Array("Name", "Salary", "Tax", "Net income")
SalaryJohn = MaxSalary * 1
SalaryBill = MaxSalary * 0.9
SalaryMary = MaxSalary * 0.9
SalaryAmy = MaxSalary * 0.8
SalaryJoseph = MaxSalary * 0.8
Sheets(2).Cells(2, 2).Value = SalaryJohn
Sheets(2).Cells(3, 2).Value = SalaryBill
Sheets(2).Cells(4, 2).Value = SalaryMary
Sheets(2).Cells(5, 2).Value = SalaryAmy
Sheets(2).Cells(6, 2).Value = SalaryJoseph
Sheets(2).Cells(2, 3).Value = SalaryJohn * StateTax
Sheets(2).Cells(3, 3).Value = SalaryBill * StateTax
Sheets(2).Cells(4, 3).Value = SalaryMary * StateTax
Sheets(2).Cells(5, 3).Value = SalaryAmy * StateTax
Sheets(2).Cells(6, 3).Value = SalaryJoseph * StateTax
'Net income calculated
Sheets(2).Cells(2, 4).Value = SalaryJohn - (SalaryJohn * StateTax)
Sheets(2).Cells(3, 4).Value = SalaryBill - (SalaryBill * StateTax)
Sheets(2).Cells(4, 4).Value = SalaryMary - (SalaryMary * StateTax)
Sheets(2).Cells(5, 4).Value = SalaryAmy - (SalaryAmy * StateTax)
Sheets(2).Cells(6, 4).Value = SalaryJoseph - (SalaryJoseph * StateTax)
In this tutorial you learned how to use variables to make your code readable, reusable, and flexible. Don’t forget to always declare your variables to avoid any bugs – Option Explicit is a huge help when doing so!
Now it’s the time for you to start writing your own macros with variables. Experiment with them… Pay attention to their types and limits, and most importantly have fun writing programs that make your job easier!
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.
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.