Worksheet function in VBA

Question

So I'm trying to create a function in VBA to whenever I type in specific parameters to a function in excel that'll give me the outcome of those parameters.

For example, I have a list of names in one column; John, Jane, and Joey.... and then in another column I've got numbers that coincide with those names... The objective here is for the user to type in a function in any given cell with the name and have the spreadsheet output the number that goes with that name.

This is what I've got but I've hit a dead end because I've got no clue what to do.

Function NumberOfHits(Name As String)
Dim Vegetable As String 
NumberOfHits = Application.WorksheetFunction.VLookup(Name, Sheets("Hits From Player").Range("B38:D74"), 3)  
End Function 

Answer 1

This works fine for me both on and off the sheet called Hits From Player once

1. I made the function volatile (so that it updates). You can also use RAND() in combination with this formula for the same impact.

2. Did you remember to enclose name in " "

   =NumberOfHits("mary")

Recut function:

Function NumberOfHits(Name As String)
    Application.Volatile
    NumberOfHits = Application.WorksheetFunction.VLookup(Name, _
    Sheets("Hits From Player").Range("B38:D74"), 3)
End Function

Updated:to handle no exact match try

Function NumberOfHits(strName As String) As Variant
Dim StrOut

Application.Volatile

On Error Resume Next
StrOut = Application.WorksheetFunction.VLookup(strName, _
Sheets("Hits From Player").Range("B38:D74"), 3, False)
On Error GoTo 0

If IsEmpty(StrOut) Then
    NumberOfHits = CVErr(xlErrNA)
Else
    NumberOfHits = StrOut
End If
End Function