VBA process and parameters detailed

Procedures and parameters of VBA the procedure in VBA (Procedure) has two kinds, one called function and the other is subroutine (subroutine), using function and sub keyword respectively. They are an independent process that can get parameters, execute a series of statements, and change the values of their parameters. Unlike the Function procedure, a Sub procedure with a return value cannot be used in an expression.

This article mainly describes the use of subroutines, as well as these methods can be applied to function.

Grammar

[Private | Public | Friend] [Static] Sub name [(arglist)]
[Statements]
[Exit Sub]
[Statements]
End Sub
* Options enclosed in the [] symbol are optional

[Private | Public | Friend]
These three keywords are related to the scope of the action.
Private means that the process can only be called from within this module. Using this keyword, you will not see this procedure from the macro ... "macro", "Tools".
Public means that the process can be accessed from other modules as well. If you do not explicitly specify with public, Private, or Friend, the Sub procedure is common by default. The common process can be seen from the menu "Tools", "Macros", "Macros ...".
Friend used in the class module, less use, here is not introduced.

Static means (you may remember to declare a static variable with static), declaring a procedure with it, indicating that the local variable declared in the process still retains its original value the next time the procedure is called.
The following is the use of the static declaration procedure.

Static SubM1 ()DimI as IntegerDimJ as Integeri = i + 1j = j + 1Debug.Print"I=" & I & "j=" & JEnd SubPrivate SubM2 ()DimI as IntegerDimJ as Integeri = i + 1j = j + 1Debug.Print"I=" & I & "j=" & JEnd SubSubTry1 ()DimI as IntegerDebug.Print"Static Process:" fori = 1 to10PagerM1NextIdebug.Print"Private Process:" fori = 1 to10PagerM2NextIEnd Sub

Run the Try1 process, and you can see the results in the Immediate window.
=

Here are some things to note when using the process.

• A Sub procedure can be recursive, that is, the procedure can invoke itself to accomplish a particular task. However, recursion can cause the stack to overflow. Normally the Static keyword and the recursive Sub procedure are not used together.
• All executable code must be part of a process. You cannot define a Sub procedure in another sub, Function, or property procedure.
• The exit Sub statement causes the execution to exit immediately from a Sub procedure. The program then executes from the next statement in the statement that called the Sub procedure. You can have an Exit sub statement anywhere in the Sub procedure.
• The variables used in sub procedures fall into two categories: one is explicitly defined within a procedure, and the other is not. Variables that are explicitly defined within a procedure (using the Dim or equivalent method) are local variables. For variables that are used but are not explicitly defined in the procedure, they are also local unless they are clearly defined at a higher level outside the procedure.
• Caution A procedure can use a variable that is not explicitly defined within a procedure, but a name conflict occurs whenever any name defined at the module level has the same names. If the undefined variable used in the procedure has the same name as another procedure, constant, or variable, the procedure is assumed to be using the module-level name. This type of conflict can be avoided by explicitly defining variables. You can use the Option Explicit statement to force an explicit definition of a variable.
• Note: You cannot use GoSub, GoTo, or Return to enter or exit a Sub procedure.

Parameter Table arglist

Grammar:
[Optional] [ByVal | BYREF] [ParamArray] varname[()] [as type] [= defaultvalue]

Optional indicates that this parameter is optional, that is, you can pass a value to this parameter without passing a value when calling the procedure. If there is a pass defaultvalue to this parameter (such as optional IInput2 as integer=13), this defaultvalue is used by default in the procedure when the calling procedure does not pass the value to this parameter.
Optional must be used for those parameters of the last face, that is, a parameter uses optional, and the arguments following it must also use optional.

SubMMM (iInput1 as Integer,OptionalIInput2 as Integer= 13,OptionalIInput3 as Integer)' after iInput2 used optional, IINPUT3 must also use optionalMsgBox "iinput1=" & IInput1 & vbCrLf & "iinput2=" & IInput2 & vbCrLf & "iinput3=" & IINPUT3End SubSubSubtry ()' can assign 3 parameters to a value    PagerMMM (23, 34, 2)' iinput=23, iinput2=34, iinput3=2' You can also assign a 3rd assignment without assigning a value to the 2nd parameter so that the IINPUT2 will be equal to the default value of    PagerMmm (23,, 2)' iinput=23, iinput2=13, iinput3=2' can assign a value to the 2nd parameter without assigning a value to the 3rd parameter    PagerMMM (23, 34)' iinput=23, iinput2=34, iinput3=0' 2nd, the 3rd parameter is not assigned to a value    PagerMMM (23)' iinput=23, iinput2=12, iinput3=0End Sub

The use of ParamArray
ParamArray can only be used for the last parameter of arglist, indicating that the last parameter is a Optional array containing variant elements, but when you pass the value to the procedure or use a comma to separate multiple arguments, the process will combine several parameters into an array. The advantage of using ParamArray is that you can provide an indefinite number of variable-type parameters to the process. ParamArray cannot be used with byval,byref, or Optional.

SubM1 (IINPUT1 as Integer,ParamArrayArgarr ())DimStrlist as StringDimI as IntegerStrlist = "iinput1=" & IInput1 & VbCrLf fori = 0 to UBound(Argarr) on Error Resume NextStrlist = strlist & "Argarr (" & I & ") =" & Argarr (i) & VbCrLf' If the argument is empty, an error is generated        IfErr.Description <> "" ThenStrlist = strlist & "Argarr (" & I & ") = Missing parameter" & Vbcrlferr.clear' Clear Error        End IfNextImsgbox strlistEnd SubSubTry1 ()PagerM1 (23, 24, 25)' iinput=23, Argarr (0) =24, Argarr (1) =25    PagerM1 (23, 24,, 25)' iinput=23, Argarr (0) =24, Argarr (1) = missing parameter, Argarr (2) =25    PagerM1 (+, 64.4, +, "data")' iinput=23, Argarr (0) =24, Argarr (1) =64.4, Argarr (2) =25, Argarr (3) =dataEnd Sub

ByVal and ByRef
ByRef is used by default in VBA. ByVal means that parameters are passed by value, because it is passed by value, and the value of this parameter in the process changes so that it affects the range just inside the process. There is no use out of the process. While ByRef is passed by address or by reference, the actual value passed to the process is the address of this value, rather than the values themselves, in the process to change this parameter is to change the value of this address, so that the process can be seen outside the value has been changed. Run the following example to see the difference.

SubMmmByValII1 as Integer, iI2 as Integer) iI1 = iI1 + 10ii2 = iI2 + 10MsgBox "inside:ii1=" & iI1 & "ii2=" & iI2End SubSubMySub ()DimII1 as IntegerDimII2 as IntegeriI1 = 10ii2 = 12MsgBox "before:ii1=" & iI1 & "ii2=" & iI2PagerMMM (iI1, iI2) MsgBox "after:ii1=" & iI1 & "ii2=" & iI2' shown separately in order    ' Show before:ii1=10 ii2=12    ' Show before:ii1=20 ii2=22    ' Show before:ii1=10 ii2=22    ' Process mmm modifies iI1 and iI2, but iI1 is passed by value and is affected by iI1 outside the MMM process    while iI2 is passed by reference, the iI2 outside the MMM process has also been changed.End Sub

Also, if the argument is an array, it can only be passed by reference, because the address of the first element of the array is actually passed. For example, the following code uses.

SubGetArray (Arrtemp () as Integer)DimI as Integer fori = 0 to UBound(arrtemp) Debug.Print"Item" & I & ":" & Arrtemp (i)NextIEnd SubSubPassarray ()DimArrint (3) as IntegerArrint (0) = 1arrInt (1) = 2arrInt (2) = 3arrInt (3) = 4PagerGetArray (Arrint)' Print out in the Immediate window' Item 0:1' Item 1:2' Item 2:3' Item 3:4End Sub

However, for an object, using ByVal actually passes a reference to the object, so that the modification of the object in the procedure will affect the value or property of the external object of the procedure. If you use ByVal, and you create a new object instance in a procedure that assigns the object to the passed object, it does not affect the external properties or values of the calling object. If you use ByRef, you create a new object instance in the procedure that assigns the object to the passed object, but it affects the property or value of the object outside the procedure.

SubTestbyvalbyref ()DimObjdic as ObjectSetObjdic = CreateObject ("Scripting.Dictionary") objdic (1) = 100Debug.Print"ByValueTest1" Debug.Print"Original value: Objdic (1) =" & Objdic (1)PagerByValTest1 (Objdic) Debug.Print"External value: Objdic (1) =" & Objdic (1) & VbCrLf objdic (1) = 100Debug.Print"ByValueTest2" Debug.Print"Original value: Objdic (1) =" & Objdic (1)PagerByValTest2 (Objdic) Debug.Print"External value: Objdic (1) =" & Objdic (1) & VbCrLf objdic (1) = 100Debug.Print"Byreftest" Debug.Print"Original value: Objdic (1) =" & Objdic (1)PagerByreftest (Objdic) Debug.Print"External value: Objdic (1) =" & Objdic (1)End SubPrivate SubByValTest1 (ByValC as Object)DimA as ObjectSetA = CreateObject ("Scripting.Dictionary") A (1) = 200Setc = AEnd SubPrivate SubByValTest2 (ByValC as Object) C (1) = 200End SubPrivate SubByreftest (ByRefC as Object)DimA as ObjectSetA = CreateObject ("Scripting.Dictionary") A (1) = 200Setc = AEnd Sub

When you run the process testbyvalbyref, the following results are printed in the Immediate window.

ByValueTest1
Original value: Objdic (1) =100
External value: Objdic (1) =100

ByValueTest2
Original value: Objdic (1) =100
External value: Objdic (1) =200

Byreftest
Original value: Objdic (1) =100
External value: Objdic (1) =200

Source: http://www.360doc.com/content/10/0112/16/406571_13355136.shtml

VBA process and parameters detailed