Well, guess what, you can’t. Microsoft Office 2007. A brand new way to create frustration in your life.
I was trying to create a macro in Microsoft Excel 2007 and nearly tore out my hair. The thing with macros in Excel 2003 was that it was relatively easy to accomplish, but it’s a whole different ballgame in Excel 2007. Here’s an account of what I did, only to discover that there’s no sane way to have macros run in Excel 2007.
So firstly, my setup: I’m using Excel 2007, and I’m on Windows XP SP 2.
I created an Excel workbook, and thinking that newer is better (Oh so wrong), I chose to create it under the new Excel 2007 format. Then I wrote my basic macro in that workbook, tested it, and all was fine. However, the workbook contained sensitive information, so I wanted to password-protect it. So I encrypted the file, choosing to have it prompt for a password before it would open.
Once that was done, I saved and closed the file as a macro-encrypted workbook in the trusted folder location that I supplied. Like any other normal person, I re-opened the file immediately to see if everything worked. I was actually almost 100% certain that nothing would go wrong. Hoho if only I knew.
I was surprised with a message in the message bar saying that the macro has been disabled. There was the Options button on the message bar, so I clicked that, and was faced with a prompt that said:
“This file contains macros that have been disabled because there is no antivirus software installed that can scan them. To run these macros, remove the encryption or permission restrictions on the file”
At this point, you’re given a comprehensive list of choices that allow you to resolve this issue in the most efficient manner possible. Well, actually, I wish. You’re just given a radio button list of ONE choice that says, “Help protect me from unknown content (recommended)”. Well it looks like it’s not only recommended, but it’s the only option.
So I meddled around, and lost about one year of my life in frustration and annoyance. My meddling led me to the Trust Center, where apparently I thought I had to put my macro in a trusted location. “Okay, for the sake of better security,” I thought. So I made these changes:
- Message bar: “Show Message Bar in all applications when content has been blocked”
- Macro settings: “Disable all macros with notification”
Looking at that, I had the impression that I should get some notification if Excel blocks something (Oh so wrong again).
I also figured that I should also add a trusted folder location in the Excel trust center. So I go add my current working directory to it. A little bit annoying, but if it means better security, I guess it’s okay right? (Oh so wrong – it doesn’t do crap).
When I opened the Excel workbook, I immediately noticed that macros were silently disabled. Remember how I set the Trust Centre settings above to show some notification? Well yeah, no notifications, nothing on the message bar at all, and I couldn’t find any way to enable macros.
Weird, I thought. So I used the Developer tab (some newfangled contraption only found in the fabulous Excel 2007), and tried to run the macro that I created. But, whoops! You’ll see this message: “Because of your security settings, macros have been disabled. To run macros, you need to reopen the workbook, and then choose to enable macros.”
Well, that’s weird, because I just opened the workbook and I was NOT given any choice to enable macros. So why was the message telling me to enable macros??
So, let’s summarise:
- If you try to run a macro-enabled Excel workbook (an xlsm file) in a location that is not trusted, you are told that macros have been disabled, and you can’t enable them.
- If you put that workbook in a trusted location, your macros are silently disabled.
The conclusion then? In Excel 2007, you can’t have a workbook with usable macros if it’s encrypted with a password. The ONLY way that I found was to save the workbook under Excel 2003.
I hope this has saved somebody some grief and frustration.