Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Undo in VBA code (97, 2000,xp,2003)

    Jan Karel,

    That's an interesting technique, but...
    - It doesn't seem to work when you try to set the Value or Formula property of a cell.
    - It only works for setting properties, not for executing a method.
    - It makes writing VBA code tedious, and reading it harder.

    Isn't Microsoft going to make Excel VBA code undoable in a future version? After all, Word has had this capability for some time now.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undo in VBA code (97, 2000,xp,2003)

    Hi Hans,

    I agree about the readability, it is poor. If anyone has any suggestiond for improvement, let me know!
    I'll see what is wrong with Value and Formula.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undo in VBA code (97, 2000,xp,2003)

    It does work when setting the Value and formula property for me.

    One problem however. If you set the Value property of a cell that contains a formula, undoing the changes doesn't restore the former formula, but just the value. I guess it is safer to alway remember the formula property and restore that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undo in VBA code (97, 2000,xp,2003)

    Ah, of course.

    This isn't by far as simple as it appears...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Undo in VBA code (97, 2000,xp,2003)

    If I use code modeled on yours, with the line

    mUndoClass.AddAndProcessObject ActiveCell, "Formula", "=SUM(A1:A10)"

    it works fine if ActiveCell already contains a formula (or value). However, if ActiveCell is blank, the code doesn't set the formula. This is because the function GetOldValue in clsUndoObject returns False if the old value is "", and hence SetNewValue isn't excecuted. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Undo in VBA code (97, 2000,xp,2003)

    The functions:
    Split
    CallByName

    and the constant:
    vbGet

    are not available in XL97.

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Undo in VBA code (2000,xp,2003)

    As an excercise to get to grips with class modules I decided to try and write a generic routing one can plug into a project, to create an undo stack of actions performed by one
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undo in VBA code (97, 2000,xp,2003)

    That is correct Steve, I forgot to change that. I updated the title of the message.

    Thanks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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