Assigning Values to Variables

Now that you know how to name and declare variables, it's time to start using them. Let's begin by learning how to create a variable. In Visual Basic you can create a variable anywhere within your procedure by assigning it a specific value.

1. Open a new workbook and save it as Chap03.xls. Activate the Visual Basic Editor window.

In the Project Explorer window, select the new project and change the name of the project in the Properties window to Chap03. Choose Insert | Module to add a new module to the Chap03 project. In the Properties window, change the name of Module1 to Variables. In the Code window, enter the CalcCost procedure shown below. This procedure calculates the cost of purchasing a calculator using the following assumptions: the price of a calculator is 35 dollars and the sales tax equals 8.5%.

Sub CalcCost() slsPrice = 35 slsTax = 0.085

Range("A1 Range("A4 Range("B4 Range("A5 Range("A6 Range("B5

).Formula = "The cost of calculator"

).Formula = slsPrice

).Formula = slsPrice * slsTax

Cost = slsPrice + (slsPrice * slsTax)

With Range("B6") .Formula = Cost .NumberFormat = "0.00" End With strMsg = "The calculator total is " & "\$" & Cost & "." Range("A8").Formula = strMsg

End Sub

The CalcCost procedure uses four variables: slsPrice, slsTax, Cost, and strMsg. Because none of these variables have been explicitly declared, they all have the same data type—Variant. The variables slsPrice and slsTax were created by assigning some values to variable names at the beginning of the procedure. The Cost variable was assigned a value that is a result of a calculation: slsPrice + (slsPrice * slsTax). The cost calculation uses the values supplied by the slsPrice and slsTax variables. The strMsg variable puts together a text message to the user. This message is then entered as a complete sentence in a worksheet cell.

When you assign values to variables, place an equal sign after the name of the variable. After the equal sign, you should enter the value of the variable. This can be a number, a formula, or text surrounded by quotation marks. While the values assigned to the variables slsPrice, slsTax, and Cost are easily understood, the value stored in the strMsg variable is a little more involved. Let's examine the contents of the strMsg variable.

strMsg = "The calculator total is " & "\$" & Cost & "."

■ The string "The calculator total is " is surrounded by quotation marks. Notice that there is an extra space before the ending quote.

■ The & character allows appending to the string another string or the contents of a variable.

■ The dollar sign in quotes ("\$") is used to denote the type of currency. Because the dollar symbol is a character, it is surrounded by the quotes.

■ The & character must be used every time you want to append a new piece of the information to the previous string.

■ The Cost variable is a placeholder. The actual cost of the calculator will be displayed here when the procedure runs.

■ The & character attaches yet another string.

■ The period is surrounded by quotes. When you require a period at the end of the sentence, you must attach it separately when it follows the name of the variable.

Now try out this procedure. Position the cursor anywhere within the CalcCost procedure and choose Run | Run Sub/UserForm.

0 0