Passing Range Into User Defined Functions

djt76010

Board Regular
Joined
Feb 23, 2007
Messages
109
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.

Code:
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

Board Regular
Joined
Feb 23, 2007
Messages
109
Sorry, that was a typo. It should have been

Code:
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,099
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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:
Code:
Function MyFunction(Age, Table As range)

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

End Function
 

djt76010

Board Regular
Joined
Feb 23, 2007
Messages
109
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

Board Regular
Joined
Feb 23, 2007
Messages
109
Is there an code inefficiency in calling a vlookup function in Visual Basic?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,099
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Compared to in a cell, yes, almost certainly.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,260
Messages
5,769,091
Members
425,516
Latest member
Melzebu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top