Understanding and Using Static Variables

A variable declared with the Static keyword is a special type of local variable. Static variables are declared at the procedure level. As opposed to local variables declared with the Dim keyword, static variables do not lose their contents when the program is not in their procedure. For example, when a VBA procedure with a static variable calls another procedure, after Visual Basic executes the statements of the called procedure and returns to the calling procedure, the static variable still retains the original value.

The CostOfPurchase procedure demonstrates the use of the static variable named allPurchase. This variable keeps track of the running total.

Sub CostOfPurchase() 'declare variables Static allPurchase

Dim newPurchase As String Dim purchCost As Single newPurchase = InputBox("Enter the cost of a purchase:") purchCost = CSng(newPurchase) allPurchase = allPurchase + purchCost

'display results

MsgBox "The cost of a new purchase is: " & newPurchase MsgBox "The running cost is: " & allPurchase End Sub

The above procedure begins with declaring a static variable named allPurchase and two other local variables: newPurchase and purchCost. The InputBox function used in this procedure displays a dialog box and waits for the user to enter the value. As soon as the user inputs the value and clicks OK, Visual Basic assigns this value to the variable newPurchase. The InputBox function is discussed in detail in Chapter 4. Because the result of the InputBox function is always a string, the newPurchase variable was declared as the String data type. You can't, however, use strings in mathematical calculations. That's why the next instruction uses a type conversion function (CSng) to translate the text value into a numeric variable of the Single data type. The CSng function requires one argument—the value you want to translate. The number obtained as the result of the CSng function is then stored in the variable purchCost.

0 0

Post a comment