11.3.1 Understanding Visual Basic Syntax
This section explains the most common syntax elements.
11.3.1.1 Syntax of Activate method
grammar:
In the syntax of the Activate method, object is a placeholder for the information provided, and the code in this example returns an object. For example, the following procedure activates the second window in the active document.
Sub MakeActive（）
Windows（2）.Activate
End Sub
11.3.1.2 Syntax of MsgBox function
Syntax: MsgBox (prompt[, buttons] [, title] [, helpfile, context])
In the syntax of the MsgBox function, the parameters in brackets are named parameters of this function. The parameters contained in square brackets are selective (without typing square brackets in Visual Basic). In the MsgBox function, the only parameter (prompt) that must be provided is the text as the prompt.
In the code, you can use location or name to specify parameters of functions and methods. If you use position to specify parameters, you must use commas to separate each parameter according to the order in the syntax, for example:
MsgBox "Your answer is correct!",0,"Answer Box"
If you specify a parameter by name, you must use the parameter name or follow the colon and equal sign (:=), and then add the parameter value. Named parameters can be specified in any order, for example:
MsgBox Title:="Answer Box", Prompt:="Your answer is correct!"
The syntax of functions and some methods encloses the parameters using parentheses. These functions and methods will return values, so the parameters must be enclosed in parentheses before they can be assigned to variables. If the return value is ignored or all parameters are not passed, parentheses can be used. If the method does not return a value, it does not need to close the parameters in parentheses. The above criteria apply whether they use named parameters or positional parameters.
In the following example, the return value of the MsgBox function is a number that is stored in the variable myVar to indicate the selected button. Because the return value is required, parentheses must be used when calling. Another message box is used to display the value of the variable.
Sub Question（）
myVar = MsgBox（Prompt:="I enjoy my job.", _
Title:="Answer Box", Buttons:="4"）
MsgBox myVar
End Sub
11.3.1.3 Syntax of option statements
Syntax: Option Compare {Binary | Text | Database}
In the syntax of an Option Compare statement, braces and vertical lines indicate mandatory choices in the three items (no braces are required in Visual Basic statements). For example, the following statement indicates that in a module, the comparison of strings is case-insensitive according to the order of the text.
Option Compare Text
11.3.1.4 Syntax of Dim statement
Syntax: Dim varname[ ( [subscripts] ) ] [As type] [, varname[([subscripts])] [As type]] . . .
In the syntax of Dim statements, Dim is a must-have keyword. The only necessary element is varname (variable name). For example, the following statement creates three variables: myVar, nextVar, and thirdVar. They are automatically declared as Variant variables.
Dim myVar, nextVar, thirdVar
The following example declares a String type variable. It contains data types, which saves memory and helps identify errors from the code.
Dim myAnswer As String
If several variables are declared in a statement, the data type of each variable must be included. If the variable is declared with less data types, it will be automatically declared as Variant.
Dim x As Integer, y As Integer, z As Integer
In the following statements, both x and y are specified as Variant data types, and only z is specified as String data types.
Dim x, y As Integer，Z As String
If an array variable is declared, it must include parentheses, but the subscript is optional. The following statement defines a dynamic array myArray.
Dim myArray（）
11.3.2 Effectively utilize data syntax
Table 11-1 lists the data types supported by VBA, as well as the size and range of storage space.
Notice:
Any array of data type requires 20 bytes of memory space, plus the dimension of each array takes up 4 bytes, plus the space occupied by the data itself. The memory space occupied by the data can be calculated by multiplying the number of data elements by the size of each element. For example, the data in a one-dimensional array composed of 4 2-byte Integer data elements accounts for 8 bytes. These 8 bytes plus an additional 24 bytes make the total memory space required for this array to be 32 bytes. A Variant containing an array requires 12 bytes more than a single array.
Notice:
Use the StrConv function to convert string data from one type to another.
Undeclared variables are specified as Variant data type unless otherwise specified. This data type makes writing programs easier, but it is not always the most efficient data type in use.
Other data types must be considered if the following situations are present:
The program is very large and uses a lot of variables.
The execution speed of the program must be as fast as possible.
Write the data directly into a random storage file.
In addition to Variant, supported data types include Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, and String. You can use the Dim statement to declare a variable of a specified type, for example:
Dim X As Integer
The above statement states that the variable X is an integer with a range between -32,768 and 32,767. If you try to set a value outside this range to X, an error will occur. If you try to specify a score to X, the score will be rounded automatically. For example:
X = 32768 ' An error occurred. X = 5.9 ' Set x = 6.
11.3.3 Naming rules for Visual Basic
When naming procedures, constants, variables, and parameters in Visual Basic modules, the following rules can be used:
The first character must use English letters.
You cannot use spaces, periods (.), exclamation marks (!), or @, &, $, # and other characters in your name.
The length of the name cannot exceed 255 characters.
Generally, the name used cannot be the same as the name of the Function procedures, statements, and methods of Visual Basic itself. You must exit using the same name as the keyword in the programming language. If the intrinsic language function, statement or method used conflicts with the specified name, it must be identified displayably. The name of the built-in function, statement, or method is routinely preceded by the name of the associated type library. For example, if there is a variable called Left, you can only call the Left function with .
Duplicate names cannot be used in the same hierarchy of the scope. For example, two variables named age cannot be declared in the same process. However, a private variable named age and a process-level variable named age can be declared in the same module.
Notice:
Visual Basic is case-sensitive, but it retains capitalization in statements whose names are declared.
11.3.4 Declare variables, constants, and arrays
11.3.4.1 Declare variables
A Dim statement is usually used to declare variables. A declaration statement can be placed in a process to create variables at the level of the process. Or in the declaration section you can place it on top of the module to create variables that are at the module level.
The following example creates the variable strName and is specified as the String data type. Dim strName As String
If the statement appears in the process, the variable strName can only be used in this process. If the statement appears in the declaration part of the module, the variable strName can be used by all processes in this module, but cannot be used by processes contained in different modules in the same project. In order for variables to be used by all processes in the project, a Public statement is preceded by the variable, as in the following example:
Public strName As String
Variables can be declared as one of the following data types: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String (variable length string), String * length (fixed length string), Object, or Variant. If no data type is specified, the Variant data type is given the default. You can also use the Type statement to create user-defined types.
Several variables can be declared in one statement. In order to specify the data type, the data type of each variable must be included. In the following statement, the variables intX, intY, and intZ are declared as Integer types.
Dim intX As Integer, intY As Integer, intZ As Integer
In the following statement, the variables intX and intY are declared as Variant types; only intZ is declared as Integer types.
Dim intX, intY, intZ As Integer
In a declaration statement, it is not necessary to provide the data type of the variable. If the data type is omitted, the variable is set to Variant type.
Use Public statements: Public statements can be used to declare public module-level variables.
Public strName As String
Public variables can be used in any process in the project. If a public variable is declared in a standard module or a class module, it can also be used in any project that references to the project to which this public variable belongs.
Use Private statement: You can use Private statements to declare private module-level variables.
Private MyName As String
Private variables can only be used in procedures in the same module.
Notice:
Using Dim statements at the module level is the same as using Private statements. However, using Private statements makes it easier to read and interpret code.
Use Static statement: When a Static statement is used instead of a Dim statement, the declared variable still retains its original value when invoked.
Using the Option Explicit statement: In Visual Basic, you can simply use an assignment statement to implicitly declare variables. All implicit declaration variables are of type Variant, and type Variant requires more memory resources than most other types of variables. If the variable is explicitly declared to be the specified data type, the application will be more efficient. Explicitly declaring all variables reduces the incidence of naming conflicts and misspellings.
If you do not want Visual Basic to generate an implicit declaration, you can place the Option Explicit statement before all procedures in the module. This statement requires explicit declaration of all variables in the module. If the module contains an Option Explicit statement, it will cause a compile time error when Visual Basic encounters a previously undefined variable or spelling error.
An option in the Visual Basic program environment can be set to automatically include Option Explicit statements in all new modules.
Note: Fixed-size arrays and dynamic arrays need to be explicitly declared.
Declare an object variable for automation: When using one application to control objects in another application, a reference to the type library of other applications should be set. If you set a reference, you can declare object variables based on the type they most often specify. For example, if you are in Microsoft Word, when you make a reference setting for the Microsoft Excel type library, you can declare a variable of type Worksheet in Microsoft Word to represent a Worksheet object in Microsoft Excel.
If you use other applications to control Microsoft Access objects, in most cases, object variables can be declared based on the type they most often specify. You can also use the keyword New to automatically generate a new instance of an object. However, you may want to indicate that it is a Microsoft Access object. For example, when an object variable is declared in Microsoft Visual Basic to represent a Microsoft Access form, it must be distinguished from being a Microsoft Access Form object or a Visual Basic Form object. Therefore, the statement that declares a variable must contain the name of the type library, as shown in the following example:
Dim frmOrders As New
Some applications do not recognize special Microsoft Access object types. Even if a reference to the Microsoft Access type library has been set in these applications, you must declare all Microsoft Access object variables of type Object. You cannot use the New keyword to create a new instance of this object. The following example shows how to declare a variable to represent a Microsoft Access Application object that does not recognize the Microsoft Access object type. The application then creates an instance of the Application object.
Dim appAccess As Object
Set appAccess = CreateObject（""）
11.3.4.2 Declare constants
If you want to declare a constant, you can use the Const statement to declare and set its value; and after the constant is declared, it cannot be changed or assigned a new value.
In the declaration section, constants can be declared during the process or at the top of the module. Constants in the module level default to private. To declare a public module-level constant, you can prepend the Const statement with the keyword Public. You can also use the Public keyword before the Const statement to explicitly declare a private constant, so that we can read and interpret the code more easily.
The following example declares that a Public constant conAge is of type Integer and specifies that it has a value of 34.
Public Const conAge As Integer = 34
Constants can be declared as one of the following data types: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, or Variant. Because the value of the constant is already known, you can specify the data type in the Const statement. Details about data types.
Several constants can be declared in one statement. In order to specify a data type, the data type of each constant must be included. In the following statement, the constants conAge and conWage are declared as Integer types.
Const conAge As Integer = 34, conWage As Currency = 35000
11.3.4.3 Declare array
The array is declared in the same way as other variables, and it can be declared using Dim, Static, Private, or Public statements. Scalar variables (non-arrays) differ from array variables in that they usually have to specify the size of the array. If the size of the array is specified, it is a fixed-size array. If the size of the array can be changed when the program runs, it is a dynamic array.
Whether the array is indexed from 0 or 1 is based on the settings of the Option Base statement. If Option Base is not specified as 1, the array index starts at 0.
1. Declare a fixed-size array
The following line of code declares a fixed-size array, which is an Integer array with 11 rows multiplied by 11 columns:
Dim MyArray（10, 10） As Integer
The first parameter represents the row; the second parameter represents the column.
Like other variable declarations, unless a data type is specified to the array, the data type of the element in the array is declared as Variant. The numeric Variant element of each array in the array takes up 16 bytes. Each string-type Variant element takes up 22 bytes. In order to make the code written as concise and clear as possible, the declared array must be clearly defined as a certain data type rather than a Variant. The following lines of code compare the sizes of several different arrays:
' Integer array uses 22 bytes (11 elements* 2 bytes).
ReDim MyIntegerArray（10） As Integer
' Double precision array uses 88 bytes (11 elements * 8 bytes).
ReDim MyDoubleArray（10） As Double
' Variable arrays use at least 176 bytes (11 elements * 16 bytes).
ReDim MyVariantArray（10）
' Integer arrays use 100 * 100 * 2 bytes (20,000 bytes).
ReDim MyIntegerArray （99, 99） As Integer
' Double precision array uses 100 * 100 * 8 bytes (80,000 bytes).
ReDim MyDoubleArray （99, 99） As Double
' Variable arrays use at least 160,000 bytes (100 * 100 * 16 bytes).
ReDim MyVariantArray（99, 99）
The maximum value of an array variable is based on how much memory is available in the operating system used. If the array size used exceeds the total amount of memory available in the system, the speed will be slower because data must be read and written back from disk.
2. Declare dynamic arrays
If declared as a dynamic array, the array size can be changed when executing the code. The array can be declared using Static, Dim, Private, or Public statements and make the brackets empty, as shown in the following example.
Dim sngArray（） As Single
Notice:
You can use the ReDim statement during the process to make implicit array declarations. Be careful when using ReDim statements and do not misspel the name of the array. Otherwise, even if there is an Option Explicit statement in the module, a second array will still be generated.
For array ranges in the process, you can use the ReDim statement to change its dimensions, define the number of elements and the underlying binding of each dimension. Whenever needed, you can use the ReDim statement to change the dynamic array. However, when doing this action, the values present in the array are lost. To save the original values in an array, you can use the ReDim Preserve statement to augment the array. For example, the following statement expands the varArray array by 10 elements, and the current value in the original array does not disappear.
ReDim Preserve varArray（UBound（varArray） + 10）
Notice:
When using the Preserve keyword for dynamic arrays, you can only change the upper bound of the last dimension, but not the number of dimensions.
11.3.5 Processes and calls
11.3.5.1 Function Process
The Function process is a series of Visual Basic statements contained in Function and End Function statements. The Function procedure is similar to the Sub procedure, but the function can return a value. The Function procedure can be called via the caller procedure by passing parameters such as constants, variables, or expressions. If a Function procedure has no parameters, its Function statement must contain an empty parentheses. A function specifies a value in one or more statements of the procedure to return the value to the function name.
In the following example, the Celsius function calculates the Celsius temperature based on the Fahrenheit temperature. When the Main procedure calls this function, a variable containing the parameter value will be passed to the function. The calculation result will be returned to the invoked process and displayed in a message box.
Sub Main（）
temp = （Prompt:= _
"Please enter the temperature in degrees F.",
Type:=1）
MsgBox "The temperature is " & Celsius（temp） & "
degrees C."
End Sub
Function Celsius（fDegrees）
Celsius = （fDegrees - 32） * 5 / 9
End Function
11.3.5.2 Sub Process
A Sub process is a series of Visual Basic statements contained in Sub and End Sub statements that perform actions but cannot return a value. The Sub process can call it with parameters, such as constants, variables, or expressions. If a Sub procedure has no parameters, its Sub statement must contain an empty parentheses.
During the following Sub process, each line has comments to explain its function:
'The declaration process is named GetInfo
'The Sub process has no parameters
Sub GetInfo（）
'Declare the string variable named answer
Dim answer As String
'Specify the return value of the InputBox function to answer
answer = InputBox（Prompt:="What is your name?"）
'Conditions If...Then...Else Statement
If answer = Empty Then
'Call the MsgBox function
MsgBox Prompt:="You did not enter a name."
Else
'MsgBox function is connected to the answer variable.
MsgBox Prompt:="Your name is " & answer
'End If...Then...Else statement
End If
'End Sub Process
End Sub
11.3.5.3 Attribute Process
The attribute process is a series of Visual Basic statements that allow programmers to create and manipulate custom attributes. The attribute process can be used to create read-only properties for forms, standard modules, and class modules. It can be used instead of Public variables in the code, and the above actions should be performed when setting the property value.
Unlike Public variables, the property procedure in the object browser will have some help strings assigned to custom properties.
When a property procedure is created, it becomes an attribute of the module contained in the procedure. Visual Basic provides the following three types of Property procedures:
Property Let is a process used to set property values.
Property Get The process used to return property values.
Property Set is used to set the process of referencing objects.
The syntax for declaring attribute procedures is as follows:
[Public | Private] [Static] Property {Get | Let | Set}
propertyname_ [（arguments）] [As type]
statements
End Property
Property procedures are usually used in pairs: Property Let and Property Get, and Property Set and Property Get. Declaring a Property Get procedure alone is like declaring a read-only property. When used together, it is only useful for Variant variables, because only Variant can contain information for an object or other data types. Property Set is intended to be used on objects; Property Let is not.
The parameters required in the Property process declaration are as follows:
Property Get Property Get propname（1, ..., n） As type
Property Let Property Let propname（1, ..., n, n+1）
Property Set Property Set propname（1, ..., n, n+1）
In the process of having the same name attribute, the same name and data type must be shared from the first to the last parameter (1, ..., n).
The property Get process declaration requires one less parameter than the relevant Property Let and Property Set declarations. The data type of the Property Get process must be the same as the type of the associated Property Let and the last (n+1) parameters in the Property Set declaration. For example, if you declare the following Property Let procedure, the name and data type of the parameter used by the Property Get declaration must be the same as the data type used in the Property Let procedure.
Property Let Names（intX As Integer, intY As Integer,
varZ As Variant）
' Execute statement.
End Property
Property Get Names（intX As Integer, intY As Integer）
As Variant
' Execute statement.
End Property
In the Property Set declaration, the data type of the last parameter must be an object type or a Variant.
11.3.5.4 Calling Sub and Function Processes
When calling a Sub procedure from another procedure, you must type the procedure name and any required parameter values. The Call statement does not require it, but if you use it, any parameters must be enclosed in parentheses.
You can use the Sub process to organize other processes, so they can be easily understood and debugged. In the following example, the Sub procedure Main passes the parameter value 56 to call the Sub procedure MultiBeep. After running MultiBeep, the control returns Main, and Main calls the Sub process Message. Message displays a message box; when the "OK" key is pressed, the control will return to Main, and then Main exits execution.
Sub Main（）
MultiBeep 56
Message
End Sub
Sub MultiBeep（numbeeps）
For counter = 1 To numbeeps
Beep
Next counter
End Sub
Sub Message（）
MsgBox "Time to take a break!"
End Sub
1. Calling a Sub procedure with multiple parameters
The following example shows two different ways to call a Sub procedure with multiple parameters. When HouseCalc is called the second time, because the Call statement is used, the parameters need to be enclosed in parentheses.
Sub Main（）
HouseCalc 99800, 43100
Call HouseCalc（380950, 49500）
End Sub
Sub HouseCalc（price As Single, wage As Single）
If 2.5 * wage <= 0.8 * price Then
MsgBox "You cannot afford this house."
Else
MsgBox "This house is affordable."
End If
End Sub
Use brackets when calling the Function procedure.
In order to use the return value of the function, the function must be specified to the variable and the parameters must be enclosed in brackets; as shown in the following example:
Answer3 = MsgBox（"Are you happy with your salary?", 4, "Question 3"）
If you don't care about the return value of the function, you can call the function by calling the Sub procedure. As shown in the following example, you can omit parentheses, list parameters and do not assign functions to variables:
MsgBox "Task Completed!", 0, "Task Box"
Note that if brackets are included in the above example, the statement will cause a syntax error.
2. Pass named parameters
Statements in Sub or Function procedures can use named parameters to pass values to the called procedures. There are two ways to pass parameters: by value and by address. Passing by value is just passing a copy of the parameter, creating a variable with the same type and content in the function. Changes to the parameters made by parameters within the process body will not affect the actual parameters; passing by address is not the case. It passes by the address, and all changes to the parameters in the process will affect the actual parameters. ". The composition of a named parameter is composed of the parameter name immediately followed by a colon (:=) and an equal sign, and then a value is specified to the parameter.
The following example uses named parameters to call the MsgBox function that does not have a return value.
MsgBox Title:="Task Box", Prompt:="Task Completed!"
The following example calls the MsgBox function using named parameters. Assign the return value to the variable answer3.
answer3 = MsgBox（Title:="Question 3", _
Prompt:="Are you happy with your salary?", Buttons:=4）
11.3.5.5 Calling attribute procedure
Table 11-2 lists the syntax for calling attribute procedures.
When a Property Let or Property Set procedure is called, there will always be a parameter to the right of the equal sign (=).
When a Property Let or Property Set procedure is declared with multiple parameters, Visual Basic passes the right parameter of the call to the last parameter in the Property Let or PropertySet declaration. For example, Figure 11-18 shows the relationship between parameters in a Property procedure call and parameters in a Property Let declaration:
In fact, combining the attribute procedure with multiple parameters is only used when creating an array of attributes.
This section explains the most common syntax elements.
11.3.1.1 Syntax of Activate method
grammar:
In the syntax of the Activate method, object is a placeholder for the information provided, and the code in this example returns an object. For example, the following procedure activates the second window in the active document.
Sub MakeActive（）
Windows（2）.Activate
End Sub
11.3.1.2 Syntax of MsgBox function
Syntax: MsgBox (prompt[, buttons] [, title] [, helpfile, context])
In the syntax of the MsgBox function, the parameters in brackets are named parameters of this function. The parameters contained in square brackets are selective (without typing square brackets in Visual Basic). In the MsgBox function, the only parameter (prompt) that must be provided is the text as the prompt.
In the code, you can use location or name to specify parameters of functions and methods. If you use position to specify parameters, you must use commas to separate each parameter according to the order in the syntax, for example:
MsgBox "Your answer is correct!",0,"Answer Box"
If you specify a parameter by name, you must use the parameter name or follow the colon and equal sign (:=), and then add the parameter value. Named parameters can be specified in any order, for example:
MsgBox Title:="Answer Box", Prompt:="Your answer is correct!"
The syntax of functions and some methods encloses the parameters using parentheses. These functions and methods will return values, so the parameters must be enclosed in parentheses before they can be assigned to variables. If the return value is ignored or all parameters are not passed, parentheses can be used. If the method does not return a value, it does not need to close the parameters in parentheses. The above criteria apply whether they use named parameters or positional parameters.
In the following example, the return value of the MsgBox function is a number that is stored in the variable myVar to indicate the selected button. Because the return value is required, parentheses must be used when calling. Another message box is used to display the value of the variable.
Sub Question（）
myVar = MsgBox（Prompt:="I enjoy my job.", _
Title:="Answer Box", Buttons:="4"）
MsgBox myVar
End Sub
11.3.1.3 Syntax of option statements
Syntax: Option Compare {Binary | Text | Database}
In the syntax of an Option Compare statement, braces and vertical lines indicate mandatory choices in the three items (no braces are required in Visual Basic statements). For example, the following statement indicates that in a module, the comparison of strings is case-insensitive according to the order of the text.
Option Compare Text
11.3.1.4 Syntax of Dim statement
Syntax: Dim varname[ ( [subscripts] ) ] [As type] [, varname[([subscripts])] [As type]] . . .
In the syntax of Dim statements, Dim is a must-have keyword. The only necessary element is varname (variable name). For example, the following statement creates three variables: myVar, nextVar, and thirdVar. They are automatically declared as Variant variables.
Dim myVar, nextVar, thirdVar
The following example declares a String type variable. It contains data types, which saves memory and helps identify errors from the code.
Dim myAnswer As String
If several variables are declared in a statement, the data type of each variable must be included. If the variable is declared with less data types, it will be automatically declared as Variant.
Dim x As Integer, y As Integer, z As Integer
In the following statements, both x and y are specified as Variant data types, and only z is specified as String data types.
Dim x, y As Integer，Z As String
If an array variable is declared, it must include parentheses, but the subscript is optional. The following statement defines a dynamic array myArray.
Dim myArray（）
11.3.2 Effectively utilize data syntax
Table 11-1 lists the data types supported by VBA, as well as the size and range of storage space.
Notice:
Any array of data type requires 20 bytes of memory space, plus the dimension of each array takes up 4 bytes, plus the space occupied by the data itself. The memory space occupied by the data can be calculated by multiplying the number of data elements by the size of each element. For example, the data in a one-dimensional array composed of 4 2-byte Integer data elements accounts for 8 bytes. These 8 bytes plus an additional 24 bytes make the total memory space required for this array to be 32 bytes. A Variant containing an array requires 12 bytes more than a single array.
Notice:
Use the StrConv function to convert string data from one type to another.
Undeclared variables are specified as Variant data type unless otherwise specified. This data type makes writing programs easier, but it is not always the most efficient data type in use.
Other data types must be considered if the following situations are present:
The program is very large and uses a lot of variables.
The execution speed of the program must be as fast as possible.
Write the data directly into a random storage file.
In addition to Variant, supported data types include Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, and String. You can use the Dim statement to declare a variable of a specified type, for example:
Dim X As Integer
The above statement states that the variable X is an integer with a range between -32,768 and 32,767. If you try to set a value outside this range to X, an error will occur. If you try to specify a score to X, the score will be rounded automatically. For example:
X = 32768 ' An error occurred. X = 5.9 ' Set x = 6.
11.3.3 Naming rules for Visual Basic
When naming procedures, constants, variables, and parameters in Visual Basic modules, the following rules can be used:
The first character must use English letters.
You cannot use spaces, periods (.), exclamation marks (!), or @, &, $, # and other characters in your name.
The length of the name cannot exceed 255 characters.
Generally, the name used cannot be the same as the name of the Function procedures, statements, and methods of Visual Basic itself. You must exit using the same name as the keyword in the programming language. If the intrinsic language function, statement or method used conflicts with the specified name, it must be identified displayably. The name of the built-in function, statement, or method is routinely preceded by the name of the associated type library. For example, if there is a variable called Left, you can only call the Left function with .
Duplicate names cannot be used in the same hierarchy of the scope. For example, two variables named age cannot be declared in the same process. However, a private variable named age and a process-level variable named age can be declared in the same module.
Notice:
Visual Basic is case-sensitive, but it retains capitalization in statements whose names are declared.
11.3.4 Declare variables, constants, and arrays
11.3.4.1 Declare variables
A Dim statement is usually used to declare variables. A declaration statement can be placed in a process to create variables at the level of the process. Or in the declaration section you can place it on top of the module to create variables that are at the module level.
The following example creates the variable strName and is specified as the String data type. Dim strName As String
If the statement appears in the process, the variable strName can only be used in this process. If the statement appears in the declaration part of the module, the variable strName can be used by all processes in this module, but cannot be used by processes contained in different modules in the same project. In order for variables to be used by all processes in the project, a Public statement is preceded by the variable, as in the following example:
Public strName As String
Variables can be declared as one of the following data types: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String (variable length string), String * length (fixed length string), Object, or Variant. If no data type is specified, the Variant data type is given the default. You can also use the Type statement to create user-defined types.
Several variables can be declared in one statement. In order to specify the data type, the data type of each variable must be included. In the following statement, the variables intX, intY, and intZ are declared as Integer types.
Dim intX As Integer, intY As Integer, intZ As Integer
In the following statement, the variables intX and intY are declared as Variant types; only intZ is declared as Integer types.
Dim intX, intY, intZ As Integer
In a declaration statement, it is not necessary to provide the data type of the variable. If the data type is omitted, the variable is set to Variant type.
Use Public statements: Public statements can be used to declare public module-level variables.
Public strName As String
Public variables can be used in any process in the project. If a public variable is declared in a standard module or a class module, it can also be used in any project that references to the project to which this public variable belongs.
Use Private statement: You can use Private statements to declare private module-level variables.
Private MyName As String
Private variables can only be used in procedures in the same module.
Notice:
Using Dim statements at the module level is the same as using Private statements. However, using Private statements makes it easier to read and interpret code.
Use Static statement: When a Static statement is used instead of a Dim statement, the declared variable still retains its original value when invoked.
Using the Option Explicit statement: In Visual Basic, you can simply use an assignment statement to implicitly declare variables. All implicit declaration variables are of type Variant, and type Variant requires more memory resources than most other types of variables. If the variable is explicitly declared to be the specified data type, the application will be more efficient. Explicitly declaring all variables reduces the incidence of naming conflicts and misspellings.
If you do not want Visual Basic to generate an implicit declaration, you can place the Option Explicit statement before all procedures in the module. This statement requires explicit declaration of all variables in the module. If the module contains an Option Explicit statement, it will cause a compile time error when Visual Basic encounters a previously undefined variable or spelling error.
An option in the Visual Basic program environment can be set to automatically include Option Explicit statements in all new modules.
Note: Fixed-size arrays and dynamic arrays need to be explicitly declared.
Declare an object variable for automation: When using one application to control objects in another application, a reference to the type library of other applications should be set. If you set a reference, you can declare object variables based on the type they most often specify. For example, if you are in Microsoft Word, when you make a reference setting for the Microsoft Excel type library, you can declare a variable of type Worksheet in Microsoft Word to represent a Worksheet object in Microsoft Excel.
If you use other applications to control Microsoft Access objects, in most cases, object variables can be declared based on the type they most often specify. You can also use the keyword New to automatically generate a new instance of an object. However, you may want to indicate that it is a Microsoft Access object. For example, when an object variable is declared in Microsoft Visual Basic to represent a Microsoft Access form, it must be distinguished from being a Microsoft Access Form object or a Visual Basic Form object. Therefore, the statement that declares a variable must contain the name of the type library, as shown in the following example:
Dim frmOrders As New
Some applications do not recognize special Microsoft Access object types. Even if a reference to the Microsoft Access type library has been set in these applications, you must declare all Microsoft Access object variables of type Object. You cannot use the New keyword to create a new instance of this object. The following example shows how to declare a variable to represent a Microsoft Access Application object that does not recognize the Microsoft Access object type. The application then creates an instance of the Application object.
Dim appAccess As Object
Set appAccess = CreateObject（""）
11.3.4.2 Declare constants
If you want to declare a constant, you can use the Const statement to declare and set its value; and after the constant is declared, it cannot be changed or assigned a new value.
In the declaration section, constants can be declared during the process or at the top of the module. Constants in the module level default to private. To declare a public module-level constant, you can prepend the Const statement with the keyword Public. You can also use the Public keyword before the Const statement to explicitly declare a private constant, so that we can read and interpret the code more easily.
The following example declares that a Public constant conAge is of type Integer and specifies that it has a value of 34.
Public Const conAge As Integer = 34
Constants can be declared as one of the following data types: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, or Variant. Because the value of the constant is already known, you can specify the data type in the Const statement. Details about data types.
Several constants can be declared in one statement. In order to specify a data type, the data type of each constant must be included. In the following statement, the constants conAge and conWage are declared as Integer types.
Const conAge As Integer = 34, conWage As Currency = 35000
11.3.4.3 Declare array
The array is declared in the same way as other variables, and it can be declared using Dim, Static, Private, or Public statements. Scalar variables (non-arrays) differ from array variables in that they usually have to specify the size of the array. If the size of the array is specified, it is a fixed-size array. If the size of the array can be changed when the program runs, it is a dynamic array.
Whether the array is indexed from 0 or 1 is based on the settings of the Option Base statement. If Option Base is not specified as 1, the array index starts at 0.
1. Declare a fixed-size array
The following line of code declares a fixed-size array, which is an Integer array with 11 rows multiplied by 11 columns:
Dim MyArray（10, 10） As Integer
The first parameter represents the row; the second parameter represents the column.
Like other variable declarations, unless a data type is specified to the array, the data type of the element in the array is declared as Variant. The numeric Variant element of each array in the array takes up 16 bytes. Each string-type Variant element takes up 22 bytes. In order to make the code written as concise and clear as possible, the declared array must be clearly defined as a certain data type rather than a Variant. The following lines of code compare the sizes of several different arrays:
' Integer array uses 22 bytes (11 elements* 2 bytes).
ReDim MyIntegerArray（10） As Integer
' Double precision array uses 88 bytes (11 elements * 8 bytes).
ReDim MyDoubleArray（10） As Double
' Variable arrays use at least 176 bytes (11 elements * 16 bytes).
ReDim MyVariantArray（10）
' Integer arrays use 100 * 100 * 2 bytes (20,000 bytes).
ReDim MyIntegerArray （99, 99） As Integer
' Double precision array uses 100 * 100 * 8 bytes (80,000 bytes).
ReDim MyDoubleArray （99, 99） As Double
' Variable arrays use at least 160,000 bytes (100 * 100 * 16 bytes).
ReDim MyVariantArray（99, 99）
The maximum value of an array variable is based on how much memory is available in the operating system used. If the array size used exceeds the total amount of memory available in the system, the speed will be slower because data must be read and written back from disk.
2. Declare dynamic arrays
If declared as a dynamic array, the array size can be changed when executing the code. The array can be declared using Static, Dim, Private, or Public statements and make the brackets empty, as shown in the following example.
Dim sngArray（） As Single
Notice:
You can use the ReDim statement during the process to make implicit array declarations. Be careful when using ReDim statements and do not misspel the name of the array. Otherwise, even if there is an Option Explicit statement in the module, a second array will still be generated.
For array ranges in the process, you can use the ReDim statement to change its dimensions, define the number of elements and the underlying binding of each dimension. Whenever needed, you can use the ReDim statement to change the dynamic array. However, when doing this action, the values present in the array are lost. To save the original values in an array, you can use the ReDim Preserve statement to augment the array. For example, the following statement expands the varArray array by 10 elements, and the current value in the original array does not disappear.
ReDim Preserve varArray（UBound（varArray） + 10）
Notice:
When using the Preserve keyword for dynamic arrays, you can only change the upper bound of the last dimension, but not the number of dimensions.
11.3.5 Processes and calls
11.3.5.1 Function Process
The Function process is a series of Visual Basic statements contained in Function and End Function statements. The Function procedure is similar to the Sub procedure, but the function can return a value. The Function procedure can be called via the caller procedure by passing parameters such as constants, variables, or expressions. If a Function procedure has no parameters, its Function statement must contain an empty parentheses. A function specifies a value in one or more statements of the procedure to return the value to the function name.
In the following example, the Celsius function calculates the Celsius temperature based on the Fahrenheit temperature. When the Main procedure calls this function, a variable containing the parameter value will be passed to the function. The calculation result will be returned to the invoked process and displayed in a message box.
Sub Main（）
temp = （Prompt:= _
"Please enter the temperature in degrees F.",
Type:=1）
MsgBox "The temperature is " & Celsius（temp） & "
degrees C."
End Sub
Function Celsius（fDegrees）
Celsius = （fDegrees - 32） * 5 / 9
End Function
11.3.5.2 Sub Process
A Sub process is a series of Visual Basic statements contained in Sub and End Sub statements that perform actions but cannot return a value. The Sub process can call it with parameters, such as constants, variables, or expressions. If a Sub procedure has no parameters, its Sub statement must contain an empty parentheses.
During the following Sub process, each line has comments to explain its function:
'The declaration process is named GetInfo
'The Sub process has no parameters
Sub GetInfo（）
'Declare the string variable named answer
Dim answer As String
'Specify the return value of the InputBox function to answer
answer = InputBox（Prompt:="What is your name?"）
'Conditions If...Then...Else Statement
If answer = Empty Then
'Call the MsgBox function
MsgBox Prompt:="You did not enter a name."
Else
'MsgBox function is connected to the answer variable.
MsgBox Prompt:="Your name is " & answer
'End If...Then...Else statement
End If
'End Sub Process
End Sub
11.3.5.3 Attribute Process
The attribute process is a series of Visual Basic statements that allow programmers to create and manipulate custom attributes. The attribute process can be used to create read-only properties for forms, standard modules, and class modules. It can be used instead of Public variables in the code, and the above actions should be performed when setting the property value.
Unlike Public variables, the property procedure in the object browser will have some help strings assigned to custom properties.
When a property procedure is created, it becomes an attribute of the module contained in the procedure. Visual Basic provides the following three types of Property procedures:
Property Let is a process used to set property values.
Property Get The process used to return property values.
Property Set is used to set the process of referencing objects.
The syntax for declaring attribute procedures is as follows:
[Public | Private] [Static] Property {Get | Let | Set}
propertyname_ [（arguments）] [As type]
statements
End Property
Property procedures are usually used in pairs: Property Let and Property Get, and Property Set and Property Get. Declaring a Property Get procedure alone is like declaring a read-only property. When used together, it is only useful for Variant variables, because only Variant can contain information for an object or other data types. Property Set is intended to be used on objects; Property Let is not.
The parameters required in the Property process declaration are as follows:
Property Get Property Get propname（1, ..., n） As type
Property Let Property Let propname（1, ..., n, n+1）
Property Set Property Set propname（1, ..., n, n+1）
In the process of having the same name attribute, the same name and data type must be shared from the first to the last parameter (1, ..., n).
The property Get process declaration requires one less parameter than the relevant Property Let and Property Set declarations. The data type of the Property Get process must be the same as the type of the associated Property Let and the last (n+1) parameters in the Property Set declaration. For example, if you declare the following Property Let procedure, the name and data type of the parameter used by the Property Get declaration must be the same as the data type used in the Property Let procedure.
Property Let Names（intX As Integer, intY As Integer,
varZ As Variant）
' Execute statement.
End Property
Property Get Names（intX As Integer, intY As Integer）
As Variant
' Execute statement.
End Property
In the Property Set declaration, the data type of the last parameter must be an object type or a Variant.
11.3.5.4 Calling Sub and Function Processes
When calling a Sub procedure from another procedure, you must type the procedure name and any required parameter values. The Call statement does not require it, but if you use it, any parameters must be enclosed in parentheses.
You can use the Sub process to organize other processes, so they can be easily understood and debugged. In the following example, the Sub procedure Main passes the parameter value 56 to call the Sub procedure MultiBeep. After running MultiBeep, the control returns Main, and Main calls the Sub process Message. Message displays a message box; when the "OK" key is pressed, the control will return to Main, and then Main exits execution.
Sub Main（）
MultiBeep 56
Message
End Sub
Sub MultiBeep（numbeeps）
For counter = 1 To numbeeps
Beep
Next counter
End Sub
Sub Message（）
MsgBox "Time to take a break!"
End Sub
1. Calling a Sub procedure with multiple parameters
The following example shows two different ways to call a Sub procedure with multiple parameters. When HouseCalc is called the second time, because the Call statement is used, the parameters need to be enclosed in parentheses.
Sub Main（）
HouseCalc 99800, 43100
Call HouseCalc（380950, 49500）
End Sub
Sub HouseCalc（price As Single, wage As Single）
If 2.5 * wage <= 0.8 * price Then
MsgBox "You cannot afford this house."
Else
MsgBox "This house is affordable."
End If
End Sub
Use brackets when calling the Function procedure.
In order to use the return value of the function, the function must be specified to the variable and the parameters must be enclosed in brackets; as shown in the following example:
Answer3 = MsgBox（"Are you happy with your salary?", 4, "Question 3"）
If you don't care about the return value of the function, you can call the function by calling the Sub procedure. As shown in the following example, you can omit parentheses, list parameters and do not assign functions to variables:
MsgBox "Task Completed!", 0, "Task Box"
Note that if brackets are included in the above example, the statement will cause a syntax error.
2. Pass named parameters
Statements in Sub or Function procedures can use named parameters to pass values to the called procedures. There are two ways to pass parameters: by value and by address. Passing by value is just passing a copy of the parameter, creating a variable with the same type and content in the function. Changes to the parameters made by parameters within the process body will not affect the actual parameters; passing by address is not the case. It passes by the address, and all changes to the parameters in the process will affect the actual parameters. ". The composition of a named parameter is composed of the parameter name immediately followed by a colon (:=) and an equal sign, and then a value is specified to the parameter.
The following example uses named parameters to call the MsgBox function that does not have a return value.
MsgBox Title:="Task Box", Prompt:="Task Completed!"
The following example calls the MsgBox function using named parameters. Assign the return value to the variable answer3.
answer3 = MsgBox（Title:="Question 3", _
Prompt:="Are you happy with your salary?", Buttons:=4）
11.3.5.5 Calling attribute procedure
Table 11-2 lists the syntax for calling attribute procedures.
When a Property Let or Property Set procedure is called, there will always be a parameter to the right of the equal sign (=).
When a Property Let or Property Set procedure is declared with multiple parameters, Visual Basic passes the right parameter of the call to the last parameter in the Property Let or PropertySet declaration. For example, Figure 11-18 shows the relationship between parameters in a Property procedure call and parameters in a Property Let declaration:
In fact, combining the attribute procedure with multiple parameters is only used when creating an array of attributes.