Found a glitch in the OFFSET function.

[mickey]

While developing a spreadsheet I noticed that the "Save" window kept popping
up even though no changes had been made to the sheet. After a lengthy
investigation I tracked to problem to the OFFSET function. I had just
replaced several LOOKUP functions with OFFSET functions: which I considered
would be faster. When I went back to the lookup functions the "Save" window
quit popping up. I did some experiments on a separate spreadsheet with the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous "Calculate"
events were being generated. For example double-clicking on a row or column
to check the height or width caused a calulation when I released my finger
after the second click (but not before). I have since created my own offset
function in visual basic which works perfectly and does not cause any
anomalous events and the "Save" pop-up does not occur, unless of course I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ab7e720&dg=microsoft.public.excel.programming

 

[Peter T]

Offset is a volatile function, Lookup functions aren't.

Regards,
Peter T

While developing a spreadsheet I noticed that the "Save" window kept popping
up even though no changes had been made to the sheet. After a lengthy
investigation I tracked to problem to the OFFSET function. I had just
replaced several LOOKUP functions with OFFSET functions: which I considered
would be faster. When I went back to the lookup functions the "Save" window
quit popping up. I did some experiments on a separate spreadsheet with the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous "Calculate"
events were being generated. For example double-clicking on a row or column
to check the height or width caused a calulation when I released my finger
after the second click (but not before). I have since created my own offset
function in visual basic which works perfectly and does not cause any
anomalous events and the "Save" pop-up does not occur, unless of course I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming

 

[mickey]

No properly designed built-in function need be, or should be "volatile".
Volatile code is a result of sloppy programming. Of course I would never
accuse MS of sloppy programming.

 

[Jon Peltier]

Stop complaining, it's been like that from the beginning. Take your code
that uses sloppy OFFSETs, and clean it up with lookups or INDEXes.

- Jon

 

[Kieranz]

Hi Mickey
Great to know this. I suffered and gave up.
You mentioned "I have since created my own offset function in visual
basic which works perfectly", I wondered if this could be made
available to us on this UG.
Many thks
Rgds
KZ

 

[mickey]

Kieranz,

I would be happy to share the code with you, however once I decided to code
it myself I made it somewhat unique to my application. I'll explain how it
works and what it expects, and if you still feel it would be of use to you
I'll provide it.

First, my spreadsheet is in the form of a table with various columns (so far
nothing unique here).

Fromula Returns Specific Date

Row Number
__________________________________________________________
Dates | ColName2 | ColName3 | etc.
__________________________________________________________
Date 1 Data Data etc.
Date 2 Data Data etc.
Date 3
Date 4
etc.

Above the table is a cell which contains a formula which returns a specific
date, I my case the last date, but it could be any date. Just above the
upper lefthand corner of the table is a cell that contains the relative ROW
number of the specific date.

My UDF require two things: The "Name" label of the Column of interest (i.e.
ColName2); and the "Name" of the cell containing the row number of the
searched for date(in my case "RowIndex"). The routine then returns the Value
of the data in "ColName2" at the row specified by RowIndex. Because of the
way I provide the information the UDF is one line of VB code.

Here's what the function looks like in a cell: =ELEMENT(ColName2, RowIndex)

I could have done this 100 different ways, but this was very efficient.
ColName2, and RowIndex are passed as "Range" types and my UDF can use the
reference to RowIndex to calculate the absolute row address of the target
cell, as the reference to the target column provides the column information.

This can work for any table where there's a column that is the reference to
the rest of the table,(i.e. the reference column data should be unique:
dates, time, index numbers, etc.) something you can search for to determine a
row number. You'll note that the reference column could be in any position,
it need not just be the left column. However, the "RowIndex" cell must be in
the row just above the table (as it's position is used by ELEMENT, combined
with it's contents to determine the absolute cell address in the table), in
my case it's also in a hidden row.


If this works for you I'll provide you the code.

 

[Jon Peltier]

Mickey -

You said in the first place you switched from lookups to offsets for
performance reasons. Offset gave problems with its volatility, so you coded
a UDF. How does this UDF compare with the lookups? I would expect it to be
slow.

Charles Williams has a lot of information about optimizing Excel's
calculations on his web site:

http://www.decisionmodels.com/index.htm

- Jon

 

[mickey]

Perhaps my last post regarding the UDF I wrote wasn't clear as to the way it
functions. Once a target row is identified the UDF does not need to perform
any searches, it vectors directly to the target cell via row and column index
(same as OFFSET) and returns the target value. Every element in the target
row can be retrieved by direct vector. The UDF itself is comprised of one VB
instruction: I don't think you'll find anything faster.

 

[Peter T]

Would you post your UDF and perhaps an example of usage if not obvious how
to use it.

Regards,
Peter T

 

[mickey]

Peter,

Here's the code:
Function Element(ByVal CRef As Range, Row As Integer) As Variant

Element = CRef.Cells(Row, 1).Value

End Function

CRef refers to a "Named" column range (data only, no headers, if you want to
include the header you'd have to add 1 to the row) in the table. Row is
simply the relative row from the top of the column. In a passed post I
mention how my table is constructed, in which one column serves as the unique
reference for the table. In my application the reference column has dates.
To find a specific row I have a cell using the MATCH function that returns
the row of interest. Once the row is determined I reference that cell in my
UDF to directly fetch corresponding data from any column on the same row.

By the way I mis-interpreted your statement about the OFFSET function being
Volatile. For "Volatile" I was reading unstable. I now understand that
"Volatile" in MS terms means always re-calculates when any change occurs in
the sheet. This still doesn't explain what I was seeing, where the OFFSET
function was causing a "Calculate Event" to occur even when no cell changes
had occured. Sorry for the confusion on my part.

Hope you find my simple UDF useful - post any questions you may have.

Cheers.

 

[Peter T]

Doesn't this simple worksheet formula do same more efficiently

=INDEX(Cref,row,1)

I must admit I don't know why volatile functions recalc when double clicking
a row/col border to autosize. However I think only rarely worth replicating
a volatile function with a UDF, or better still use an alternative as in
this case and as suggested by Jon.

Typically the impact of volatile functions won't be noticed, however need to
be aware. When using a new function it's always worth using the function
wizard and looking for the indication of Volatile, though I think there are
one or two functions falsely not labelled as volatile. Despite being
volatile Offset is very useful for some purposes, though not this one.

Regards,
Peter T

 

[Lori]

There is a reason why OFFSET is a volatile function:-

If it were not volatile, the formula would need to recalc each time
there was any change on the same sheet as row,column,height,width are
variable. So any formula that contained OFFSET would need to contain an
implicit reference to the whole sheet but this breaks the Excel
smartcalc rule that formulas only recalculate based on the dependent
cells explicitly referenced in the formula.

If you don't want a volatile function you can generally use a dynamic
range:

INDEX($1:$65536,row,column):INDEX($1:$65536,row2,column2)

Like volatile functions though dynamic ranges still recalc everytime
the workbook is opened so you get the same changes prompt after closing
even if no changes were actually made.

 

[Peter T]

I agree with most of your comments except volatile functions don't recalc on
change of R/C width/height, though they do on Autosize and some other things
besides the obvious.

But as you say some functions need to be volatile. Had mickey tried to
replicate Offset like this -

Function fnOffset(ref As Range, rows) As Range
Set fnOffset = ref.Offset(0, 0).Resize(rows, 1)
End Function

=SUM(fnOffset(A1,D1))

The UDF will calculate if A1 or D1 change but not if a constant in A2
changes, unlike say (not that I'd use this) -
=SUM(OFFSET(A1,0,0,D1,1))

Regards,
Peter T

 

[Lori]

Hi Peter,

I was not clear on that - by row, column, height, width I was
referring to the other arguments of the offset function. I agree that
changing these cell attributes doesn't trigger a recalc and I was not
aware that autosizing did...

Lori

 

[Peter T]

Sorry Lori, I misunderstood reading height/width in context with comments
from the OP's first post, normally it would have been clear you meant array
dimensions.

Regards,
Peter T

 

[mickey]

Hi Peter,

After my bad experiences with OFFSET I was relultant to try INDEX which
seemed similar. It was so easy to create my own, and I could save a
parameter (shortens long formulas). Also I was looking to the future for my
application, I am going to have another UDF which does some unique
conversions after calling ELEMENT. This will be easier to code with ELEMENT.

Going back to the OFFSET issue, so there no misunderstanding about the
problem I was having: when the OFFSET function was present, I could simply
open the workbook and then close it, without touching anything, and the SAVE
window would pop-up. This occur with just one OFFSET function, simply
changing it to LOOKUP or my own UDF stopped the SAVE window.

Cheers

 

[Peter T]

Indeed with Offset in a formula you will get the save message on close even
if no 'apparent' changes. Personally I wouldn't substitute Offset with a
UDF, not withstanding the alternatives, just to avoid that. I suppose if for
you it's really a problem you could add the following in the thisworkbook
module -

Private Sub Workbook_Open()
ThisWorkbook.Saved = True
End Sub

and I could save a parameter (shortens long formulas).

=Element(Cref,row,1)
vs
=INDEX(Cref,row,1)
?

Regards,
Peter T

 

[mickey]

This is what I meant by OFFSET being unstable - there is no reason why that
should happen, it could be indicative of a deeper problem, which MS should
investigate and take steps to correct.

Also I am not that adverse to UDF's, believe me I am doing a lot weirder
stuff, that were to mention on this Forum, I would elicit responces as though
I were creating a "Black Hole" that would destroy all mankind (whoop's ,
personkind - I don't want to appear politically incorrect).

However, I do appreciate all your suggestions - Thanks again.

By the way would by any chance know of a Range property which could
effectively shield a cell from automatic re-calculation (kind of the opposite
of the "Dirty" method)?

Cheers.

 

[Lori]

This is not strange but consistent for all volatile functions.
RAND(),TODAY(),NOW(),... need to recalculate at startup to show the
current date etc. therefore a save changes prompt appears by default
when the workbook is closed. It's probably not necessary for OFFSET and
INDIRECT to do so but placing them in the volatile category drives this
behavior, this does not mean they are unstable or there is a deep
problem. Personally, I have other worries than this.

 

[mickey]

Yes, I agree with you regarding the functions you mentioned. In fact TODAY
and NOW are the ones I first looked for when I encountered the problem,
thinking that perhaps I forgot and used one some where in the Worksheet (I
don't offen use RAND).

But, alias it was the OFFSET function.

Cheers.