Excel VBA Tutorial – #8 Introduction to Variables and Constants

[IMAGE] 1-Real-world-programming

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

«« Previous … | … Next »»

Download the Sample Workbook

[Image] Download Workbook Download the sample file with VBA code
Excel-VBA-Constants.xlsm (126 Kb)

#1 – Variables in general

» Back to contents

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.

[IMAGE] 2-Ikea-storage-shelves

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

» Back to contents

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.

[IMAGE] Constants can't change - Statue of David

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:

[IMAGE] Message Box 42 Sunday

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

» Back to contents

Filepath

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.

[IMAGE] 5-payroll-calculations

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

» Back to contents

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!

#5 – About the Author

» Back to contents

[IMAGE] Daniel Lajosbanyai

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 Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.