Preserving the Excel undo stack while also changing formula arrays value

[domenic....@gmail.com]

Hello,

I am trying to replicate a feature in an excel add-in and do not know how this feature is being accomplished.  I have an excel add-in that imports array formulas to excel.  I allow excel references to be used for time stamps.  A user can use native excel functions like =Now() and select a reference to that cell for the add-in to use.  However, when a user uses the =Now() function, any time a cell is edited, the =Now() function updates which also updates the formula array function dependent on it.  When the formula array updates (it rewrites the formula array to the cell) the excel undo stack gets cleared -- which seems to be a common issue.  When using =Now() the undo stack will get cleared after every edit you make to the workbook since =Now() is volatile which hinders the productivity of add-ins greatly.  

However in this add-in this issue is being handled differently and I'm wondering if someone might know how to replicate this.  If one of the add-ins functions gets updated due to =Now() being called, the add-in will recognize if the data has grown or shrunk.  If it has grown, the last values in the array get changed from data to a string "Resize array" which signifies that the function has been updated but it remained the same size to prevent a formula array being written to a cell and the undo stack from getting cleared.  I have not been able to figure out how to change the values inside of an array formula without re-writing the actual formula itself.  And it also seems regardless of me re-writing the formula, any changes made from a VSTO/XLL will clear the undo stack? Does anyone know how to replicate the behavior described?

this picture is the data on the formula array on the first pass.  After I got this data, I waited about a second or two and hit ctrl + alt + F9 to update the formula which resulted in more data.

this is the same array after updating it.  since there was more data, the last two cells got changed, from values, to a string telling the user they need to resize this formula.  The undo stack remained in tact after updating and I was still able to undo things after.

[domenic....@gmail.com]

it might also be worth mentioning that you cannot undo anything that the add-in does.  the undo stack just picks up where it left off before updating the array.  so if you write "Hello" in cell A4, then update the array, then hit ctrl + Z, "Hello" will be undone, not the array update.

[Govert van Drimmelen]

Hi,

 

It is easy for an array function to check the size of the calling range.

It can also return a mix of value types in the array – they don’t all have to be numbers, but some values can be strings.

So the array function you are looking at might be returning the message strings as the last row of the returned array, without changing the calling array formula range, and without writing to the sheet. That means the undo stack is not cleared by Excel – it’s just like a regular recalculation of a UDF celled from the sheet.

 

It’s quite a nice solution to the problem – give an indication to let the user resize the array rather than trying to do it with a macro triggered by the function call.

 

-Govert

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of domenic....@gmail.com
Sent: 10 October 2022 21:13
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Re: Preserving the Excel undo stack while also changing formula arrays value

 

it might also be worth mentioning that you cannot undo anything that the add-in does.  the undo stack just picks up where it left off before updating the array.  so if you write "Hello" in cell A4, then update the array, then hit ctrl + Z, "Hello" will be undone, not the array update.

On Monday, October 10, 2022 at 3:08:54 PM UTC-4 domenic....@gmail.com wrote:

Hello,

 

I am trying to replicate a feature in an excel add-in and do not know how this feature is being accomplished.  I have an excel add-in that imports array formulas to excel.  I allow excel references to be used for time stamps.  A user can use native excel functions like =Now() and select a reference to that cell for the add-in to use.  However, when a user uses the =Now() function, any time a cell is edited, the =Now() function updates which also updates the formula array function dependent on it.  When the formula array updates (it rewrites the formula array to the cell) the excel undo stack gets cleared -- which seems to be a common issue.  When using =Now() the undo stack will get cleared after every edit you make to the workbook since =Now() is volatile which hinders the productivity of add-ins greatly.  

 

However in this add-in this issue is being handled differently and I'm wondering if someone might know how to replicate this.  If one of the add-ins functions gets updated due to =Now() being called, the add-in will recognize if the data has grown or shrunk.  If it has grown, the last values in the array get changed from data to a string "Resize array" which signifies that the function has been updated but it remained the same size to prevent a formula array being written to a cell and the undo stack from getting cleared.  I have not been able to figure out how to change the values inside of an array formula without re-writing the actual formula itself.  And it also seems regardless of me re-writing the formula, any changes made from a VSTO/XLL will clear the undo stack? Does anyone know how to replicate the behavior described?

this picture is the data on the formula array on the first pass.  After I got this data, I waited about a second or two and hit ctrl + alt + F9 to update the formula which resulted in more data.

this is the same array after updating it.  since there was more data, the last two cells got changed, from values, to a string telling the user they need to resize this formula.  The undo stack remained in tact after updating and I was still able to undo things after.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/f1e1533b-0e48-4a08-9ace-f5ee9aa802aan%40googlegroups.com.

[domenic....@gmail.com]

I kind of assumed that is what was going on but I was not able to only update the values in the array without changing the calling array formula range, and without writing to the sheet.  And I agree, this is a great workaround to this issue.  Is it possible for you to give me an example of how you would go about implementing something similar?  I've tried every Range.Value call I could think of and every time I try to change the value of the formula, I end up clearing the undo stack.  I'm actually using the Resize methods you provide for arrays similar to this so I'm able to check the size of everything in that method, just haven't been able to figure out how to only change the last values of the array.

[Govert van Drimmelen]

 

object[,] result = …

 

var callingRows = … // suppose this is 5

var resultRows = …  // suppose this is 10

 

if (callingRows < resultRows)

{

   // overwrite the last row that will be displayed with message to user

    result[callingRows – 1, 0] = “Resize Array”;

    result[callingRows – 1, 1] = “Resize Array”;

}

 

return result;

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/9832360c-387f-45cf-8c20-ced775e4ccb4n%40googlegroups.com.

[domenic....@gmail.com]

Thank you govert.  I will give this a try tomorrow and let you know how it works out.

[domenic....@gmail.com]

This workaround works wonderfully.  great solution for the undo stack issue in Excel.

[Yashar Heydari]

Hi Domenic, 

In your scenario the custom function is called by user or it's an update to the array and array contains the custom functions?  

I'm looking for some way to tackle the Undo issue, and I couldn't really relate with your scenario. 

Thank you,

[Yashar Heydari]

Hi Govert, 

 

I'm just wondering if this is an Interop code or Office JS API code?  And how do we read the result object array  and how it's affecting Excel without setting the range again in the worksheet? I'll appreciate if you could guide me through this. 

Thank you,