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”
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
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