Macros in Excel

updated: 11/28/2022

Macros provide an exceptional method to put little ‘programs’ into things like spreadsheets. They are easy to create and to modify. They can add tremendous functionality to an Excel spreadsheet. But because they are so easy to create, they are often the first stop for virus-writing wanabees. You should be very careful before running any macro; think about whether it might be a virus. But if you are reading this because Pete sent you a spreadsheet, if it’s from him, it is safe and they might *save you work!*

The default setting in Excel is to not allow macros to run, to protect you from the possibility of one of these viruses. You must ‘enable macros’ in order to use their functionality.

November, 2022 experience: I just helped a friend get one of my spreadsheets going on his computer. (Windows, Excel ‘2-3 years old’). He said that when he clicked on one of the macro buttons, Excel just responded that he needed to ‘learn more’ and it gave him a link to internet help. What we did was to go to the File Menu, Options, Trust Center, Trust Center Settings, Trusted Locations, and then he Added a New Location. This entails browsing to a folder in your documents (you might create it ahead of time – maybe something like ‘Ski Spreadsheets with Macros’) and selecting it as a place that Excel trusts that macros are ok. He then quit Excel, put the spreadsheet with macros into the folder, relaunched Excel and it worked as it was supposed to.

I’d love to talk to someone about doing this with an Apple computer 🙂 I’m guessing that it’s something similar.

The following instructions worked in the ‘good old days’, but may be obsolete:

To Enable Macros to run

Apple Computers: all but Excel 2008

When you open the spreadsheet in Excel, a dialog box opens and allows you to choose Enable Macros (or not). I believe that most ASD computers have Excel installed and that this option is available. (You must use Excel – the program Numbers will not work.)

Windows Computers – all but Excel 2003

You can start them individually by opening the spreadsheet and then clicking on the Options button that is found to the right of the Security Warning near the upper left corner of the screen (careful -it’s faint and hard to see). Select ‘Enable This Content’. If you are going to do it consistently, you can create a ‘Trusted Location’. Excel will automatically enable macros on any file saved in that folder. To set this up, from the ’round button’, select Excel Options / Trust Center. Read the Help file if necessary.

Excel 2003 – Windows

The default installation does not allow you to enable macros. You must change the security level. From the Tools menu, select Macros, Security and change the level to Medium. Restart Excel, open this spreadsheet and select Enable Macos. They will then work.

Apple Computers – Excel 2008

Unfortunately, Microsoft and Apple did not support Visual Basic macros in Office 2008 on Macintosh computers. There is no way to make them work. But, Microsoft brought them back with Excel 2011. When you open the spreadsheet, it will give you an option to enable them.

To learn more:

To make use of the functionality of these macros, you don’t need the following, but anyone with a mathematical / computer interest, might want to dig a little deeper. (Students, this is a really interesting way to get a little programming experience. If you are STEM interested, it could help you in future classes. (Contact me for more discussion!))

Getting started doesn’t take much: Modest instructions. Or if you just want to look at existing macros to see an example and what is going on: Instructions are posted