VBA Brush Up 03：Variables Data Types And Constant

來自：Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p28-p51, p69-p71

Technorati 標籤: constants,variables,data types,vba,type conversion

 

 

1. The variable type can be indicated by the As keyword or by attaching a type symbol. If you don’t add the type symbol or the As command, VBA will default the variable to the Variant data type.

   1. Dim FirstName$ '相當於Dim FirstName as string 
   2. Dim age% 
   3. FullName$ = "John Smith" 
   4. Debug.Print FullName$ & " is " & age% & " years old." 

2. Visual Basic automatically initializes a new variable to its defau lt value when it is created. Numerical variables are set to zero (0), Boolean variables are initialized to False, string variables are set to the empty string (" "), and Date variables are set to December 30, 1899.

3. VBA has special functions that allow you to change the format of data.
   Format(expression, format)
   where expression is a value or variable you want to format and format is the type of format you want to apply.

4. The Option Explicit statement will cause Visual Basic to generate an error message when you try to run a procedure that contains undeclared variables. One big advantage of using Option Explicit is that misspellings of variable names will be detected at compile time. The Option Explicit statement must appear in a module before any procedures. To automatically include Option Explicit in every new module you create, follow these steps:
   1. Choose Tools | Options.
   2. Make sure the Require Variable Declaration check box is selected in the Options window (Editor tab).
   3. Choose OK to close the Options window.

5. When you declare a local variable with the Static statement, the value of the variable is preserved after the procedure in which the variable was declared has finished running. Static variables are reset when you quit the Microsoft Access application or when a run-time error occurs while the procedure is running.

6. Module-level variables are declared at the top of the module (above the first procedure definition) by using the Dim or Private statement（Dim和Private等價）. These variables are available to all of the procedures in the module in which they were declared, but are not available to procedures in other modules.
   If you declare a public variable in a module that contains the Option Private tatement, this variable will be available only to procedures in the current database. The Option Private statement can be used at the module level to indicate that the entire module is private.

7. Type Conversion:

   1. Sub AddTwoNums() 
   2.     Dim myPrompt As String 
   3.     Dim value1 As String 
   4.     Dim mySum As Single 
   6.     Const myTitle = "Enter data" 
   7.     myPrompt = "Enter a number:" 
   8.     value1 = InputBox(myPrompt, myTitle, 0) 
   9.     mySum = value1 + 2 
   10.     MsgBox mySum & " (" & value1 & " + 2)" 
   11. End Sub

    

   because the value1 variable’s data type is String, prior to using this variable’s data in the computation, Visual Basic goes to work behind the scenes to perform the data type conversion. Visual Basic has the brains to understand the need for conversion. Without it, the two incompatible data types (text and number) would generate a Type Mismatch error.

   • To avoid the Type Mismatch error, use the built-in CSng function to convert the string stored in the value1 variable to a Single type number. You would write the following statement:
     mysum = CSng(value1) + 2

8. The variables you’ve learned so far are used to store data. Object variables don’t store data. They store the location of the data. After declaring an object variable, you also have to assign a specific value to the object variable before you can use this variable in your procedure. You assign a value to the object variable by using the Set keyword.If you omit the word Set, Visual Basic will display the error message “Run-time error 91: Object variable or With block variable not set.”

9. you can quickly locate the definition of the variable by selecting the variable name and pressing Shift+F2. Alternately, you can choose View | Definition. Visual Basic will jump to the variable declaration line. To return your mouse pointer to its previous position, press Ctrl+Shift+F2 or choose View | Last Position.

10. Visual Basic has a built-in VarType function that returns an integer indicating the variable’s type.
    ?varType(age)
    

11. A constant, like a variable, has a scope.
    Const Age As Integer = 25, City As String = "Denver", PayCheck As Currency = 350

12. Exploring Intrinsic Constants
    1. In the Visual Basic Editor window, choose View | Object Browser.
    2. In the Project/Library list box, click the drop-down arrow and select the Access library.
    3. Enter constants as the search text in the Search Text box and either press Enter or click the Search button.Notice that the names of all the constants begin with the prefix “ac.”
    4. To look up VBA constants, choose VBA in the Project/Library list box.Notice that the names of the VBA built-in constants begin with the prefix “vb.”