Rewrite a table lookup function in VBA

Found the GetTableEntry function below on this site:

It works but I would like to rewrite it so I could use an actual named Excel table as the LookupTable parameter instead of a range.

It workes with this formula: =gettableentry(A1:C3,"Ben","Orange")
I would like to rewrite to work with this: =gettableentry("tblFruits","Ben","Orange")

Public Function GetTableEntry( _
            ByVal LookupTable As Range, _
            ByVal RowHeader As String, _
            ByVal ColHeader As String)

    With Application
        With .WorksheetFunction
            GetTableEntry = LookupTable.Parent.Cells( _
                .Match(RowHeader, LookupTable.Columns(1), 0) + LookupTable.row - 1, _
                .Match(ColHeader, LookupTable.Rows(1), 0) + LookupTable.Column - 1).Value
        End With
    End With
End Function

Open in new window

Who is Participating?
MacroShadowConnect With a Mentor Commented:
No need to rewrite anything, just pass the table name.

i.e. GetTableEntry(tblFruits, "Ben", "Orange")
NBVCConnect With a Mentor Commented:
Just a quick observation... tblFruits should not be in quotes if it is a named range.
hallpettAuthor Commented:
NBVC, your observation gave me an idea. It workes when I change:

( the [#Alle] is Norwegian Excel, guess it should be [#All] in English Version)

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

You're welcome
how is mine assisted, when it is the same solution as the other, and given first?
hallpettAuthor Commented:
Sorry. I just share the points between you since you gave med the idea to do some more testing and MacroShadow presicely told me that there was no need for rewriting. Probably should have given you the "Accepted solution", but was not aware that this ment something.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.