Managing Macros in MS Office

It would be wonderful if there was one single way of handling macros in MS Office. But it does not look like Microsoft has yet come to understand what a unified office suite should do, at least not as well as LibreOffice does. Still there are some common themes here. The tricky thing here is that each component works slightly differently, and this becomes clear when we look at management of the macros.

Microsoft Word

In Microsoft Word, you have the option of recording a macro using the macro recorder. This is easy, and we did an example in the previous lesson. You can also write code directly using VBA. When it comes to storing your macros and making them available in documents, you need to begin with an understanding of Templates. I won’t go into an explanation here on this topic. I cover it in the similar suite LibreOffice Writer Templates in some depth if you want the overall theory, but in brief, Word has a default Template that serves as the basis for creating new documents unless you have specified a special Template of some kind. (Again, see my LibreOffice articles for more on the theory here.) For Word, this is a file on your hard drive called Normal, and it can have an extension of .dot, .dotx, or .dotm.

  • Normal.dot was the default Template for all versions of Word up through 2003.
  • Normal.dotx became the standard beginning with Word 2007, and the x is a clue to the change. That was when Office became an XML-based program, which is a topic for a different day in detail, but that is when all of the extensions got an x added to them. Word documents became *.docx files, for instance.
  • Normal.dotm refers to a Template that contains macros. In most respects it does the same job as Normal.dotx, but has the added capability of containing one or more macros. If you have never created a macro you won’t see this, and will have a Normal.dotx as your default Template. But once you create a macro and save it as one you want to use for all documents, your Template will change to Normal.dotm.

NOTE: Macros are a security hazard. Many of the more interesting malware attacks start with a malicious macro in a document. So don’t be casual about macros. The first time you open a file (even one you created) that has a macro included you will be asked if you want to enable the macro. You can decide to say it is a “trusted” file, or just make a decision each time you open it. But you want to be very sure you know it is safe before you open a macro.

Now, if you have a macro you want to access in a document and perhaps make available to all documents, or otherwise move from one document to another, you need to make use of the Organizer. This lets you copy styles from one document to another, or to a Template, or from one Template to another, and so on, but now it also has a tab for doing that with Macros. To access this, you need to have your Developer tab activated in the ribbon bar (see https://www.ahuka.com/?page_id=1348) and when you do you will see at the right-end of the ribbon a button that says Document Template. Click it, then the button on the lower left for Organizer, and then the tab for Macro Project Items, and you will see this:

Organizer open to Macro Project Items

Organizer open to Macro Project Items

 

 

 

 

 

 

As an alternate method, click Alt+F8 and there is an Organizer button there as well.

Microsoft Excel

In Excel 2016 things are a little different. You want to put your global macros in something called a Personal Macro Workbook. Any macros in there are global, meaning you can access them in any workbook you create. There can also be macros stored in a file or specific Template which are only available in that file or in files created from that specific Template. You create this Personal Macro Workbook by creating a macro and electing to store it in the Personal Macro Workbook. This will cause Excel to create it. It works just like this:

  1. Make sure you have enabled the Developer tab as we discussed previously. From now on I won’t remind you about this, I will just assume it has been done.
  2. Create a blank workbook in Excel.
  3. Go to the Developer tab, and find the Record Macro button on the left side of the ribbon.
  4. Click the Record Macro Button, and accept the default name for now. This macro isn’t important in itself, it is just a vehicle for creating the Personal Macro Workbook.
  5. In the Store Macro In drop-down choose Personal Macro Workbook.
  6. Click OK. This will close this dialog box and start the recording.
  7. In any cell, type the number 1.
  8. Then click the Stop Recording button in the left side of the ribbon. This will stop recording the macro.

Now, at this point you have a pretty useless macro, but you have induced Excel to create the Personal Macro Workbook, which was the objective here. If you want to delete the macro, you can do that now and you will still have the Personal Macro Workbook. To do that, you should:

  1. Click the View tab on the ribbon bar.
  2. Click the Unhide button in the middle of the ribbon bar.
  3. In the dialog box that opens, select PERSONAL. In a default installation that is all it will say since file extensions are normally hidden in Windows, but if you are displaying them look for PERSONAL.XLSB. Click the OK button.
  4. Now you are actually inside your Personal Macro Workbook!
  5. Click the Developer tab, then the Macros button on the left side of the ribbon. This opens the Macros dialog box.
  6. Select the Macro you just recorded and click the Delete button.
  7. Click Yes in the confirmation box.

So once you create a Macro you have several options for storing it for future use. You can put them in:

  • The Personal Macro Workbook. This will make the macro available to any workbook you create from now on.
  • A specific Template. If you have have created a Template you can store a macro in it, and that macro will be available in any workbook based on that Template.
  • A specific Workbook. This macro will only be in this workbook, or in any copy you make of this workbook.

Note: In essence, the PERSONAL.XLSB file is the new default Template for Excel, just as *.dotm becomes the new default Template for Word.

Recording a Macro in Excel

Just as we did with Word in the previous lesson, we can record a macro in Excel and save it for future use. So here is an example that does this:

  1. Open a blank Workbook.
  2. Click the Developer tab.
  3. Click cell A1. You may already be there, but click it anyway because now you are recording your macro. You may want to run it in a workbook where you are not already starting in cell A1.
  4. Type the number 10.
  5. Click the cell A2, and type the number 20.
  6. Hold down the Shift key while clicking cell A1. This should have both cells selected with a single border around both.
  7. Click and drag down the column to cell A10.
  8. This should fill in the numbers 10 through 100, in steps of 10.
  9. Click the Stop Recording button.

Now, this is just a sample, so no need to save it for suture use, but to see how it works go to another blank sheet in this workbook, go the Macros button, and run it.

And here is what the code looks like in VBA. Go to Macros, select the macro, then click Edit to bring up the VBA editor in a separate window:

Sub Test()
'
' Test Macro
'

‘
Range(“A1”).Select
ActiveCell.FormulaR1C1 = “10”
Range(“A2”).Select
ActiveCell.FormulaR1C1 = “20”
Range(“A1:A2”).Select
Selection.AutoFill Destination:=Range(“A1:A10”), Type:=xlFillDefault
Range(“A1:A10”).Select
End Sub

Final Thoughts on Recording Macros

Recording a macro is a good way to get your feet wet, and whenever you do it you should take a moment to look at the actual code that was generated. This will help to learn the underlying structure of VBA. But there are weaknesses in using the recording function. One of the major ones is that the recording function is very simple-minded. It records key presses and mouse clicks exactly as you did them when recording. If you made a mistake and typed the wrong character, then deleted it and entered the right character, every time you run the macro it will type the wrong character again, then delete it again and enter the right character again. In a simple macro this is not too much of a problem perhaps, but it is sloppy. So we want to get more familiar with Visual Basic Editor to figure out how to fix code directly.

 Save as PDF