Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OFFSET and UDF bug
There seems to be a bug in the way UDFs handle input Range parameters which
use OFFSET: In a workbook with more than one sheet add the following UDF: Function SheetCheck(theRange as range) SheetCheck=theRange end function on Sheet 1 put 1 in cell a1 on Sheet2 put 2 in cell a1 in sheet 1 b1 put =sheetcheck(Sheet1!$A$1:OFFSET(Sheet1!$A$1,1,0)) in sheet 1 b2 put =sheetcheck(OFFSET(Sheet1!$A$1,0,0,1,1)) select Sheet 1 and press F9: both cells show 1 select Sheet 2 and press F9: select sheet 1 and you will see that B1 shows 2 but B2 shows 1 The formula in B1 incorrectly refers to the active sheet rather than Sheet1 (tested on Excel97 Excel2000 and Excel2002) This bug also happens when using Defined names containing these kinds of formulae: this makes it important when using Dynamic Range Names to use the second type of Offset formulae rather than the first. Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
OFFSET and UDF bug
Charles,
Your first formula seems like a fairly non-standard usage of OFFSET, and seems to result in two different values for the parameter passed to the UDF. The first parameter is A1:A2 and the second would be just A1 Or am I mis-interpreting your formula? Is there some advantage to your first OFFSET usage compared to the standard usage? HTH, Bernie Excel MVP "Charles Williams" wrote in message ... There seems to be a bug in the way UDFs handle input Range parameters which use OFFSET: In a workbook with more than one sheet add the following UDF: Function SheetCheck(theRange as range) SheetCheck=theRange end function on Sheet 1 put 1 in cell a1 on Sheet2 put 2 in cell a1 in sheet 1 b1 put =sheetcheck(Sheet1!$A$1:OFFSET(Sheet1!$A$1,1,0)) in sheet 1 b2 put =sheetcheck(OFFSET(Sheet1!$A$1,0,0,1,1)) select Sheet 1 and press F9: both cells show 1 select Sheet 2 and press F9: select sheet 1 and you will see that B1 shows 2 but B2 shows 1 The formula in B1 incorrectly refers to the active sheet rather than Sheet1 (tested on Excel97 Excel2000 and Excel2002) This bug also happens when using Defined names containing these kinds of formulae: this makes it important when using Dynamic Range Names to use the second type of Offset formulae rather than the first. Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
OFFSET and UDF bug
Hi Bernie,
to make the two formulae strictly comparable I suppose the second should be =sheetcheck(OFFSET(Sheet1!$A$1,0,0,2,1)) but it makes no difference to the bug. There is no advantage to to the first formula: as outlined it can give the wrong answer with UDFs, but the correct answer with SUM. I don't know which is the "standard" useage. I have seen both approaches used in dynamic range formulae and they seem to work equally well, apart from this UDF bug. It would be good if someone could test the example given on their system to see if they get the same results. regards Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Bernie Deitrick" wrote in message ... Charles, Your first formula seems like a fairly non-standard usage of OFFSET, and seems to result in two different values for the parameter passed to the UDF. The first parameter is A1:A2 and the second would be just A1 Or am I mis-interpreting your formula? Is there some advantage to your first OFFSET usage compared to the standard usage? HTH, Bernie Excel MVP "Charles Williams" wrote in message ... There seems to be a bug in the way UDFs handle input Range parameters which use OFFSET: In a workbook with more than one sheet add the following UDF: Function SheetCheck(theRange as range) SheetCheck=theRange end function on Sheet 1 put 1 in cell a1 on Sheet2 put 2 in cell a1 in sheet 1 b1 put =sheetcheck(Sheet1!$A$1:OFFSET(Sheet1!$A$1,1,0)) in sheet 1 b2 put =sheetcheck(OFFSET(Sheet1!$A$1,0,0,1,1)) select Sheet 1 and press F9: both cells show 1 select Sheet 2 and press F9: select sheet 1 and you will see that B1 shows 2 but B2 shows 1 The formula in B1 incorrectly refers to the active sheet rather than Sheet1 (tested on Excel97 Excel2000 and Excel2002) This bug also happens when using Defined names containing these kinds of formulae: this makes it important when using Dynamic Range Names to use the second type of Offset formulae rather than the first. Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Q | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Using Offset in VBA | Excel Discussion (Misc queries) | |||
OFFSET HELP | Excel Worksheet Functions | |||
Offset help | Excel Discussion (Misc queries) |