|
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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 |
Found a glitch in the OFFSET function.
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" wrote: 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 |
Found a glitch in the OFFSET function.
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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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,c olumn2) 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 wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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" wrote in message ups.com... 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,c olumn2) 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 wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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 wrote: 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" wrote in message ups.com... 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,c olumn2) 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 wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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 "Lori" wrote in message oups.com... 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 wrote: 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 |
Found a glitch in the OFFSET function.
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" wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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" wrote in message ... 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" wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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. "Peter T" wrote: 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" wrote in message ... 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" wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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 wrote: 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. "Peter T" wrote: 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" wrote in message ... 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" wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
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. "Lori" wrote: 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 wrote: 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. "Peter T" wrote: 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" wrote in message ... 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" wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
Not sure why you regard Offset as unstable!
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)? Nothing I know of other than setting calc to manual. Can't imagine how anything like what you describe would work. FWIW, I have noticed sometimes wb's with volatile functions don't re-calc on file open even with calc set to automatic. The tell-tale is "Calculate" in the status bar. As for creating a "Black Hole" nobody's accused you of that. UDF's are a valuable resource and I'm quite sure yours would do no harm. If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function :-) Regards, Peter T "mickey" wrote in message ... 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. "Peter T" wrote: 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" wrote in message ... 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" wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
Peter,
Instability is defined as something erratic, however for code it is generally accepted to add, producing unpredicted and unexplained behaviors. Unpredicted and unexplained behaviors are indications of coding errors, which could also have as yet undiscovered deleterious effects. So far no one has explained why it would be necessary for OFFSET to force a SAVE: hence it fits the generally accepted definition for unstable code. Regarding cell calculation: cells have many properties, not the least of which is a "dirty" property which Excel uses to mark cells for re-calculation. I was hoping that there would be a property, which would cause Excel to ignore the dirty-flag. If it existed it could be implemented by Cell.EnableCalculate = False. Internally Excel would AND the two properties to determine whether it should recalculate a given cell. Regarding the "Black Hole" statement, note that I was referring to the "Forum" in general, not this specific "thread". You may have misunderstood my comment due to a type-o, where my comment read "...were to mention" should have read "were I to mention". I was speaking about a POSSIBLE response if I were ever to post the code behind some of my workbooks, which could be interpreted as deviating from Excel general design principles. Further I only made this comment because of various responses I've seen to posts on this forum regarding the use of UDF's, which have in many cases ranged from general reluctance to out-right antagonism. Regarding your statement: "If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function." Firstly, in no way did I ever feel as though I was ever "jumped on": quite the opposite I have gone out of my way to thank those who provided suggestions. I'm glad to see that you qualified your statement with the word "appeared" as I do take issue with your use of the term "unqualified"; for in your own statement you use the term "problematic" when referring to the subject worksheet function. I never used the term €śsuperior€ť in referring to my UDF, however, if superior could be used to differentiate a function, which does not exhibit "problematic" behavior from one that does, then my UDF could be said to be "superior" to the subject Excel worksheet function. As far as the term €śdogmatic€ť goes, it was certainly not my intent to leave the impression that I was dogmatic in any reference to my UDF, and in fact I would appreciate it if you would point out the specific statements of mine, which you interpreted as €śdogmatic€ť. :-) Cheers. "Peter T" wrote: Not sure why you regard Offset as unstable! 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)? Nothing I know of other than setting calc to manual. Can't imagine how anything like what you describe would work. FWIW, I have noticed sometimes wb's with volatile functions don't re-calc on file open even with calc set to automatic. The tell-tale is "Calculate" in the status bar. As for creating a "Black Hole" nobody's accused you of that. UDF's are a valuable resource and I'm quite sure yours would do no harm. If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function :-) Regards, Peter T "mickey" wrote in message ... 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. "Peter T" wrote: 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" wrote in message ... 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" wrote: 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 "mickey" wrote in message ... 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" wrote: 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 |
Found a glitch in the OFFSET function.
Hello again, comments in line
Peter, Instability is defined as something erratic, however for code it is generally accepted to add, producing unpredicted and unexplained behaviors. Unpredicted and unexplained behaviors are indications of coding errors, which could also have as yet undiscovered deleterious effects. So far no one has explained why it would be necessary for OFFSET to force a SAVE: hence it fits the generally accepted definition for unstable code. Both Lori and I tried to describe why functions some functions need to be volatile. Perhaps in some cases 'need to be' is too strong but they just are, no doubt due to legacy. In complex spreadsheets they may indeed give rise to unexpected problems. However their behaviour IS explainable and well documented (incl the force a save) and therefore predicatble, eg by Charles Williams in the link referred to by Jon Peltier. As I mentioned earlier, personally I don't understand why they recalc on autosize which is a slightly different matter. Regarding cell calculation: cells have many properties, not the least of which is a "dirty" property which Excel uses to mark cells for re-calculation. I was hoping that there would be a property, which would cause Excel to ignore the dirty-flag. If it existed it could be implemented by Cell.EnableCalculate = False. Internally Excel would AND the two properties to determine whether it should recalculate a given cell. OK I see what you are getting at. But I can't imagine how in practice that could be implemented, eg multiple dependancies, named formulas etc. Regarding the "Black Hole" statement, note that I was referring to the "Forum" in general, not this specific "thread". You may have misunderstood my comment due to a type-o, where my comment read "...were to mention" should have read "were I to mention". I was speaking about a POSSIBLE response if I were ever to post the code behind some of my workbooks, which could be interpreted as deviating from Excel general design principles. Further I only made this comment because of various responses I've seen to posts on this forum regarding the use of UDF's, which have in many cases ranged from general reluctance to out-right antagonism. Fair enough Regarding your statement: "If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function." Firstly, in no way did I ever feel as though I was ever "jumped on": OK I got the wrong impression quite the opposite I have gone out of my way to thank those who provided suggestions. Always appreciated, makes a big difference from those who don't bother to follow up at all ! I'm glad to see that you qualified your statement with the word "appeared" as I do take issue with your use of the term "unqualified"; for in your own statement you use the term "problematic" when referring to the subject worksheet function. I never used the term "superior" in referring to my UDF, however, if superior could be used to differentiate a function, which does not exhibit "problematic" behavior from one that does, then my UDF could be said to be "superior" to the subject Excel worksheet function. Yes I know you didn't use the term "superior" vis your UDF, perhaps I chose the wrong word but a shorthand way of describing the overall impression you appeared to give (see below). As far as the term "dogmatic" goes, it was certainly not my intent to leave the impression that I was dogmatic in any reference to my UDF, and in fact I would appreciate it if you would point out the specific statements of mine, which you interpreted as "dogmatic". :-) A couple of examples - "No properly designed built-in function need be, or should be "volatile". Volatile code is a result of sloppy programming." Seemed not only dogmatic but also unqualified due to your later response in which you said - "...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 ..." "The UDF itself is comprised of one VB instruction: I don't think you'll find anything faster." Less dogmatic but in overall context gave the impression you were proposing your UDF as a superior alternative to the volatile worksheet function. JP had earlier suggested Index, which as it turned out was directly equivalent to your UDF yet would be very significantly faster. Regards, Peter T pmbthornton gmal com "Peter T" wrote: Not sure why you regard Offset as unstable! 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)? Nothing I know of other than setting calc to manual. Can't imagine how anything like what you describe would work. FWIW, I have noticed sometimes wb's with volatile functions don't re-calc on file open even with calc set to automatic. The tell-tale is "Calculate" in the status bar. As for creating a "Black Hole" nobody's accused you of that. UDF's are a valuable resource and I'm quite sure yours would do no harm. If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function :-) Regards, Peter T <snip |
Found a glitch in the OFFSET function.
Hello again, comments in line
Peter, Instability is defined as something erratic, however for code it is generally accepted to add, producing unpredicted and unexplained behaviors. Unpredicted and unexplained behaviors are indications of coding errors, which could also have as yet undiscovered deleterious effects. So far no one has explained why it would be necessary for OFFSET to force a SAVE: hence it fits the generally accepted definition for unstable code. Both Lori and I tried to describe why functions some functions need to be volatile. Perhaps in some cases 'need to be' is too strong but they just are, no doubt due to legacy. In complex spreadsheets they may indeed give rise to unexpected problems. However their behaviour IS explainable and well documented (incl the force a save) and therefore predictable, eg by Charles Williams in the link referred to by Jon Peltier. As I mentioned earlier, personally I don't understand why they recalc on autosize which is a slightly different matter. Regarding cell calculation: cells have many properties, not the least of which is a "dirty" property which Excel uses to mark cells for re-calculation. I was hoping that there would be a property, which would cause Excel to ignore the dirty-flag. If it existed it could be implemented by Cell.EnableCalculate = False. Internally Excel would AND the two properties to determine whether it should recalculate a given cell. OK I see what you are getting at. But I can't imagine how in practice that could be implemented, eg multiple dependencies, named formulas etc. Regarding the "Black Hole" statement, note that I was referring to the "Forum" in general, not this specific "thread". You may have misunderstood my comment due to a type-o, where my comment read "...were to mention" should have read "were I to mention". I was speaking about a POSSIBLE response if I were ever to post the code behind some of my workbooks, which could be interpreted as deviating from Excel general design principles. Further I only made this comment because of various responses I've seen to posts on this forum regarding the use of UDF's, which have in many cases ranged from general reluctance to out-right antagonism. Fair enough Regarding your statement: "If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function." Firstly, in no way did I ever feel as though I was ever "jumped on": OK I got the wrong impression quite the opposite I have gone out of my way to thank those who provided suggestions. Always appreciated, makes a big difference from those who don't bother to follow up at all ! I'm glad to see that you qualified your statement with the word "appeared" as I do take issue with your use of the term "unqualified"; for in your own statement you use the term "problematic" when referring to the subject worksheet function. I never used the term "superior" in referring to my UDF, however, if superior could be used to differentiate a function, which does not exhibit "problematic" behavior from one that does, then my UDF could be said to be "superior" to the subject Excel worksheet function. Yes I know you didn't use the term "superior" vis your UDF, perhaps I chose the wrong word but a shorthand way of describing the overall impression you appeared to give (see below). As far as the term "dogmatic" goes, it was certainly not my intent to leave the impression that I was dogmatic in any reference to my UDF, and in fact I would appreciate it if you would point out the specific statements of mine, which you interpreted as "dogmatic". :-) A couple of examples - "No properly designed built-in function need be, or should be "volatile". Volatile code is a result of sloppy programming." Seemed not only dogmatic but also unqualified due to your later response in which you said - "...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 ..." "The UDF itself is comprised of one VB instruction: I don't think you'll find anything faster." Less dogmatic but in overall context gave the impression you were proposing your UDF as a superior alternative to the volatile worksheet function. JP had earlier suggested Index, which as it turned out was directly equivalent to your UDF yet would be very significantly faster. Regards, Peter T pmbthornton gmal com "Peter T" wrote: Not sure why you regard Offset as unstable! 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)? Nothing I know of other than setting calc to manual. Can't imagine how anything like what you describe would work. FWIW, I have noticed sometimes wb's with volatile functions don't re-calc on file open even with calc set to automatic. The tell-tale is "Calculate" in the status bar. As for creating a "Black Hole" nobody's accused you of that. UDF's are a valuable resource and I'm quite sure yours would do no harm. If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function :-) Regards, Peter T <snip |
Found a glitch in the OFFSET function.
Sorry about the double post, not sure why, I didn't send twice.
Peter T |
Found a glitch in the OFFSET function.
Hi, Also In line *.
"Peter T" wrote: Hello again, comments in line Peter, Instability is defined as something erratic, however for code it is generally accepted to add, producing unpredicted and unexplained behaviors. Unpredicted and unexplained behaviors are indications of coding errors, which could also have as yet undiscovered deleterious effects. So far no one has explained why it would be necessary for OFFSET to force a SAVE: hence it fits the generally accepted definition for unstable code. Both Lori and I tried to describe why functions some functions need to be volatile. Perhaps in some cases 'need to be' is too strong but they just are, no doubt due to legacy. In complex spreadsheets they may indeed give rise to unexpected problems. However their behaviour IS explainable and well documented (incl the force a save) and therefore predicatble, eg by Charles Williams in the link referred to by Jon Peltier. As I mentioned earlier, personally I don't understand why they recalc on autosize which is a slightly different matter. * I would agree that "legacy" is a central reason for the "unexpected problems", as they are very rarely revisited and correctly resolved. I also agree that the resulting KNOWN problems have been well documented. My issue is still with unknown problems, which could manifest themselves in the future. This is not an idle speculation: I have witnessed coding problems that were not properly run to ground, responsible for bring down aircraft, when just the right combination of improbable events coincides. For the record I fully understand the need for €śvolatile€ť functions, but I still view it as incomplete coding when a function gives rise to unnecessary consequences. Right now it appears that volatile functions like OFFSET simply perform a recalculation on loading, and never confirm whether that has resulted in an actual change. Perhaps Im just being obtuse here, but I dont like code that (Ill make a concession here) if not unstable, gives the impression of being unstable, as Ive defined instability. Regarding cell calculation: cells have many properties, not the least of which is a "dirty" property which Excel uses to mark cells for re-calculation. I was hoping that there would be a property, which would cause Excel to ignore the dirty-flag. If it existed it could be implemented by Cell.EnableCalculate = False. Internally Excel would AND the two properties to determine whether it should recalculate a given cell. OK I see what you are getting at. But I can't imagine how in practice that could be implemented, eg multiple dependancies, named formulas etc. * With the property I'm suggesting individual cell calculation would come under control of UDF VB code. In proposing this particular property I'm assuming that each cell maintains a register (i.e. memory block) that represents the value it displays. The property I'm suggesting would simply force the cell to retain it's last value (freeze if you will), even though the dependencies may have changed (this of course would affect cells that are dependent on the frozen cell, but this would be the programmers responcibility). Then when the VB code determined it wanted an update, it would unfreeze the cell (i.e. Cell.EnableCalculate = True) and the entire sheet would resolve itself to the re-calculated value. I do have a specific, albeit unorthodox application, which would simplify things greatly if this property existed. Regarding the "Black Hole" statement, note that I was referring to the "Forum" in general, not this specific "thread". You may have misunderstood my comment due to a type-o, where my comment read "...were to mention" should have read "were I to mention". I was speaking about a POSSIBLE response if I were ever to post the code behind some of my workbooks, which could be interpreted as deviating from Excel general design principles. Further I only made this comment because of various responses I've seen to posts on this forum regarding the use of UDF's, which have in many cases ranged from general reluctance to out-right antagonism. Fair enough Regarding your statement: "If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function." Firstly, in no way did I ever feel as though I was ever "jumped on": OK I got the wrong impression * By the with the "Black Hole" comment I was actually trying to inject a little humor - Im sorry it fell flat. quite the opposite I have gone out of my way to thank those who provided suggestions. Always appreciated, makes a big difference from those who don't bother to follow up at all ! I'm glad to see that you qualified your statement with the word "appeared" as I do take issue with your use of the term "unqualified"; for in your own statement you use the term "problematic" when referring to the subject worksheet function. I never used the term "superior" in referring to my UDF, however, if superior could be used to differentiate a function, which does not exhibit "problematic" behavior from one that does, then my UDF could be said to be "superior" to the subject Excel worksheet function. Yes I know you didn't use the term "superior" vis your UDF, perhaps I chose the wrong word but a shorthand way of describing the overall impression you appeared to give (see below). As far as the term "dogmatic" goes, it was certainly not my intent to leave the impression that I was dogmatic in any reference to my UDF, and in fact I would appreciate it if you would point out the specific statements of mine, which you interpreted as "dogmatic". :-) A couple of examples - "No properly designed built-in function need be, or should be "volatile". Volatile code is a result of sloppy programming." Seemed not only dogmatic but also unqualified due to your later response in which you said - "...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 ..." * Well again, my initial statement was a misunderstanding of your use of the word "volatile", which as it happens, is a synonym for "unstable". Your original terse statement "Offset is a volatile function" could be interpreted as a statement resigning oneself to the fact that OFFSET had problems: at least this was the way I erroneously interpreted your original comment. Given that I explained my error and that should have negated any categorization of the comment being "dogmatic". "The UDF itself is comprised of one VB instruction: I don't think you'll find anything faster." Less dogmatic but in overall context gave the impression you were proposing your UDF as a superior alternative to the volatile worksheet function. JP had earlier suggested Index, which as it turned out was directly equivalent to your UDF yet would be very significantly faster. * This was simply a response to the previous post by Peltier: "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." Particularly his coment "I would expect it to be slow." Again not "dogmatic" simply a response to his unqualified speculation that my UDF would be slow. Well Cheers again, I think we've beaten this "dead horse" enough :-), although I would still appreciate comments on the proposed "EnableCalculate" property. Regards, Peter T pmbthornton gmal com "Peter T" wrote: Not sure why you regard Offset as unstable! 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)? Nothing I know of other than setting calc to manual. Can't imagine how anything like what you describe would work. FWIW, I have noticed sometimes wb's with volatile functions don't re-calc on file open even with calc set to automatic. The tell-tale is "Calculate" in the status bar. As for creating a "Black Hole" nobody's accused you of that. UDF's are a valuable resource and I'm quite sure yours would do no harm. If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function :-) Regards, Peter T <snip |
Found a glitch in the OFFSET function.
Well Cheers again, I think we've beaten this "dead horse" enough :-),
although I would still appreciate comments on the proposed "EnableCalculate" property. Yes the old horse has had enough ! One last trivial thing, my very first reply to you was not intended as "terse" but succinct with the assumption (wrong) that most round here would know of volatile functions. You could start a new topic for your cell EnableCalculate property but I don't think you'll get very far. A workaround for your special needs - Sub CelCalc(cel As Range, bCalc As Boolean, Optional bFuncOnly As Boolean) Dim pos As Long Dim sFmla As String Const cN1 As String = "+N(""=" Const cN2 As String = """)" If Not cel.HasFormula Then Exit Sub sFmla = cel.Formula pos = InStr(2, sFmla, cN1) If bCalc And pos Then sFmla = Mid$(sFmla, pos + 4, Len(sFmla) - pos - 5) cel.Formula = sFmla ElseIf Not bCalc And pos = 0 Then ' don't process if includes the N function If bFuncOnly Then If InStr(2, sFmla, "(") = 0 Then 'no possibility of volatile function in formula Exit Sub End If End If 'temporary #NAME? error if string sFmla = "=" & cel.Value2 & cN1 & Mid$(sFmla, 2, 255) & cN2 cel.Formula = sFmla End If End Sub You argued your points well and I accept some. Anyway, argument is the essence of discussion * Regards, Peter T * Oscar Wilde, misquoted You could start a new topic for "mickey" wrote in message ... Hi, Also In line *. "Peter T" wrote: Hello again, comments in line Peter, Instability is defined as something erratic, however for code it is generally accepted to add, producing unpredicted and unexplained behaviors. Unpredicted and unexplained behaviors are indications of coding errors, which could also have as yet undiscovered deleterious effects. So far no one has explained why it would be necessary for OFFSET to force a SAVE: hence it fits the generally accepted definition for unstable code. Both Lori and I tried to describe why functions some functions need to be volatile. Perhaps in some cases 'need to be' is too strong but they just are, no doubt due to legacy. In complex spreadsheets they may indeed give rise to unexpected problems. However their behaviour IS explainable and well documented (incl the force a save) and therefore predicatble, eg by Charles Williams in the link referred to by Jon Peltier. As I mentioned earlier, personally I don't understand why they recalc on autosize which is a slightly different matter. * I would agree that "legacy" is a central reason for the "unexpected problems", as they are very rarely revisited and correctly resolved. I also agree that the resulting KNOWN problems have been well documented. My issue is still with unknown problems, which could manifest themselves in the future. This is not an idle speculation: I have witnessed coding problems that were not properly run to ground, responsible for bring down aircraft, when just the right combination of improbable events coincides. For the record I fully understand the need for "volatile" functions, but I still view it as incomplete coding when a function gives rise to unnecessary consequences. Right now it appears that volatile functions like OFFSET simply perform a recalculation on loading, and never confirm whether that has resulted in an actual change. Perhaps I'm just being obtuse here, but I don't like code that (I'll make a concession here) if not unstable, gives the impression of being unstable, as I've defined instability. Regarding cell calculation: cells have many properties, not the least of which is a "dirty" property which Excel uses to mark cells for re-calculation. I was hoping that there would be a property, which would cause Excel to ignore the dirty-flag. If it existed it could be implemented by Cell.EnableCalculate = False. Internally Excel would AND the two properties to determine whether it should recalculate a given cell. OK I see what you are getting at. But I can't imagine how in practice that could be implemented, eg multiple dependancies, named formulas etc. * With the property I'm suggesting individual cell calculation would come under control of UDF VB code. In proposing this particular property I'm assuming that each cell maintains a register (i.e. memory block) that represents the value it displays. The property I'm suggesting would simply force the cell to retain it's last value (freeze if you will), even though the dependencies may have changed (this of course would affect cells that are dependent on the frozen cell, but this would be the programmers responcibility). Then when the VB code determined it wanted an update, it would unfreeze the cell (i.e. Cell.EnableCalculate = True) and the entire sheet would resolve itself to the re-calculated value. I do have a specific, albeit unorthodox application, which would simplify things greatly if this property existed. Regarding the "Black Hole" statement, note that I was referring to the "Forum" in general, not this specific "thread". You may have misunderstood my comment due to a type-o, where my comment read "...were to mention" should have read "were I to mention". I was speaking about a POSSIBLE response if I were ever to post the code behind some of my workbooks, which could be interpreted as deviating from Excel general design principles. Further I only made this comment because of various responses I've seen to posts on this forum regarding the use of UDF's, which have in many cases ranged from general reluctance to out-right antagonism. Fair enough Regarding your statement: "If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function." Firstly, in no way did I ever feel as though I was ever "jumped on": OK I got the wrong impression * By the with the "Black Hole" comment I was actually trying to inject a little humor - Im sorry it fell flat. quite the opposite I have gone out of my way to thank those who provided suggestions. Always appreciated, makes a big difference from those who don't bother to follow up at all ! I'm glad to see that you qualified your statement with the word "appeared" as I do take issue with your use of the term "unqualified"; for in your own statement you use the term "problematic" when referring to the subject worksheet function. I never used the term "superior" in referring to my UDF, however, if superior could be used to differentiate a function, which does not exhibit "problematic" behavior from one that does, then my UDF could be said to be "superior" to the subject Excel worksheet function. Yes I know you didn't use the term "superior" vis your UDF, perhaps I chose the wrong word but a shorthand way of describing the overall impression you appeared to give (see below). As far as the term "dogmatic" goes, it was certainly not my intent to leave the impression that I was dogmatic in any reference to my UDF, and in fact I would appreciate it if you would point out the specific statements of mine, which you interpreted as "dogmatic". :-) A couple of examples - "No properly designed built-in function need be, or should be "volatile". Volatile code is a result of sloppy programming." Seemed not only dogmatic but also unqualified due to your later response in which you said - "...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 ..." * Well again, my initial statement was a misunderstanding of your use of the word "volatile", which as it happens, is a synonym for "unstable". Your original terse statement "Offset is a volatile function" could be interpreted as a statement resigning oneself to the fact that OFFSET had problems: at least this was the way I erroneously interpreted your original comment. Given that I explained my error and that should have negated any categorization of the comment being "dogmatic". "The UDF itself is comprised of one VB instruction: I don't think you'll find anything faster." Less dogmatic but in overall context gave the impression you were proposing your UDF as a superior alternative to the volatile worksheet function. JP had earlier suggested Index, which as it turned out was directly equivalent to your UDF yet would be very significantly faster. * This was simply a response to the previous post by Peltier: "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." Particularly his coment "I would expect it to be slow." Again not "dogmatic" simply a response to his unqualified speculation that my UDF would be slow. Well Cheers again, I think we've beaten this "dead horse" enough :-), although I would still appreciate comments on the proposed "EnableCalculate" property. <snip |
Found a glitch in the OFFSET function.
"Peter T" wrote: Well Cheers again, I think we've beaten this "dead horse" enough :-), although I would still appreciate comments on the proposed "EnableCalculate" property. Yes the old horse has had enough ! One last trivial thing, my very first reply to you was not intended as "terse" but succinct with the assumption (wrong) that most round here would know of volatile functions. You could start a new topic for your cell EnableCalculate property but I don't think you'll get very far. A workaround for your special needs - Sub CelCalc(cel As Range, bCalc As Boolean, Optional bFuncOnly As Boolean) Dim pos As Long Dim sFmla As String Const cN1 As String = "+N(""=" Const cN2 As String = """)" If Not cel.HasFormula Then Exit Sub sFmla = cel.Formula pos = InStr(2, sFmla, cN1) If bCalc And pos Then sFmla = Mid$(sFmla, pos + 4, Len(sFmla) - pos - 5) cel.Formula = sFmla ElseIf Not bCalc And pos = 0 Then ' don't process if includes the N function If bFuncOnly Then If InStr(2, sFmla, "(") = 0 Then 'no possibility of volatile function in formula Exit Sub End If End If 'temporary #NAME? error if string sFmla = "=" & cel.Value2 & cN1 & Mid$(sFmla, 2, 255) & cN2 cel.Formula = sFmla End If End Sub You argued your points well and I accept some. Anyway, argument is the essence of discussion * Regards, Peter T * Oscar Wilde, misquoted Thanks for the coding suggestion, I check it out. By the way one item I forgot to mention in my first post, when I was experimenting with OFFSET. In addition to it generating a "Calculate Event" when double clicking on a row or column, it would sometimes generate a "Calculate Event" when cell selection was changed, but not always. I was did not have the time to figure out why it sporadically generated the event when selecting a different cell. It's been a pleasue - I enjoy a good discussion. Cheers :-) You could start a new topic for "mickey" wrote in message ... Hi, Also In line *. "Peter T" wrote: Hello again, comments in line Peter, Instability is defined as something erratic, however for code it is generally accepted to add, producing unpredicted and unexplained behaviors. Unpredicted and unexplained behaviors are indications of coding errors, which could also have as yet undiscovered deleterious effects. So far no one has explained why it would be necessary for OFFSET to force a SAVE: hence it fits the generally accepted definition for unstable code. Both Lori and I tried to describe why functions some functions need to be volatile. Perhaps in some cases 'need to be' is too strong but they just are, no doubt due to legacy. In complex spreadsheets they may indeed give rise to unexpected problems. However their behaviour IS explainable and well documented (incl the force a save) and therefore predicatble, eg by Charles Williams in the link referred to by Jon Peltier. As I mentioned earlier, personally I don't understand why they recalc on autosize which is a slightly different matter. * I would agree that "legacy" is a central reason for the "unexpected problems", as they are very rarely revisited and correctly resolved. I also agree that the resulting KNOWN problems have been well documented. My issue is still with unknown problems, which could manifest themselves in the future. This is not an idle speculation: I have witnessed coding problems that were not properly run to ground, responsible for bring down aircraft, when just the right combination of improbable events coincides. For the record I fully understand the need for "volatile" functions, but I still view it as incomplete coding when a function gives rise to unnecessary consequences. Right now it appears that volatile functions like OFFSET simply perform a recalculation on loading, and never confirm whether that has resulted in an actual change. Perhaps I'm just being obtuse here, but I don't like code that (I'll make a concession here) if not unstable, gives the impression of being unstable, as I've defined instability. Regarding cell calculation: cells have many properties, not the least of which is a "dirty" property which Excel uses to mark cells for re-calculation. I was hoping that there would be a property, which would cause Excel to ignore the dirty-flag. If it existed it could be implemented by Cell.EnableCalculate = False. Internally Excel would AND the two properties to determine whether it should recalculate a given cell. OK I see what you are getting at. But I can't imagine how in practice that could be implemented, eg multiple dependancies, named formulas etc. * With the property I'm suggesting individual cell calculation would come under control of UDF VB code. In proposing this particular property I'm assuming that each cell maintains a register (i.e. memory block) that represents the value it displays. The property I'm suggesting would simply force the cell to retain it's last value (freeze if you will), even though the dependencies may have changed (this of course would affect cells that are dependent on the frozen cell, but this would be the programmers responcibility). Then when the VB code determined it wanted an update, it would unfreeze the cell (i.e. Cell.EnableCalculate = True) and the entire sheet would resolve itself to the re-calculated value. I do have a specific, albeit unorthodox application, which would simplify things greatly if this property existed. Regarding the "Black Hole" statement, note that I was referring to the "Forum" in general, not this specific "thread". You may have misunderstood my comment due to a type-o, where my comment read "...were to mention" should have read "were I to mention". I was speaking about a POSSIBLE response if I were ever to post the code behind some of my workbooks, which could be interpreted as deviating from Excel general design principles. Further I only made this comment because of various responses I've seen to posts on this forum regarding the use of UDF's, which have in many cases ranged from general reluctance to out-right antagonism. Fair enough Regarding your statement: "If you got the impression of being jumped on earlier on it may have been triggered by what appeared to be an unqualified yet dogmatic view that your UDF was a superior alternative to Excel's problematic worksheet function." Firstly, in no way did I ever feel as though I was ever "jumped on": OK I got the wrong impression * By the with the "Black Hole" comment I was actually trying to inject a little humor - Im sorry it fell flat. quite the opposite I have gone out of my way to thank those who provided suggestions. Always appreciated, makes a big difference from those who don't bother to follow up at all ! I'm glad to see that you qualified your statement with the word "appeared" as I do take issue with your use of the term "unqualified"; for in your own statement you use the term "problematic" when referring to the subject worksheet function. I never used the term "superior" in referring to my UDF, however, if superior could be used to differentiate a function, which does not exhibit "problematic" behavior from one that does, then my UDF could be said to be "superior" to the subject Excel worksheet function. Yes I know you didn't use the term "superior" vis your UDF, perhaps I chose the wrong word but a shorthand way of describing the overall impression you appeared to give (see below). As far as the term "dogmatic" goes, it was certainly not my intent to leave the impression that I was dogmatic in any reference to my UDF, and in fact I would appreciate it if you would point out the specific statements of mine, which you interpreted as "dogmatic". :-) A couple of examples - "No properly designed built-in function need be, or should be "volatile". Volatile code is a result of sloppy programming." Seemed not only dogmatic but also unqualified due to your later response in which you said - "...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 ..." * Well again, my initial statement was a misunderstanding of your use of the word "volatile", which as it happens, is a synonym for "unstable". Your original terse statement "Offset is a volatile function" could be interpreted as a statement resigning oneself to the fact that OFFSET had problems: at least this was the way I erroneously interpreted your original comment. Given that I explained my error and that should have negated any categorization of the comment being "dogmatic". "The UDF itself is comprised of one VB instruction: I don't think you'll find anything faster." Less dogmatic but in overall context gave the impression you were proposing your UDF as a superior alternative to the volatile worksheet function. JP had earlier suggested Index, which as it turned out was directly equivalent to your UDF yet would be very significantly faster. * This was simply a response to the previous post by Peltier: "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." Particularly his coment "I would expect it to be slow." Again not "dogmatic" simply a response to his unqualified speculation that my UDF would be slow. Well Cheers again, I think we've beaten this "dead horse" enough :-), although I would still appreciate comments on the proposed "EnableCalculate" property. <snip |
| All times are GMT +1. The time now is 09:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com