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/comm...el.programming

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

Regards,
Peter T


"mickey" wrote in message
...
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/comm...id=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming

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.

"Peter T" wrote:

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

Regards,
Peter T


"mickey" wrote in message
...
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/comm...id=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
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.

"Peter T" wrote:

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

Regards,
Peter T


"mickey" wrote in message
...
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/comm...id=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming

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 wrote:
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/comm...el.programming

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.

:-)





"Kieranz" wrote:

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 wrote:
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/comm...el.programming

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
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.

:-)





"Kieranz" wrote:

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 wrote:
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/comm...el.programming

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.


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
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.

:-)





"Kieranz" wrote:

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 wrote:
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/comm...el.programming

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

Regards,
Peter T

"mickey" wrote in message
...
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.


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
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.

:-)





"Kieranz" wrote:

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 wrote:
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/comm...id=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming