VBA procedures: subroutines and functions

Procedure is to perform a collection of one or more given tasks. are divided into two types: subroutines and functions. The main difference between the two is that the function returns a value and the subroutine does not return a value.

1. Sub-Program. A subroutine is the smallest part of a program that is executable, and its syntax is:

[Private | Public | Friend] [Static] Sub name [(arglist)]　
[statements]
[Exit Sub]
[statements]
End Sub

The syntax for a SUB statement contains the following sections:

Partial description

Public optional. This Sub procedure is accessible to all other processes in all modules. If used in a module that contains option Private, the process is not available outside the project.

Private Optional. Indicates that the Sub procedure can be accessed only by other procedures in the module that contains its declaration.

Friend is optional. Can only be used in class modules. Indicates that the Sub procedure is visible throughout the project, but is invisible to the controller of the object instance.

Static Optional. Represents the value of a local variable that retains a Sub procedure between calls. The Static property does not affect variables declared outside of a sub, even if they are used in the procedure.

Name is required. The name of the Sub, followed by a standard variable naming convention.

ArgList is optional. Represents a list of variables to be passed to the arguments of a Sub procedure at call time. Multiple variables are separated by commas.

Statements is optional. Any group of statements that are executed during a Sub procedure.

The syntax of the arglist parameter and the various parts of the syntax are as follows:

[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]

Partial description

Optional is optional. Indicates that the parameter is not a required keyword. If this option is used, subsequent parameters in arglist must be optional and must all be declared using the Optional keyword. If ParamArray is used, Optional is not used for any parameters.

ByVal is optional. Indicates that the parameter is passed by value.

ByRef Optional. Indicates that the parameter is passed by address. BYREF is the default option for Visual Basic.

ParamArray is optional. The last argument used only for arglist, indicating that the last argument is a Optional array of Variant elements. Use the ParamArray keyword to provide any number of parameters. The ParamArray keyword cannot be used with byval,byref, or Optional.

VarName is required. The name of the variable representing the parameter, followed by a standard variable naming convention.

Type is optional. The data type of the parameter passed to the procedure can be Byte, Boolean, Integer, Long, Currency, single, Double, Decimal (not currently supported), Date, String (only variable length), Object, or Variant. If you do not select a parameter Optional, you can specify a user-defined type, or an object type.

DefaultValue is optional. Any constant or constant expression. Only valid for Optional parameters. If the type is Object, the explicit default value can only be nothing.

2, Function: The biggest difference between a function and a subroutine is that it can return a value, while other places are similar to subroutines, the syntax is as follows:

[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]　
[statements]
[name = expression]
End Function

It can be seen that in addition to the declared keywords, other basic similarity, the description and usage are similar, here will not repeat.