VBA Programming Basics _VBA

11.3.1 Understand visual Basic syntax
This section explains the most common syntax elements.
Syntax of 11.3.1.1 Activate method
Syntax: Object. Activate
In the syntax of the Activate method, object is a placeholder for the information provided, and in this case the code returns an object. For example, the following procedure activates the second window in the active document.
Sub makeactive ()
Windows (2). Activate
End Sub

Syntax for 11.3.1.2 MsgBox functions
Syntax: MsgBox (prompt[, buttons] [, title] [, HelpFile, context])
In the syntax of the MsgBox function, the arguments in parentheses are named arguments for this function. The brackets contain parameters that are optional (you do not need to type square brackets in visual Basic). In the MsgBox function, the only argument (prompt) that must be supplied is the text to be prompted.
You can use a location or name in your code to specify the parameters of a function and a method. If you use a location to specify parameters, you must use commas to separate each argument 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. You can specify named parameters in any order, for example:
MsgBox title:= "Answer box", prompt:= "Your Answer is correct!"
Functions, and the syntax of some methods, use parentheses to close the arguments. These functions and methods return a value, so you must enclose the arguments with parentheses before you can assign values to variables. If you omit the return value or do not pass all the arguments, you can use no parentheses. If the method does not return a value, you do not have to enclose the argument in parentheses. The above guidelines apply either by using 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 the value that is used to display the variable.

Sub question ()
MyVar = MsgBox (prompt:= "I enjoy my job.", _
title:= "Answer box", buttons:= "4")
MsgBox MyVar
End Sub

Syntax for 11.3.1.3 option statements
Syntax: Option Compare {Binary | Text | Database}
In the syntax of the Option Compare statement, curly braces and vertical lines indicate mandatory selections in three items (you do not have to type curly braces in visual Basic statements). For example, the following statement indicates that in a module, the comparison of strings is case-insensitive according to the sort order of the text.
Option Compare Text


Syntax for 11.3.1.4 Dim statements
Syntax: Dim varname[([subscripts])] [as type] [, varname[([subscripts])] [as type]] ...
In the syntax of the Dim statement, Dim is a prerequisite keyword. The only element that is required is the 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 variable of type string. It contains data types, so you can save memory and help you find errors from your code.
Dim Myanswer as String
If you declare several variables in one statement, you must include the data type of each variable. A variable is automatically declared as a variant when it is declared with less data type.
Dim x As Integer, y As Integer, z as Integer
In the following statements, X and y are specified as Variant data types, and only z is specified as a String data type.
Dim x, y as integer,z as String
If you declare an array variable, you must include parentheses, but the subscript is optional. A dynamic array myarray is defined in the following statement.
Dim MyArray ()

11.3.2 Efficient use of data syntax
Table 11-1 lists the types of data that VBA supports and the size and scope of the storage space.


Attention:
An array of any data type requires 20 bytes of memory space, plus 4 bytes per array dimension, plus the amount of space occupied by the data itself. The memory space occupied by the data can be computed by multiplying the size of each element with the number of data elements. For example, the data in a one-dimensional array consisting of 4 2-byte integer data elements occupies 8 bytes. This 8 byte plus an extra 24 bytes makes the total memory space required for this array to be 32 bytes. A Variant that contains an array requires 12 more bytes than a single array.
Attention:
Use the StrConv function to convert string data from one type to another.
Unless otherwise specified, the variable is not declared as a Variant data type. This data type makes writing programs easier, but it's not always the most efficient data type in use.
You must consider using a different data type if you have the following conditions:

The program is very large and uses many variables.
The program must execute as quickly as possible.
Write the data directly into the random storage file.
In addition to variants, the 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 declares that the variable X is an integral type with a range between -32,768~32,767. If you try to set a value beyond this range to X, an error occurs. If you try to specify a fraction to X, the score is rounded automatically. For example:
X = 32768 ' generates an error. x = 5.9 ' Set x = 6.

11.3.3 the naming rules for Visual Basic
When you name procedures, constants, variables, and parameters in a Visual Basic module, you can use the following rules:
The first character must use an English letter.
You cannot use spaces, periods (.), exclamation marks (!), or @, &, $,# characters in names.
The name cannot be longer than 255 characters in length.
Typically, the name used cannot be the same as the Function procedure, statement, and method name of Visual Basic itself. You must quit using the same name as the keyword in the program language. If the intrinsic language function you are using, the statement or method conflicts with the name you specify, you must identify it by displaying it. The general will precede the name of the built-in function, statement, or method with the name of the associated type library. For example, if you have a variable named left, you can only use VBA. Left to call the left function.
Duplicate names cannot be used at the same level in the scope. For example, you cannot declare two variables named age in the same procedure. However, you can declare a private variable named age and a procedure-level variable named age in the same module.
Attention:
Visual Basic is case-insensitive, but it retains uppercase at the statement where the name is declared.

11.3.4 declaring variables, constants, and arrays

11.3.4.1 declaring variables
You will typically use the DIM statement to declare variables. A declaration statement can be placed in a procedure to create a variable that belongs to the level of the procedure. Or in the Declarations section, you can place it at the top of the module to create variables that are at the module level.
The following example creates a variable strName and specifies the String data type. Dim StrName as String
If the statement appears in the procedure, the variable strName can only be used in this procedure. If the statement appears in the Declarations section of the module, the variable strName can be used by all the processes in the module, but not by the process contained in the different modules in the same project. In order for the variable to be used by all the procedures in the project, precede the variable with the public statement, 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 a user custom type.
You can declare several variables in one statement. To specify a data type, you must include the data type for each variable. In the following statement, variables IntX, inty, and Intz are declared as Integer types.
Dim IntX As Integer, Inty As Integer, Intz as Integer
In the following statement, variable IntX and inty are declared as Variant types, and only Intz is declared as an Integer type.
Dim IntX, Inty, Intz as Integer
In a declaration statement, you do not necessarily provide the data type of the variable. If the data type is omitted, the variable is set to a variant type.
Use the public statement: You can declare a common module-level variable by using the publicly statement.
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 by any project that references the project to which the public variable belongs.
Use private statements: You can use private statements to declare proprietary module-level variables.
Private MyName as String
Private variables can only be used in procedures in the same module.
Attention:
Using the Dim statement at the module level is the same as using Private statements. However, using Private statements makes it easier to read and interpret code.
Use the static statement: When a static statement is used to replace the DIM statement, the declared variable retains its original value at the time of the call.
Use the Option Explicit statement: In Visual Basic, you can simply implicitly declare a variable by an assignment statement. All implicitly declared variables are variant types, and Variant type variables require more memory resources than most other types of variables. If you explicitly declare a variable to be of the specified data type, the application will be more efficient. Explicitly declaring all variables reduces the incidence of naming conflicts and spelling errors.
If you do not want visual Basic to generate an implied declaration, you can place the option Explicit statement before all the procedures in the module. This statement requires an explicit declaration of all variables in the module. If the module contains the option Explicit statement, it can cause a compile-time error when Visual Basic encounters a variable or spelling error that was not previously defined.
You can set an option in the Visual Basic program environment to automatically include the option Explicit statement in all new modules.
Note: You need to explicitly declare a fixed-size array and a dynamic array.
Declare an object variable for Automation: When you use an application to control an object for another application, you should set a reference to the type library for the other application. If you set a reference, you can declare an object variable based on the type that they most commonly specify. For example, if you are in Microsoft Word, when you make a reference setting to a Microsoft Excel type library, you can declare a variable of type worksheet in Microsoft Word to represent the Workshe in Microsoft Excel ET objects.
If you use other applications to control Microsoft Access objects, in most cases, you can declare object variables based on the type they most commonly specify. You can also use the keyword new to automatically generate a new instance of an object. However, you might want to indicate that it is a Microsoft Access object. For example, when you declare an object variable in Microsoft Visual Basic to represent a Microsoft Access form, you must distinguish it from a Microsoft Access form object or a Visual basic form object. So you must include the name of the type library in the statement that declares the variable, as shown in the following example:
Dim Frmorders as New access.form
Some applications do not recognize specific types of Microsoft Access objects. Even if you have already set up a reference to a Microsoft Access type library in these applications, you must declare that all Microsoft Access object variables are of type object. You cannot create a new instance of this object using the New keyword. The following example shows an application that does not recognize a Microsoft Access object type, and how to declare a variable to represent a Microsoft Access Application object. The application then creates an instance of the Application object.
Dim appaccess as Object
Set appaccess = CreateObject ("Access.Application")

11.3.4.2 Declaration Constants
If you want to declare a constant, you can use the CONST statement to declare it and set its value, and you cannot change or assign a new value after the constant declaration is complete.
In the Declarations section, you can declare constants either in the procedure or at the top of the module. Constants in the module level are private by default. To declare a common module-level constant, you can precede the Const statement with the keyword public. It is also possible to explicitly declare a private constant by adding the public keyword before the Const statement, making it easier to read and interpret the code.
The following example declares that a public constant conage is an Integer type and that its value is 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 you already know the value of a constant, you can specify the data type in the Const statement. More information about the data type.
You can declare several constants in one statement. In order to specify a data type, you must include the data type for each constant. In the following statement, constants Conage and Conwage are declared as Integer types.
Const conage as Integer = conwage as Currency = 35000

11.3.4.3 Declaration Array
Arrays are declared like other variables, and can be declared using Dim, Static, Private, or public statements. The difference between a scalar quantity (not an array) and an array variable is that you 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 while the program is running, it is a dynamic array.
Whether an array is indexed from 0 or 1 is based on the setting of the Option Base statement. If Option Base is not specified as 1, the array index starts at 0.
1. Declaring an array of fixed sizes
The following line of code declares a fixed-size array, which is an integer array of 11 rows multiplied by 11 columns:
Dim MyArray as Integer
The first parameter represents the row, and the second parameter represents the column.
As with the declaration of other variables, the data type of the element in the declaration array is Variant unless you specify a data type to the array. The numeric Variant element of each array in the array occupies 16 bytes. Each string variant element occupies 22 bytes. To make the written code as concise as possible, the explicitly declared array is a data type rather than a Variant. The following lines of code compare the sizes of several different arrays:
' Integer array uses 22 bytes (11 element * 2 bytes).
ReDim Myintegerarray (a) as Integer
The ' Double array ' uses 88 bytes (11 element * 8 bytes).
ReDim Mydoublearray (a) as Double
' Variant array uses at least 176 bytes (11 element * 16 bytes).
ReDim Myvariantarray (10)
' Integer array uses 100 * 100 * 2 bytes (20,000 bytes).
ReDim Myintegerarray () as Integer
The ' Double array ' uses 100 * 100 * 8 bytes (80,000 bytes).
ReDim Mydoublearray () as Double
' Variant array uses 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 to the operating system that is being used. If you use an array that is larger than the total amount of available memory in the system, the speed slows because you must read and write back data from the disk.
2. Declaring a dynamic array
If declared as a dynamic array, you can change the size of the array when code is executed. You can declare an array with Static, Dim, Private, or public statements, and leave the parentheses blank, as shown in the following example.
Dim Sngarray () as single
Attention:
You can use the REDIM statement in the procedure to make an implied array declaration. Be careful when using the ReDim statement, and do not misspell the name of the array. Otherwise, the second array is generated even if there is an Option Explicit statement in the module.
For an array range in a procedure, you can use the ReDim statement to change its dimensions to define the number of elements and the underlying bindings for each dimension. You can use the ReDim statement to change the dynamic array whenever you need to. However, when this action is taken, the values that exist in the array are lost. To save the original values in the array, you can use the ReDim Preserve statement to expand the array. For example, the following statement expands the Vararray array to 10 elements, and the current value in the original array is not lost.
ReDim Preserve Vararray (UBound (vararray) + 10)
Attention:
When using the Preserve keyword for a dynamic array, only the upper bound of the last dimension can be changed, but the number of dimensions cannot be changed.

11.3.5 process and its invocation
11.3.5.1 Function Procedure
A Function procedure is a series of Visual Basic statements that are contained by function and End Function statements. Function procedures are similar to SUB procedures, but functions can return a value. A Function procedure can be invoked through the caller's procedure by passing arguments, such as constants, variables, or expressions. If a Function procedure has no arguments, its function statement must contain an empty parenthesis. The 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 according to the Fahrenheit temperature. When the Main procedure calls this function, a variable containing the value of the parameter is passed to this function. The result of the calculation is returned to the calling procedure and is displayed in a message box.
Sub Main ()
temp = Application.inputbox (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 Procedure
A Sub procedure is a series of Visual Basic statements contained by sub and End Sub statements that perform actions but cannot return a value. A Sub procedure can have parameters, such as a constant, a variable, or an expression, to invoke it. If a Sub procedure has no arguments, its sub statement must contain an empty parenthesis.
In the following Sub procedure, each row has a comment explaining its effect:

' Declaration procedure named GetInfo
' This Sub procedure has no parameters
Sub GetInfo ()
' Declare a string variable named answer
Dim Answer as String
' Specifies the return value of the InputBox function to answer
Answer = InputBox (prompt:= "What is your name?") ）
' Condition If ... Then ... Else statement
If answer = Empty Then
' Call the MsgBox function
MsgBox prompt:= "You did the not enter a name."
Else
The ' MsgBox function is connected to the answer variable.
MsgBox prompt:= "Your name is" & Answer
' End If ... Then ... Else statement
End If
' End Sub procedure
End Sub

11.3.5.3 Property Procedure
A Property procedure is a series of Visual Basic statements that allow programmers to create and manipulate custom properties. Property procedures can be used to create read-only properties for forms, standard modules, and class modules. Can be used to replace the public variable in the code, which should be executed when the property value is set.
Unlike public variables, property procedures in the Object Browser have some help strings assigned to custom properties.
When you create a property procedure, it becomes an attribute of the module that this procedure contains. Visual Basic provides the following three kinds of property procedures:
The procedure used by property let to set the value of an attribute.
The procedure that property get uses to return an attribute value.
Property set is used to set the procedure for referencing an object.
The syntax for declaring a property procedure looks like this:
[Public | Private] [Static] Property {Get | Let | Set}
propertyname_ [(arguments)] [as type]
Statements
End Property
Attribute procedures are usually used in pairs: The Property Let is a set of properties get, and the set is in a group with a. Declaring a property-get procedure individually is like declaring a read-only attribute. When three property procedures are used together, only variant variables are useful because only a variant can contain information for an object or other data type. The property Set is intended to be used on an object;
The parameters required in the property procedure declaration are as follows:
Property Get Property Get PropName (1, ..., n) as type
Property Let PropName (1, ..., N, n+1)
Property Set Property Set PropName (1, ..., N, n+1)
You must share the same name and data type from the first to the last argument (1, ..., n) during the same name property.
The Property Get procedure declaration requires a less parameter than the associated Property Let and property Set declarations. The data type of the property Get procedure must be the same as the associated Property Let and the type of the last (n+1) parameter in the property Set declaration. For example, if you declare the following property-let procedure, the name 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 the statement.
End Property
Property Get Names (IntX As Integer, inty as Integer)
As Variant
' Execute the statement.
End Property
In a property Set declaration, the data type of the last parameter must be an object type or Variant.

11.3.5.4 call Sub and Function procedures
When you call a Sub procedure from another procedure, you must type the procedure name and any required parameter values. The call statement is not required, but if you use it, any arguments must be enclosed in parentheses.
You can use SUB procedures to organize other processes, so you can easily understand and debug them. In the following example, the Sub procedure Main passes the parameter value 56 to invoke the Sub procedure multibeep. After the Multibeep is run, the control returns main, and then main calls the SUB procedure message. The message displays an information box, and when you press the OK key, the control returns main, and 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. To invoke a Sub procedure with multiple parameters
The following example shows two different ways to invoke a Sub procedure that has multiple parameters. When Housecalc is invoked for the second time, it is necessary to enclose the parameters using parentheses because the call statement is used.

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 the house."
Else
MsgBox "This House is affordable."
End If
End Sub
Parentheses are used when calling a Function procedure.
In order to use the return value of a function, you must specify the function to give the variable, and enclose the arguments in parentheses, as shown in the following example:
Answer3 = MsgBox ("Are You happy with your salary?", 4, "Question 3")
If you do not return a value for an Italian 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 a function to a variable:
MsgBox "Task completed!", 0, "task box"
Note that if you include parentheses in the example above, the statement can cause a syntax error.
2. Passing named parameters
A statement in a Sub or Function procedure can use named arguments to pass a value to the invoked procedure. Parameters are passed in two ways: by value and by address. Passing by value is just a copy of the pass parameter, creating a variable with the same parameter type and content within the function. The parameter changes in the internal parameters of the process will not affect the actual parameters; by address, it passes the address of the actual parameter, and all changes to the parameters in the process will affect the actual parameters. A named parameter is composed of a parameter name followed by a colon (: =) and an equal sign, and then a value is given to the parameter.
The following example uses named arguments to invoke an MsgBox function that does not have a return value.
MsgBox title:= "Task Box", prompt:= "task completed!"
The following example invokes the MsgBox function using named arguments. Assigns 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 Call Property Procedure
Table 11-2 lists the syntax for invoking the property procedure.

When a Property let or property Set procedure is invoked, a parameter always appears to the right of the equal sign (=).
When declaring a property let or property Set procedure with multiple parameters, Visual Basic passes the right argument of the call to the last argument in the property let or PropertySet declaration. For example, figure 11-18 shows the relationship between a parameter in a property procedure call and a parameter in a Property Let declaration:

In fact, combining a property procedure with multiple parameters is only used when creating an array of attributes.