Results 1 to 12 of 12

Thread: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    I use a "Copy" macro to copy a page, and a "Paste" macro to paste the cells (please see below).

    I have a button for each in my Macro ribbon.

    If I Ctrl+V the data I copied, then Ctrl+Z works.

    However, if I use the the macro "Paste" button, then Ctrl+Z does not work.

    Any idea why? Any idea what the difference is between the two?

    Is it something with how it uses the memory? What is it?

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    Sub Copy_a_Page()

    ActiveWorkbook.save
    Range(ActiveCell, ActiveCell.Offset(54, 22)).Copy

    End Sub

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    Sub Paste_a_Page()

    ActiveSheet.Paste
    Application.Goto Reference:=ActiveCell

    End Sub

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    when any maro is run all undo information is lost

    in word this does not happen, so i would believe that it is just the way the excel object model works
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    . . . in word this does not happen, so i would believe that it is just the way the excel object model works

    are you saying that you can do a copy and paste macro like mine above, in Word, and Ctrl+Z will work, if you used the paste macro?

    What I'm getting at here is that in Excel, after you do the Copy macro, then Ctrl+V / Ctrl+Z works on the cells . . . but if you use the "Paste" macro in Excel, for some reason, it will not let you Ctrl+Z the cells . . . I'm wondering what the reason is for this . . . It's just copy and paste . . . Why is Ctrl+V more "dynamic" than a "Paste" macro?

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    are you saying that you can do a copy and paste macro like mine above, in Word, and Ctrl+Z will work, if you used the paste macro?
    i have not tested that, but the undo from previous edits are still available

    Ctrl+V = undo last edit
    I'm wondering what the reason is for this
    ask someone at microsoft, as i doubt anyone here (or any other forum) knows why, we just accept and move on
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    I think the answer would be in these links:

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    A clipboard object for VBA, including Microsoft Word . . . Requirement: Polite macros. A macro that mangles the user's clipboard is rude. For example, your macro may do a Range.Copy. This copies the range into the Windows clipboard, clobbering whatever was there before. If your macro doesn't restore the clipboard, the user will discover, after running your macro, that a precious piece of text the user had copied to the clipboard earlier on has vanished. Bad macro. No biscuit . . . So the requirement is to preserve the clipboard contents before using the clipboard within the macro, then restore the clipboard contents afterwards. The DataObject is incapable of doing this -- and VBA does not provide native support to the Windows clipboard . . . https://social.msdn.microsoft.com/Fo...?forum=worddev

    Getting Started with VBA in Office . . . https://msdn.microsoft.com/en-us/vba...-vba-in-office

    Send Information to the Clipboard . . . https://msdn.microsoft.com/en-us/vba...-the-clipboard

    Retrieve Information from the Clipboard . . . https://msdn.microsoft.com/en-us/VBA...-the-clipboard

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    The issue has to do with Objects and API - I'm not really sure what these are.

    Does MS Office still have that capability to paste from memory 1, 2, 3, 4 ? . . . I forget the max . . . I don't use that, I just use the one memory buffer - wherever it goes when I do Ctrl+C, and then I use Ctrl+V

    I wonder how much code is involved, to have an Excel macro "copy to" and "paste from" something like memory 4.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    Excel has a maximum of 24 memory buffers:

    http://www.dummies.com/software/micr...in-excel-2010/

    Home tab / Clipboard group / click the "Dialog Box" launcher button in the bottom-right corner of the "Clipboard group"

    Options button (bottom-left) / "Show Office Clipboard Automatically" . . . then it will appear after making two cuts or copies

    Options button (bottom-left) / "Show Office Clipboard When Ctrl+C Pressed Twice" . . . then Ctrl+CC will open the task pane . . . It doesn't toggle, though, you have to use the Close button

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    I copied the numbers 1 to 24, and see that all the memory buffers are filled with "their" number.

    I then use my macro "Copy," and see that it only takes up one of the memory buffers - behavior is as expected . . . And I see that the macro is not "mangling" the clipboard, as described in the article above.

    I see that when pasting (one click) from the Clipboard sidebar:
    1.) Ctrl+Z works
    2.) but, text colors and shading are lost . . . but, they are pasted if you use Ctrl+V

    I see that when I use my macro "Copy" button, the "copy" shows up in the Clipboard sidebar . . . Using my macro "Paste" button works (and, text colors and shading are pasted), but Ctrl+Z still does not work

    It would just be nice if Ctrl+Z worked, when I used my macro "Paste" button . . . How do I get that to work? . . . To be safe, I am left with having to use Ctrl+V

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    Ok, I figured it out. This is a common question, with a "hard-to-find" answer:

    1.) There is no "undo" for a macro, by design, because the undo history could be huge.

    2.) So, as I see it, the only way you can "undo," is to have the macro save your Worksheet, before running the macro, and if there is a problem, your "undo" is the saved Worksheet.

    Please see "Note 1" and "Note 2" on this page for some peculiarities

    ActiveSheet.Paste / Runtime Error 1004-VBForums . . . http://www.vbforums.com/showthread.p...ime-Error-1004

    Below are the links, with some excerpts, showing how I determined this:

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    POSSIBLE BUT NOT EASY

    How to revoke a macro in Excel? . . . 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" . . . https://superuser.com/questions/5085...macro-in-excel

    Undoing A VBA Subroutine - Excel Tips From John Walkenbach . . . http://spreadsheetpage.com/index.php...ba_subroutine/

    Undo function to undo Visual Basic commands . . . Running VBA any code clears the Undo stack therefore preventing anything being 'undone'. You can use Application.Undo immediately in a worksheet change event if someone has entered a value you don't want them to for example but that's about it . . . I've seen code that builds up a history of changes that then allows a custom undo routine to be used but never really tried it myself and don't think it's ever that perfect . . . I thought I'd read somewhere that things might have changed in Excel 2010 (don't quote me on that though as not seen it yet) . . . https://www.excelforum.com/excel-pro...-commands.html

    - - - - -

    THE UNDO STACK IS PURGED, BY DESIGN

    How to clear memory to prevent “out of memory error” in excel vba? . . . It seems that Excel basically purges the memory when you save the workbook. Pretty much like sql purges the transaction log when committing data . . . https://stackoverflow.com/questions/...r-in-excel-vba

    Enabling "Undo" after running a macro - Excel 2010/2013 . . . If you will run VBA code, undo stack will be cleared - it is not possible to prevent this . . . I don't think it's all that ridiculous. Just think of the resources Excel would require from a computer to retrace and reverse custom code. I guess a "snapshot" of the state the Excel sheet was in before code ran might be doable in the future but even that wouldn't be good enough to undo custom code that say works on 20 sheets in a workbook . . . https://www.excelforum.com/excel-pro...10-2013-a.html

    Excel Undo/Redo . . . As soon as the Excel sheet is modified through code (either VBA or VSTO), the Undo stack gets cleared. That is why the Undo/Redo button goes disabled. This is by design . . . https://social.msdn.microsoft.com/Fo...forum=exceldev

    - - - - -

    MICROSOFT HINTS AT WHAT'S GOING ON

    How to modify the number of undo levels in Excel . . . Note - When you run a Visual Basic for Applications macro, Excel allocates no memory for undoing actions. This feature is disabled for optimization of performance when you run a macro. [IT WOULD BE NICE IF THEY EXPLAINED WHY UNDO IS DISABLED FOR MACROS] . . . https://support.microsoft.com/en-us/...evels-in-excel

    - - - - -

    THE EXPLANATION IS BEST STATED HERE

    Why does Excel not allow "Undo" after a VBA execution? . . . [paraphrased, here's how I understand it] When changes are made directly in a Worksheet, temporary snap shots are stored, which can be rolled back (a total of 24) . . . Excel has no idea of how many programmatic changes might take place in the "Undo" history, so the "Undo stack" is cleared after VBA code (or any other code) makes any changes to a Worksheet . . . I'm not aware of any plans to change this, and it would be a major undertaking to cater for that . . . https://social.msdn.microsoft.com/Fo...forum=exceldev

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    also remember vba changes may not only be to one workbook and changes may be made to other objects that are not workbooks at all, do you think you should be able to undo non excel file creation or editing
    i have some workbooks with no data, just code, what undo would you think that should have?

    there are several alternatives to roll you own undo to a specific (or even multiple) workbook, including saveAs with versioning, before writing any data, as mentioned in your other thread
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    west, yes, apparently this can be more complex than what I'm doing - working with just one Worksheet.

    They should let you have the option to Undo, where they let you specify how much memory it can use, before it zaps it . . . for example, I have 16GB of RAM, 4GB is currently in use, maybe I could specify to let it go up to 4GB.

    If I mark this as resolved, does that mean no more posts are possible? Maybe I'll leave it open a while, in case others would like to comment.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    But now I'm thinking . . . If the size of the Undo file is an issue, why don't they just let you write it to a temporary file on your hard drive? Then the file size becomes much less important. Is this "no Undo" a rule that was made, for when computers had much less capacity? Maybe they should re-think it?

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    for when computers had much less capacity?
    but worksheets were also much smaller and recycle bins did not exist
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Oct 2017
    Posts
    17

    Re: Why doesn't Ctrl+Z work on a "Paste" macro, but it does if you used Ctrl+V ?

    I have posted the question here:

    VBA - There is no Undo for a Macro today, by design. Why? . . . https://answers.microsoft.com/en-us/...=1508510461390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width