≡ Menu

What to do with the Personal Macro Workbook

Macro

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

Developer Ribbon

Step 2 – Record a macro

  • Record ButtonOpen 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

  • Unhide Personal Macro WorkbookNavigate to the View menu on the excel ribbon.
  • Click on the Unhide image
  • Select PERSONAL and hit OK

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.

Visual Basic Editor

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:

1
2
3
4
5
6
7
Sub Macro1()
'
' Macro1 Macro
'

'
End Sub

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:

DeleteRowOnBlankCell()

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.

DeleteRowOnBlankCell()

1
2
3
4
5
6
7
8
9
10
Sub DeleteRowOnBlankCell()

'Deletes entire row within the selected column where cell within the selection is blank
'Select column then run the macro

On Error Resume Next
Selection.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0

End Sub

DeleteBlankRows()

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.

DeleteBlankRows()

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub DeleteBlankRows()

'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
Selection.Rows(i).EntireRow.Delete
End If
Next i

End Sub

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.

Doctor Moxie

Written by -

NHS Accountant with geeky tendencies - serial blogger on subjects varying from Excel, Raspberry Pi, productivity, allotment gardening and running. The NHSExcel blog is reserved for Excel topics.

Comments on this entry are closed.

  • Healthcare Analyst

    I have one that encapsulates a formula with IFERROR([formula],0) which comes in handy when calculating year to year growth rates on a line item where the denominator will inevitably be zero…

    • http://whatapalaver.co.uk Dr Moxie

      I’m sad that its taken me 2 years to spot your comment. I’ve only come across it now as I am returning to this post after writing one about the joys of a macro that wraps an IFERROR formula. I could have been using it for 2 years!