OFFSET and UDF bug

[Charles Williams]

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

 

[Bernie Deitrick]

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]

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

Charles,

Your first formula seems like a fairly non-standard usage of OFFSET, and

seems to result in two different values for the parameter