Excel VBA: How to Put a Named Range in User Defined Function using VLookup

Topic

New Reply

I am attempting to build an Excel VBA User Defined Function (UDF) that uses VLookup within the UDF to find a value from a table of dates and values. The dates are in column $A and the values in column $C

My function looks like this:

[INDENT]Function JunkTest(strA As String, strB As String, intC As Integer) As String[/INDENT]
[INDENT]
[/INDENT]
[INDENT]’ strA is a cell reference to the first column in Named Range strB, say $A$1[/INDENT]
[INDENT]’ strB is the name of a Named Range, say “JunkTable” having the range $A$1:$D$10[/INDENT]
[INDENT]’ intC is the column number of the value to be extracted by the VLookUp function with this UDF[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Dim Msg, Button, Title, Response As String[/INDENT]
[INDENT]
[/INDENT]
[INDENT]JunkTest = Application.WorksheetFunction.VLookup(strA, Range(strB), intC, False)[/INDENT]
[INDENT]
[/INDENT]
[INDENT] Title = “JunkTest Function…”[/INDENT]
[INDENT] Button = vbExclamation[/INDENT]
[INDENT] Msg = “strA: ” & strA & vbCrLf & _[/INDENT]
[INDENT] “strB: ” & strB & vbCrLf & _[/INDENT]
[INDENT] “intC: ” & intC & vbCrLf & _[/INDENT]
[INDENT] “JunkTest: ” & JunkTest[/INDENT]
[INDENT] Response = MsgBox(Msg, Button, Title)[/INDENT]
[INDENT]
[/INDENT]
[INDENT]End Function

[/INDENT]
The above Function results in #VALUE!

The problem I have is how to bring the named range into the function and the expand within the VLookup function.

I also have concerns about the formation of the VLookup() statement.

Any assistance will be much appreciated.

Cheers

BygAuldByrd

Reply | Quote

Replies

Can I ask why you are passing two range references as strings? Why not pass them as ranges? (if you don’t you need to make the function volatile)

Reply | Quote

BygAuldByrd,

I’m having a problem seeing what you’re trying to accomplish by this code? You are merely adding the overhead of calling code to accomplish the same thing you can do in line, e.g.
Your Function calling sequence: [noparse]=JunkTest(LookupValue, TableRange , ColToReturn )[/noparse]
Vlookup inline calling sequence: [noparse]=VLookup(LookupValue, TableRange, ColToReturn)[/noparse]

My question why use a User Defined Function that doesn’t save you any coding?

Also in your example code comments your LookupValue is the 1st value in the TableRange, hopefully this was just an oversight or typo. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RetiredGeek and Rory,

To RetiredGeek: The code I included in my post is only a snippet for a much longer function that will be required to do other vlookups related to that first lookup. I only posted the snippet to resolve the particular problem at hand. Once it is solved I can move on to my more complex coding issues.

Setting the first value in the table was fortuitous only, it could just as easily been $A$5.

To Rory: Strings or Ranges – I’ll use whichever provides a solution. At the moment I get the same result regardless of which variable type I use.

Cheers

BygUaldByrd

Reply | Quote

How are you calling the function (ie what are you entering in the cell)? You will get an error if the lookup value is not found since you don’t have an error handler in your function. Also your code requires the lookup table to be on the active sheet at the time the function calculates which is why you should pass the ranges as ranges rather than the text names of ranges.

Reply | Quote

Hi Rory,

In testing thus far the formula is being used in a test workbook with only 1 sheet, thus the formula is being used in the active sheet.

The function is called in a worksheet cell in the form: =JunkTest(strA,strB,intC)
For example: =JunkTest(a5,DataTable,3)

Where: [INDENT]A5 is a date (all data in column A are dates)
DataTable is the named range $A$1:$D$10 in the active sheet (I do not want to use specific ranges as this seriously limits the utility of the resulting function)
The value being sought is in column C, or in vlookup parlance, 3 (all data in column C is text)[/INDENT]

My sample data table is:

32375-20121115-JunkTest-DataTable

Regardless of whether strA and strB are defined as String or Range, or strB is entered as the Named Range “DataTable” or as “$A$1:$D$10”, the result is always #VALUE!

Hope this clarifies what I’m trying to do.

Cheers

BygAuldByrd

Reply | Quote

If you use ranges the error occurs in your Msg statement when you try and append the entire datatable array into the message

Try:

Code:

Function JunkTest(strA As Range, strB As Range, intC As Integer) As String

' strA is a cell in the first column in Named Range strB, say $A$1
' strB is a Named Range, say JunkTable having the range $A$1:$D$10
' intC is the column number of the value to be extracted by the VLookUp function with this UDF


   Dim Msg As String, Button, Title As String, Response As String


   JunkTest = Application.WorksheetFunction.VLookup(strA, strB, intC, False)


   Title = "JunkTest Function..."
   Button = vbExclamation
   Msg = "strA: " & strA & vbCrLf & _
         "strB: " & strB.Address & vbCrLf & _
         "intC: " & intC & vbCrLf & _
         "JunkTest: " & JunkTest
   Response = MsgBox(Msg, Button, Title)


End Function

Reply | Quote

Hi Rory,

Many thanks – that’s solve my problem for the time being. Now to continue with the rest of my exotic function.

Cheers

BygAuldByrd

Reply | Quote