Excel VBA programming-syntax

Basic statement
1. Control Program Flow Statement
(1) GoTo statement
This statement transfers the executed program to the statement instruction where the specified tag is located, but cannot be transferred to an instruction outside the process. For example, when an error occurs during error capture, the program is transferred to the tag for execution.
(2) If... Then statement
This type of statement is used for condition determination. When the condition is met, the corresponding statement is executed. When the condition is not met, other operations are performed.
The basic syntax is:
If <condition> Then <execution statement when the condition is met>
If... The Then statement can be used in different forms:
① When there is only one condition, the following structure can be used:
If <condition> Then <execution statement when the condition is met> [Else <execution statement when the condition is not met>]
The Else clause is optional. If the statement is not in the same row, the End If statement should be followed, that is:
If <condition> Then
[Command]
End If
Or:
If <condition> Then
[Command]
Else
[Command]
End If
When the condition is true, execute the statement after Then and end If... The execution of the Then statement. Otherwise, the statement after Else is executed or If... The execution of the Then statement.
② When there are two or more conditions, you can use nested If... Then structure:
If <condition> Then
[Command]
ElseIf <condition 1> Then
[Command]
[Else]
[Command]
End If
The above is only two layers of nesting. You can use multiple layers of nesting as needed. When the condition is true, execute the statement after Then and end If... The execution of the Then statement. Otherwise, condition 1 is judged. When condition 1 is true, the statement after Then is executed and If... The execution of the Then statement. Otherwise, the command after Else is executed.
(3) Select Case statement
When three or more conditions are required, the Select Case statement is used. The basic syntax is:
Select Case <test expression>
[Case conditional expression 1]
[Command]
[Case conditional expression 2]
[Command]
......
[Case Else]
[Command]
End Select
When a conditional expression matches a test expression, the subsequent command is executed. Otherwise, the Else (if any) command is executed and the execution of the Select Case block is ended.
In addition, Select Case statements can be nested.
(For further introduction and examples of program control statements, see a series of articles)
2. Loop statements
Loop means repeated execution of a code segment. In VBA, there are multiple statement structures that can constitute loops.
(1)... Next Loop
The syntax is as follows:
For <counter = start count> To <End count> [step size]
[Command]
[Exit For]
[Command]
Next [Counter]
From the beginning to the end, execute the command block between For and Next repeatedly, unless the Exit For statement is encountered, it will jump out of the loop in advance. The step size, Exit For statement, and Next counter are optional.
For... The Next loop can include... Next loop, that is,... The Next loop can be nested.
(2) Do While LOOP
The Do While loop is executed only when the specified conditions are met. There are two forms:
■ First form
Do [While condition]
[Command]
[Exit Do]
[Command]
Loop
Execute the command when the conditions are met.
■ Second form
Do
[Command]
[Exit Do]
[Command]
Loop [While condition]
Execute the command first, and then judge the condition. If the condition is met, execute the command again.
The Exit Do statement indicates that the command block is exited in advance.
(3) Do Until Loop
Like the Do While LOOP, there are two forms;
■ First form
Do [Until condition]
[Command]
[Exit Do]
[Command]
Loop
■ Second form
Do
[Command]
[Exit Do]
[Command]
Loop [Until condition]
Execute the command until the condition is met and exit the loop.
(4) While... Wend Loop
Its syntax is:
While <condition>
[Command]
Wend
When the conditions are met, the command is executed.
(For more information and examples of loop statements, see a series of articles)
--------------------
Process
A process consists of a group of VBA statements that complete the required operation tasks. Sub-processes do not return values. Therefore, they cannot be part of a parameter.
Its syntax is:
[Private | Public] [Static] Sub <process name> ([parameter])
[Command]
[Exit Sub]
[Command]
End Sub
Note:
(1) Private is optional. If the Private declaration process is used, the process can only be accessed by other processes in the same module.
(2) Public is optional. If the Public declaration process is used, it indicates that the process can be accessed by all other processes in the workbook. However, if it is used in a Module that contains the Option Private Module statement, this process can only be used for other processes in the project.
(3) Static is optional. If the Static declaration process is used, all variables in the process are Static variables and their values are saved.
(4) Sub is required. Indicates that the process starts.
(5) <process name> is required. You can use any valid process name. The naming rules are usually the same as those for variables.
(6) The parameter is optional. Represents a series of variables separated by commas. These variables are accepted as parameter values passed to the process. If there is no parameter, It is empty parentheses.
(7) Exit Sub is optional. Indicates that the process is exited before the process ends.
(8) End Sub is required. Indicates that the process has ended.
If you write a sub-process in the class module and declare it as Public, it will become the method of this class.
(For detailed introduction and examples of the process, see a series of articles later)
--------------------
Function
A function is a set of related statements and expressions that can complete a specific task. When the function is executed, it returns a value to the statement that calls it. If the return value type of the specified function is not displayed, the default data type value is returned.
The syntax for declaring a function is:
[Private | Public] [Static] Function <Function Name> ([parameter]) [As type]
[Command]
[Function name = expression]
[Exit Function]
[Command]
[Function name = expression]
End Function
Note:
(1) Private is optional. If you use Private to declare a function, the function can only be accessed by other processes in the same module.
(2) Public is optional. If you declare a function using Public, it indicates that the function can be accessed by all other processes in the Excel VBA project. When the scope of the function process is not declared, the default scope is Public.
(3) Static is optional. If you use Static to declare a function, all variables remain unchanged during the function call.
(4) Function is required. Indicates that the function process starts.
(5) <Function Name> is required. You can use any valid function name. The naming rules are the same as those for variables.
(6) The parameter is optional. Represents a series of variables separated by commas. These variables are the parameter values passed to the function process. Parameters must be enclosed in parentheses.
(7) type is optional. Specifies the data type returned by the function process.
(8) Exit Function is optional. Indicates that the process is exited before the function process ends.
(9) End Function is required. Indicates that the function process has ended.
Generally, assign a value to the function name before the function execution ends.
A function can be a component of a parameter. However, a function returns only one value and cannot perform actions related to objects.
If you write a custom function in the class module and declare the function's scope as Public, this function will become the method of this class.
(For detailed descriptions and examples of the Function process, see a series of articles later)
--------------------
Event handling process
To write an event handler for a control event, open the code window of the form and select the required control from the drop-down list of available objects. Then, select the event from the available events drop-down list of the control. At this time, the Definition Statement of the event handler will automatically appear in the code window, you can directly write the event handler.
In Excel, there are the following types of events, such as Excel application events, workbook events, Worksheet events, chart events, and user form events.
(For an introduction to event processing in Excel, see a series of articles below)
--------------------
Class Module
A class module is a repository that stores shared variables and code. Creating a class module is actually creating a COM (Component Object Model) interface. Therefore, the class module allows you to describe an application to the outside world through a programmable interface consisting of attributes, methods, and events, while retaining control of the application. That is to say, the class module enables the program to implement "encapsulation". In this way, you can directly use a class module in other projects without accessing the source code. In addition, you can use a class to create your own database. to use it, you only need to add a reference to the class in any new project. In addition, if you want to change the program, you only need to modify the class module, instead of modifying every part of the program.
(For more information about the class module, see a series of articles)
--------------------
Attribute Process
Property procedure is a special process used to grant and obtain values of custom properties. The attribute process can only be used in an object module, such as a form or a class module.
There are three attribute processes:
Property Let
Assign attribute values
Property Get
Get Attribute Value
Property Set
Assign object reference to attribute reference
--------------------
Call sub-process and function Process
The sub-process can be called using the following three methods. First, use the Call statement:
Call DoSomething (parameter 1, parameter 2 ,......)
If you use the Call statement, you must enclose the parameter list in parentheses.
The second method is to directly use the process name:
DoSomething parameter 1, parameter 2 ,......
In this case, you do not need to add parentheses on both sides of the parameter list.
If you do not want to use the return value of a function, you can use any of the preceding methods to call the function. Otherwise, the function name can be used as an integral part of the expression, as shown in figure
If GetFunctionResult (parameter) = 1 Then
If function calls are used as part of an expression, the parameter list must be placed in parentheses.
The third is to use the Run method.
(For more detailed descriptions and examples of process calls, see a series of articles)
--------------------
Passing parameters between processes
In many cases, you need to call another user-defined function or sub-process in the sub-process or function. In this case, a variable used in the call process is used in the call process. Therefore, the variable can be passed as a parameter to the called process. Regardless of whether the called process is in the same module, project, or a method in the remote server class, the principle of transferring variables from one process to another is the same.
The called process (rather than the called process) determines how variables are transferred from the call process to the called process.
1. VBA allows passing parameters between processes and components in two different ways. In the sub-process or function definition section, you can specify the transmission mode of variables in the parameter list: ByRef (by reference) or ByVal (by value ).
(1) ByRef
This is the default method for transferring variables between processes in VBA. ByRef refers to the transfer of variables by reference, that is, the reference of the original variable passed to the called process. Therefore, if you change the value of a variable during the call process, the change will reflect the variable in the call process, because they are actually the same variable.
(2) ByVal
If you use the ByVal keyword to pass a variable, the independent copy of the variable is obtained by the call process. Therefore, changing the value of this variable during the use of the temple will not affect the original value of this variable during the call process.
2. Optional parameters
The Optional keyword is used to specify a specific parameter, which is Optional. However, this parameter must be placed at the end.
3. ParamArray
The ParamArray keyword can be used to make the process subject to a set of variable parameters. The ParamArray parameter must be the last parameter in the parameter list and cannot be used in the list of parameters that use the Optional keyword.
--------------------
Variable (constant) Scope and lifetime
Sometimes a variable needs to be used in all processes of the project, and sometimes some variables only need to be used in some specific processes. This visibility of variables is called the scope of variables.
The time when a variable exists and acts.
The position declared by a variable or constant in a program determines the scope and lifetime of the variable.
In general, variables declared with the Private keyword in the module declaration can be used by all processes in the module; variables declared with the Public keyword in the module declaration can be used by the entire project; if an object references a certain type of module, variables declared with the Public keyword in the declaration part of the class module can be used throughout the project; variables declared Using Dim statements in sub-processes or functions can only be used in the process of declaring these variables.
(1) process-level scope
Variables declared in a process (I .e. a Sub-process or Function) can only be used in this process, and its survival ends after the End Sub or End Function statement is executed. Therefore, different variables with the same name can be defined in different procedures. Declare the variables of the process-level scope, and declare the variables with Dim statements during the process.
In addition, there is a special variable with a process-level scope, called a static variable. Static variables are defined in the process. Although such variables also have a process-level scope, they have a module-level survival period. This means that these variables can only be used during the definition of static variables, but the value of the variables remains unchanged between two process calls. Declare Static variables with the Static Keyword:
Static lngExcuted As Long
You can also declare a process as a static process. In this case, all variables declared in the process are considered as static variables, and their values remain unchanged between two process calls, as shown in figure
Static Procedure MyProcedure ()
Dim iCtr As Integer
(2) module-level or private scope
Variables With module-level scopes can be used by all sub-processes and functions in a module, or stored in memory during the module-level lifetime.
In the declaration part of the module (that is, any sub-process or function), declare the variable with the Dim statement or Private statement to create a variable with the module-level scope.
(3) Friend Scope
The Friend keyword can only be used to declare variables and procedures in an object module (such as a class module or a form module. Variables declared with Friend allow other object modules in the project to access the variables or methods in the original module, but do not need to use Public statements to declare these variables or methods.
(4) Public Scope
Variables declared using the Public statement outside the process can be used by all modules in the current project.

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/RainyLin/archive/2008/03/12/2173024.aspx