• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Undo-ing with Macro's

E

EtienneHofmeyr

New Member
Hi

I constantly have this problem with my (rather pre-historic) macro functions in that once I've hit Ctrl + ... and realise that that WASN'T what I was meant to do and hit Ctrl + z , Excel gives me a screen saying "NO! You can NEVER UNDO what you DID! Why did you DO THAT!?!"

The quick question: Is there an undo function for Macros?

Et
 
Dang .. that's a shame. And you're right, I forgot that on top of that Excel even has the nerve to say, "in fact, I'm not letting you undo ANYTHING so I hope you're happy with what you did! (idiot!)"

I take it that the dialogue box wouldn't let you preview the change first either before saying "no thanks" :)

I believe Excel could really do a lot more to allow macros to be more user friendly for the average user (the kind that thinks of the word "macro" in the same way as "staphylococcus" or "Latin"), because they can be incredibly useful .. just, difficult (and potentially destructive as shown above) to use.

Thanks for the links, I'll check them out :)
 
Hi Etienne ,

It is up to the programmer to decide what level of protection / information to give to the user.

For example , if you are writing a macro which is going to delete rows from a worksheet , you can always make a copy of the entire worksheet before doing anything else ; you can also prompt the user at the end of the entire operation to review the changes which will be made , for example what are the row numbers that will be deleted , before the actual deletion takes place.

In fact , every macro can have a flag which will decide whether the macro actually carries out the changes or not ; if the flag is false , the macro does nothing , if true , the macro actually implements the proposed changes.

Writing every macro in this fashion is a complex exercise , and the basic premise is that every macro can have only action built into it , since if there are to be multiple actions , where the subsequent actions depend on the previous action having been executed , then this kind of preview is not possible.

Any macro that does a save operation can ensure that a backup save is carried out before the macro runs.

Narayan
 
There's one Undo which is possible even after you run the macro in a specific condition.

Condition is the macro itself doesn't have code to save the changes made in the workbook.

In such cases, manually save the workbook before running the macro and then run the macro. If you are not happy with the changes just close the workbook but choose option "Don't save". It will undo the changes made by the macro. When you reopen the workbook you should be able to see it in pre-macro last saved condition.

Are you a microbiologist by any chance? Because the last time I had heard about staphylococcus aureus was in a Pharma micro lab.
 
It still has limitations, but might check out the:
Application.OnUndo Text, Procedure

You can put a line like this at the end of your macro, and it tells the Undo command what Text to display in Undo dropdown, and you can specify a macro to run. Obviously, not a perfect undo, but has some use.
 
@Luke: That's handy to know. Thank for that gem.
@Narayan: That Macabacus undo/redo functionality is referring to just the routines within the addin itself. Funnily enough I tried this addin out the other day (there's a free version) but I think some things still wiped the undo stack.

Re the gmarcan code, how to actually use that code is really unclear to me, not to mention to people that have left comments in that thread. I'd be interested to hear how to use it if you know how. Looks like it (and the Walkenbach code it refers to) simply record a range's values or formulas from before you run a macro, so that they can be restored after the macro has run. But this obviously won't be that useful (if at all) in the case that the macro is doing something like filtering, deleting, formatting, or pretty much anything non-trivial as far as I can tell.

So about the only bulletproof approaches are the two you've already highlighted:
  1. Warn the user in the event that the worksheet will be changed.
  2. Save the file, so that it can be restored in need.
Of course, 1) can get really annoying if you use the macro a lot. And 2) can take a long time for big files.
 
Hi Jeff ,

Thanks for the information ; I have not used these approaches , so I cannot add anything to what you have posted. Has anything changed in Excel 2013 ?

Narayan
 
You must log in or register to reply here.
Back
Top