When you first start to dabble with macros and VBA, one of the first challenges you will face is fathoming out what to do with the personal macro workbook.
What is the Personal Macro Workbook
The Personal Macro Workbook called personal.xlsb does not exist until you create it and even then it tends to remain hidden from general view.
This personal workbook is the best place to store useful macros as it is loaded automatically as excel starts (after you’ve created it for the very first time) and means that the snippets of code and macros that you’ve deemed to be super useful are always available and not reserved for a single workbook which is the case with macros stored in all other workbooks.
Along with macros, you can also store user defined functions in the personal workbook macro so you can expand on those already provided by Excel.
How to create a Personal Macro Workbook for the first time
I create a personal.xls file for the first time by recording the worlds simplest (and most useless) macro.
Step 1 – Make sure the developer ribbon has been enabled
- File – Options – Customise Ribbon and tick the option for Developer
Step 2 – Record a macro
- Open a blank workbook
- Go to the Developer tab and select Record Macro
- In the option box ensure that it is set to store macro in: Personal Macro Workbook – press OK
- The macro is now recording your every Excel based move but we aren’t trying to do anything other than create personal.xls so now you should hit the stop recording button
You’ve now created your personal macro workbook but you will probably need to unhide it in order to edit it.
How to Unhide the Personal Macro Workbook
You have now opened what appears to be a blank workbook – very dull.
How to Store Useful Macros in the Personal Macro Workbook
In order to put useful macros in your personal macro workbook, you have to open the Visual Basic editor where your code will be stored.
You can do this by navigating to the Developer tab on the ribbon and then hitting the Visual Basic icon or you can just tap ALT and F11 to toggle between the editor and excel.
Within the editor you should see something like this, although the number of VBAProjects listed will depend on how many workbooks you have open. If you can’t see the Project window just amend your view by choosing View – Project Explorer and you may as well also open Properties Window using the same method.
Now go down to VBAProject (Personal.xlsb) and pop open the hierarchy until you reveal the modules. The modules are where the macro code will be saved and you will have one module called Module 1, that was created with our useless macro above. Double click this module to open and you will see this:
' Macro1 Macro
As this is the world’s most useless macro we can happily delete or overwrite this code with something far more useful.
What Macros should I put in my Personal Macro Workbook
Your personal workbook is a very personal affair and you should put in here anything that you find useful to your particular workflow.
Here’s a few of my choice examples for deleting blank rows:
This first macro will delete any row in which the cell in the selected column is blank. So in this example, with column B selected, rows 4,7 & 9 will be deleted. I find this particularly useful for dealing with ledger downloads where I may only be interested in details where actual costs have been incurred, regardless of whether there is a budget or not.
'Deletes entire row within the selected column where cell within the selection is blank
'Select column then run the macro
On Error Resume Next
On Error GoTo 0
The second macro will only delete rows if the entire row is blank. Just select the area you want to evaluate and run the macro. So in this example, only row 9 will be deleted and is useful as a clean up macro which doesn’t risk deleting values.
'Deletes the entire row within the selection provided the ENTIRE row is blank.
'Select the area for the macro to work on
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
When you’ve put a new macro in your personal macro workbook you must remember to save the file. When you close Excel it will ask you if you want to save changes to your personal macro workbook.
I’d be really interested to hear what macros you choose to store in your personal workbook.