Declaring Variables, Constants, and Data Types
|
| < Day Day Up > |
|
Most procedures will need some type of temporary storage area to manipulate data and hold it for later use. VBA stores this data in memory using a variable. It is also possible to store in memory a value that is not going to change; these values are called constants. Variables can hold values of different data types, which are specified when the variable is named, or declared. Variables should always be declared to reduce the chance of introducing bugs in the procedure and to properly allocate the amount of storage needed to hold the variable.
Declaring Variables
Variables are declared within a procedure by using the Dim, or dimension, statement. The most common usage of the Dim statement is
Dim varname as type
This statement explicitly declares a variable named varname as a variable of type. Multiple variables can be declared on one line by separating them with a comma, like this:
Dim varname1 as type, varname2 as type, ...
Defining Data Types
When variables are declared, they should be declared as a specific data type. This data type determines the values that can be stored within the variable and how much memory is required to store the value. VBA provides different data types to handle numbers, strings, dates and times, Boolean values, and objects. You can even declare your own data type when needed.
The majority of the data types available in VBA are numeric types, each of which handles a different range of values with varying amounts of precision. The numeric data types along with their specifications are listed in Table 4-7.
| Type | Range | Description | Storage Requirements |
|---|---|---|---|
| Byte | 0 to 255 | Unsigned, integer number | 1 byte |
| Currency | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | A signed, fixed-point number with up to 15 digits to the left of the decimal and 4 digits to the right; used for financial or other calculations requiring a high degree of precision | 8 bytes |
| Decimal | +/- 79,228,162,514,264,337,593,543,950,335 with no decimal point and +/- 7.9228162514264337593543950335 with 28 digits behind the decimal point. | Can’t be directly declared in VBA; requires the use of a Variant data type | 12 bytes |
| Double | Negative values: | Signed double-precision floating-point number | 8 bytes |
| Integer | -32,768 to 32,767 | Signed integer number | 2 bytes |
| Long | -2,147,483,648 to 2,147,483,647 | Signed integer number | 4 bytes |
| Single | Negative values: | Signed single-precision floating-point number | 4 bytes |
Defining Constants
Some procedures will require a reference to a particular value that rarely, if ever, changes. Rather than repeatedly entering the same value, you can create a reference, called a constant, for it. Defining a constant lets you specify the actual value only once in your code and use the reference whenever you need it.
VBA itself has many built-in constants, called intrinsic constants, designed to make it easier for you to work with the many functions available. For example, if you had a procedure to create a line chart, you could use the intrinsic constant xlDot to make the line dotted in appearance.
| Note | You can display a list of the available intrinsic constants by typing intrinsic constants in the Help box in the Visual Basic Editor. |
You specify your own constants by using the Const statement, which works the same as the Dim statement with the exceptions that you must supply the value of the constant and only one constant can be declared on a line. The following lines declare two constants, the first a byte value with the number of days in December and the second a Single variable with an accepted value for pi.
Const conDaysDec as Byte = 31
Const conPi as Single = 3.1415929
Handling Strings
As mentioned earlier, there are other data types besides numeric ones. Variables can also hold text values using the String data type. Strings can be either variable-length or fixed-length. Variable-length strings can hold approximately 2 billion (2 ^ 31) characters, subject to available memory, and fixed-length strings can hold about 65,000 characters (2 ^ 16).
Both types of strings are declared similarly using the Dim statement just as was done with the numeric types. Fixed-length strings add extra code to specify the length of the string. The first line in the following code fragment specifies a variable-length string, and the second line specifies a fixed-length string of 25 characters:
Dim MyString as String
Dim MyFixedString as String * 25
Strings that have been declared but have not yet had a value assigned to them are known as empty strings. To assign a value to a string variable, it needs to be enclosed within double quotation marks. The following lines are examples of assigning values to strings:
MyString = "Hello world."
MyFixedString = "This is a fixed string."
MyEmptyString = ""
| Note | Fixed strings must be exactly 25 characters in length; therefore, the Visual Basic Editor will either add spaces or truncate the string so it is the proper length. |
Handling Dates and Times
Dates and times are stored within the Date data type. The range of dates that can be stored is from January 1, 100, to December 31, 9999, with all times from 0:00:00 to 23:59:59. Although a Date variable can hold both a date and time, if you need only one element, only that element needs to be assigned to the variable; the other will be omitted from the variable.
You can assign values to a date variable by enclosing a recognizable literal string within number signs (#). VBA can recognize dates and times in just about any format, but you should always be as explicit as possible to ensure the correct value is being used. Here are some examples of dates and times VBA can recognize.
MyDate = #15 July 1999#
StartDate = #April 8, 2001#
MyTime = #8:47 PM#
StartingDateTime = #05/07/1992 15:56#
The Visual Basic Editor might change the dates and times that you type to reflect your computer’s date and time settings. For example, #15 July 1999# might become #7/15/1999#.
| Note | When entering dates, VBA will recognize two-digit years and uses 2029 as the cut- off year. All two-digit years from 00 to 29 are recognized as being a part of the 2000s. The remaining years, 30 to 99, are considered part of the 1900s. It is best to always include the full four-digit year to remove any ambiguities. |
Handling Variants and Data Type Conversions
Variants are the catchall data type of VBA. If a variable is declared but not typed, it will be cast as a Variant data type. Variants can hold any type of data except for fixed-length strings and must be used for certain data types, such as Currency.
When working with variants, VBA will attempt to use it as the data type that best fits the data being stored in the variable, but it might not always pick the right type. Assigning a variant the value of 64 * 1024 produces an error message. Because both operands, 64 and 1024, are considered Integer data types by VBA, it tries to store the result as an Integer. The actual result, 65536, is too large for an Integer data type and produces the error message.
To avoid having VBA pick the wrong data type, you can convert values to a specific data type. In the preceding example, by explicitly converting one of the values to the Long data type, VBA will cast the result as a Long also and avoid the error.
VBA provides functions to convert values to any numeric data type. When converting from one data type to another, keep in mind the level of precision that is used by each type and the limitations of each data type. For example, converting a Single variable, which has a decimal component, to a Long, which does not, will result in losing the decimal portion of the value, and converting the variable back to a Single will not restore the decimal portion of the original value.
lngOffset = CLong(sngOffset)
A list of the conversion functions along with the type of data returned and rounding rules is provided in Table 4-8. The expression argument provided to the functions can be any numeric or string expression. String expressions need to be in a format that can be recognized as a numeric value, but the specific format varies from region to region.
| Function | Result | Remarks |
|---|---|---|
| CBool(expression) | True/False (-1/0) | Any non-zero expression will result in True (-1). |
| CByte(expression) | Byte | .5 will round to the nearest even integer. |
| CCur(expression) | Currency | Rounding to four decimal places. Five decimals and greater is undocumented and might return unexpected results. |
| CDate(expression) | Date/Time | Numeric expressions will return a date matching number of days from January 1, 100. String expressions will return an interpreted date. |
| CDbl(expression) | Double | Rounding to the nearest floating-point number within range. |
| CDec(expression) | Decimal | Rounding to a variable number of decimal places dependent upon size of number. |
| CInt(expression) | Integer | .5 will round to the nearest even integer. |
| CLng(expression) | Long Integer | .5 will round to the nearest even integer. |
| CSng(expression) | Single | Rounding to the nearest floating-point number within the range. |
| CVar(expression) | Variant | Numeric expressions convert to a Variant Double. Expressions enclosed within # convert to a Variant Date. All others convert to string. |
Understanding Variable Scope and Lifetimes
Variables have a set lifetime and visibility within modules and procedures. A variable lifetime begins when the variable is declared and lasts until the procedure or module that declared the variable has completed execution. If a procedure calls a second procedure, the variables of the first procedure remain alive and retain their values during the execution of the second procedure.
| Note | Although only the term variable is used in this section regarding Scope and Lifetime, the same rules apply to constants also. |
A variable can also have a virtually unlimited lifetime if it’s declared as a static variable. Static variables within a procedure will retain their values from one call to the containing procedure to the next so long as the module containing the procedure has not been stopped or reset. Static variables are declared using the same syntax as normal variables, replacing the statement Dim with Static.
Besides having a set lifetime, variables also have a certain visibility. Variables declared within a procedure can be used only within that procedure; other procedures within the same module can’t see them. This means that two procedures within the same module can each declare a variable using the same name, but the two variables will be separate entities and visible only by the procedure that declared them. Consider the following code as an example:
Sub Procedure1()
Dim intCounter as Integer, intResult as Integer
intCounter = 87
intResult = intCounter * 74
End Sub
Sub Procedure2()
Dim intCounter as Integer, intResult
intResult = intCounter * 74
End Sub
In the preceding procedures, both Procedure1 and Procedure2 have declared a variable named intCounter. In Procedure1, intCounter has been set to 87, this means intResult will be set to 6438 when the third line is executed. In Procedure2, intCounter has not been set to a specific value so it retains the initialized value for an Integer, which is 0. Then the second line is executed, and intResult is given a value of 0 also (0 * 74 = 0).
To use a variable in more than one procedure, it needs to be declared at the module level. Variables declared at the module level can be used by any procedure that is within the same module. To declare a module variable, the declaration statement for the variable needs to be entered in the Declaration section of the module (following any Option statements and before any procedures), as shown in Figure 4-12.
Figure 4-12: If you want a variable to be available to all procedures in a module, you need to declare the variable in the declaration section for modules.
Module variables can be exposed to other modules by using the Public modifier. When a variable is declared Public, it becomes visible to all other modules, not just the module it is declared in.
Although all module variables are private by default, for clarity it is better to declare them using Private instead of Dim. Variables that are to be visible to all modules are declared using the Public statement. The following two declarations illustrate the difference between a Public and a Private declaration:
Private intThisModuleOnly as Integer
Public intAllModules as Integer
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 161