Is there a way to "look up" a value in a sheet table from a UDF?

[JenniferMurphy]

I would like to write a UDF that can "look up" a value in a sheet table if passed the name of the table and the search text. Here's a simplified example of what I would like to do:

Suppose I have this table in Sheet1 containing the scores in a trivia game:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Amy[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Brad[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Chris
[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Dave[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Emily[/TD]
[TD="align: right"]19[/TD]
[/TR]
</tbody>[/TABLE]

The range $C$4:$D$8 has the global name "TriviaScores".

I would like to write a UDF that can "look up" a player's score if passed the name of the player and the name of the table.

The UDF needs to be able to be called from any sheet in the workbook, not necessarily the sheet where the table is.

This code seems to work.

Public Function Trivia(PlayerName As String, TableName As Range) As Variant

Dim Score As Integer
Dim NumRows As Integer
NumRows = TableName.Rows.Count  'The number of columns

Dim Rowi As Integer
For Rowi = 1 To NumRows
  If UCase(PlayerName) = UCase(TableName(Rowi, 1)) Then
    Trivia = CDbl(TableName(Rowi, 2))
    Exit Function
  End If
Next Rowi

Trivia = CVErr(xlErrName)

End Function

For example, if a cell contains the expression,

=Trivia("Chris","TriviaScores")

The result in that cell would be "35". The "35" needs to be a numeric value (Double).

A few questions:

1. Is there a more efficient way than looping through each row? These tables are going to have just a few rows (<10).
2. Do I need the CDbl function? It seems to work without it.
3. If I change the UDF to Double (from Variant), it returns a Value error instead of the Name error. Why?

 

[JenniferMurphy]

Here is an example of the UDF in action:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Brad[/TD]
[TD="align: center"]21[/TD]
[TD]D12: =trivia(C12,TriviaTable)[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]EMILY[/TD]
[TD="align: center"]19[/TD]
[TD]D13: =trivia(C13,TriviaTable)[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]dave[/TD]
[TD="align: center"]12[/TD]
[TD]D14: =trivia(C14,TriviaTable)[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]aMy[/TD]
[TD="align: center"]28[/TD]
[TD]D15: =trivia(C15,TriviaTable)[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]ChRiS[/TD]
[TD="align: center"]35[/TD]
[TD]D16: =trivia(C16,TriviaTable)[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Zeus[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]D17: =trivia(C17,TriviaTable)[/TD]
[/TR]
</tbody>[/TABLE]

 

[silentwolf]

Hi,
why don`t you use a vlookup in vba.

Just an idea.

 

[JenniferMurphy]

Hi,
why don`t you use a vlookup in vba.

You mean call the worksheet function?

I have had trouble getting that to work in the past. I'll give it a try.

Thanks

 

[JenniferMurphy]

Well, that wasn't too bad and it seems to work:

Public Function Trivia2(PlayerName As String, TableName As Range) As Variant

On Error GoTo Error
Trivia2 = Application.WorksheetFunction.VLookup(PlayerName, TableName, 2, False)
Exit Function

Error:
Trivia2 = CVErr(xlErrNA)

End Function

Thanks

 

[silentwolf]

Hi,
yes I mean worksheet function.

Here is a link it may help you

https://www.exceltrick.com/formulas_macros/vlookup-in-vba/

 

[JenniferMurphy]

Hi,
yes I mean worksheet function.

Here is a link it may help you

https://www.exceltrick.com/formulas_macros/vlookup-in-vba/

That article says that VLookup can be called two ways:

Result = Application.WorksheetFunction.VLookup(...
Result = Application.VLookup(...

Is there any difference between these two calls? If not, why not always use the shorter one?

Thanks

 

[Fluff]

If you use worksheetfunction then you need an error handler, but if you just use Application you dont need the error handler

Dim Check As Variant
Check = Application.vlookup(...)
If IsError(Check) Then
   MsgBox "not found"
End If

 

[JenniferMurphy]

If you use worksheetfunction then you need an error handler, but if you just use Application you dont need the error handler

Dim Check As Variant
Check = Application.vlookup(...)
If IsError(Check) Then
   MsgBox "not found"
End If

This is very helpful. I just tested it and it appears to work exactly as you describe.

Thank you

 

[Fluff]

You're welcome & thanks for the feedback