VBA学习笔记 3

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">一些最近可能用到的函数或者表达式的测试， 第一二个的测试数据如下：</span>

1. 检测第三列所列举的动作，并把相应的值填入a列中，如果非法则警告。

用到的函数有switch, msgbox, 代码如下：

Sub test1()
    'check the value of Ci and fill out a corresponding value in Ai
    ' if invalid then show a message, the result is as below
    'Insert
    'Delete
    'Update
    'show a message  states 'invalid action'
    
    Dim strC As String
    For i = 1 To 4
         strC = Sheet1.Cells(i, 3).Value
         Sheet1.Cells(i, 1).Value = Switch(LCase(strC) = "add", "Insert", _
                                             LCase(strC) = "delete", "Delete", _
                                             LCase(strC) = "change", "Update")
         If Sheet1.Cells(i, 1).Value = "" Then
            MsgBox "Invalid action"
        End If
        Next i
End Sub

2. 判断输入的数据是否数字，如果不是则替换可能出现的的符号‘-’，并且把多去的空格去掉

用到的内置函数有IsNumeric(), Trim(), Replace()

Sub test2()
    ' process account number, if it's not numeric, trim spaces and replace the character "-" to "", the result is as below:
    '565266
    '4565.21
    '456521
    '456521
    
    For i = 1 To 4
        If IsNumeric(Sheet1.Cells(i, 4).Value) Then
            Debug.Print Trim(Sheet1.Cells(i, 4).Value)
        Else
            Debug.Print Replace(Trim(Sheet1.Cells(i, 4).Value), "-", "")
        End If
        Next i
End Sub

3.  测试函数的参数， byVal, byRef 

Sub test3()
    ' test the difference of byVal and byRef when using fuctions, the result is as below
    '10
    '20
    '20
    '10
    
    Dim a  As Integer
    Dim b As Integer
        a = 10
        b = 20
        ' swap a and b using swapbyval
        If swapbyval(a, b) Then
            Debug.Print a
            Debug.Print b
       End If
       ' swap a and b using swapbyRef
       If swapByRef(a, b) Then
            Debug.Print a
            Debug.Print b
       End If
End Sub
'swap a and b byRef
Function swapByRef(ByRef a As Integer, ByRef b As Integer) As Boolean
    Dim temp As Integer
    temp = a
    a = b
    b = temp
    swapByRef = True
End Function
'swap a and b byVal
Function swapbyval(ByVal a As Integer, ByVal b As Integer) As Boolean
    Dim temp As Integer
    temp = a
    a = b
    b = temp
    swapbyval = True
End Function