Assign Shortcut keys within the VBE Window

Question

From the Excel Window, I can assign a shortcut key to my macro by:

1. going to the Developer tab
2. touching the Macros button
3. touching Options... in the resulting Dialog Box

Can I assign this from within the VBE Window, using the VBE menu bar instead??

Answer 1

The Application.OnKey approach keeps the shortcut separate from the procedure (which makes maintenance difficult), and you might end up with conflicts - particularly if you've assigned keys using the Macros dialog and the Application.OnKey property. And, of course, you need the SetShortCutKeys code to run on workbook_open, which might not always run.

Fortunately, it is possible to assign the shortcut key in VBA, and within the procedure itself, but you'll need to export the module, edit the text file, and then reimport. Unfortunately, while VBA does maintain the code, the shortcut keys only seem to work in Excel. Perhaps it's a feature of VBA that the host application can optionally support.

Let's say you have the following VBA in a standard module:

Option Explicit

Public Sub Bar()
  MsgBox "Bar"
End Sub

If you use the Macros dialog in Excel, and choose a shortcut key for the Bar macro to Ctrl+Shift+T, and then Export the module to a text file, you'll find the module source is:

Attribute VB_Name = "Module1"
Option Explicit

Sub Bar()
Attribute Bar.VB_ProcData.VB_Invoke_Func = "T\n14"
  MsgBox "Bar"
End Sub

Notice the Attribute Bar.VB_ProcData.VB_Invoke_Func = "T\n14" attribute, which wasn't visible in the VBIDE, but is persisted by VBA in the underlying source. The T in T\n14 is a case-sensitive key (which implies a Shift), and I think the \n14 must refer to the Ctrl key. By following the VB Attribute naming pattern and syntax, and including similar lines in your other procedures, you can assign shortcut keys in other procedures using the text editor, and then, when you're done, you can import the modified module back into the VBA project.

Note that once you import the modified module, you'll no longer be able to see the attribute lines, and you'll need to take care not to change the name or number of parameters in the procedure, as the VBIDE might delete the underlying attribute without warning you.

Answer 2

Alternatively, if you want to do it using VBA you can do something like:

Sub SetShortcutKeys()
With Application
    .OnKey Key:="^+K", Procedure:="YourMacroGoesHere"
End With
End Sub