Tip Variable Initialization

When Visual Basic creates a new variable, it initializes the variable. Variables assume their default value. Numerical variables are set to zero (0),

Boolean variables are initialized to False, string variables are set to the empty string (""), and Date variables are set to December 30, 1899.

Note: When you run this procedure, Visual Basic may display the following message: "Compile error: Variable not defined." If this happens, click OK to close the message box. Visual Basic will select the slsPrice variable and highlight the name of the Sub CalcCost procedure. The title bar displays "Microsoft Visual Basic - Chap03.xls [break]." The Visual Basic break mode allows you to correct the problem before you continue. Later in this book, you will learn how to fix problems in the break mode. For now, if you encounter the above-mentioned error, exit this mode by choosing Run | Reset. Next, go to the top of the Code window and delete the statement Option Explicit that appears on the first line. The Option Explicit statement means that all variables used within this module must be formally declared. You will learn about this statement in the next section. When the Option Explicit statement is removed from the Code window, rerun the procedure.

When the procedure has run, switch to Microsoft Excel. The result of the procedure should match Figure 3-1.

Figure 3-1:

The VBA procedure can enter data and calculate results in a worksheet.

Cell A8 displays the contents of the strMsg variable. Notice that the cost entered in cell B6 has two decimal places, while the cost in strMsg displays three decimals. To display the cost of a calculator with two decimal places in cell A8, you must apply the required format not to the cell but to the Cost variable itself.

VBA has special functions that allow you to change the format of data. To change the format of the Cost variable, you will now use the Format function. This function has the following syntax:

Format(expression, format)

Expression is a value or variable that you want to format, and format is the type of format you want to apply.

1. Change the calculation of the Cost variable in the CalcCost procedure: Cost = Format(slsPrice + (slsPrice * slsTax), "0.00")

Gj Chapil3.xls

, A i





1 i

IThe cost ofj








Sales Tax 1


1 '





The calculator total ¡s

137.S 75

n 1



► M \sheetl/sheet |

The VBA procedure can enter data and calculate results in a worksheet.

2. Replace the With.. .End With block of instructions with the following: Range("B6").Fonriula = Cost

3. Replace the statement Range("B5").Formula = slsPrice * slsTax with the following instruction:

Range("B5").Formula = Format((slsPrice * slsTax), "0.00")

4. Rerun the modified procedure.

After trying out the CalcCost procedure, you may wonder why you should bother declaring variables if Visual Basic can handle undeclared variables so well. The CalcCost procedure is very short, so you don't need to worry about how many bytes of memory will be consumed each time Visual Basic uses the Variant variable. In short procedures, however, it is not the memory that matters but the mistakes you are bound to make when typing variable names. What will happen if the second time you use the Cost variable you omit the "o" and refer to it as Cst?

Range("B6").Formula = Cst

What will you end up with if instead of slsTax, you use the word Tax in the formula?

Cost = Format(slsPrice + (slsPrice * Tax), "0.00")

The result of the CalcCost procedure after introducing the above mentioned mistakes is shown in Figure 3-2.




A i




l he cost ofl







Sales Tax 1

I "299





The calculator total is


n 1


H i

► »1 \Sheetl ¿Sheer |

Mistakes in the names of variables can produce wrong results.

Figure 3-2:

Mistakes in the names of variables can produce wrong results.

Notice that in Figure 3-2 cell B6 does not show a value because Visual Basic does not find the assignment statement for the Cst variable. Because Visual Basic does not know the sales tax, it displays the price of the calculator as the total cost (see cell A8). Visual Basic does not guess. It simply does what you tell it to do. This brings us to the next section, which explains how to make sure that errors of this kind don't occur. Before you continue, make sure to replace the names of the variables Cst and Tax with Cost and slsTax.

0 0

Post a comment