Assign a keyboard shortcut to a Microsoft Excel Add-in

Question

My friend shared a .bas file with me and told me to save it as .xlam in the vbEditor to have it as an Addin.

I've browsed in Add-ins and am able to enable it in my workbook.

Is there a way I can assign a keyboard shortcut to the Add-in? There's only one sub in that add-in file now.

I tried writing another sub with

Application.onKey "+^{C}" ,'Calculate'

But it doesn't trigger the sub to be executed.

If Calculate is the sub to run then you need to write Application.onKey "+^{C}" ,"Calculate" . Everything beginning with ' is a comment for VBA. — Storax
Thanks for helping me understand this. I thought the single quotes and the double quotes function the same like in JavaScript. — Naga Sai

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-09-20T17:12:40

You don't assign a shortcut to an add-in. Rather, you assign a shortcut to a macro - that is, a Public Sub procedure in your standard module.

So your code file might look like this:

Option Explicit

Public Sub Calculate()
    '...code...
End Sub

Open it in Notepad. I'll look like this:

Attribute VB_Name = "Module1"
Option Explicit

Public Sub Calculate()
    '...code...
End Sub

Under Public Sub Calculate(), you want to add an attribute so that the file looks like this:

Attribute VB_Name = "Module1"
Option Explicit

Public Sub Calculate()
Attribute Calculate.VB_ProcData.VB_Invoke_Func = "C\n14"
    '...code...
End Sub

This is exactly how Excel's macro recorder assigns macro hotkeys: no need for any Application.OnKey work-arounds.

Save the file, import it into your VBA project: Ctrl+Shift+C will now invoke that macro.

If you're using Rubberduck, forget all of the above and just go to your module in the VBA editor, find the procedure and annotate it like so:

Option Explicit

'@ExcelHotkey("C")
Public Sub Calculate()
    '...code...
End Sub

Where "C" will make the hotkey Ctrl+Shift+C; I'd warmly recommend not using "c" to avoid hijacking Ctrl+C (Copy).

Bring up the code inspections toolwindow, hit the "refresh" button; under "Rubberduck Opportunities" there should be an inspection result warning about annotations & attributes being out of sync - select "add member attribute" from the "Fix" menu, and you're done - no need to export/edit/import or deal with any obscure syntax, and if you want to change the hotkey, simply edit the comment accordingly and re-synchronize annotations & attributes.

See VB_Attribute annotations for more information.

As for saving your VBA project as an add-in, simply save your VBA project's host workbook as a .xlam add-in file, then close Excel altogether and re-open it - load your .xlam from the Developer Ribbon tab's "Excel Add-ins" button (hit "Browse" to locate your .xlam file if it's not in the list).

Assign a keyboard shortcut to a Microsoft Excel Add-in

1 Answers