How to avoid clearing undo stack after macro?

[Auric__]

Hmm... Excel 2000 SR-1... every time pretty much any VBA is run (except
toggling the gridlines), the undo stack gets cleared. Very annoying.
Anyone know a way to avoid it? (FWIW, I *don't* need to undo the VBA
actions.)

 

[Tushar Mehta]

AFAIK, there is no simple way to protect the undo stack. More
important, I don't know what it means to 'undo' something after the
execution of VBA code -- code that could have made the very idea of
undoing something meaningless. [As an example, suppose the last non-
VBA act was to bold cell A1. Subsequently, the VBA code deletes row 1.
What would it mean to undo the bolding of cell A1?]

I imagine someone has devloped some complex set of gyrations that will
allow your VBA code to recreate the undo stack after its own actions
are complete, but it has always seemed too much work to figure out. ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Auric__]

AFAIK, there is no simple way to protect the undo stack.

More
important, I don't know what it means to 'undo' something after the
execution of VBA code -- code that could have made the very idea of
undoing something meaningless. [As an example, suppose the last non-
VBA act was to bold cell A1. Subsequently, the VBA code deletes row 1.
What would it mean to undo the bolding of cell A1?]

Hmm... set the Bold property of A1 to False? That's an argument I
thought of before posting; stuff like that is probably why the undo
stack gets cleared in the first place

I imagine someone has devloped some complex set of gyrations that will
allow your VBA code to recreate the undo stack after its own actions
are complete, but it has always seemed too much work to figure out. ;-)

Would be nice if there was just an add-in or a DLL or something where
you just call a function that saves the stack (to a file or elsewhere in
memory) and another that restores it.