Honesty Rocks! truth rules.

Vb 'undo' Help

HOME      >>       Programming


Not sure if this is the correct place to post this so I apologize in advance if it is not. Before I post some code I was just wondering if someone could help me out. I have some VB code that goes inside an excel sheet that does a few things and it works correctly. The problem is that when you create something like this you lose the ability to use the default 'undo' excel has. From what I understand to make this work you need to create a 'undo' script for your function. Thats my problem, I have no clue how to create and undo script for my function. I got the code from a guy but am unable to get ahold of him for help on this.Can someone in here help me create the undo function? I will post what I have if so...


I imagine an undo script would be doable if you simply had the code that alters a cell or cells in an excel sheet save a copy of those cells in a variable in memory prior to making the changes. This would allow for a review of the contents as altered by your new code and if they were incorrect a click of the undo button could over-write the new cell data with that stored in the variables of the original data. I'm not sure what scale or how precise you want the undo function but I'm sure you could change the granularity of such a procedure to whatever you need or want.But yea, to summarize: Determine cell(s) to be affected, back them up in some way, over write the cells, review changes, continue as normal or replace with original data if required.Hope this helps, if not maybe more information on your part could be useful.


The basis of creating an undo mechanism is the set of data structures you use. You will have to use a stack (two if you want to allow redo operations too). You will need to create a class which will store the address of the cell (Row, Column) and the value of the cell, before the change occured. C# code for the same is given below.

Class Action{ int Row, Column; object Value;}

The stack should be of this type:-

Stack<Action> UndoHistory, RedoHistory;

Now, whenever you are about to change a value, create an instance of the Action class storing the details of the cell, i.e. its location and current value. Then, proceed with the change of value. You can enable/disabled the undo button by checking the length of the UndoHistory stack (disabled if its size is 0). Same goes for RedoHistory.

When you do an undo operation, an item from the UndoHistory stack should be popped. The Current value and location of the cell should be stored in a new instance of the Action class. This should be added to the RedoHistory stack.

While doing a redo, you must do the reverse. Push the current value & location to the UndoHistory. Pop an item from the RedoHistory and change the current value to match it.

This is a simple implementation. Your Action class can be much more complex. It might take into consideration multiple cells being changed by one operation, etc. But, the base mechanism would be similar.


Making an undo function in VBA is certainly do-able.
For starters in you OP you state VB I am assuming that you actually intended to say VBA.
A quick search for "VBA Undo" gave me several results. A quick look at the code here seemed to fit the bill. Microsoft's website has some information as well though it seems to be geared to the latest version of Office. The specific example is in Project though I do believe the code would be interchangeable to Excel. The MSDN website often has very helpful information for VBA programming.

Best of luck.


How to implement UNDO and REDO operations in vb.netVb 'undo' Help

How to implement UNDO and REDO operations in vb.Net windows application.. 

-reply by Dilip