VBA小技巧

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