Sunday, 10 December 2017

VBA - Using WorksheetFunction in code doesn't have to throw an error

So one can use worksheet function from code from two different points in the object model (i) Application (ii) Application.WorksheetFunction. If you use the latter any errors will throw an error which you must catch with On Error. If you want to run with the error in the same way that the worksheet does then use the former and test with IsError. Here is some code which illustrates

Option Explicit

Sub Test_Application_WorksheetFunction_VLookup()
    Dim rng As Excel.Range
    Set rng = ThisWorkbook.Sheets.Item(1).Range("A1:b3")
    
    rng.Value = Application.Evaluate("{""Red"",1;""Green"",2;""Blue"",4}")


    Debug.Assert Application.WorksheetFunction.VLookup("Red", rng.Value, 2, False) = 1
    Debug.Assert Application.WorksheetFunction.VLookup("Green", rng.Value, 2, False) = 2
    Debug.Assert Application.WorksheetFunction.VLookup("Blue", rng.Value, 2, False) = 4
    
    Dim lSavedError As Long, sSavedError As String
    On Error Resume Next
    Dim vLU As Variant
    vLU = Application.WorksheetFunction.VLookup("Yellow", rng.Value, 2, False)
    sSavedError = Err.Description
    lSavedError = Err.Number
    On Error GoTo 0

    Debug.Assert lSavedError = 1004
    Debug.Assert sSavedError = "Unable to get the VLookup property of the WorksheetFunction class"
    Debug.Assert IsEmpty(vLU)

End Sub


Sub Test_Application_VLookup()
    Dim rng As Excel.Range
    Set rng = ThisWorkbook.Sheets.Item(1).Range("A1:b3")
    
    rng.Value = Application.Evaluate("{""Red"",1;""Green"",2;""Blue"",4}")


    Debug.Assert Application.WorksheetFunction.VLookup("Red", rng.Value, 2, False) = 1
    Debug.Assert Application.WorksheetFunction.VLookup("Green", rng.Value, 2, False) = 2
    Debug.Assert Application.WorksheetFunction.VLookup("Blue", rng.Value, 2, False) = 4
    
    Dim vLU As Variant
    vLU = Application.VLookup("Yellow", rng.Value, 2, False)

    Debug.Assert Not IsEmpty(vLU)
    Debug.Assert IsError(vLU)

End Sub



No comments:

Post a Comment