Piotr - many many thanks for the quick reply. I understand what you are
doing in your function (-checking for non numeric values), but that was
not what I had meant to find out.

What I am trying to find out is why, when there *is* a numeric value in
the top right hand cell of the "values" range, why can it be not
multiplied without creating an error? (i.e. I can actually multiply the
result of the topright() function in Excel and get the right answer,
but I cannot do that inside the VBA fucntion itself without creating an
error). Any ideas? Many thanks as always!



Piotr Lipski wrote:
On 29 Jun 2006 00:15:07 -0700, wrote:

Can anybody please enlighten me why this Excel VBA function* works:

Function topright(values)
topright = values.Offset(0, values.Columns.Count - 1)
End Function

and why this one does not (it alwasy returns a #VALUE! error):

Function topright(values)
topright = values.Offset(0, values.Columns.Count - 1) * 2
End Function

Public Function TopRight(ByRef rngValues As Excel.Range) As Variant
Dim varTmp As Variant
On Error GoTo tr_err
VarTmp = values.Offset(0, values.Columns.Count - 1)
If(IsNumeric(VarTmp)) Then
TopRight = values.Offset(0, values.Columns.Count - 1) * 2
Else
TopRight = Null 'or 0 or "err" or ...
End If

tr_ex:
Exit Function

tr_err:
TopRight = Null 'or 0 or "err" or...
Resume tr_ex
End Function