How To Create A Personal Macro File

How To Create Personal VBA Macro File Excel Macros

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.

How To Create Personal VBA Macro File Excel

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.

How To Create Personal VBA Macro File Excel

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.

How To Create Personal VBA Macro File Excel

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.

How To Create Personal VBA Macro File Excel
How To Create Personal VBA Macro File Excel

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 setup 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.

 

How To Create Personal VBA Macro File Microsoft Word

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.

How To Create Personal VBA Macro File Microsoft Word

Now that you are recording you can type some gibberish into your document and then click Stop Recording on the Developer tab. 

How To Create Personal VBA Macro File Microsoft Word

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.

How To Create Personal VBA Macro File Microsoft Word

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.

How To Create Personal VBA Macro File Microsoft PowerPoint

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.

How To Create Personal VBA Macro File Microsoft PowerPoint

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.

How To Create Personal VBA Macro File Microsoft PowerPoint
How To Create Personal VBA Macro File Microsoft 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 Code 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.