<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
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