Excel VBA Tutorial: Get your personal macro code vault (aka Personal Macro Workbook)
Posted on July 20, 2018 By Victor Chan
Summary: Learn how to create your own Personal Macro Workbook. It acts like your personal code vault where you keep frequently used macros. Any macros in the Personal Macro Workbook can be called from any workbook that’s open on your computer. You can also use it to build a library of reusable VBA code to copy and paste into other macros. This can save you lots of time.
Difficulty: Beginner
The Personal Macro Workbook (Excel 2007 – 2016)
The Personal Macro Workbook (PERSONAL.XLSB) needs to be created before you can use it for the first time.
It is a hidden workbook that is automatically opened when you start Excel.
Any code you keep in it is available in all workbooks that are open.
I highly recommend you start using it.
Excel VBA Cheat Sheets » Find it hard to write Excel VBA Macros? Think there’s
too much to remember? You’re not alone! Check out my convenient one-page
guides. They’ll help you write Excel VBA Macros better and faster.
Click here to find out more…
#1 – Personal Macro Workbook – what is it and why does it matter?
Think of it as a personal code vault
Think of your Personal Macro Workbook as a personal code vault that stays in your computer.
It’s a hidden workbook that opens automatically when you start Excel.
You can run macros in the Personal Macro Workbook any time you have Excel open without needing to manually load the workbook yourself (because it’s opened by Excel on start up).
Any VBA code that you keep in this workbook will be accessible from any workbook that’s open in Excel. So you don’t need to put code in other workbooks to run the macros in your Personal Macro Workbook. You just run them from the hidden Personal Macro Workbook.
Another reason to use the Personal Macro workbook is that it’s a great place to store code samples (also known as code snippets) that you can copy and paste into your other macros. You can see it as your Personal Macro code vault (or library).
What to keep in the Personal Macro Workbook?
Anything you use that speeds up your work is a great thing to store here.
Later in section #5 I’ll show you some VBA gems that you can copy and paste into your own code vault.
In the mean time, to give you ideas… here are some macros that other Excel users have in their Personal Macro Workbooks:
- Macros to speed up data work (e.g. delete any rows with blank cells) – see NHS Excel’s list
- Macros to speed up formula work (e.g. wrap any existing formulas with IFERROR() – useful if you type that a lot) – again from NHS Excel’s list
- Macros to speed up filtering (e.g. filter on values NOT equal to active cell) – see Chandoo guest post by secret agent KV
- Macros to speed up review (e.g. create a summary sheet with all comments and highlights) – see Accounting Macros
Remember if it’s in your Personal Macro Workbook you can use it on any open workbook without putting any VBA code into that workbook.
One more thing…
Before you say “thank you very much, I going off to start using my own Personal Macro Workbook”…
… you first need to know how to create your own!
Yes that’s right, Excel doesn’t come with the Personal Macro Workbook ready to use – you need to make your own before using it as a code vault.
That’s what I’ll show you next.
#2 – Get your own – how to create a Personal Macro Workbook
Here are 5 steps to get your Personal Macro Workbook.
Watch the animated GIF for the full walkthrough (1 min) and read the step-by-step instructions below.
Step 1 – Record a dummy macro in “Personal Macro
Workbook”
Click the Record button on the bottom left corner of the window (next to “Ready” in the status bar) to start the macro recorder.
In the dialog box make sure you choose to store macro in “Personal Macro Workbook” then click OK.
Click the Stop button on the bottom left corner of the window to stop the macro recorder. We don’t record any actions because we just want Excel to create the Personal Macro Workbook file. (That’s why we call this a dummy macro!)
Step 2 – Check the Personal Macro Workbook exists in the VBA Editor
Before we check the Personal Macro Workbook, make sure you have the Developer tab visible in the Excel Ribbon.
While you don’t need the Developer tab to access the VBA Editor, it does give you access to useful features like Form Control buttons.
To display the Developer Tab, right click on an empty space in the Ribbon and choose “Customize the Ribbon”.
On the right hand side of the dialog box, under Main Tabs, check the “Developer” box and click OK.
In the new Developer Tab (by default at the right end of the Excel ribbon), click Visual Basic to enter the VBA Editor (or use shortcut ALT + F11).
Look on the left for the Project Explorer window.
If you can’t see the Project Explorer use the shortcut CTRL + R to display it.
In the Project Explorer you will see a VBAProject called “PERSONAL.XLSB”.
This is your Personal Macro Workbook.
You will also see “Microsoft Excel Objects” and “Modules”.
Step 3 – Delete the dummy macro
In step 1 we recorded an empty macro in the Personal Macro Workbook.
Now we can remove the empty macro as we don’t need it.
Click on the “+” next to Modules to expand that node.
Then double-click on Module1 to bring up the code window. It normally shows on the right side of the VBA Editor window.
Delete the text for the dummy macro as we don’t need it.
Step 4 – Save the Personal Macro Workbook
In the VBA Editor File Menu, click on Save (or press shortcut CTRL + S).
Any time you modify VBA code in the Personal Macro Workbook you should save it. If you forget to save it, Excel will prompt you to save on exit.
Step 5 – (Optional) Change the VBA Project name
You can change the project name to something more descriptive.
In the Project Explorer window, click on the VBA Project “PERSONAL.XLSB”.
Then press F4 twice (to edit the name in the Properties Window).
Here I have changed the name to “My_Code_Vault”. Note you can’t have spaces in the name.
#3 – Location – where is it stored?
If the Personal Macro Workbook exists, you can find it in the Excel startup folder:
C:\Users\Username\AppData\Roaming\Microsoft\Excel\XLSTART
(where you replace Username with your own username)
You can also use the VBA Editor to display the full path on your computer like this:
- Open the VBA Editor (press ALT + F11)
- Go to the Immediate Window (press CTRL + G)
- Type this in the Immediate Window: ?Application.StartupPath
- Presss Enter
It’s useful to know this location so you can make backups of the Personal Macro Workbook (for details see section #6 – Backups).
AppData is a hidden folder
When I first looked for my Personal Macro Workbook, I couldn’t find the AppData folder.
If this happens don’t panic!
AppData is a hidden folder. To show it, follow these steps:
- Open a Windows File Explorer window (shortcut WIN + E)
- In the File menu click on Options (change Folder and Search Options)
- In the Folder Options dialog box click on the View tab
- Under Advanced Settings, select Show hidden files, folders and drives
You should now be able to view the AppData folder.
#4 – Storing and Running Macros – from the Personal Macro Workbook
AppData is a hidden folder
Your new Personal Macro Workbook has one module (Module1).
You can add any VBA code here by typing it or copying and pasting code from elsewhere. Remember to open the VBA Editor first (ALT + F11).
Here are step by step instructions:
- Select the PERSONAL.XLSB project in the Project Explorer (CTRL + R)
- Click on the ‘+’ to the left of the project to expand it (you will see Objects and Modules)
- Double click on Module1 to open the code window (normally opens on the right)
- Write or Copy/Paste the macro code you want to keep in Module1
- Save the workbook using CTRL + S (or File > Save)
Run your macros in Excel
There are several different ways to run your macros.
If you’re in the VBA Editor you can:
- Place the insertion point within the Sub… End Sub of a procedure and press F5 to run it, or;
- In the Run menu, click on Run Macro, or;
- In the Standard toolbar, there is a green triangle point right. That’s the run button – click it to run the current procedure.
If you’re in the Excel Interface you can:
- Press ALT + F8 to bring up the Macro dialog box. In the Macro dialog box, select the macro you want to run and click on run.
- From the Macro dialog box you can also assign a shortcut key combination (e.g. CTRL + SHIFT + C) to a macro. First select the macro, then click on Options, then enter your shortcut key combination and click OK. Back in Excel press your key combination to run the assigned macro.
- Add the macro to a custom ribbon menu. Right click on an empty space in the ribbon and select “Customize the Ribbon”. In the Excel Options dialog box add a new custom tab for your macro. Select to choose commands from “Macros”. Then add the macro to the new custom tab using the “Add” button.
Note: Remember you can press ALT + F11 to switch between VBA Editor and Excel.
Store your macros in Modules
Now that you have a Personal Macro Workbook, you will want to add macros to it. Place code in here that you will use yourself (and not for distribution to other users).
Use modules to organize your code. Think of code modules as chapters of a book, and your functions and subroutines as paragraphs in each chapter.
To add a Module:
- In the VBA Editor select Insert > Module to add more code modules (shortcut ALT > I > M).
To copy a Module:
- You can copy Modules quickly between workbooks by clicking and dragging from one VBAProject to another.
To remove a Module:
- In the VBA Editor Project Explorer, right click on the module and select “Remove …”
- In the next dialog box, click “No” to remove without exporting the code or click “Yes” to export first and then remove, or cancel to keep the module
Rename your Modules
You can rename your modules to something more descriptive than Module1 (e.g. Mod_Worksheets to store VBA code that helps you work with worksheets).
To rename a module:
- Click the module in the Project Explorer to select it
- Go to the Properties Window and click on the name box
- Type in a new name then press Enter
Also… I like using keyboard shortcuts for tasks I do frequently. If you like shortcuts too, try this one. I think you’ll like it 😊
Here’s the key combination to insert a new module and rename it:
- Insert new module (ALT > I > M)
- Rename module (press F4 twice)
#5 – Sample Code – some VBA code gems to keep in your new code vault
Here are some VBA code snippets for you to stash in your new code vault.
And there are tons more that you can find freely on the web (using Google or another search engine).
When you see “Private Sub” in the code below, this means a user cannot run the macro in the Excel Macro Dialog box. If they press ALT + F8 they will not see any private macros. You can remove the Private to make the macro visible in the Excel Macro Dialog box.
Sort worksheets in alphabetical order (A – Z)
Private Sub SortSheetsAtoZ() ' PURPOSE : Sort worksheets in alphabetical order '// Declare variables Dim Counter_1 As Integer Dim Counter_2 As Integer '// First For Loop through every sheet For Counter_1 = 2 To Sheets.Count '// Second For Loop through every sheet from 1 to Counter_1 - 1 For Counter_2 = 1 To Counter_1 - 1 '// Compare name of current sheet and previous sheet If Sheets(Counter_2).Name > Sheets(Counter_1).Name Then '// Move current before previous if alphabetically correct Sheets(Counter_1).Move before:=Sheets(Counter_2) '// Close If statement End If '// Loop back Next Counter_2 '// Loop back Next Counter_1 End Sub
Sort worksheets in reverse alphabetical order (Z – A)
Private Sub SortSheetsZtoA() ' PURPOSE : Sort worksheets in reverse alphabetical order '// Declare variables Dim Counter_1 As Integer Dim Counter_2 As Integer '// First For Loop through every sheet For Counter_1 = 2 To Sheets.Count '// Second For Loop through every sheet from 1 to Counter_1 - 1 For Counter_2 = 1 To Counter_1 - 1 '// Compare name of current sheet and previous sheet If Sheets(Counter_1).Name > Sheets(Counter_2).Name Then '// Move current before previous if alphabetically correct Sheets(Counter_1).Move before:=Sheets(Counter_2) '// Close If statement End If '// Loop back Next Counter_2 '// Loop back Next Counter_1 End Sub
Synchronize all worksheets in the active workbook to the same view as the active sheet
This subroutine is handy for resetting the view of all worksheets in your workbooks.
Let’s say you have 20 worksheets with different levels of zoom and different cells selected….
… you could manually adjust each worksheet to select A1 and set the zoom to 100%.
Or you could adjust one worksheet to the correct view and then run the macro to sync all the other worksheets to the active worksheet.
It’s so much easier to use the macro than manually adjust each worksheet!
Private Sub SyncWorksheetsToActiveSheet() ' PURPOSE : Synchronize all worksheets to same view as ActiveSheet '// Check if the User Selected Sheet is a worksheet If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub '// Declare variables Dim Wks_Selected As Worksheet Dim Wks As Worksheet Dim View_Row As Long Dim View_Col As Long Dim View_Zoom As Variant Dim View_Selected As String '// Set User Selected Sheet in memory Set Wks_Selected = ActiveSheet '// Extract view information from Active Sheet '// Row: ActiveWindow.ScrollRow '// Col: ActiveWindow.ScrollColumn '// Zoom: ActiveWindow.Zoom '// Rng: ActiveWindow.RangeSelection.Address View_Row = ActiveWindow.ScrollRow View_Col = ActiveWindow.ScrollColumn View_Zoom = ActiveWindow.Zoom View_Selected = ActiveWindow.RangeSelection.Address '// Loop through all worksheets in active workbook For Each Wks In ActiveWorkbook.Worksheets '// Skip hidden sheets (check using If Then) If Wks.Visible = xlSheetVisible Then '// Activate worksheet Wks.Activate '// Sync view on Active Sheet to Selected Sheet ActiveWindow.ScrollRow = View_Row ActiveWindow.ScrollColumn = View_Col ActiveWindow.Zoom = View_Zoom Range(View_Selected).Select '// Close If statement End If '// Loop back to next sheet Next Wks '// Restore Original Position Wks_Selected.Activate End Sub
Export each worksheet to its own workbook
Private Sub WorksheetsToWorkbooks() ' PUPPOSE : Create a new Workbook for every worksheet in the Active Workbook '// Declare variables Dim Wbk As Workbook Dim Wks As Worksheet '// Turn off Application Alerts so you don't get '// alerts when deleting worksheets Application.DisplayAlerts = False '// Loop through every worksheet in this workbook For Each Wks In ActiveWorkbook.Worksheets '// Create new workbook Set Wbk = Workbooks.Add '// Save workbook in same folder as this workbook '// with same name as worksheet Wbk.SaveAs ThisWorkbook.Path & "\" & Wks.Name '// Copy target worksheet to the new workbook Wks.Copy before:=Wbk.Worksheets(1) '// Delete pre-existing worksheet Wbk.Worksheets(Wbk.Worksheets.Count).Delete '// Close workbook and save changes Wbk.Close savechanges:=True '// Loop back to the next worksheet Next Wks '// Turn on Application Alerts so you get alerts '// when deleting worksheets Application.DisplayAlerts = False End Sub
Function to find the last Row, Column or Cell in a
worksheet
This function allows you to find the last non-blank row, column or cell in specified range or worksheet.
I recently wrote about different ways to achieve this result… so if you’re interested to learn more, click here to read the tutorial.
Private Function FindLast(SearchType As String, _ Optional Str_Worksheet As String, _ Optional Str_Range As String) ' PURPOSE : Find last row, column or cell in specified range ' ' PARAMETERS: ' SearchType ' Row = last row returned as Long ' Col = last column returned as Long ' Cell = last cell returned as String Dim Last_Row As Long Dim Last_Col As Long Dim Wks_Search As Worksheet Dim Rng_Search As Range On Error GoTo ErrHandler 'Default to ActiveSheet if Worksheet not specified If Str_Worksheet = "" Then Set Wks_Search = ActiveSheet Else Set Wks_Search = Worksheets(Str_Worksheet) End If 'Default to all cells if Range not specified If Str_Range = "" Then Set Rng_Search = Wks_Search.Cells Else Set Rng_Search = Wks_Search.Range(Str_Range) End If Select Case SearchType Case "Row", "row": 'Find last row with data On Error Resume Next FindLast = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row On Error GoTo 0 Case "Col", "col": 'Find last column with data On Error Resume Next FindLast = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column On Error GoTo 0 Case "Cell", "cell": 'Find last cell using last row and column On Error Resume Next Last_Row = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Last_Col = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column FindLast = Wks_Search.Cells(Last_Row, Last_Col).Address(False, False) 'Return Address Row, Col as Relative References (not Absolute) 'If Last_Row or Last_Col = 0 then entire sheet is blank, return first cell If Err.Number > 0 Then FindLast = Rng_Search.Cells(1).Address(False, False) 'Return Address Row, Col as Relative References (not Absolute) Err.Clear End If On Error GoTo 0 End Select Exit Function ErrHandler: MsgBox "Error setting the worksheet or range." End Function
Do you have any VBA code to share?
If you have any favorite VBA code that you think others would benefit from using in their Personal Macro Workbooks… please share in the comments below. Would love to help more people with code that’s useful
#6 – Backups – how to save copies for safe keeping
The Personal Macro workbook is stored in your XLSTART folder (see section #3 – Location – where is it stored?) and there is only one version at any given time.
It’s a good idea to backup the workbook in another location in case you accidentally overwrite some code and lose it.
How to backup your Personal Macro Workbook
You can do that by manually copying the file from the XLSTART folder to a backup folder of your choice.
Or you can run the following macro “Save_Backup”.
Sub Save_Backup() ' PURPOSE : Save a copy of this workbook with date appended ' Drive:\path\filename-yyyymmdd.xlsb ThisWorkbook.SaveCopyAs Filename:= _ "C:\Backups\Excel Files\" & _ Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".", , vbTextCompare) - 1) & _ "-" & Format(Date, "yyyymmdd") & ".xlsb" End Sub
This saves the Personal Macro Workbook to a folder of your specification and appends the date in yyyymmdd format.
Replace the folder path “C:\Backups\Excel Files\” with your own backup folder path.
How to restore your Personal Macro Workbook
To restore a backup:
- Go to the XLSTART folder and delete the existing Personal Macro Workbook
- Copy the backup workbook into the XLSTART folder
- Rename the backup workbook to “PERSONAL.XLSB”
#7 – Recap – remember what you learned
Let’s review the main points we covered today. This will help you understand and remember it for longer. Any questions just let me know in the comments section.
Personal Macro Workbook
- The Personal Macro Workbook is a hidden workbook that opens automatically when you start Excel.
- Think of it as your personal code vault that is only accessible to you on your own computer.
- Any VBA code you keep in it will be accessible in any workbook that’s open in Excel.
- It’s a great place to store code samples (also known as code snippets) that you can copy and paste into your other macros.
Get your own
- When you start using Excel with a fresh installation, there is no Personal Macro Workbook.
- To get your own Personal Macro Workbook follow these steps:
- Step 1 – Record a dummy macro in “Personal Macro Workbook”
- Step 2 – Check the Personal Macro Workbook exists
- Step 3 – Delete the dummy macro
- Step 4 – Save the Personal Macro Workbook
- Step 5 – (Optional) Change the VBA Project name
Location
- If the Personal Macro Workbook exists, you can find it in the Excel startup folder.
- C:\Users\Name\AppData\Roaming\Microsoft\Excel\XLSTART
- AppData is a hidden folder – don’t panic if you can’t find it!
- To make AppData visible, use the Folder Options dialog box in Windows Explorer to show hidden files, folders and drives
Storing and Running macros
- You organize your macros in code modules.
- Think of the Personal Macro Workbook as a book, with code modules being chapters of the book and procedures being paragraphs in each chapter.
- There are many ways to run the macros in your Personal Macro Workbook. Here are ways we covered:
- (VBA Editor) Hit F5 to run the current macro.
- (VBA Editor) In the Run menu, click on Run Macro.
- (VBA Editor) In the Standard toolbar, click on the run button with green triangle pointing right.
- (Excel) Press ALT + F8 to bring up the Macro dialog box and choose a macro to run.
- (Excel) Use the Macro dialog box to assign a shortcut key combination.
- (Excel) Add macro to a custom ribbon menu.
Sample Code
- I shared some sample VBA code you can put in your new code vault. Here’s a list:
- Sort worksheets in alphabetical order (A – Z)
- Sort worksheets in reverse alphabetical order (Z – A)
- Synchronize all worksheets in the active workbook to the same view as the active sheet
- Export each worksheet to its own workbook
- Function to find the last Row, Column or Cell in a worksheet
- Feel free to share any VBA code you think is useful in the comments below.
Backups
- To create a backup you can manually copy the PERSONAL.XLSB file from the XLSTART folder to a backup folder of your choice.
You can also run the macro “Save_Backup” to automatically save to a folder of your choice with the date appended.
Please leave a comment below if you have any questions or need help with your Personal Macro Workbook. I’ll try my best to help!
And if you have any favorite VBA code that you think others would benefit from using in their Personal Macro Workbooks… please share in the comments below.
#8 – References – further reading to learn more
Here are references you might find useful.
- Accounting Macros – for automating simple tasks
- Chandoo.org (What would James Bond have in his Personal Macro Workbook?)
- Chandoo.org (Secret Agent KV’s Chops… what’s in his personal macro workbook?)
- Cybertext Consulting (Can’t see the AppData folder?)
- Excel Campus (How to create a Personal Macro Workbook video series)
- Make Use Of (What’s the Difference Between AppData Roaming and Local?)
- Microsoft Support (Copy your macros to a Personal Macro Workbook)
- MrExcel forum (Macro to save backup copy of this workbook with date appended)
- MrExcel forum (How do I import my personal macro workbook to another computer?)
- NHS Excel (What to do with the Personal Macro Workbook + VBA Samples)
- Reddit (What are some of the best macros you have saved in your personal workbook?)
- Ron de Bruin (Excel Automation – create and use a PERSONAL file for my VBA code)
Excel VBA Cheat Sheets » Find it hard to write Excel VBA Macros? Think there’s too much to remember? You’re not alone! Check out my convenient one-page guides. They’ll help you write Excel VBA Macros better and faster.
Click here to find out more…
Related Posts:
Author: Victor Chan
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.