A Brief Look at Excel Macro Variables - dummies

A Brief Look at Excel Macro Variables

By Michael Alexander

Variables play a big part in most of the Excel macros you encounter. You can think of variables as memory containers that you can use in your procedures. There are different types of variables, each tasked with holding a specific type of data.

Following are some of the common types of variables you will see:

  • String: Holds textual data

  • Integer: Holds numeric data ranging from –32,768 to 32,767

  • Long: Holds numeric data ranging from –2,147,483,648 to 2,147,483,647

  • Double: Holds floating-point numeric data

  • Variant: Holds any kind of data

  • Boolean: Holds binary data that returns True or False

  • Object: Holds an object from the Excel Object model

When you create a variable in a macro, you are declaring a variable. You do so by entering Dim (abbreviation for dimension), then the name of your variable, and then the type. For instance:

Dim MyText as String
Dim MyNumber as Integer
Dim MyWorksheet as Worksheet

After you create your variable, you can fill it with data. Here are a few simple examples of how you could create a variable, and then assign values to it:

Dim MyText as String
MyText = Range("A1").Value
Dim MyNumber as Integer
MyNumber = Range("B1").Value * 25
Dim MyObject as Worksheet
Set MyWorksheet = Sheets("Sheet1")

The values you assign to your variables often come from data stored in your cells. However, the values may also be information that you create. It all depends on the task at hand. This notion will become clearer as you go through the macros in this book.

Although it’s possible to create code that does not use variables, you’ll encounter many examples of VBA code where variables are employed. There are two main reasons for this.

First, Excel doesn’t inherently know what your data is used for. It doesn’t see numerals, symbols, or letters. It sees only data. When you declare variables with specific data types, you help Excel know how it should handle certain pieces of data so that your macros will produce the results you’d expect.

Second, variables help by making your code more efficient and easier to understand. For example, suppose you have a number in cell A1 that you are repeatedly referring to in your macro. You could retrieve that number by pointing to cell A1 each time you need it:

Sub Macro1()
Range("B1").Value = Range("A1").Value * 5
Range("C1").Value = Range("A1").Value * 10
Range("D1").Value = Range("A1").Value * 15
End Sub

However, this macro would force Excel to waste cycles storing the same number in memory every time you point to cell A1. Also, if you need to change your workbook so that the target number is not in cell A1, but in, say, cell A2, you would need to edit your code by changing all the references from A1 to A2.

A better way is to store the number in cell A1 just once. For example, you can store the value in cell A1 in an Integer variable called myValue:

Sub WithVariable()
Dim myValue As Integer
myValue = Range("A1").Value
Range("C3").Value = myValue * 5
Range("D5").Value = myValue * 10
Range("E7").Value = myValue * 15
End Sub

This approach not only improves the efficiency of your code (ensuring Excel reads the number in cell A1 just once) but also ensures that you only have to edit one line should the design of your workbook change.