Run Macros with Excel 2007 and above

Many of the tools, templates and systems that we build use macros in Excel.  Some of you will be quite capable and experienced with Excel macros and VBA.  Others could be coming across Excel macros for the first time.

Excel 2007 upwards has some additional security for running macros.  If you have run macros before on Excel 2007 and have no problems with security, then read no further.  If it is the first time, or the macros in our demo systems and templates don’t run, then this might be useful for you.

In Excel 2007, Microsoft introduced a new file extension.  Standard workbooks end with .xlsx.  Workbooks with functioning macros need to be saved as an Excel Macro-Enabled Workbook, .xlsm.  All our development templates for 2007 and upwards will have this macro extension.  In fact, if you frequently use macros then you might like change the default file extension (File>Options>Save>Save Workbooks).  Or, start with our free Development Template.

When you open a xlsm file with the default Office security settings, you will likely see the following:

When you get this warning band, you will not be able to run the macros until you enable the content or adjust the security settings.   One option is to enable the content every time you open the file.  This will get tiresome and security may prevent other macros or imported data from functioning.

A better solution is to adjust the security settings.  Go to the top left of the window and click Office>Options (Excel 2007) or File>Options (Excel 2010).  You will then get this menu.  We are going to go to “Trust Center Settings”…

Macro Settings and select the radio button for “Enable all macros (not recommended, potentially dangerous code can run)”.  Yes, we are recommending that you choose this option.  Microsoft may prefer you to play it safe, but we want to help you build awesome production scheduling tools and you are going to need macros.

.

Next is External Content.  “Enable all Data Connections” and “Enable automatic update for all workbook links”

Hit “OK” and “OK”.  Save the workbook, close and re-open.  You should see this with the warning message gone:

You can now get started to use the Development Template and macros and data connections in other files.

So what about security?  Well, going to this new level of Excel programming means that you may have to come out from underneath Microsoft’s nanny skirts.  Good security is always going to rely on user judgment.  The truth is that you should always take care to only open and run the files from a trusted source.

If you are worried about security from our downloads, then you can always check out the code and run the file through a virus checker before you change these settings.  We provide all open code, nothing hidden or locked away so you can check it and be sure.

At Production-Scheduling.com we have a policy of always using recorded macros, unless there is a good reason to go to VBA.  Sometimes there is a function you just can’t perform with the macro recorder.  Sometimes VBA will give you a significant performance gain.  Most of the code in our templates and tools can be recreated with the macro recorder.  This goes a long way to reassure people that the macros are only automating tasks that you can perform in the normal Excel interface.

If you want to know more about recording and using macros, then there is a tutorial in the Fast Excel e-Learning section.  Sign up to that and you will soon be confident about automating Excel tasks.