SharePoint People use SQL too, especially for Data Analysis

Programming in Microsoft Excel – Macros – Part 3

In the previous examples in this series, you created macros that Excel stored directly with the workbook itself. In fact, that is why you have to save your spreadsheet not as a standard Excel spreadsheet with the extension of .xlsx, but with the extension that indicates the workbook has macros. To do this, you must use the dropdown Save as Type in the Save or Save As dialog. From the list of possible options (bet you did not know that spreadsheets could be saved so many different ways.) select Excel Macro-Enabled Workbook.

Once saved, you can copy the workbook to another location. You can even give a copy of the workbook to another user who can then install it on their machine. When they copy the workbook, and open it, they will see a yellow banner across the top of the spreadsheet warning them that: Macros have been disabled. This is a default security setting in Excel to protect you from malicious macros embedded in other people’s workbooks that you subsequently open on your computer. While it is nice that Microsoft has our back like this, it can become annoying to get this prompt every time that we open the workbook forcing us to click the Enable Content button at the end of the message if we want to use the embedded macros. Furthermore, Excel does not care whether someone else created the macro containing workbook or if you did. The security warning appears in all cases.

A way around this is to create a ‘safe’ folder on your machine where you can store not only your own spreadsheets that use macros, but also the workbooks of friends and colleagues whom you trust. Once placed in this safe folder, Excel can open the workbook and immediately enable macros without the security warning and prompt. To create this safe folder, follow these steps:

1) With Windows Explorer, create a new folder on your local hard drive where you want to save your macro-enabled workbooks. This folder can be created directly off the root of your drive or it can exist several folders down such as under Documents.

2) Open Microsoft Excel 2010.

3) Open the File menu and choose Options found near the bottom of the File menu.

4) Choose Trust Center.

5) In the detail area to the right, select the button Trust Center Settings.

6) In the resulting dialog, click Select Trusted Locations in the left menu.

7) In the detail area, you will see a list of the folders that Excel currently trusts. To add a new folder, click the button: Add New Location.

8) In the dialog that appears, select Browse to navigate to the folder where you want to store your macros. (Yes, you could directly enter the folder’s path, but navigating to it prevents any possibility of typing in the path incorrectly.

9) Click the checkbox to allow Excel to trust sub-folders

10) Click OK to complete this dialog.

11) Click OK in the Trust Center dialog to close it unless you have additional folders to mark as trusted locations.

12) Click OK in the Excel Options dialog to close it unless you have further changes to make.

At this point, you can open all macro enabled workbooks stored in this trusted location without having to see the Security Warning and with macros automatically enabled when you open the workbook. This applies not only to workbooks you create, but also workbooks created by others. Therefore, make sure you trust the macros that other people may have embedded in your workbooks before adding them to this folder.

How can you examine the macros in a workbook that you get from someone? After you open it, you must enable the macros if you want to be able to open them to examine the code. Don’t worry, if you immediately go to the Developer tab, there should be no events to trigger the macros into executing. Then go to your Developer tab and click on Macros in the Code group. The dialog that appears displays all the macros in the currently open workbooks. If you only have the one workbook open then this list is by definition all the macros in that workbook as shown in the following figure.

If there is more than one macro, select the macro you want to examine by clicking on it once and then clicking on the Edit button found on the right side of the dialog. This action opens the Microsoft Visual Basic for Applications integrated development environment shown below.

As I mentioned before, this series will not attempt to cover everything involve in programming in Excel, but if you have any familiarity with the Microsoft Visual Studio development environment, you should have no trouble figuring out how to use this environment. Since we are dealing with a simple macro, the code generated when recording the macro appears in the default window that appears in the development environment. You can read through the code one line at a time to determine what it does and whether you really do trust the code. You can even execute the code one line at a time, although this would not be my recommended method to determine if the macro has any malicious code because even when stepping through the macro one command at a time, it is too easy to execute just one command too many.

After reviewing the code, you can close the Visual Basic for Application window and then either go back to the Macros list to examine another macro, or you can exit Excel and copy the examined workbook to your trusted location folder. If for any reason you do not trust one or more of the macros in the spreadsheet, you should just exit Excel and delete the workbook from your system so you don’t accidentally run any of the macros at a future time. If the workbook comes from a co-worker, you may want to talk with them about your concerns about loading and running their workbook.

If on the other hand, you plan on creating macros that only you will use while developing spreadsheets but which the spreadsheet itself is not going to be dependent on if you were to give the workbook to another user, you have another option when creating your macros.

Remember when you create your macro Excel displays a dialog asking for the name of the macro and the shortcut key. Right after the shortcut key and before the Description there is another option: Store macro in. This option defaults to the current workbook. However, by using the dropdown, you can see two other options, a new workbook and something called a Personal Macro Workbook. If you select Personal Macro Workbook, Excel creates a new workbook behind the scenes and marks it as hidden. This workbook is not used to store data. Rather it is used only to store your macros. In fact, every time you open a new spreadsheet, Excel also opens the Personal Macro Workbook. You can then use macros stored here in other spreadsheets without having to recreate them in the new workbook. You might think of this workbook as your macro library. Remember that other users will not have access to the macros you create in your Personal Macro Workbook so do not store anything that they might need there. However, it is a great way to keep all of your prized tools available.

So where is this Personal Macro Workbook stored? On my machine the path is:

This folder is obviously not where I normally would store my workbooks. However, it is the place where Microsoft stores information that it wants to keep from session to session. In this case the XLSTART folder under Excel includes workbooks that you want Excel to automatically open when you start Excel, in this case the PERSONAL.XLSB workbook which contains the macros I want to use in any workbook that I’m developing without having to store those macros in each workbook.

So today, you should be able to take away knowledge of how to enable macros automatically by making a Trusted Location for your Excel workbooks with embedded macros. You should also know how to save macros to your own personal macro library that you can use to develop any workbook without having to add those macros to each one. I hope this makes creating and using macros more efficient for you.

Next time, we will return to our initial macro to restructure our production data for one more time to show how you can not only edit your macros, but how to make them loop so you do not get carpel tunnel syndrome from repeating pressing those shortcut keys.