Excel VBA Tutorial – #9 Writing Macros with Variables

Last updated on January 25, 2019 By Victor Chan

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!


Difficulty: Beginner

Download the Sample Workbook

Download the sample file with VBA code

Excel VBA Variables.xlsm (133 Kb)

#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:

  1. 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.
  2. 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

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!", _
  "StateTax")

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:

Sub PayrollSystemWithVariables()

'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!", _
  "StateTax")

'Writing names
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"

'Writing Headers
Sheets(2).Range("A1:D1").Value = Array("Name", "Salary", "Tax", "Net income")

'Calculating Salaries
SalaryJohn = MaxSalary * 1
SalaryBill = MaxSalary * 0.9
SalaryMary = MaxSalary * 0.9
SalaryAmy = MaxSalary * 0.8
SalaryJoseph = MaxSalary * 0.8

'Writing Salaries
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

'Tax calculated
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)

End Sub

#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!

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

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.

RECOMMENDED READING

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.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.