ExcelBanter - View Single Post

Piotr Lipski

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