Excel VBA 24-Hour Trainer (2015)

Part II
Diving Deeper into VBA

Lesson 6
Variables, Data Types, and Constants

Many of the macros you develop will involve the need for referencing an item you are working on without specifying that item by its name, amount, or location. This concept may sound strange at first, but you will quickly discover with your macros that in many situations it makes sense, and indeed is necessary, to manipulate or analyze data in one part of your macro, and hold the results in virtual memory for later use.

What is a Variable?

VBA stores data in memory using a variable. A variable is a name given by you, to which you assign a piece of data that is stored in an area of the computer's memory, allowing you to refer to that data when you need to later in the macro. VBA handles the task of finding an appropriate place in the computer's memory to store your variable data, and dutifully retrieves the data when you ask for it by its variable name.

Variables hold values of different data types (more on this later) that are specified when the variable is declared. When you declare a variable, you do so by entering a declaration statement that includes four keywords in a particular order:

1. The Dim statement (VBA's abbreviation for Dimension), which all variable declarations start with.

2. The name of your variable, which you create, such as myValue.

3. The word As.

4. The type of data being stored.

One common data type is called Integer, which, as you see in Table 6.1, refers to whole numbers within a certain range. Using the preceding four steps as a sequential construction guide, here is a typical-looking variable declaration statement:

Dim myValue As Integer

NOTE A few rules in VBA for variable names:

· Cannot be greater than 255 characters in length.

· Cannot contain a space.

· Cannot contain mathematical operation characters +, -, /, *, =, <, >, or ^.

· Cannot contain punctuation characters, such as a comma, period, question mark, or exclamation.

· Cannot contain characters @, #, $, %, &, (, ), {, }, [, ], \, :, “, ‘, ’, ˜, or |.

· Cannot be terms reserved in VBA, for example Dim, Sub, or Function.

· Must be unique in the macro or procedure that uses it.

· May contain, but cannot start with, a number or an underscore character.

Basically, when it comes to naming your variables, keep it simple. Use only letters (and maybe numbers after the first character) for a name that is concise and gives a clue as to the general purpose of the variable.

Table 6.1 Data Types

You'll soon see the enormous benefit that this kind of innocent-looking statement can have in your macro. Although a few wrinkles exist in the variable declaration process, a variable declaration statement will often look no more complicated than this.

Assigning Values to Variables

After the variable declaration statement, which might be the next code line or 100 code lines later in your macro, depending on what you are doing, you will have a statement that assigns some value or attribute to the myValue variable. Here's an example of assigning the number in cell A1 to the myValue variable:

myValue = Range("A1").Value

The value you assign might be an actual value that is stored in a cell, as in the preceding example, or it might be a value you create or define in some way, again, depending on the task at hand. This notion will become clearer with more examples you'll be seeing throughout the book.

Why You Need Variables

I mentioned earlier that in some situations, employing a variable will be a sensible option. Suppose you have a number in cell A1 that you are referring to for several analytical purposes throughout your macro. You could retrieve that number by referring to its A1 cell address every time, but that would force Excel to look for the same cell address and to recommit the same number to memory every time.

As a simplified example, here is a macro with four commands, all invoking the value in cell A1:

Sub WithoutVariable()

Range("C3").Value = Range("A1").Value

Range("D5").Value = Range("A1").Value / 12

Range("E7").Value = Range("A1").Value * 365

MsgBox "The original value is " & Range("A1").Value

End Sub

For VBA to execute this macro, it must go through the same behind-the-scenes gyrations four separate times to satisfy each of the four commands that reference range A1. And if your workbook design changes, where you move the number of interest from cell A1 to cell K5, you need to go into the code, find each related code line, and change the cell reference from A1 to K5.

Fortunately, there is a better way to handle this kind of situation—by declaring a variable to refer to the value in cell A1 just once, like this:

Sub WithVariable()

Dim myValue As Integer

myValue = Range("A1").Value

Range("C3").Value = myValue

Range("D5").Value = myValue / 12

Range("E7").Value = myValue * 365

MsgBox "The original value is " & myValue

End Sub

By assigning the number value in cell A1 to the myValue variable, you've increased your code's efficiency and its readability, and VBA will keep the number value in memory without having to reevaluate cell A1. Also, if your cell of interest changes from A1 to some other cell, say cell K5, you only need to edit the cell address in the assignment code line to refer to cell K5, like so:

myValue = Range("K5").Value

As you've probably noticed in this situational example, a variable declaration is advisable, but it is not an absolute requirement for the WithoutVariable macro to function. However, as you will see in the upcoming lessons, variable declaration will be a necessary practice for handling more complex tasks that involve loops, object manipulation, and conditional decision-making. Don't worry—after you see a few examples of variables in action and start practicing with them on your own, you'll quickly get the hang of when and how to declare variables.

Data Types

Simply stated, VBA's role in life is to manipulate data in a way your computer can understand it. A computer sees information only as a series of binary numbers such as 0s and 1s—very differently than how humans see information as numerals, symbols, and letters of the alphabet.

Your macros will inevitably manipulate data of varying types, such as text, numbers, or Range objects. Part of VBA's job is to bridge the communication gap between humans and computers, by providing a method for telling the computer what type of data is being referred to in code. When you specify a data type in VBA, you help the computer to know how it should regard your data so that your macros will produce the results you'd expect, based on the types of data you are manipulating.

Understanding the Different Data Types

Data types are the different kinds of ways you can store data in memory. Table 6.1 shows a list of common data types with their descriptions and memory usage.

Declaring a Variable for Dates and Times

The Date data type is worth an extra look because it is the data type with which variables for both dates and times can be declared. You can assign values to a date variable by enclosing them in the # (number sign) character, with the value being recognizable to Excel as either a date or time. For example:

myDate = #09 October 1958#

or

myDate = #October 9, 1958#

or

myTime = #9:10 PM#

or

myTime = #10/9/1958 9:10:00 PM#

NOTE When entering dates, get into the good habit of entering the year as a full four-digit number. The year 2029 is the dividing line in VBA for two-digit years belonging to either the twentieth or twenty-first centuries. All two-digit years from 00 to and including 29 are regarded as belonging to the 2000s, and 30 to 99 are regarded as belonging to the 1900s. For example, the expression 10/10/29 in Excel is October 10, 2029, but 10/10/30 is regarded by Excel as October 10, 1930.

Declaring a Variable with the Proper Data Type

As you become more familiar with VBA, you'll notice that different developers have their preferred writing styles when declaring variables. For example, you can declare several variables on one line, each separated by a comma, like this:

Dim myValue1 as Integer, myValue2 as Integer, myValue3 as Integer

There is nothing wrong with that construction, but be careful not to make this common mistake:

Dim myValue1, myValue2, myValue3 as Integer

If you do not specify a data type after a variable name, such as in the latter case with myValue1 and myValue2, VBA assigns the default Variant data type. Only the Value3 variable has been specified the Integer data type. Variant is a catch-all data type that is the most memory-intensive, and the least helpful in understanding the purpose of its associated variables if anyone else should read your code.

The Variant data type does have its place, for instance when dealing with arrays or conversions of data types, but you should take care to specify the appropriate data types of all your variables. In so doing, your macros will run faster, they'll be easier to read, and they'll be more reliable.

Forcing Variable Declaration

Declaring your variables can only be a good thing. It takes a little extra thought and effort, but not declaring your variables can cause a lot more trouble when reading or debugging your code. Macros run faster and use less memory when all variables are properly declared.

You can tell if variable declaration is being enforced by seeing if the statement Option Explicit is at the top of your module. If you do see the Option Explicit statement, write a quick macro that tries to call an undeclared variable, such as you see depicted in Figure 6.1. When you attempt to run the macro, you receive a compile error as shown in Figure 6.1, informing you a variable is not defined. In this scenario, the error occurred because the myName variable was not declared with a statement such as Dim myName as String.

Figure 6.1

If you do not see the Option Explicit statement at the top of your modules, go into the VBE and from the menu bar, select ToolsOptions, as shown in Figure 6.2.

Figure 6.2

You see the Options dialog box. On the Editor tab, select the option Require Variable Declaration, as shown in Figure 6.3, and click OK.

Figure 6.3

Figure 6.4 shows the Option Explicit statement at the top of the module. The statement appears in every new module you insert thereafter.

Figure 6.4

Understanding a Variable's Scope

Variables and constants (explained in the next section) do not live forever in memory. They have a set lifetime and visibility within macros and modules. A variable's lifetime begins when it is declared, and ends when the macro that declared the variable completes its execution.

Local Macro Level Only

The visibility of a variable or constant also depends on how it is declared. If declared within a macro, a variable can only be used by that macro. For example, when Macro1 is run, the intSum variable would be calculated to a result of 41 (by adding 10 to the intAdd variable of 31), and that is what the message box would show:

Sub Macro1()

Dim intAdd As Integer, intSum As Integer

intAdd = 31

intSum = intAdd + 10

MsgBox intSum

End Sub

If you attempted to run another macro with same-looking but undeclared variables, you would receive a message box with the Compile error prompt, as shown in Figure 6.5 for Macro2. Just because a variable is declared in a macro elsewhere does not mean that VBA will recognize that same-looking variable in another macro.

Figure 6.5

Module Level

It is possible for a variable to be usable in the same module by more than one macro, by having the declaration statement at the top of the module instead of inside a particular macro. In Figure 6.6, both Macro1and Macro2 can utilize the intSum and intAdd variables.

Figure 6.6

Application Level

Finally, you can declare the variables as Public, which will make them visible to all macros in all modules. You only need to place the statements at the top of one standard module, like so:

Public intAdd As Integer

Public intSum As Integer

Constants

A variable's value may often change during a macro's execution, but some macros are better served with a reference to a particular value that will not change. A constant is a value in your macro that does not change while the macro is running. Essentially, constants are variables that do not change.

When you declare a constant, you do so by entering a declaration statement that starts with the Const statement, followed by the constant's name you specify, then the data type, and finally the value, all on one line. Here is an example:

Const myMonths as Integer = 12

It's a good practice to use constants for the same reasons you would use a variable. Instead of hard-coding the same value in your macro over and over, you define the constant just once and use the reference as you need to. For example, your macro may be analyzing the company's sales amounts, and needing to factor in the sales tax at various points in the macro. This constant statement at the start of the macro would allow you to reference the 8.25% sales tax:

Const SalesTax as Double = .0825

NOTE After you declare a constant in the macro, you cannot assign a different value to it later in the macro. If you need the value to change during the macro, what you really need is a variable instead of a constant.

Choosing the Scope and Lifetime of Your Constants

The scope and lifetime of constants are much the same as for variables:

· For the constant to be available only to a particular macro, declare the constant within that macro.

· For the constant to be available only to the macros that are housed in the same module, declare the constant at the top of that module, above and outside all macros.

· For the constant to be available to all macros in all modules, prefix the constant declaration with the Public statement, and set it at the top of a standard module, above and outside all macros. For example:

Public Const SalesTax as Double = .0825

Try It

In this lesson you practice creating a macro that includes a declared variable.

Lesson Requirements

To get the sample workbook file, you can download Lesson 6 from the book's website at www.wrox.com/go/excelvba24hour.

Step-by-Step

For this lesson you create a macro, without using the Macro Recorder, in which you declare a variable for the String data type, and you manipulate the string text with a few lines of practice code.

1. Create a macro that includes the following actions:

a. Declare a String type variable.

b. Assign text to the String variable.

c. Populate a range of cells with the String variable's text.

2. Open Excel and add a new workbook.

3. In your active worksheet, enter the text Hello in cell A1.

4. Press Alt+F11 to get into the Visual Basic Editor.

5. From the VBE menu, click InsertModule.

6. In the new module, type in the name of your macro as Sub Test6.

7. Press the Enter key, which will cause Excel to place a set of parentheses after the Test6 macro name, and also will create the End Sub statement. Your macro so far will look like this:

8. Sub Test6()

End Sub

8. In the empty line between Sub Test6() and End Sub, type Dim myString As String and press Enter.

9. Now is the time to define the myString variable by telling VBA that it will be equal to the value in cell A1, which is the word Hello you entered in Step 3. To do that, type the following line of code into your macro and press Enter:

myString = Range("A1").Value

10.With your String variable defined, try entering its defined text into a few cells, starting with cell B3. If you combine the variable with a space and the word “World,” you can programmatically enter the text “Hello World” into B3. To do that, type this line of code into your macro and press Enter:

Range("B3").Value = myString & " World!"

11.Just for fun, repeat the variable's text three times in succession, which would be HelloHelloHello, and tell VBA to enter that into cell B4. For the next line in your macro, type Range(“B4”).Value = myString & myString & myString and press Enter.

12.As a third and final entry, show the text Hello and Goodbye in cell B5 by typing this last line of code into your macro:

Range("B5").Value = myString & " and Goodbye".

At this point, your macro is completed, and it will look like this:

Sub Test6()

Dim myString As String

myString = Range("A1").Value

Range("B3").Value = myString & " World!"

Range("B4").Value = myString & myString & myString

Range("B5").Value = myString & " and Goodbye"

End Sub

13.Press Alt+Q to return to your worksheet.

14.Watch your new macro in action. Press Alt+F8 to display the Macro dialog box.

15.Select the Test6 macro name in the large window, as shown in Figure 6.7, and click the Run button.

Figure 6.7

REFERENCE Please select the video for Lesson 6 at www.wrox.com/go/-excelvba24hour. You will also be able to download the code and resources for this lesson from the website.