Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

A variable is a named storage location in your computer's memory. Variables can accommodate a wide variety of data types-from simple Boolean values (TRUE or FALSE) to large, double-precision values (see the following section). You assign a value to a variable by using the assignment operator, which is an equal sign.

The following are some examples of assignment statements that use various types of variables. The variable names are to the left of the equal sign. Each statement assigns the value to the right of the equal sign to the variable on the left.

x = 1 InterestRate = 0.075 LoanPayoffAmount = 243089 DataEntered = False x = x + 1 MyNum = YourNum * 1.25 HallOfFamer = "Trevor Hoffman" DateStarted = #3/14/2007#

VBA has many reserved words, which are words that you can't use for variable or procedure names. If you attempt to use one of these words, you get an error message. For example, although the reserved word Next (which is used in a For-Next loop) may make a very descriptive variable name, the following instruction generates a syntax error:

Next = 132

Unfortunately, sometimes syntax error messages aren't descriptive. The preceding instruction generates the error Compile error: Expected: variable in Excel 2007. (Earlier versions of Excel may produce a different error.) So if an assignment statement produces an error that does not seem to make sense, check the Help system to make sure that your variable name does not have a special use in VBA.

Defining Data Types

VBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data. Data type refers to how data is stored in memory-as integers, logical values, strings, and so on.

Although VBA can take care of data typing automatically, it does so at a cost-namely, slower execution and less efficient use of memory. If you want optimal speed for your functions, you need to be familiar with data types. Generally, it's best to use the data type that uses the smallest number of bytes yet still is able to handle all of the data that will be assigned to it. When VBA works with data, execution speed is a function of the number of bytes that VBA has at its disposal. In other words, the fewer bytes used by data, the faster VBA can access and manipulate the data. Table 24-1 lists VBA's assortment of built-in data types.

Declaring Variables

Before you use a variable in a procedure, you may want to declare it. Declaring a variable tells VBA its name and data type. Declaring variables provides two main benefits:

• Your procedures run faster and use memory more efficiently. The default data type-Variant-causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows the data type for a variable, it does not have to investigate; it can reserve just enough memory to store the data.

• If you use an Option Explicit statement at the top of your module, you avoid problems resulting from misspelled variable names. Suppose that you use an undeclared variable named CurrentRate. At some point in your procedure, however, you insert the statement CurentRate = .075. This misspelled variable name, which is very difficult to spot, will likely cause your function to return an incorrect result. See the nearby sidebar, "Forcing Yourself to Declare All Variables."

You declare a variable by using the Dim keyword. For example, the following statement declares a variable named Count to be an integer.

Dim Count As Integer

You also can declare several variables with a single Dim statement. For example

Dim x As Integer, y As Integer, z As Integer Dim First As Long, Last As Double

Dim i, j, k As Integer

In the preceding statement, only k is declared to be an integer. To declare all variables as integers, use this statement:

Dim i As Integer, j As Integer, k As Integer

If you don't declare the data type for a variable that you use, VBA uses the default data type-Variant. Data stored as a variant acts like a chameleon: It changes type depending on what you do with it. The following procedure demonstrates how a variable can assume different data types:

Function VARIANT_DEMO() MyVar = "123" MyVar = MyVar / 2 MyVar = "Answer: " & MyVar VARIANT_DEMO = MyVar End Function

To force yourself to declare all the variables that you use, include the following as the first instruction in your VBA module:

Option Explicit

This statement causes your procedure to stop whenever VBA encounters an undeclared variable name. VBA issues an error message (Compile error: Variable not defined), and you must declare the variable before you can proceed.

To ensure that the Option Explicit statement appears in every new VBA module, enable the Require Variable Declaration option on the Editor tab of the VB Editor Options dialog box. To display this dialog box, choose Tools Options.

In the VARIANT_DEMO Function procedure, MyVar starts out as a three-character text string that looks like a number. Then this string is divided by two, and MyVar becomes a numeric data type. Next, MyVar is appended to a string, converting MyVar back to a string. The function returns the final string: Answer: 61.5.

Using Constants

A variable's value may-and often does-change while a procedure is executing. That's why it's called a variable. Sometimes, you need to refer to a named value or string that never changes: in other words, a constant.

You declare a constant by using the Const statement. Here are some examples:

Const NumQuarters as Integer = 4 Const Rate = .0725, Period = 12 Const CompanyName as String = "Acme Snapholytes"

The second statement declares two constants with a single statement, but it does not declare a data type. Consequently, the two constants are variants. Because a constant never changes its value, you normally want to declare your constants as a specific data type. The scope of a constant depends on where it is declared within your module:

• To make a constant available within a single procedure only, declare it after the Sub or Function statement to make it a local constant.

• To make a constant available to all procedures in a module, declare it before the first procedure in the module.

• To make a constant available to all modules in the workbook, use the Public keyword and declare the constant before the first procedure in a module. The following statement creates a constant that is valid in all VBA modules in the workbook:

Public AppName As String = "Budget Tools"

Using constants throughout your code in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it's better to declare the value as a constant and use the constant's name rather than its value in your expressions. This technique makes your code more readable and makes it easier to change should the need arise-you have to change only one instruction rather than several.

VBA and Excel define many constants that you can use in your code without declaring them. For example, the following statement uses a constant named vbInformation:

MsgBox "Hello", vbInformation

The vbInformation constant has a value of 64, but it's not important that you know that. If you use the Excel macro recorder to record you actions, you'll find many other constants in the recorded code.

Using Strings

Like Excel, VBA can manipulate both numbers and text (strings). VBA supports two types of strings:

• Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535 characters.

• Variable-length strings theoretically can hold up to 2 billion characters.

Each character in a string takes 1 byte of storage. When you declare a string variable with a Dim statement, you can specify the maximum length if you know it (that is, a fixed-length string), or you can let VBA handle it dynamically (a variable-length string). In some cases, working with fixed-length strings may be slightly more efficient in terms of memory usage.

In the following example, the MyString variable is declared to be a string with a fixed length of 50 characters. YourString is also declared as a string but with an unspecified length.

Dim MyString As String * 50 Dim YourString As String

Using Dates

You can use a string variable to store a date, of course, but then you can't perform date calculations using the variable. Using the Date data type is a better way to work with dates.

A variable defined as a Date uses 8 bytes of storage and can hold dates ranging from January 1, 0100, to December 31, 9999. That's a span of nearly 10,000 years-more than enough for even the most aggressive financial forecast! The Date data type is also useful for storing time-related data. In VBA, you specify dates and times by enclosing them between two pound signs (#).

Here are some examples of declaring variables and constants as Date data types:

Dim Today As Date Dim StartTime As Date Const FirstDay As Date = #1/15/2007# Const Noon = #12:00:00#