Excel VBA Tutorial – #8 Introduction to Variables and Constants

Last updated on January 18, 2019 By

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.


Difficulty: Beginner

Download the Sample Workbook

Download the sample file with VBA code

Excel-VBA-Constants.xlsm (126 Kb)

#1 – Variables in general

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!

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 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) -2,147,483,648 to
+2,147,483,647
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 -922,337,203,685,477.5808 to
+922,337,203,685,477.5807
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. 1st January 100 to
31st December 9999
Object 4 bytes A reference to an object Any Object
Reference
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 Fixed - Up to 65,500
characters
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. Number - same as
Double Type
String - same as
String Type

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:
https://www.excelfunctions.net/vba-variables-and-constants.html

#2 – Using Constants

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.

Declaring constants

When declaring (in other words, defining) a constant in VBA, you will need to remember the following:

  • You must use a letter as the first character
  • You can’t use a space, period, exclamation mark, or special characters in the name
  • Name can’t exceed 255 characters in length
  • You cannot use Visual Basic reserved keywords as variable name

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.

#3 – Writing macro with constants

Declaring constants

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:

Sub OpenFileWithConstantPath()

Const MyFile As String = "C:\Excel\TestOpen.xlsx"
  
  Set wb = Workbooks.Open(MyFile)
  
  wb.Close 1

End Sub

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

Payroll system

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:

Sub PayrollSystemWithConstants()

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.Clear

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

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

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

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


End Sub

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.

I suggest you download the accompanying workbook and step into the code line by line.

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.

#4 – Summary

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!

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