Enabling Macros In Excel Spreadsheets
Since I get a fair amount of readers downloading my financial spreadsheets and asking, "How do I get the macros to work?" I decided to write a quick tutorial on just that topic.
"Macros," where Excel is concerned, are simply sets of instructions that tell Excel to automate some process (or processes) inside the spreadsheet. In my case, I often use macros to sort and/or clear out spreadsheet data (with a single click!) and to perform a variety of similar mundane duties. Macros can be huge time- and effort-savers, but because they could conceivably be programmed to do pretty much anything to the computer they're running on — say, wipe out the hard drive, or even something worse — we have to be very careful with the Excel macros we run. You always want to verify a spreadsheet macro's source, and make sure you trust the soul who created the thing.
In any case, when you're ready to enable macros in your Excel program, you'll find that it's a snap.
Enabling Macros in Excel 2003 and Older
This will open up a message box like the one shown below.
Checking the radio button beside "Medium" (which is what I'd recommend) means Excel will prompt you for permission each time a macro tries to run. Checking the radio button beside "Low" (not something I'd encourage!) means Excel will have free reign to run any and all macros in the spreadsheets you open.
You might notice the words "Virus scanner(s) installed" in the bottom left corner of that screenshot. Having a good antivirus program running at all times is a great idea; having a good antivirus program running when you're using Excel macros is a must.
- Complete the procedure above, then save and close your spreadsheet.
- Close Excel completely.
- Reopen Excel and your spreadsheet
- Complete the procedure above once more.
- Save and close Excel again.
- When you open Excel the next time, your macros should be operating normally.
Yeah, it's odd, but I find that in Excel 2003, I usually have to change the security settings, save, and close Excel a couple of times before the changes take effect. It's annoying ... but that's life, right?
Enabling Macros in Excel 2007
The "Office Button" is the fancy button in the top left corner — the one with the colorful Office logo. Click it to open the menu shown in the next step.
The image above can be enlarged by clicking.
Now there should be a SECURITY WARNING notification beneath the Office ribbon, as shown in the next step.
After you click "OK" and the window closes, your macros should function normally. Because you selected "Disable all macros with notification" in Step 4 above, you will need to follow Steps 8 and 9 each time you open a spreadsheet that contains macros (if you want the macros to run, that is).
Alternately, in Step 4 above, you could select "Enable all macros." This would allow any and all macros to run freely at all times. But I would not suggest it! Don't do this unless you are ABSOLUTELY SURE you won't be downloading or using unsafe spreadsheets (and by extension, macros) from ANY source! Malicious macros and Active-X controls can do serious damage to your computer and/or network!
Excel 2007, Macros, Encryption, and Antivirus Issues
If Excel 2007 is blocking macros due to problems with "encryption" and your antivirus software, and you're not seeing the "Enable this content" radio button as shown above in Step 9, you likely need to download the latest Service Pack for Office 2007. For me, the fix was installing Office Suite Service Pack 2, found here:
Microsoft has a bit of info on this issue on its Knowledge Base Article 927150.
Alternately, you can save the offending file in Excel 2003 format (.xls), and the macros should run properly — assuming you've made the Excel 2007 changes noted above in Steps 1 through 9, of course.
If you need more help with your security settings and macro operation, just drop me a line. I can't promise to have all the answers, but I'll do my best!
