How To Create A Personal Macro File
What Is A Personal Macro File?
A Personal Macro file is a file you can create to store VBA code that you want to run whenever your application is open. The VBA code in this file is typically written to target whatever window is active at the point of command, much like how all the commands on the Ribbon Tabs are. This means that for the macros stored in this file you will want to code your VBA so that it is not specific to one project. For example, in Excel you would want to use ActiveWorkbook instead of ThisWorkbook references. Likewise, in Word you would want to use ActiveDocument instead of ThisDocument. Below I will show you how to setup of a Personal Macro file for Excel, Word, and PowerPoint as each is slightly different.
Setting Up A Personal Macro File In Excel
The easiest way to generate this file is to use the Macro Recorder. Go to the Developer tab (if it is not showing you can make it viewable in the Customize Ribbon section in your Excel Options) and click Record Macro.
Next you will want to choose where to store the macro that you are about to record. Select Personal Macro Workbook as your Store Macro In option. Then click OK.
Now that you are recording, you can select a few random cells and click Stop Recording on the Developer tab. In my example I just typed My Name in Cell A1.
Excel has now automatically generated your Personal Macro file. You can see it by going into the Visual Basic Editor and in the Project Window you should see a VBA Project named Personal.xlsb. You should also notice that in the Modules folder there will be a Module1 created. If you double click on Module1 you will see the code that you recorded.
Make sure you save your changes to your Personal Macro file before closing Excel. You can do this by either clicking the Save button in the Visual Basic Editor Window or by clicking Yes to the prompt that Excel will give you before closing the application.
Here is where your Personal Macro file is stored on you computer:
Windows XP
C:\Documents and Settings\[insert your username]\Application Data\Microsoft\Excel\XLSTART
Windows Vista, Windows 7, Windows 8
C:\Users\[insert your username]\AppData\Roaming\Microsoft\Excel\XLSTART
Setting Up A Personal Macro File in Microsoft Word
Similar to the Excel method, we will use the Macro Recorder to set up our Word Personal Macro File. Go to the Developer tab (if it is not showing you can make it viewable in the Customize Ribbon section in your Word Options) and click Record Macro.
Next you will want to choose where to store the macro that you are about to record. Select All Documents (Normal.dotm) as your Store Macro In option. Then click OK.
Now that you are recording you can type some gibberish into your document and then click Stop Recording on the Developer tab.
Word has now automatically generated your Normal File (aka Personal Macro file). You can see it by going into the Visual Basic Editor and in the Project Window you should see a VBA Project named Normal. You should also notice that in the Modules folder there will be a NewMacros module that was created. If you double click on the module, you will see the code that you recorded.
Make sure you save your changes to your Personal Macro file before closing Microsoft Word. You can do this by either clicking the Save button in the Visual Basic Editor Window or by clicking Yes to the prompt that Word will give you before closing the application.
Here is where your Personal Macro file is stored on your computer:
Windows XP
C:\Documents and Settings\[insert your username]\Application Data\Microsoft\Templates
Windows Vista, Windows 7, Windows 8
C:\Users\[insert your username]\AppData\Roaming\Microsoft\Templates
Setting Up a Personal Macro File in PowerPoint
Since PowerPoint does not have a Macro Recorder, you will have to create an add-in file to act as your Personal Macro file. All you need to do is store some VBA code in your presentation and then Save your presentation as a PowerPoint Add-In file.
I recommend saving your add-in file in the default Microsoft location for ease of access but you can technically save the file anywhere on your computer.
Microsofts Default Add-in Folder
Windows XP
C:\Documents and Settings\[insert your username]\Application Data\Microsoft\AddIns
Windows Vista, Windows 7, Windows 8
C:\Users\[insert your username]\AppData\Roaming\Microsoft\AddIns
After you have saved your file you will want to go into the Developer tab (if it is not showing you can make it viewable in the Customize Ribbon section in your PowerPoint Options) and click the Add-Ins button.
Then in the Add-Ins dialog box, click Add New and locate the PowerPoint Add-in file you saved. PowerPoint should default to the folder path I recommended above. Make sure that your Personal Macro file is checked and this file will automatically open every time you open PowerPoint.
What Next?
Now you should be all set to implement some major automation into your workflow! If you are not sure what to add to your newly created Personal Macro file(s) you can head over to the VBA Vault and browse the many snippets of code that I have posted there. If you have been using personal macros for a while, why don’t you leave your most useful macro in the comments section to give everyone else an idea of what they can add to their files.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.