Overlapping Variables

When writing code, be careful to use a variable name only once. If you declare a global variable of strString and then declare a variable within your procedure named strString, VBA will always use the procedure-level variable. If you are using procedures to call routines in other procedures, you may forget that one has a variable with the same name as a global variable, and that can create unexpected results. Here's an example:

Option Compare Database Option Explicit

'this module demonstrates that local variables 'take precedence over global variables. Public intQuantity As Integer Public curPrice As Currency

Private Sub FindTotals() Dim intQuantity As Integer Dim curTotalPrice As Currency

'this sub declares the local variable intQuantity 'but does not give it a value, so the value is 0.

curPrice = InputBox("Please enter the bike price.", "Enter Bike Price")

curTotalPrice = intQuantity * curPrice MsgBox curTotalPrice, vbOKOnly, "Total Price"

End Sub

Private Sub EnterValues()

'this is storing the value into the global variable. intQuantity = InputBox("Please enter the number of bikes", "you want to buy.", "Total Bikes") End Sub

Private Sub CalculatePrice()

'This sub runs the two subs listed below. 'Although Enter Values stores a quantity in the 'global Variable, intQuantity, the FindTotals sub will 'use the local variable intQuantity to calculate curTotalPrice. EnterValues FindTotals

End Sub

These three procedures illustrate how variables can overlap. If you run the CalculatePrice procedure, Access VBA will run the other two procedures, EnterValues and FindTotals. When that code is run, the EnterValues procedure asks you for the total number of bikes you want to buy. This stores the value into the global variable, intQuantity. The FindTotals procedure asks you for the bike price and calculates the total purchase price (quantity of bikes multiplied by the purchase price). However, there's one problem here. The line in the FindTotals procedure—Dim intQuantity as Integer—causes the calculation to return zero. This one line tells Access VBA to create a local procedure-level variable with the same name as the public variable declared in the General Declarations section of the module. Because there is no input for this local variable, it uses the default value of 0. The procedure uses the local variable instead of the global any time intQuantity . So the equation curTotalPrice yields 0 as its result.

If you want Access VBA to use the global variable, you can add the module's name before the variable name. The following code works as intended:

Option Compare Database Option Explicit

'this module demonstrates that by explicitly naming a variable 'with both the module and variable name, value of the global 'global variable will be used. mintQuantity would also work.

Public intQuantity As Integer Public curPrice As Currency

Private Sub FindTotals() Dim intQuantity As Integer Dim curTotalPrice As Currency

'This sub declares the local variable intQuantity 'but does not give it a value, so the value is 0. 'replace [ModuleName] with the name of the current module.

curPrice = InputBox("Please enter the bike price.",_ "Enter Bike Price")

curTotalPrice = [ModuleName].intQuantity * curPrice MsgBox curTotalPrice, vbOKOnly, "Total Price"

End Sub

Private Sub EnterValues()

'this is storing the value into the global variable. intQuantity = InputBox("Please enter the number of bikes", "you want to buy.", "Total Bikes") End Sub

Private Sub CalculatePrice()

'This sub runs the two subs listed below. 'Although Enter Values stores a quantity in the 'global Variable, intQuantity, the FindTotals sub will 'use the local variable intQuantity to calculate curTotalPrice. EnterValues FindTotals

End Sub

Adding the name of the module in front of the variable name is an easy way to tell Access VBA exactly which variable you need. You'll also recall that some developers like to use a prefix to specify the variable scope. Using mintQuantity in the General Declarations section would have prevented the overlap. It's best, however, to avoid this situation entirely. Utilize naming conventions and declare your variables with as narrow a scope as you need. If you don't need to declare a public variable, it is better to use a procedure-level variable. For your convenience, these two modules are included in the download code for this chapter.

Was this article helpful?

0 0

Responses

  • Frank Gloeckner
    How to compare two variable value in access vba?
    8 years ago
  • emilia
    How do you use a global variable in vba in two subs?
    8 years ago

Post a comment