Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
LastInColumn() is a VBA function I got from the web for finding the
last value in a colummn. It works, and returns a reference to the cell instead of the value. I want to look up the last value in P, move over to L and look up the last value before that. I've figured out how to do this correctly. My question is about the operation of the OFFSET function. Two different ways to get the same range, or so I thought: (1) =LastInColumn(OFFSET(L$1,0,0,ROW(LastInColumn(P$1: P30)),1)) (2) =LastInColumn(L$1:OFFSET(LastInColumn(P$1:P30),0,-4,1,1)) I have these formulas in multiple worksheets. (1) always works properly. The problem is (2) has some very odd behaviour. What happens is on the active worksheet (2) works correctly, but on the non-active worksheets (2) updates immediately (Is OFFSET volatile? Seems to be.) and gets a range that includes cells from the active worksheet! This then causes the formula to get incorrect data. This happens whether I use my function inside the OFFSET() call, or simply hardcode say P23. Debugging my function shows that some cells in the range come from the wrong worksheet. So it definitely seems to be a problem with the L$1:OFFSET() notation. I think this should work, but it obviously doesn't. Why not? Of course, I started out with (2) because it avoids one level of nesting. I was able to use (1) when I found CHOOSE() which flattened out the nested IF() problem. ....Stu |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
A couple of thing... Yes Offset is Volatile. Secondly post your UDF. It
probably/possibly has references to activesheet (possible by default) which it should not have. -- HTH... Jim Thomlinson "Stuart MacDonald" wrote: LastInColumn() is a VBA function I got from the web for finding the last value in a colummn. It works, and returns a reference to the cell instead of the value. I want to look up the last value in P, move over to L and look up the last value before that. I've figured out how to do this correctly. My question is about the operation of the OFFSET function. Two different ways to get the same range, or so I thought: (1) =LastInColumn(OFFSET(L$1,0,0,ROW(LastInColumn(P$1: P30)),1)) (2) =LastInColumn(L$1:OFFSET(LastInColumn(P$1:P30),0,-4,1,1)) I have these formulas in multiple worksheets. (1) always works properly. The problem is (2) has some very odd behaviour. What happens is on the active worksheet (2) works correctly, but on the non-active worksheets (2) updates immediately (Is OFFSET volatile? Seems to be.) and gets a range that includes cells from the active worksheet! This then causes the formula to get incorrect data. This happens whether I use my function inside the OFFSET() call, or simply hardcode say P23. Debugging my function shows that some cells in the range come from the wrong worksheet. So it definitely seems to be a problem with the L$1:OFFSET() notation. I think this should work, but it obviously doesn't. Why not? Of course, I started out with (2) because it avoids one level of nesting. I was able to use (1) when I found CHOOSE() which flattened out the nested IF() problem. ....Stu |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
Jim Thomlinson wrote:
A couple of thing... Yes Offset is Volatile. Secondly post your UDF. It probably/possibly has references to activesheet (possible by default) which it should not have. It's a function I found on the web, but I have modified it a little: Function LastInRange(InputRange As Range) Dim CellCount As Long Dim i As Long CellCount = InputRange.Count For i = CellCount To 1 Step -1 If Not IsEmpty(InputRange(i)) And IsNumeric(InputRange(i)) Then Set LastInRange = InputRange(i) Exit Function End If Next i LastInRange = "" End Function I don't see where an ActiveSheet reference would hide, but I'm newish at VB/A. ....Stu |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
Your UDF does not reference the active sheet explicitly. UDF's however assume
teh active sheet unless otherwise specified. When a range is passed in the sheet it came from is normally implied as ranges can only exist in one sheet. When not specified otherwise ranges are assumed to originate in the sheet where the formula is entered. In your example 2 when you essentially create the range that you pass to the function you do not speicfy the sheet anywhere so the UDF assumes the active sheet. The problem arises because you are creating the range in in pieces. The sheet it originates from is lost. -- HTH... Jim Thomlinson "studog" wrote: Jim Thomlinson wrote: A couple of thing... Yes Offset is Volatile. Secondly post your UDF. It probably/possibly has references to activesheet (possible by default) which it should not have. It's a function I found on the web, but I have modified it a little: Function LastInRange(InputRange As Range) Dim CellCount As Long Dim i As Long CellCount = InputRange.Count For i = CellCount To 1 Step -1 If Not IsEmpty(InputRange(i)) And IsNumeric(InputRange(i)) Then Set LastInRange = InputRange(i) Exit Function End If Next i LastInRange = "" End Function I don't see where an ActiveSheet reference would hide, but I'm newish at VB/A. ....Stu |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
Jim Thomlinson wrote:
When not specified otherwise ranges are assumed to originate in the sheet where the formula is entered. In your example 2 when you essentially create the range that you pass to the function you do not speicfy the sheet anywhere so the UDF assumes the active sheet. The problem arises because you are creating the range in in pieces. The sheet it originates from is lost. I think I understand: OFFSET() is volatile, so it causes a recalc on the non-active sheet. The range that is constructed on the non-active sheet is missing a sheet specifier, and thus *assumes the active sheet, instead of the sheet it resides on*? That would be the source of my confusion; I assumed, sans specifier, the range would refer to its home worksheet. Is there a reason for making it work like that? ....Stu |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
Because you are making the range yourself you are overriding XL from making
what seems like a fairly simple decision. It is just an odd quirk of UDF's (that to be quite honest I have never run into before but when I thought about it it made sense)... -- HTH... Jim Thomlinson "studog" wrote: Jim Thomlinson wrote: When not specified otherwise ranges are assumed to originate in the sheet where the formula is entered. In your example 2 when you essentially create the range that you pass to the function you do not speicfy the sheet anywhere so the UDF assumes the active sheet. The problem arises because you are creating the range in in pieces. The sheet it originates from is lost. I think I understand: OFFSET() is volatile, so it causes a recalc on the non-active sheet. The range that is constructed on the non-active sheet is missing a sheet specifier, and thus *assumes the active sheet, instead of the sheet it resides on*? That would be the source of my confusion; I assumed, sans specifier, the range would refer to its home worksheet. Is there a reason for making it work like that? ....Stu |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
Jim Thomlinson wrote:
Because you are making the range yourself you are overriding XL from making what seems like a fairly simple decision. It is just an odd quirk of UDF's (that to be quite honest I have never run into before but when I thought about it it made sense)... So it seems to be a combination of the volatile worksheet function, and the range construction then. Okay. Thanks, ....Stu |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET oddity
Very interesting glitch !
And no easy way to fix it... You should send a "bug report" to Microsoft... Thanks. -- Festina Lente "studog" wrote: Jim Thomlinson wrote: Because you are making the range yourself you are overriding XL from making what seems like a fairly simple decision. It is just an odd quirk of UDF's (that to be quite honest I have never run into before but when I thought about it it made sense)... So it seems to be a combination of the volatile worksheet function, and the range construction then. Okay. Thanks, ....Stu |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Question for use of offset and range | Excel Worksheet Functions |