VBA的过程及参数详解

Static Sub m1()
Dim i As Integer
Dim j As Integer
i = i + 1
j = j + 1
Debug.Print "i=" & i & " j=" & j
End Sub
Private Sub m2()
Dim i As Integer
Dim j As Integer
i = i + 1
j = j + 1
Debug.Print "i=" & i & " j=" & j
End Sub
Sub try1()
Dim i As Integer
Debug.Print "静态过程:"
For i = 1 To 10
Call m1
Next i
Debug.Print "私有过程:"
For i = 1 To 10
Call m2
Next i
End Sub

Sub mmm(iInput1 As Integer, Optional iInput2 As Integer = 13, Optional iInput3 As Integer)
‘iInput2用了Optional后，iInput3也必须用Optional
    MsgBox "iInput1=" & iInput1 & vbCrLf & "iInput2=" & iInput2 & vbCrLf & "iInput3=" & iInput3
End Sub
Sub subTry()
‘ 可以给3个参数都赋值
    Call mmm(23, 34, 2)           	‘iInput=23, iInput2=34, iInput3=2
	‘也可以给第3个赋值，而不给第2个参数赋值，这样iInput2会等于默认值13
    Call mmm(23, , 2)         		‘iInput=23, iInput2=13, iInput3=2
	‘可以给第2个参数都赋值而不给第3个参数赋值
    Call mmm(23, 34)             	‘iInput=23, iInput2=34, iInput3=0
	‘ 第2个，第3个参数都不赋值
    Call mmm(23)                	‘iInput=23, iInput2=12, iInput3=0
End Sub

Sub m1(iInput1 As Integer, ParamArray argArr())
Dim strList As String
Dim i As Integer
 
strList = "iInput1=" & iInput1 & vbCrLf
For i = 0 To UBound(argArr)
On Error Resume Next
strList = strList & "argArr(" & i & ")=" & argArr(i) & vbCrLf
‘ 如果参数为空，将生成错误
        If Err.Description <> "" Then
strList = strList & "argArr(" & i & ")=缺失参数" & vbCrLf
Err.Clear		‘ 清除错误
        End If
Next i
MsgBox strList
End Sub
Sub try1()
Call m1(23, 24, 25)					‘ iInput=23, argArr(0)=24, argArr(1)=25
    Call m1(23, 24, , 25)				‘ iInput=23, argArr(0)=24, argArr(1)=缺失参数, argArr(2)=25
    Call m1(23, 24, 64.4, 25, "data")	‘ iInput=23, argArr(0)=24, argArr(1)=64.4, argArr(2)=25, argArr(3)=data
End Sub

Sub mmm(ByVal iI1 As Integer, iI2 As Integer)
iI1 = iI1 + 10
iI2 = iI2 + 10
MsgBox "Inside: iI1=" & iI1 & " iI2=" & iI2
End Sub
Sub mySub()
Dim iI1 As Integer
Dim iI2 As Integer
iI1 = 10
iI2 = 12
MsgBox "Before: iI1=" & iI1 & " iI2=" & iI2
Call mmm(iI1, iI2)
MsgBox "After: iI1=" & iI1 & " iI2=" & iI2
‘ 按顺序分别显示
    ‘ 显示 Before: iI1=10 iI2=12
    ‘ 显示 Before: iI1=20 iI2=22
    ‘ 显示 Before: iI1=10 iI2=22
    ‘ 过程mmm中修改了iI1和iI2，但是iI1是按值传递，在mmm过程之外的iI1并受影响
    ‘ 而iI2是按引用传递，mmm过程之外的iI2也被改变了
End Sub

Sub GetArray(arrTemp() As Integer)
Dim i As Integer
For i = 0 To UBound(arrTemp)
Debug.Print "Item " & i & ": " & arrTemp(i)
Next i
End Sub
Sub PassArray()
Dim arrInt(3) As Integer
arrInt(0) = 1
arrInt(1) = 2
arrInt(2) = 3
arrInt(3) = 4
Call GetArray(arrInt)
‘ 在立即窗口打印出
	‘ Item 0: 1
	‘ Item 1: 2
	‘ Item 2: 3
	‘ Item 3: 4
End Sub

Sub TestByValByRef()
Dim objDic As Object
 
Set objDic = CreateObject("Scripting.Dictionary")
objDic(1) = 100
Debug.Print "byValueTest1"
Debug.Print "原始值: objDic(1)=" & objDic(1)
Call byValTest1(objDic)
Debug.Print "外部值: objDic(1)=" & objDic(1) & vbCrLf
 
objDic(1) = 100
Debug.Print "byValueTest2"
Debug.Print "原始值: objDic(1)=" & objDic(1)
Call byValTest2(objDic)
Debug.Print "外部值: objDic(1)=" & objDic(1) & vbCrLf
 
objDic(1) = 100
Debug.Print "byRefTest"
Debug.Print "原始值: objDic(1)=" & objDic(1)
Call byRefTest(objDic)
Debug.Print "外部值: objDic(1)=" & objDic(1)
End Sub
Private Sub byValTest1(ByVal c As Object)
Dim a As Object
Set a = CreateObject("Scripting.Dictionary")
a(1) = 200
Set c = a
End Sub
Private Sub byValTest2(ByVal c As Object)
c(1) = 200
End Sub
Private Sub byRefTest(ByRef c As Object)
Dim a As Object
Set a = CreateObject("Scripting.Dictionary")
a(1) = 200
Set c = a
End Sub