The function wizard calls the specified function *during* data entry by
the user. It is called as soon as the user enters something in the
last non-optional field (*something* as in *any* thing). Your function
has to be able to handle that. That means that the use of Msgbox can
be extremely disruptive. So, it is not weird that removing the Msgbox
fixes the problem. Also, if I were you, I would not relegate the
informative error message to a interruptive msgbox but actually return
the information(instead of the oh-so-60s #N/A or #VALUE stuff)

To see how the function wizard works, note that in its dialog box, the
result of the function are shown *twice.* The first display is to the
right of and just below the area where the arguments are specified.
The second is towards the bottom left of the dialog box where it reads
"Formula result=". In fact the function is *called* twice. This
becomes very obvious with the following two functions. Try using them
with the function wizard and observe when the wizard starts displaying
the results as well as what the results are.

Function getRand(a, b, c)
getRand = Rnd()
End Function
Function getRandOpt(Optional a, Optional b, Optional c)
getRandOpt = Rnd()
End Function

Also, you could better leverage the error handling and not leave it to
the user to guess what didn't work.

If I were in your shoes, I would code your function along the lines of:

Function GETENTRY(atRow As Double, atCol As Double, _
inTable As Range) As Variant
Dim i As Long, j As Long
On Error Resume Next
With Application.WorksheetFunction
i = .Match(atRow, inTable.Columns(1), 0)
j = .Match(atCol, inTable.Rows(1), 0)
End With
On Error GoTo 0
If i = 0 Then
GETENTRY = "atRow value (" & atRow & ") not found"
ElseIf j = 0 Then
GETENTRY = "atCol value (" & atCol & ") not found"
Else
GETENTRY = inTable(i, j)
End If
End Function

The above function works for direct data entry or through the function
wizard.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Sorry, I'm back again.

I've written a user-defined function to replace the often confusing
V/HLookup(...Match(...) .) combination used in spreadsheets to return an
element in a bordered (top and left edges) table. Here's the code:

Function GETENTRY(atRow As Variant, _
atCol As Variant, _
inTable As Range) As Variant

Dim i As Integer, j As Integer

On Error Resume Next
With Application
i = .Match(atRow, inTable.Columns(1), 0)
j = .Match(atCol, inTable.Rows(1), 0)
End With

If Err < 0 Then
MsgBox "One of " & atRow & " or " & atCol & " _
not found.", vbCritical
GETENTRY = CVErr(xlErrNA)
Exit Function
End If

GETENTRY = inTable(i, j)

End Function

Here's the problem: It works fine if invoked directly, but if I use Excel's
Paste (Insert) function dialogue box, then as soon as I click on the top
left corner of the range for the 3rd arg (inTable), it triggers the error
message.

(Actually, it doesn't exit the function as I expect either, instead it
flashes the message and waits for new input. Hitting the cancel
button on the dialogue box triggers the message again. But that's
a whole nuther confusion and I don't want to get side-tracked.)

I can avoid this outcome in 3 ways:

a) enter one of the other two args as the last one specified in the dialogue
box,
b) remove the On Error Resume Next statement, or
c) and this is really wierd, remove the MsgBox statement.

Choice a) is clearly not reasonable - I can't expect others to know that
trick - and choices b) & c) frustrate my *truly wonderful* error handling.

('course, the only reason I found out about this bug is that I was so
excited to see Excel list MY FUNCTION that I just had to try it. Hubrous
was my downfall.)

I'm really confused about this, especially the REM'ing out the msgbox part.
Does anyone have any idea about what's going on.