Geek Answers Handbook

Vlookup and ISERROR

I am using Office 2007. I have a PowerPoint macro that uses an Excel spreadsheet to perform vLookup. I made a public function for vLookup. It works well when all values ​​are properly filed. Now I'm trying to capture errors for those applications where the search value can not be found. Function Code:

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 main 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 search fails, I hit Debug, pointing to

  varResult = objExcelAppVL.Application.WorksheetFunction.VLookup

.. expression. He never misses the operator of If IsError(varResult)... , when a vlookup error. How to correctly catch vLookup error?

+5
source share
1 answer

Object WorksheetFunction returns error values back to the embodiment; he just choking on them. Use the Excel object to WorksheetFunction function could not work with error value. You have already created the Excel.Application object; use this.

Recall for the creation (and destruction) with the application object CreateObject function can be avoided by making the object variable declaration static. This is particularly useful in the UDF, which can be copied on a long column.

Own worksheet function VLOOKUP written to allow full conversion columns without penalty; truncation of full column references a property Worksheet.UsedRange help of 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 .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 _ 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 .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 String 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 .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 ) 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 .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 .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 .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 ) 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 .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 specifically pass the string, so the numbers and dates will be their text equivalents. I would suggest that this is the best way to get value in PowerPoint.

udf_vlookup

+5
source

More articles:


All Articles