By binding macros to shortcuts in the form of Ctrl-Shift-x they can be run pretty conveniently on the fly, which is useful when using Excel for modelling purposes. I set out in note form how to go about this below.

1) Create personal.xls

If you don’t already have personal.xls when you hit Alt-F11 to bring up the VBA macro editor in excel you will need to record a dummy macro in order to create it. In the drop-down which asks where to save the macro, select personal.xls.

Record the dummy macro, close Excel and hit save, re-open, hit Alt-F11 and you should be able to see VBAProject(PERSONAL.XLS) on the left hand side.

This is where the macros are kept (under Modules).

2) Stick in the hooks for the macros

In the VBA viewer, under VBAProject(PERSONAL.XLS), go into Microsoft Excel Objects and ThisWorkbook.

Stick in the hooks which we can use to over-ride the default shortcuts using Ctrl-Alt-X:

Private Sub Workbook_Open()

Application.OnKey “^+1”, “Format_Number” Application.OnKey “^+4”, “Format_Multiples” Application.OnKey “^+5”, “Format_Percentage” Application.OnKey “^+7”, “Format_Toggle_Box” Application.OnKey “^:”, “Macro_Cycle_Text_Colour”

End Sub

3) Stick in the macros

These can be pasted into VBAProject(PERSONAL.XLS) -> Moldules -> Module1

A few example macros are below.

Sub Format_Percentage() Selection.NumberFormat = “0.0%);(0.0%);0.0%);@_)” End Sub

Sub Format_Number() Selection.NumberFormat = “#,##0.0);(#,##0.0);#,##0.0);@_)” End Sub

Sub Format_Multiples() Selection.NumberFormat = “(0.0x))‘;((0.0x)‘;(“” - ““)%;@(%” End Sub

Sub Macro_Cycle_Text_Colour()

Dim c1 As Long
Dim c2 As Long
Dim c3 As Long
Dim c4 As Long
Dim c5 As Long
    
c1 = RGB(0, 0, 0)
c2 = RGB(0, 0, 255)
c3 = RGB(155, 187, 89)
c4 = RGB(112, 48, 160)
c5 = RGB(192, 0, 0)
    
With Selection.Font
    Select Case .Color
        Case c1
            .Color = c2
        Case c2
            .Color = c3
        Case c3
            .Color = c4
        Case c4
            .Color = c5
        Case Else
            .Color = c1
    End Select
End With

End Sub