运用VBA时,可以构造一些函数去实现诸如printf的方便函数。
Public Function printf(mask As String, ParamArray tokens()) As String
Dim i As Long
For i = 0 To UBound(tokens)
mask = Replace(mask, "{" & i & "}", tokens(i))
Next
printf = mask
End Function
Sub test()
cc = genSearchedArr(6554, Array("a", "b", "e", "f", "g"))
MsgBox cc
End Sub
Function genSearchedLines(searchedRow As Integer, columns()) As String
Dim searchedVal As String
Dim i As Long
For i = 0 To UBound(columns)
If i = 0 Then
searchedVal = printf("{0}{1}", columns(i), searchedRow)
Else
searchedVal = searchedVal & printf("&{0}{1}", columns(i), searchedRow)
End If
Next
genSearchedLines = searchedVal
End Function
Function genSearchedArr(searchedRow As Integer, columns()) As String
Dim searchedArr As String
Dim i As Long
For i = 0 To UBound(columns)
If i = 0 Then
searchedArr = printf("{0}1:{0}{1}", columns(i), (searchedRow - 1))
Else
searchedArr = searchedArr & printf("&{0}1:{0}{1}", columns(i), (searchedRow - 1))
End If
Next
genSearchedArr = searchedArr
End Function
特别地,将以上辅助函数用于Match,会非常方便:
Sub test()
Dim warehouseWorkbook As Workbook
Set warehouseWorkbook = Workbooks("测试表.xls")
Set w1 = warehouseWorkbook.Sheets("terry")
Dim paraArr()
paraArr = Array("b", "e", "f", "g", "h", "i")
eva_exp = printf("Match({0}, {1}, 0)", _
genSearchedLines(12, paraArr), _
genSearchedArr(12, paraArr))
MsgBox w1.Evaluate(eva_exp)
End Sub
Sub test()
Dim warehouseWorkbook As Workbook
Set warehouseWorkbook = Workbooks("测试表.xls")
Set w1 = warehouseWorkbook.Sheets("terry")
eva_exp = printf("Match({0}, {1}, 0)", _
genSearchedLines(12, "a", "b", "c"), _
genSearchedArr(12, "a", "b", "c"))
MsgBox w1.Evaluate(eva_exp)
End Sub