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