questions about macros in excel

[1HandInMyPocket]

I recently trying to create a macro in excel but didn't like it so I tried to delete it. I found out I had to lower my security settings in Excel to delete the macro. I did that and re-raised my security to high again. Now when I open the file that had the macro I get a window that opens up that says:

"Macros are disabled because the security level is set to High and a digitally signed Trusted Certificate is not attached to the macros. To run the macros, change the security level to a lower setting (not recommended), or request the macros be signed by the author using a certificate issued by a Certificate Authority"

Does this mean I have a problem, and how do I stop this window from opening. I do not have any other macros saved.

I did a viurs check 2 days ago if that helps.

thanks

 

[osanowo]

That's a big annonyance from Excel but I guess it is intended to make it safer.

So unless you have a digital signature, or an appropriate certificate, the best setting is to "normal" and then you can choose when you open the file to either disable or activate macros...

 

[HrCa]

You may not have removed the macro completely.

Press Alt-F11 to open the VB editor. On the left side of your screen, you should see a folder called Modules. Expand this, then you should see Module1, Module2 , etc. If you want to kill all the macros in a sheet, click on the module(s), then point to File, then Remove Module1 ... (or 2, or 3, etc). Say no when asked if you want to export the module.

Save your Excel file, then you shouldn't get the warning message when you next open it.

 

[1HandInMyPocket]

thanks HrCa, I'll try that in the future. Osanowo, thanks for the suggestion about adjusting the setting to "normal", but I didn't want to have to click disable macros everytime I opened the file.

What I did (before HrCa's post) was opened up a older copy of the file and then updated the data, which was a bit of an inconvience.

But does anyone know why you can't undo a macro action after you've runned it?

 

[osanowo]

You cannot undo because the actions you take with the macro don't go in excel's buffer
First of all because execution of macros is very fast and secondly because the number of operations to be saved would be too huge and would slow down your computer...

With this example:

for each a in range("A1:A65000")
if a.value <>"" then a.Clearcontents
next a


So basically if you have 25000 non empty cells in this range, are you really sure you want Excel to store what they were before and what they are now?
It would definitely be a waste of memory, though I admit that it would be handy to undo some errors... but it would transform a one second or less execution in a few seconds one, or more!

So what I do is that I save the workbook whenever I run a big macro and if it did not work properly, I close without saving and reopen it.
Another way is to include in your temp macro a line like <Activesheet.copy sheets(1)> so that it works in a copy of your datasheet.