How to Declare Variables

You declare a variable with the Dim keyword. Dim stands for Dimension. The Dim keyword is followed by the name of the variable and then the variable type.

Suppose you want the procedure to display the age of an employee. Before you can calculate the age, you must feed to the procedure the employee's date of birth. To do this, you declare a variable called DateOfBirth, as follows:

Dim DateOfBirth As Date

Notice that the Dim keyword is followed by the name of the variable (DateOfBirth). If you don't like this name, you are free to replace it with another word, as long as the word you are planning to use is not one of the VBA keywords. Specify the data type the variable will hold by including the As keyword followed by one of the data types from Table 3-1. The Date data type tells Visual Basic that the variable DateOfBirth will store a date. To store the employee's age, declare the Age variable as follows:

Dim Age As Integer

The Age variable will store the number of years between today's date and the employee's date of birth. Since age is displayed as a whole number, the Age variable has been assigned the Integer data type.

You may also want your procedure to keep track of the employee's name, so you declare another variable to hold the employee's first and last name:

Dim FullName As String

Since the word "Name" is on the VBA list of reserved words, using it in your VBA procedure would guarantee an error. To hold the employee's full name, call the variable FullName, and declare it as the String data type because the data it will hold is text.

Declaring variables is regarded as good programming practice because it makes programs easier to read and helps prevent certain types of errors.

Now that you know how to declare your variables, let's take a look at a pro-

Tip 3-5: Implicitly Declared Variables information. You can create a variable

Variables that are not explicitly declared with Dim statements are said to be implicitly declared. These variables are automatically assigned a data type called Variant. They can hold numbers, strings, and other types of by simply assigning some value to a variable name anywhere in your VBA procedure. For example, you can implicitly declare a variable in the following way: DaysLeft = 100.

cedure that uses them:

Sub AgeCalc( )

'variable declaration Dim FullName As String Dim DateOfBirth As Date Dim Age As Integer

'assign values to variables FullName = "John Smith" DateOfBirth = #01/03/1967#

'calculate age

Age = Year(Now())-Year(DateOfBirth)

'print results to the Immediate window Debug.Print FullName & " is " & Age & " years old." End Sub

The variables are declared at the beginning of the procedure where they are going to be used. In the procedure above, the variables are declared on separate lines. If you want, you can declare several variables on the same line, separating each variable name with a comma, as shown below:

Dim FullName As String, DateOfBirth As Date, Age As Integer

Notice that the Dim keyword appears only once at the beginning of the variable declaration line.

When Visual Basic executes the variable declaration statements, it creates the variables with the specified names and reserves memory space to store their values. Then specific values are assigned to these variables. To assign a value to a variable, begin with a variable name followed by an equal sign. The value entered to the right of the equal sign is the data you want to store in the variable. The data you enter here must be of the type determined by the variable declaration. Text data should be surrounded by quotation marks and dates by the # characters.

Using the data supplied by the DateOfBirth variable, Visual Basic calculates the age of an employee and stores the result of the calculation in the variable called Age. Then the full name of the employee as well as the age are printed to the Immediate window using the instruction Debug.Print. When the Visual Basic procedure is finished, you must open the Immediate window to see the results.

Let's see what happens when you declare a variable with the incorrect data type. The purpose of the following procedure is to calculate the total number of cells in a worksheet and then display the results to the user in a dialog box.

Sub HowManyCells( )

Dim NumOfCells As Integer NumOfCells = Cells.Count

MsgBox "The worksheet has " & NumOfCells & " cells."

End Sub

A wrong data type can cause an error. In the procedure above, when Visual Basic attempts to write the result of the Cells.Count statement to the variable NumOfCells, the procedure fails and Excel displays the message "Run-time error 6—Overflow." This error results from selecting an invalid data type for that variable. The number of cells in a spreadsheet does not fit the Integer data range. To correct the problem, you should choose a data type that can accommodate a larger number. However, to quickly correct the problem you encountered in the above procedure, you can delete the variable type As Integer. When you rerun the procedure, Visual Basic will assign to your variable the Variant data type. Although Variants use up more memory than any other variable type and also slow down the speed at which your procedures run (because Visual Basic has to do the extra work to check the Variant's context), when it comes to short procedures, the cost of using Variants is barely noticeable.

0 0

Post a comment