Vlookup и ISERROR

Question

Я использую Office 2007. У меня есть макрос PowerPoint, который использует лист Excel для выполнения vLookup. Я сделал публичную функцию для vLookup. Это хорошо работает, когда все значения правильно указаны. Теперь я пытаюсь зафиксировать ошибки для тех условий, где значение поиска не может быть найдено. Функциональный код:

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

Я вызываю эту функцию из основного макроса со следующим утверждением:

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

Этот код хорошо работает, когда нет ошибок. Проблема в том, что, когда поиск не удается, я брошен в Debug, указывая на

 varResult = objExcelAppVL.Application.WorksheetFunction.VLookup

.. заявление. Это никогда не добирается до If IsError(varResult)... заявление, когда есть ошибка vlookup. Как правильно перехватить ошибку vLookup?

Answer 1

Объект WorksheetFunction не передает значения ошибок обратно в вариант; это просто душит их. Используйте объект приложения Excel без функции WorksheetFunction, чтобы иметь возможность работать со значением ошибки. Вы уже создали объект Excel.Application; используйте это.

Можно избежать повторного вызова для создания (и уничтожения) объекта приложения с помощью функции CreateObject, сделав объявление переменной объекта статическим. Это особенно полезно в UDF, который может быть скопирован в длинный столбец.

Встроенная функция VLOOKUP рабочего листа написана так, чтобы разрешить полную ссылку на столбец без штрафа; Обрезка полных ссылок на столбцы в свойстве Worksheet.UsedRange поможет этой функции.

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

Я вижу, что вы специально передаете обратно строку, чтобы числа и даты были их текстовыми эквивалентами. Я бы предположил, что это лучший способ получить значения в PowerPoint.