Vlookup and ISERROR

Question

I'm using Office 2007. I have a PowerPoint macro that uses an Excel worksheet to perform vLookup. I made a public function for the vLookup. It works well when all the values are properly supplied. Now I'm trying to capture errors for those conditions where the lookup value can't be found. The function code is:

Public Function v_lookup _
            (lookup_value As Variant, _
            table_array As Range, _
            col_index_num As Integer, _
            range_lookup As Boolean) _
            As String

Dim varResult           As Variant
Dim objExcelAppVL       As Object
Set objExcelAppVL = CreateObject("Excel.Application")
objExcelAppVL.Visible = False

varResult = objExcelAppVL.Application.WorksheetFunction.VLookup _
            (lookup_value, _
            table_array, _
            col_index_num, _
            range_lookup)
If IsError(varResult) Then varResult = ""
v_lookup = varResult

objExcelAppVL.Quit
Set objExcelAppVL = Nothing

End Function

I call this function from the primary macro with the following statement:

varGatherNumber = v_lookup(varDateTime, Lit_Sched_Table_Lookup, 5, vbFalse)

This code works well when there are no errors. The problem is that when the lookup fails, I'm thrown into Debug pointing to the,

 varResult = objExcelAppVL.Application.WorksheetFunction.VLookup

.. statement. It never gets to the If IsError(varResult)... statement when there's a vlookup error. How can I properly trap a vLookup error?

Answer 1

The WorksheetFunction object does not pass error values back to a variant; it simply chokes on them. Use the Excel Application object without the WorksheetFunction to be able to work with an error value. You have already created an Excel.Application object; use that.

Repeated call to construct (and destruct) an application object with the CreateObject function can be avoided by making the object variable declaration static. This is particularly useful in a UDF that may be copied down a long column.

The native worksheet VLOOKUP function is written to allow full column referencing without penalty; truncating full column references to the Worksheet.UsedRange property will help this function.

Option Explicit

Public Function v_lookup(lookup_value As Variant, _
                         table_array As Range, _
                         col_index_num As Integer, _
                         Optional range_lookup As Boolean = False) As String

    Dim varResult           As Variant
    Static objExcelAppVL    As Object


    'only create the object if it doesn't exist
    If objExcelAppVL Is Nothing Then
        Set objExcelAppVL = CreateObject("Excel.Application")
        objExcelAppVL.Visible = False
    End If

    'restrict full column references to the worksheet's .UsedRange
    Set table_array = objExcelAppVL.Intersect(table_array.Parent.UsedRange, table_array)

    varResult = objExcelAppVL.VLookup(lookup_value, _
                                      table_array, _
                                      col_index_num, _
                                      range_lookup)

    If IsError(varResult) Then varResult = ""
    v_lookup = varResult

    'do not destruct static vars - they are reused on subsequent calls
    'objExcelAppVL.Quit
    'Set objExcelAppVL = Nothing

End Function

I see that you are specifically passing back a string so numbers and dates will be their text equivalents. I would suppose this is the best way to receive the values in PowerPoint.