Excel VBA Macro Undo

[kjv1611]

I had thought before, and had heard and read that "undoing" a VBA macro was impossible.

Well, the thought popped in my mind again, and I came across this interesting tip from John Walkenbach:

http://j-walk.com/ss/excel/tips/tip23.htm

It shows how to use the Application.OnUndo method in Excel VBA. Well, that is a really interesting concept. And I want to start building something to that extent into some of my VBA macros, b/c I know there have been times when I said... "oh, you know what..." but it was too late.

But I have one thought or question about the idea. That is, what if the selected range puts such a huge amount of data into the "undo cache" that it ends up causing the system to crash when you try to undo it? I would think that most of the time, it wouldn't be so big, but what are the possibilities?

Are there any known upper limits where it would be best to just return a message that says something like, "WARNING: This Action cannot be undone," similar to an Access Database with deleting large numbers of records at the same time?

Thanks for any thoughts or suggestions.

 

[PHV]

Why not simply use the SaveAs method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

So you're saying save the workbook as a different name prior to running the event, and then if the user wants to undo, have Excel close the "current" or "edited" workbook, and re-open the save-as workbook... then save-as to the original name.... deleting the "temporary" workbook?

I was thinking that might be a solution I could use once the data range exceeded some limits. Otherwise, the best performance would come from storing it in an array, I think.

Consider if the workbook has loads of rows, columns, and formulas in one or two columns. It can put a burden on Excel to save and reopen the entire workbook. But it might be worth it IF the range is above a certain size, I suppose.

But for most things that the user would want to undo, I think the array method would work quite well.

Is there any way to calculate how much memory an array is holding or would use based on the data input into the array?