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
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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment