Passing Range Into User Defined Functions

[djt76010]

I have created a user defined function that reads in a named range "Table" that consists of two columns. The first column contains integer ages from 15 to 70 and the second column is the number I need to manipulate. Here is a simplified version of it.

Function MyFunction(Age Table)

    Dim MyRANGE As Range

    Set MyRANGE = ThisWorkbook.Names(Table).RefersToRange
   
    MyFunction = Application.VLookup(Age, MyRANGE, 2, False)

End Function

I am having speed issues and was wondering if there is a more efficient way to replace the vlookup function.

 

[PA HS Teacher]

Is 'Age Table' in the function declaration the same as 'Table' in your Vlookup call?

How Large is the named range 'Table'

How does the speed compare to putting the Vlookup directly in the spreadsheet instead of involving VBA.

Is the purpose of the function my to make it 'easier' to use the vlookup?

 

[djt76010]

Sorry, that was a typo. It should have been

Function MyFunction(Age, Table)

    Dim MyRANGE As Range

    Set MyRANGE = ThisWorkbook.Names(Table).RefersToRange
   
    MyFunction = Application.VLookup(Age, MyRANGE, 2, False)

End Function

I have created a number of user defined functions that recursively rely on the values calculated in this function. The actual function does more than just the vlookup, but I was wondering if the function itself is the most efficient way to extract data from a named range.

 

[xenou]

I don't see how a vlookup in a UDF will work faster than it would as a normal worksheet function. Generally Excel's inbuilt formulas are the most efficient.

 

[RoryA]

I'd agree native formulas are generally better, but FWIW, you shoudl really pass the range as a range so that your function updates if you change the range values:

Function MyFunction(Age, Table As range)

    MyFunction = Application.VLookup(Age, Table, 2, False)

End Function

 

[djt76010]

I don't see how a vlookup in a UDF will work faster than it would as a normal worksheet function. Generally Excel's inbuilt formulas are the most efficient.

I am creating an Excel add-in with named ranges built into the Excel Add-In file and not viewable by the user. This function is then used as a building block for numerous other functions and unfortunately the number of calculations involved cause the functions to be slower than I would like. In looking to streamline the code, I was curious if calling Excel functions in VBasic was the most efficient way to go.

 

[djt76010]

Is there an code inefficiency in calling a vlookup function in Visual Basic?

 

[RoryA]

Compared to in a cell, yes, almost certainly.