Giving PowerPivot a Keyboard Shortcut

When I was down at Microsoft for the MVP Summit, I suggested that, as the “other most powerful part of Excel” that PowerPivot, like the Visual Basic Editor, should be given it’s own keyboard shortcut.  Since Alt + F12 is currently empty, and since it’s right next to the VBE’s Alt + F11 shortcut, it only seems logical that Alt + F12 would make a logical choice.

Well, I got tired of waiting… not that I ever expected they’d release a patch for that or anything.  So I up a macro to do it.  It’s pretty short, works with Excel 2010 and 2013, and can be stored in the Personal Macro Workbook.*

If you know your macros, or you already have code in your personal workbook, then add the Workbook_Open line and the Sub OpenPowerPivot to your project.  But if you’ve never written a macro, or you’ve never used the personal macro workbook, here’s how you do it:

• Expose the Developer tab
• Click on “Record New Macro”
• Choose to store it in the Personal Macro Workbook.  (That will create your personal macro workbook.)
• Stop recording
• Press Alt + F11
• Find VBAProject(Personal.xlsb) in the Project Explorer.  (The project explorer is the treeview on the left.  If it’s not showing, press CTRL+R to display it.)
• Double click the ThisWorkbook module inside it
• Paste the following code in the code pane:

[vb]Private Sub Workbook_Open()
Application.OnKey "%{F12}", "OpenPowerPivot"
End Sub
[/vb]

• Now open the Modules folder and double click Module 1

• Replace the code in there with this:

[vb]
Sub OpenPowerPivot()
On Error Resume Next

Select Case Val(Application.Version)
Case Is = 14
Application.SendKeys "%GY2"
Case Is > 14
Application.SendKeys "%BM"
End Select

On Error GoTo 0
End Sub[/vb]

• Close the Visual Basic Editor
• Close Excel and say Yes when prompted to save changes to the Personal Macro Workbook
• Re-open Excel and Press Alt+F12

Isn’t that just the height of lazy?  (I mean efficient!) 

*Just a quick note here… if you float back and forth between Excel 2010 and Excel 2013 on the same machine, Excel locks the personal macro workbook for editing when you open the first Excel instance.  So if you open a new copy of Excel, you’ll get a prompt.  Just say okay though, and it will still work fine.