Variables

One of the most important concepts in programming is the use of variables. A variable is a location in memory where you can store a value while your code is running. Throughout your code you'll need to declare, change, and fill variables. These variables will hold a variety of different types of data including strings, numbers, and objects. You use a variable in order to save time. For example, if you need to specify a particular state repeatedly in your code, it can be much faster and cleaner to create a variable strState than to repeatedly use "Maine" in your code. If you ever need to switch states and use California instead, all you have to do is change the value of your variable rather than find all the instances of Maine in your code. Declaring variables properly is likely one of the most important tasks you'll need to master to program in VBA. It's not hard, and we'll cover the major rules and recommendations in the next few sections.

Types of Variables

In VBA, you can declare many different types of variables. Each type of variable can hold different data. You should always make a point to declare your variables with their correct type. You can, however, choose not to declare a data type. If so, the variable is created as a variant. Variant variables require significantly more space in memory than other types of variables; so use them sparingly. The following table lists the various types of variables, the amount of memory they take up, and the data they can store.

Data Type

Size in Memory

Possible Values

Byte

1 byte

0 to 255

Boolean

2 bytes

True or False

Integer

2 bytes

-32,768 to 32,767

Long (long integer)

4 bytes

-2,147,483,648 to

2,147,483,647

Continues

Data Type

Size in Memory

(single-precision real)

4 bytes

Approximately -3.4E38 to 3.4E38

Double

(double-precision real)

8 bytes

Approximately -1.8E308 to 4.9E324

Currency (scaled integer)

8 bytes

Approximately -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Date

8 bytes

1/1/100 to 12/31/9999

Object

4 bytes

Any object reference

String

Variable length: 10 bytes + string length; fixed length: string length

Variable length: < about 2 billion (65,400 forWindows 3.1) Fixed length: up to 65,400

Variant

16 bytes for numbers 22 bytes + string length

Number: same as double String: same as string

User-defined

Varies

You'll notice that the VBA data types are similar but not exactly the same as the data types in an Access table. The major differences between Access data types and VBA data types are that there is no equivalent to the variant or object data types in Access Jet data types and the number data type in Access has a field size property that allows you to specify the field as byte, integer, long, single, decimal, or double.

Numeric Data Types

When working with numeric data, you have a choice of eight different data types. This section will provide a brief introduction to the data types available to you within your code and help you choose the proper data type for your needs.

Each different numeric data type provides a different level of accuracy; however, they also use differing amounts of space. There are some general rules of thumb you can follow when choosing the right data type for your variable:

□ When creating a variable to store whole numbers, choose the long data type.

□ When creating a variable to store fractional numbers, choose the double data type.

□ If you need to store a negative value, you can use any data type except for the byte data type.

□ If you need to store a value outside the range of -32768 to 32767, do not use the integer data type; it can't handle numbers that large.

□ To control loops, always use an integer data type.

There are a few key differences between the single and double data types. The single data type allows you a precision level of 7 digits, while the double data type gives you approximately 15 digits of precision. Both data types allow you to work with numbers larger than 2 billion.

You should never use double or single data types to represent fractional numbers. These cannot do so with extreme accuracy. For example, take the following code:

Sub TestFractionalDivision() 'Do not actually run this code!

'If need be, use Ctrl+Break to stop code execution Dim dblResult As Double dblResult =1/5 Do Until dblResult = 1

'Do something interesting dblResult = dblResult +1/5

Loop End Sub

The loop will run forever, because the value of dblResult never actually equals 1. You'll have to use Ctrl+Break to end this code loop. This is why you're always better off using integers for looping operations.

The single and double data types are floating-point data types. This means that they can handle numbers with the decimal point in a variety of places. The currency data type is a fixed-point number. This means that there are always four decimal places in a currency number to handle most types of international currency. Just because the currency data type is generally used for dealing with dollars and cents, it's not limited to financial transactions. You can use the currency data type for any number with up to four decimal places. Because the decimal is always in the same place, VBA actually performs a little trick with currency calculations. To save time, VBA removes the decimal points, performs the calculation on the integer numbers, and then puts the decimal points back. This speeds up the calculation while retaining the four decimal place accuracy.

Other Data Types

In addition to numeric data types, there are a variety of other data types you can use. Boolean variables can take only two values, True and False. If you've ever used VBScript, you probably are familiar with using numeric values to represent True and False. If you haven't, it's important to note that when referring to a Boolean data type in VBScript, 0 is False and -1 is True. You can actually still use these values even with Boolean data types. VBA interprets zero as False. The difference is that any nonzero value is always interpreted as True. The following two code samples actually produce the same result.

Sub TestIfTrue() Dim blnIsTrue As Boolean blnIsTrue = True If blnIsTrue = True Then

MsgBox "True" End If End Sub

Sub TestIsTrue() Dim blnIsTrue As Boolean blnIsTrue = 2 If blnIsTrue = True Then

MsgBox "True" End If End Sub

The Date Data Type

Remember the Y2K bug? Computers used to store dates with only two-digit years. This caused a problem when computers needed to start storing dates past the year 1999. After all, 01 could be a two-digit year code for 1901 or for 2001. Programmers scrambled for several years to fix the problem. So now computers store dates with a four-digit year. However, the problems didn't end there. Different countries represent dates in different ways. For example, the date 9/10/2003 could represent either September 10 or October 9 depending on the region of the world you live in. To work around this problem, VBA has a nifty way of dealing with dates. All dates are represented as a floating-point number (one with a flexible number of decimal places). When working with a date and time, the date portion is converted to an integer. The time is represented by the decimal portion of the number and is calculated as a percentage of a day. For example, noon would be half of a day and represented by 0.5. In order to determine the integer value for the date, an initial date is needed for calculations. That initial date is December 30, 1899. So counting that as day 0, March 1, 2004, would be represented as 38047, and 6:00 p.m. on that date would be represented as 38047.75.

So if you're working with dates in VBA, you will not have to perform conversions between 38047.75 and March 1, 2004, 6:00 p.m. VBA is aware of the regional settings specified in the user's Control Panel Regional Settings applet and will convert the date to the proper format. So, if you're in Australia, VBA will convert 38047.75 to 01/03/04. If you're in the United States, VBA will display 03/01/04 (or 03/01/2004 depending on your settings). VBA's use of a calculated number for dates ensures that dates will always be calculated correctly regardless of the specific regional settings used on the local computer.

There are a few quirks in VBA's date calculations that you should be aware of when you're working with dates. First, a variable declared as a date has an initial value of December 30, 1899 (or zero when converted to the floating-point decimal value). So the following code will produce a value of December 31, 1899.

Sub AddADay()

Dim dtInitialDate As Date dtInitialDate = DateAdd("d", 1, dtInitialDate) Debug.Print dtInitialDate End Sub

You can work with dates directly in VBA by assigning the literal date value to a date variable. To do this, use # to delimit your dates. For example, to assign a value of March 1, 2004, to the variable dtInitialDate, use the following code:

dtInitialDate = #03/01/2003#

This ensures that VBA will recognize the date properly no matter what your regional settings are.

If you're in a region of the world that enters dates with d/m/yyyy, you'll need to enter the literal date in the format m/d/yyyy when using the method described previously. Otherwise, VBA will not recognize it properly.

The String Data Type

The string data type is fairly straightforward. You use this data type for all types of alphanumeric data including names, sentences, or phrases. You can store numbers in a string data type. However, you can't perform operations on them in the same manner as you would if these were stored as numbers. Consider the following code:

Sub

FunWithStrings()

Dim strBikes As String

Dim strCost As String

Dim intBikes As Integer

Dim curCost As Currency

strBikes = "5"

strCost = "100"

intBikes = 5

curCost = 100

Debug.Print strBikes + strCost

Debug.Print intBikes + curCost

End

Sub

The first operation, Debug.Print strBikes + strCost produces a result of 5100. The operation is merely concatenating the two string variables. The second operation, Debug.Print intBikes + curCost actually performs the mathematical calculation and produces a result of 105.

When creating a string variable, the default value of the variable is a zero-length string. This isn't the same as a null value; it's more like an empty value. You can assign pretty much any value to the string variable. The trickiest problem you're likely to encounter while using string variables in VBA is dealing with quotation marks. For example, the following is a valid way to assign a string value to a variable:

strCustomerName = "ABC Textiles"

The same syntax works if you need to store a value with a single quote in the name, such as the following:

strCustomerName = "Johnny's Cycles"

However, what if you need to store a value with a double-quote character in the name? If you follow the same rules as the previous two examples, you'll end up with something like the following:

strCustomerName = "The "W" Hotel, New York City"

That might look like a valid line of code, but when you actually type that into your code, you'll get a compile error even before you run the code. The problem is that VBA sees the second double quote and thinks you want to end the string. VBA doesn't quite know what to do with the text that follows the double quote. In order to work around this issue, you'll need to use a double set of double quotes within your string. So your actual assignment statement should look like the following:

strCustomerName = "The ""W"" Hotel, New York City"

If you enter all strings with double quotes using that method, you'll get predictable results every time.

String Comparisons

You can't have a discussion about string variables without discussing how to work with strings. Some of the tasks you'll need to perform with strings include comparing two strings, finding matches to a partial string, or determining the length of a string. The next few paragraphs describe some of the tricks you can use to work with string variables.

When comparing two strings, you'll find that VBA is by default case insensitive. In other words, California and CALIFORNIA are considered to be the same string. You can change this default behavior by editing the first line of your VBA code. When opening a module in VBA, the first line is Option Compare Database. Changing this line to Option Compare Binary will have one immediate effect: All string comparisons are now case sensitive. See the following table for a summary of the various options you can select for the Option Compare statement in VBA.

Compare Statement

Definition

Option Compare Database

String comparisons are case insensitive. Local settings of the current database are used.

Option Compare Binary

String comparisons are case sensitive. Local settings of the current database are ignored.

Option Compare Text

String comparisons are case insensitive. Locale settings specified in Control Panel are used. This setting isn't often used.

Unless you have good reason to change the Option Compare statement, we suggest leaving it at the default value of Option Compare Database.

There are a variety of other string comparisons you can use besides comparing entire strings to each other. You can search for strings based on one or more characters in the string. For example, the following code sample illustrates a few of the types of string comparisons you can use within your code.

Sub CompareStrings()

Dim strStringl As String Dim strString2 As String strStringl = "Microsoft"

If strStringl Like "Micr*" Then

Debug.Print "True" End If

If strStringl Like "Mic*t" Then

Debug.Print "True" End If

End Sub

Both of these comparison operations return True. The first returns True whenever the first four letters of strStringl are Micr. The second comparison returns True whenever the first three letters of strStringl are Mic and the last letter is t. The following table describes the variety of comparison operations you can use in VBA.

Comparison Expression

Strings that Match

Strings that Do Not Match

Like "Mi*"

Microsoft, Michigan

MapPoint, Monochrome

Like "sol*d"

Sold, solid

Solids

Like "s?t"

Sit, sat

Seat

Like "177#"

1776, 1777, 1778, and so on.

1977, 177, 1077

Like "s[ea]t"

Set, sat

Sit, seat

Like "s[!ea]t"

Sit

Set, sat

As you can see from the table, the rules are fairly straightforward. The last two rules are the most confusing. Using two characters (or more) within the brackets tells VBA that any of the characters within the brackets can be used within the string. Putting the exclamation point before the characters in the brackets tells VBA that any character except those within the brackets can be used to compare the string.

Variants

The variant data type is probably the most flexible data type within VBA. Unlike other variable types, which can only hold one type of data, the variant data type can hold many different types of data. You can use it to hold text, numbers, dates, and user-defined types. The only type of data a variant data type cannot hold is a fixed-length string. As previously explained, there is one very good reason to not use variant data types for everything. As tempting as it might seem to just use variant data types for all of your coding, this practice results in much higher memory use than using the proper data type. Just as an example, if you use a variant to hold a string of data, you're using 11 extra bytes of data for every string you store. Over the course of an entire application, these extra bytes can have a significant performance impact.

There are times, though, when you need to use a variant data type. For example, if you're not sure the type of information a variable needs to hold, use the variant data type. Typically this can be used when you're asking the users of your application for data. If you're not sure whether they will enter a date, a number, or a string, you can create a variant data type and store their answer in that variable.

Nulls

There's one last concept we should discuss when talking about variables: Null. When you first learn programming, you learn that Null is the value of a field with no data. If you create a field in a table and don't fill it with any data, the value of the field is Null. So what's Null? Null is nothing, but it's also something. That sounds a bit confusing. Well, it is actually. Here's an example:

Sub

CompareNulls()

Dim varValue1 As Variant

Dim varValue2 As Variant

varValue1 = Null

varValue2 = Null

If varValue1 = varValue2 Then

Debug.Print "Nulls are not equal"

End If

End

Sub

If you run the previous code sample, you'll see that the phrase never prints in the Immediate window. But how can that be? You just set the two variables equal to the same value. Well, you might think you set them to the same value, but, really, two Nulls never equal each other.

There are a few rules of Null you need to be aware of. First, you cannot assign Null to anything other than a Variant data type. You can never have a null string; rather, you have a zero-length string. You cannot have a Null single or double; rather, you have a variable with value 0. The second rule of Null is that no two Nulls match. Null doesn't match zero; it doesn't match a zero-length string; it doesn't even match itself. The only comparison operation you can run on Null is to determine if it's null. But, as mentioned above, Null never equals Null. Well, this is where it gets really confusing. If you alter the previous code sample slightly, you get a completely different result, as shown in the following example:

Sub CompareNulls2() Dim varValue1 As Variant Dim varValue2 As Variant varValue1 = Null varValue2 = Null

If IsNull(varValue1) And IsNull(varValue2) Then

Debug.Print "Both variables are Null" End If End Sub

This code sample will print the phrase in the Immediate window. This is because you can test for the Null condition by using the IsNull function. This function evaluates to True if the value of the variable is Null and to False if the value of the variable is not Null. Confused now? Just remember the two basic rules of Null. Null doesn't equal anything and Null equals nothing. Right.

Using Variables

Now that you've learned a bit about the different types of variables, you need to know how to use those variables within your code. You can use a variable by simply adding a statement like the following to your code:

strState = "Maine"

However, simply using variables in your code without first declaring them isn't a very good programming practice for a number of reasons. First, if you don't declare your variables in advance, when the variable is created by the previous statement, it's created as a variant data type. As we saw previously, the size of a variant variable holding a string value is 22 bytes plus the length of the string. The size of a string variable is only 11 bytes plus the length of the string. So what's the big deal about 11 bytes? Well, it might not be a big deal if you're only declaring three or four variables in your entire application, but if you have several hundred or even a thousand variables in your application, you'll unnecessarily eat up a whole lot of space by using variants instead of strings.

By always declaring your variables you'll know that you're minimizing the amount of space your variables need. If you're using a large number of variables in your application, it can be hard to remember to always declare your variables. If you're happily coding along, it's easy to forget that the last line of code you just typed contained a new variable. You can have VBA help you out a little by requiring you to declare all of your variables before you use them. Making one simple change to your VBA code causes VBA to produce an error when you run your code for every undeclared variable. This is important for two reasons. First, it's just a good programming practice to declare all of your variables. This ensures that your variables have the proper types and can speed code execution. Second, if you don't declare your variables, you can actually get yourself into trouble. The following code sample illustrates this.

Sub BuyNewBike()

curBikePrice = InputBox("Please enter bike price.",

"Enter Bike Price")

If curVikePrice

> 100 Then

MsgBox "Don1

t Buy the Bike! It's too expensive!

!", vbCritical, "Bike

Purchase"

Else

MsgBox "You

can buy the bike. It's within your

budget.", vbOKOnly,

"Bike Purchase"

End If

End Sub

This code sample looks simple enough. You enter the price of the bike in the InputBox that appears on the screen when you run this code. If the price of the bike is greater than $100, the program tells you not to buy the bike. If the price is less than $100, you can buy the bike.

However, this code won't actually work. If you examine the code carefully, you'll see that the variable that accepts the price of the bike, curBikePrice, isn't the same as the variable used in the If...Then statement, curVikePrice. A mistake like this is quite easy to make. You're typing along and you hit the wrong key. You probably wouldn't even notice it until you ran the code and nothing happened. As it's written, there's actually nothing wrong with the code. It won't produce any errors; it just won't produce the required results. There's one very easy way to prevent this type of mistakes from happening. You can add a single line of code to the General Declarations section of your code, Option Explicit. This line tells VBA that all variables used within your application must be declared before they can be used. If you force variable declaration, then typing the wrong name for a variable will cause VBA to display an error when the code is compiled, as shown in Figure 4.1.

Once the error message is displayed, VBA will highlight the undeclared variable, so you can see where your error might be. You can either correct the spelling of the variable to match the declared variable or add a new line of code to declare the variable in your code.

If you want to always use Option Explicit within all of your procedures, you can configure Access to do this for you. Choose the Tools menu from anywhere in Access and select Options. Choose the Editor tab and check the box marked Require Variable Declaration. Click OK to save your changes. Now, whenever you create a new module or build code behind any Access form or report, you'll always be prompted to declare your variables.

So far you've learned that it's better to declare your variables ahead of time, and always use the correct data type for your variables. Now you need to learn how to actually perform that variable declaration.

Declaring Variables

You declare a variable by use of the Dim (short for dimension) keyword, then the variable name, the word as , and the variable type. For example, to declare the state variable in your procedure, you can use the following statement:

Dim strState as String j Microsoft Visual Basic - VBA Samples [running] - [Samples (Code)]

Project - VBA Samples

Run lools Add-lns Window Help Type a question For help

B-® VBA Samples (VBA Samples)

IB-fill Microsoft Office Access Class Objects Modules \ Module 1 Samples B-fiii Class Modules

Properties - Samples

5amples Module Alphabetic Categorized

I Samples

J S tait

Figure 4-1

I \General)

TI J GoShoppinySuits

Op t i o n C omp ar e D at ab as e Option Explicit

Private blnUnderBudget As Boolean Private blnOverBudget As Boolean Const curBudget = 1000_

Private Sub GoShoppingSuits()

KEXHll^KDi

Compile error:

Variable not defined Help e desired number of suits"

ii eur'iotaiPEiee > eurtiue lolnOver Budget = True

Else bInUnderBudget = Ealfse End If

If li'lnUnderBudget - False Then OverBudge t

End If rice + curSuitPrice .get Then

OH 1.

ÜD £..:

¿JA..

[¿¡M..

'fiM

Once you dimension your variable, you can assign it a value anywhere in your procedure. For string variables such as strState, use the following statement to assign the value "Maine" to your variable:

strState = "Maine"

Now that your variable has a value, you can use it repeatedly within your procedure. Consider the following code segment:

Private Sub OpenDatabaseConnection()

Dim objConn As ADODB.Connection Dim objRST As ADODB.Recordset Dim strSQL As String Dim strConn As String Dim strState As String

'Create the ADODB Connection and Recordset Objects Set objConn = CreateObject("ADODB.Connection") Set objRST = CreateObject("ADODB.Recordset")

'Open your ADODB Connection strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ Cust.mdb;" strSQL = "Select * from tblCust WHERE CustState = '" & "Maine" & "';"

objConn.Open (strConn) objConn.Mode = adModeRead objRST.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic objRST.MoveFirst

'Print relevant customer information While Not objRST.EOF

Debug.Print objRST.Fields("CustName") Debug.Print objRST.Fields("CustState") Debug.Print objRST.Fields("CustCountry") objRST.MoveNext

Wend objRST.Close objConn.Close 'Release your variables Set objRST = Nothing Set objConn = Nothing End Sub

This code opens an ActiveX Data Objects (ADO) connection to an Access database. It then opens a recordset of all customers in Maine and prints their name, state, and country in the Immediate window. This is pretty simple code. You're only referencing the state in one place, so you really do not need to create a variable to hold the state name. If you know without a doubt that you'll never need to change your code and you won't need the same value later in your code, you can certainly just use the actual value. However, what if you needed to write code to allow users to input their own state name? If you've hard coded the state name in the procedure, you would have no way of switching states when the user needed to do so. You could adapt the previous code through the use of a variable and InputBox to allow users to select the state they need. After changing your code, it would read similar to the following:

Private Sub OpenDatabaseConnection()

Dim objConn As ADODB.Connection Dim objRST As ADODB.Recordset Dim strSQL As String Dim strConn As String Dim strState As String

'Create the ADODB Connection and Recordset Objects Set objConn = CreateObject("ADODB.Connection") Set objRST = CreateObject("ADODB.Recordset")

'Open your ADODB Connection strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ Cust.mdb;"

strState = InputBox("Please enter a state", "Enter State")

strSQL = "Select * from tblCust WHERE CustState = '" & strState & "';"

objConn.Open (strConn) objConn.Mode = adModeRead objRST.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic objRST.MoveFirst

'Print relevant customer information While Not objRST.EOF

Debug.Print objRST.Fields("CustName") Debug.Print objRST.Fields("CustState") Debug.Print objRST.Fields("CustCountry") objRST.MoveNext

Wend objRST.Close objConn.Close 'Release your variables Set objRST = Nothing Set objConn = Nothing End Sub

Using the preceding code, users can enter any state in response to the InputBox and your code will still run.

There's one key element missing from this procedure—error handling. If users enter Sacramento as a state, misspell Mississippi, or simply choose a state for which no records exist in your database, the preceding code will generate an error. We'll provide a brief introduction to error handling in Chapter 5 and more details on error handling in Chapter 9.

Naming Your Variables

There are only a few rules you need to follow when naming a variable:

□ You can only use letters, numbers, and the underscore symbol (_). No other symbols are allowed.

□ Variable names must start with a letter.

□ You cannot use a reserved word for your variable name (reserved words are covered in Appendix H).

□ Variable names must be less than 255 characters.

In addition to the rules you must follow when naming your variables, it's customary to follow some sort of naming convention when creating your variables. Which convention you choose is up to you; however, the most popular is the Reddick naming convention. We've provided a detailed appendix on this set of naming conventions as well as guidelines for creating your own naming conventions. You should make your variable names meaningful at the very least. If you create variables with names such as var1, var2, and var3, you'll have a hard time keeping track of which variable you need to use for which statement.

Throughout this book, you'll find that we stick pretty closely to Reddick's naming conventions. That means that our variables will usually contain a prefix that determines their data type. A string variable, for example, will have the str prefix, while a Boolean variable will have a bln prefix.

In addition to Reddick's naming conventions, there is one other convention some developers like to use for additional clarity in their code. You can add a prefix to all of your variable names to denote whether the variable is a global, private, or local variable. See the following table for the prefix used to denote variable scope and lifetime.

Prefix

Variable Scope

Usage

G

Global variable

Variables declared with the

Public keyword

M

Private (module-level)

Variables declared with the

variables

Private keyword

S

Static variables

Local variables declared with

the Static keyword

Variable Scope and Lifetime

The last two important variable concepts we'll cover in this chapter are the scope and lifetime of variables. The scope of a variable defines where in the program the variable is recognized. The lifetime of a variable describes how long it exists.

If you declare your variable within a sub or function, the variable's scope is limited to that sub or function only. If you use the same name for a variable in another sub or function, you won't have to worry about the two variables conflicting with each other. The variable's scope is limited to the sub or function. The lifetime of that variable is the same sub or function. The variable lives only while the sub or function is running. As soon as the procedure ends, the variable is destroyed and the memory used by the variable is released. A subsequent call of the procedure creates the variable again from scratch.

There might be times you want your variable to exist outside a particular sub or function. If you declare the variable in the General Declarations section of the module (located at the top of the module), your variable can have a longer scope and lifetime. You can declare the variable in two different ways. If you use the Private keyword, the variable is available to any and all procedures within the current module. Use of the Public keyword makes the variable available anywhere in the entire application. The following code sample illustrates how declaring your variables differently can affect their scope and lifetime.

Option Explicit 'Used to require variable declaration

Public txtCustomerName as String 'Scope is entire application Private txtVendor as String 'Scope is any procedure in this module Dim txtSupplier as String 'Scope is entire application

Private Sub GetCustomerName()

Dim txtCustomer as String 'Scope is limited to this sub End Sub

You might be wondering why the two statements that begin with Dim have different scopes. Use of the Dim keyword in the General Declarations section limits the scope of the variable to the procedure. Any variable declared with the Dim keyword in the General Declarations section has a scope of every procedure in the module.

In the previous listing, txtVendor and txtSupplier are both module-level variables. They can be used anywhere within the module and anytime the module is loaded. txtCustomerName is a global variable. It can be used anywhere within any procedure in your application.

The last keyword we'll cover in this section is the Static keyword. Use of the Static keyword allows you to create a local variable with an ongoing lifetime. Why would you want to do this? What if you needed to know how many times a particular procedure was run? You could simply declare a global variable and increment this variable every time the procedure runs. However, it's often easier to track the use of variables when they are declared within the procedure in which they're used. There's one big difference between using the Static keyword within the procedure and using the Public keyword in the General Declarations section to declare your variables. If you declare the variable with the Public keyword in the General Declarations section, you can use the variable anywhere within your application. If you use the Static keyword within a procedure, you can only use the variable within that procedure. However, every time you call that procedure, the variable isn't destroyed when the procedure completes. The variable remains and holds its value. You cannot use the Static keyword to create a variable within Procedure A and use it within Procedure B.

Overlapping Variables

When you're writing your code, you need to be careful of using the same variable name twice in different ways. If you declare a global variable of strString and then declare a variable within your procedure named strString, how is VBA supposed to know which one you want to use? VBA will always use the global variable, but if that's not what you intended, your code could produce unexpected results. For example, consider the following code listing.

Option Compare Database Option Explicit

Public intQuantity As Integer Public curPrice As Currency

Private Sub FindTotals()

Dim intQuantity As Integer Dim curTotalPrice As Currency

curPrice = InputBox("Please enter the bike price.", curTotalPrice = intQuantity * curPrice MsgBox curTotalPrice, vbOKOnly, "Total Price" End Sub

"Enter Bike Price")

Private Sub EnterValues()

intQuantity = InputBox("Please enter the number of buy.", "Total Bikes") End Sub

bikes you want to

Private Sub CalculatePrice() 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 in this listing: EnterValues and FindTotals. When this code is run, the EnterValues procedure will ask you for the total number of bikes you want to buy. The FindTotals procedure will ask you for the bike price and calculate the total purchase price (quantity of bikes multiplied by the purchase price). However, there's one problem here. There's an added line in the FindTotals procedure that causes the calculation to fail, Dim intQuantity as Integer. That 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.

If you want Access VBA to use the procedure-level variable, you can add the module's name before the variable name. The following code will work as expected.

Option Compare Database Option Explicit

Public intQuantity As Integer Public curPrice As Currency

Private Sub FindTotals()

Dim intQuantity As Integer Dim curTotalPrice As Currency curPrice = InputBox("Please enter the bike price.", "Enter Bike Price") curTotalPrice = Module2.intQuantity * curPrice MsgBox curTotalPrice, vbOKOnly, "Total Price" End Sub

Private Sub EnterValues()

intQuantity = InputBox("Please enter the number of bikes you want to buy.", "Total Bikes") End Sub

Private Sub CalculatePrice() EnterValues FindTotals End Sub

Adding the name of the module in front of the variable name you need is an easy way to tell Access VBA exactly which variable you need. Just as a general tip however, try to avoid this situation entirely. Utilize naming conventions and declare your variables with as narrow scope as you need. If you don't need to declare a public variable but can get by with a procedure-level variable, it's better to do so.

0 0

Post a comment