Module Level Variables

Local variables help save computer memory. As soon as the procedure ends, the variable dies, and Visual Basic returns the memory space used by the variable to the computer. In programming, however, you often want the variable to be available to other VBA procedures after the procedure in which the variable was declared has finished running. This situation requires that you change the scope of a variable. Instead of a procedure-level variable, you want to declare a module-level variable. To declare a module-level variable, you must place the Dim keyword at the top of the module sheet before any procedures (just below the Option Explicit keyword).

For instance, to make the slsTax variable available to any other procedure in the Variables module, declare the slsTax variable in the following way:

Option Explicit

Dim slsTax As Single Sub CalcCost( )

<place procedure instructions here> End Sub

In the example above, the Dim keyword is located at the top of the module, just below the Option Explicit statement.

Before you can see how this works, you need another procedure that uses the slsTax variable.

1. In the Code window, cut the declaration line Dim slsTax As Single in the Variables module from the CalcCost procedure and paste it at the top of the module sheet below the Option Explicit statement.

2. Enter the code of the ExpenseRep procedure in the same module where the CalcCost procedure is located:

Sub ExpenseRep()

Dim slsPrice As Currency Dim Cost As Currency slsPrice = 55.99

Cost = slsPrice + (slsPrice * slsTax)

MsgBox slsTax MsgBox Cost End Sub

The ExpenseRep procedure declares two Currency type variables: slsPrice and Cost. The slsPrice variable is then assigned a value of 55.99. The slsPrice variable is independent of the slsPrice variable that is declared within the CalcCost procedure. The ExpenseRep procedure calculates the cost of a purchase. The cost includes the sales tax. Because the sales tax is the same as the one used in the CalcCost procedure, the slsTax variable has been declared at the module level. After Visual Basic executes the CalcCost procedure, the contents of the slsTax variable equals 0.085. If slsTax is a local variable, the contents of this variable would be empty upon the termination of the CalcCost procedure. The ExpenseRep procedure ends by displaying the value of the slsTax and Cost variables in two separate message boxes.

When you run the CalcCost procedure, Visual Basic erases the contents of all the variables, except for the slsTax variable, which was declared at a module level. As soon as you attempt to calculate the cost by running the ExpenseRep procedure, Visual Basic retrieves the value of the slsTax variable and uses it in the calculation.

0 0

Post a comment