Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts

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

    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:

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

    ' strA is a cell reference to the first column in Named Range strB, say $A$1
    ' strB is the name of 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, Button, Title, Response As String

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

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

    End Function

    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


  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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: =JunkTest(LookupValue, TableRange , ColToReturn )
    Vlookup inline calling sequence: =VLookup(LookupValue, TableRange, ColToReturn)

    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    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
    Last edited by BygAuldByrd; 2012-11-15 at 00:37.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Last edited by rory; 2012-11-15 at 01:37.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    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:
    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)

    My sample data table is:

    20121115 JunkTest DataTable.JPG

    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

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

  8. The Following User Says Thank You to rory For This Useful Post:

    BygAuldByrd (2012-11-15)

  9. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •