VBA Function won't work!

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

Help, would greatly appreciated before insanity sets in.




* this function, by the way, is supposed to find the top right hand
value in any excel range. So if, in Excel, you did =topright(A1:F5),
the function would return the value in A5.

On 29 Jun 2006 00:15:07 -0700, [email protected] 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

The first function is actually returning an array of the same
dimensions as the input range, with the top right cell of the
input as its top left; so it just happens to display the right
value. To fix the second function you need to make sure
you pick the single cell you're interested in. The line:

topright = values.Offset(0, values.Columns.Count - 1).Cells(1, 1).2

will do it.

Andrew

[email protected] 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
>
> Help, would greatly appreciated before insanity sets in.
>
>
>
>
> * this function, by the way, is supposed to find the top right hand
> value in any excel range. So if, in Excel, you did =topright(A1:F5),
> the function would return the value in A5.

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, [email protected] 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

Andrew - thank you v much - you hit the nail on th ehead (i did not
know it returned an array!). Piotr, thank you too for your input!
- Chris


Andrew Taylor wrote:
> The first function is actually returning an array of the same
> dimensions as the input range, with the top right cell of the
> input as its top left; so it just happens to display the right
> value. To fix the second function you need to make sure
> you pick the single cell you're interested in. The line:
>
> topright = values.Offset(0, values.Columns.Count - 1).Cells(1, 1).2
>
> will do it.
>
> Andrew
>
> [email protected] 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
> >
> > Help, would greatly appreciated before insanity sets in.
> >
> >
> >
> >
> > * this function, by the way, is supposed to find the top right hand
> > value in any excel range. So if, in Excel, you did =topright(A1:F5),
> > the function would return the value in A5.

Try:

Function topright(values As Range) As Double
topright = values(1, values.Columns.Count) * 2
End Function

The reason your function doesn't work is because the Offset method returns a
range of cells and your function is tyring to multiply an array by a single
element (i.e. 2).

Regards,
Greg
"[email protected]" 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
>
> Help, would greatly appreciated before insanity sets in.
>
>
>
>
> * this function, by the way, is supposed to find the top right hand
> value in any excel range. So if, in Excel, you did =topright(A1:F5),
> the function would return the value in A5.
>
>

On 29 Jun 2006 00:39:13 -0700, [email protected] wrote:

> 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.

Right. I've focused on wrong aspect of your code. Anyway, you got answer
from AT.

--
PL