3

After the execution of a macro in Excel, there's no button to revoke it, unless you close the book and choose not to save it.

Is there a neat and easy way to just revoke it?

2 Answers 2

4

The short answer is you can't Undo (I assume this is what you mean by revoke) the actions of a macro. The decisions that take place inside the programming language could be troublesome for Excel to reverse, so there is a blanket "Can't Undo" state induced by kicking off the macro.

The long answer is that if you anticipate Undo being required, your code should accommodate that request by storing the previous state of the data before execution. This is a very broad assumption that you are working with data in VBA; direct file creation and manipulation could be trickier depending on what's executed.

John Walkenbach gives a good example of storing data to provide a future Undo facility:

Computer users are accustomed to the ability to "undo" an operation. Almost every operation you perform in Excel can be undone. If you program in VBA, you may have wondered if it's possible to undo the effects of a subroutine. The answer is yes. The qualified answer is it's not always easy.

John Walkenbach, Spreadsheetpage.com

I think his opening statement is a little misleading though: for non-coding users the answer is "no, you can't undo a macro".

15
  • I do not quite understand this part: "this is a very broad assumption that you are working with data in VBA; direct file creation and manipulation could be trickier depending on what's executed."
    – frog
    Nov 22, 2012 at 3:37
  • If you are working just with data in an Excel workbook (e.g. text, numbers, dates, formulas...) then being able to restore its previous value is relatively easy, as per the link in my answer. However the range of things you can manipulate with VBA is pretty big, and you could be moving files around a network, modifying the contents of a hard drive, converting file formats and more. Trying to provide an Undo state for these operations will get laborious. Feasible though.
    – Gary
    Nov 22, 2012 at 9:06
  • Gary, I am working just with data in Excel workbook, could help on giving an easy solution to go back from the execution of a macro?
    – frog
    Nov 27, 2012 at 4:01
  • To go back from a macro you need to record / write another macro beforehand and probably have a good working knowledge of VBA.
    – Gary
    Nov 27, 2012 at 13:45
  • ,does that mean I have to write another macro for every new macro I intend to create?There is no a single macro can be created for going back from all macro execution?
    – frog
    Nov 30, 2012 at 3:45
0

Yes you can undo all actions of a macro. This is a macro below assigned to a do a DO button.

Sub do_while()
    Dim num As Integer
    num = 1
    Do While num <= 12
        Cells(num, 1) = num
        num = num + 1
    Loop
End Sub
The code below is   assigned to an UNDO button.It uses ClearContents.
 Dim num As Integer
    num = 1
    Do While num <= 12
        Cells(num, 1).ClearContents
        num = num + 1
    Loop
End Sub

Use clear contents.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .