WorksheetFunction error sub or function not defined

Question

I was wondering if someone could assist me please. I have created a very simple userform to log information. My issue is however when one of the fields is blank I am receiving an error message :

Sub or Function not defined.

Private Sub CommandButton1_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a Name number
    If Trim(QueryType.Value) = "" Then
        QueryType.SetFocus
            MsgBox "Please complete the form"
        Exit Sub
    End If

    'copy the data to the database
    ws.Cells(iRow, 1).Value = Format(Now, "DD/MM/YYYY")
    ws.Cells(iRow, 2).Value = Format(Now, "HH:MM")
    ws.Cells(iRow, 3).Value = Application.UserName
    ws.Cells(iRow, 4).Value = CType.Value
    ws.Cells(iRow, 5).Value = IName.Value
    ws.Cells(iRow, 6).Value = QType.Value
    ws.Cells(iRow, 7).Value = 1
    ws.Cells(iRow, 8).Value = Format(Date, "MMM-YY")
    'ws.Cells(iRow, 9).Value = Application.WorksheetFunction.VLookup(InternalName.Value, Sheet2.Range("C1:D23"), 2, 0)
    'ws.Cells(iRow, 9).Value = Application.WorksheetFunction.IfError(VLookup(InternalName.Value, Sheet2.Range("C1:D23"), 2, 0), "")
   ws.Cells(iRow, 10).Value = Application.WorksheetFunction.VLookup(InternalName.Value, Sheet2.Range("C1:E23"), 3, 0)
    ws.Cells(iRow, 11).Value = "IB"

Unload Me

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"

End Sub

The issue is in either one of the commented out lines where I receive the error. I only receive if I leave the dropdown box empty. If populated then no error occurrs. I could easily fit an extra menu option for "Not Applicable" however would rather it was just blank. Does anyone have any suggestions at all please?

Answer 1

Three things.

1- VLookup is not a VBA function on its own, it's a member method of either the Application object or the WorksheetFunction object. So you should qualify VLookup by either, even if you had it inside another method, such as WorksheetFunction.IfError(...).

ws.Cells(iRow, 9).Value = 
WorksheetFunction.IfError(WorksheetFunction.VLookup(InternalName.Value, Sheet2.Range("C1:D23"), 2, 0), "")
'                         ^^^^^^^^^^^^^^^^^^

2- The same methods in WorksheetFunction and in Application objects work differently. In the former, an error is raised in VBA if the result is an error (such as: no match for the searched value). In the latter, no error is raised, but the returned value is an Error Variant. This latter form is usually safer for VBA, because you dont need to have some On Error Resume Next or so, but you can just check the result with If(IsError(result)).

3- When your search criteria is empty or unmatched, you had the error raised due to the use of WorksheetFunction.VLookup (According to 2). If your intention is to just set the resulting value and proceed, you can use Application.VLookup instead:

ws.Cells(iRow, 9).Value = Application.IfError(Application.VLookup(InternalName.Value, Sheet2.Range("C1:D23"), 2, 0), "")

p.s. I personally prefer Application. most of the time. Some prefer WorksheetFunction mostly because it offers Intellisense, but I find that pretty useless, because the arguments of the methods in Intellisense are unnamed and untyped, i.e. VLookup(arg1, arg2, arg3, [arg4]).. (non-sense to me).