Excel VBA Tutorial – #9 Writing Macros with Variables
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 WorkbookDownload the sample file with VBA code
Excel VBA Variables.xlsm (133 Kb)
ContentsClick on a link to jump to that section…
#1 Variables in general
#2 Scope of variables
#3 Option Explicit
#4 Writing Macro with Variables
#6 About the Author
#1 – Variables in general
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!
#2 – Scope of 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:
- PRIVATE – I want to write a macro in my module where the variable “lrow” is meant to be accessible only in that subroutine (Sub). If other Subs have a variable with the name “lrow”, the error message “Ambiguous name detected” will display.
We can prevent this error by using the ‘Private’ keyword instead of ‘Dim’. Doing so will limit the variable’s use to within that module’s code.
- PUBLIC – I want to write a sub that gets the variable “lrow” from a previously written sub and works with the same sheet. The new sub needs to know which is the last row!
To do so, we will change the keyword from ‘Dim’ to ‘Public’, and we’ll remove the declaration row from the sub and place it at the very top of the module. Changing its location is critical, as VBA expects public variables to be declared before all macros.
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’.
#3 – Option Explicit
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:
- In the Visual Basic Editor, select Tools → Options…
- Select the ‘Editor’ tab
- Check the box next to the option ‘Require Variable Declaration’
- Click OK
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”
#4 – Writing Macro with Variables
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)
#5 – Summary
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!
#6 – About the Author
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!