Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function won't work!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function won't work!
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function won't work!
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 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function won't work!
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 " 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function won't work!
On 29 Jun 2006 00:39:13 -0700, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function doesn't work | Excel Worksheet Functions | |||
need a function that will work using multiple work books and sheet | Excel Worksheet Functions | |||
Why does my Function not work? | Excel Discussion (Misc queries) | |||
NZ function doesn't work! | Excel Programming | |||
Why it can work in Function but ok in Sub | Excel Programming |