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 – Excel 365)
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.
#1 – Personal Macro Workbook – what is it and why does it matter?
#2 – Get your own – how to create a Personal Macro Workbook
#3 – Location – where is it stored?
#4 – Storing and Running Macros – from the Personal Macro Workbook
#5 – Sample Code – some VBA code gems to keep in your new code vault
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…
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).
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:
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.
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.
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.
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!)
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”.
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.
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.
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.
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:
It’s useful to know this location so you can make backups of the Personal Macro Workbook (for details see section #6 – Backups).
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:
You should now be able to view the AppData 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:
There are several different ways to run your macros.
If you’re in the VBA Editor you can:
If you’re in the Excel Interface you can:
Note: Remember you can press ALT + F11 to switch between VBA Editor and Excel.
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:
To copy a Module:
To remove a Module:
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:
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:
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.
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
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
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
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
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
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
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.
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.
To restore a backup:
#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.
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.
Here are references you might find useful.
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:
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
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.