« Best wishes for 2007
Inconsistent Formulas »

Macro Shortcut Keys

5th January 2007, 05:55 pm by Rob van Gelder

I often use Excel’s macro recorder to perform repetitive tasks.
It’s usually a list of cells with a handful of exceptions. That is, I cant just run the macro from start to finish - I have to give each item a brief glance before the macro runs.
At the end of the macro, it’s handy to position the selected cell as the start of the next item in the list.
I’ll assign a shortcut key to the macro, such as ctrl+w, then use it on demand.
The process becomes simple: look at the item, is it ok? yes, press ctrl+w, next item, is it ok? yes, press ctrl+w… over and over.

You can reassign your shortcut keys from the Macros window:
From Excel’s menu: Tools, Macro, Macros… (or hit Alt+F8)
Highlight a macro, then click Options.

More often than not, the macro recorder will give me a good first draft but I’ll have to edit it some more from within the VB editor.
While coding the changes, I wondered where Excel stores the shortcut key.

Could it be that Excel recognises the code comment?

So I deleted the comments to be sure. No, it wasn’t the comment.

It turns out that the shortcut key is stored in the Code Module, but it’s hidden from sight.

Export the Code Module (right-click the Module, click Export File) then open it in Notepad.

You will notice a line that looks like this:
Attribute Macro1.VB_ProcData.VB_Invoke_Func = “w\n14″

Category: VBA, Modules, VBA Functions and Keywords  |  Comment (RSS)  |  Trackback

11 Comments

  1. John Walkenbach:

    Good one, Rob. I rarely export/import modules so I never noticed that.

    5 January 2007, 6:21 pm

  2. chip:

    I always wondered that one myself. I’d tried the comment deletion test, but thought it unlikely. I had chalked it up to some sort of magic, but you’ve revealed the secret.

    5 January 2007, 6:39 pm

  3. Ivan F Moala:

    Yes, it is the “Attribute” that is the key.
    I beleive Attribute is like a tag in which you can specify additional
    information about entries defined for the VBA Modulw.
    It remains hidden within the VBE and is only
    viewable when exported out, as Rob has done.

    Typical format of of Module named:=OneTest

    Attribute OneTest.VB_Description = “This is a test macro by IFM”
    Attribute OneTest.VB_ProcData.VB_Invoke_Func = “d\n14″

    The VB_Description is what you have typed in from the Macro options.

    If the VB_Invoke_Func letter is Capital the Keyboard Shortcut = Ctrl + Shift + [letter you typed in]
    If not the Keyboard Shortcut = Ctrl + [letter you typed in]
    You cannot use SHIFT + letter (which makes sence as it would possibly conflict with
    Excels / Windows KB), so the key is weather the letter is Capitalised.

    I have an Addin that gets your Macro short cut keys using the above info.

    http://www.xcelfiles.com/GetShortCutKeys.html

    If you need to see the code (it’s a bit old) the password is “test” I think ?

    6 January 2007, 2:08 am

  4. Greg:

    Great site, first time poster. I have a marco, to send workbook as an attachment that I am working in, however I want to add this to a command button.

    As a side question, I want to name the file and save it with another marco, it would be two fields using a date/time field and 1 additional field to have the file named and saved to a certain location. How can this be accomplished? Is it possible?

    29 January 2007, 11:08 pm

  5. patti irwin:

    I’ve assigned a macro shortcut key that works initially. Now for some reason the shortcut key isn’t working, but if I “point” to the macro name the macro runs fine. I verified that the shortcut key is still hidden in the Attribute section of the code. How do I get it to work again?

    28 June 2007, 12:11 pm

  6. Julian:

    I’ve got the same problem as Patti Irwin reported.
    In Notepad, I see the hidden code:

    Attribute Create_TRF_from_webform.VB_ProcData.VB_Invoke_Func = “K\n14″

    It’s worked fine for weeks, but now does not invoke.

    Any ideas anyone?

    thanks, Julian

    27 November 2007, 7:16 am

  7. Brett:

    Julian, Patti,

    What’s the shortcut you assigned? If you use one that is an Excel shortcut, like Ctrl+c (copy) or Ctrl+w (close file, this works in other Office applications), Excel will do that instead.

    Brett

    27 November 2007, 9:53 am

  8. Brett:

    Looks like I’m wrong about the shortcut keys already assigned by Excel. I couldn’t get Ctrl+w to do anything but close the file in XL97. Sorry about that!

    Brett

    27 November 2007, 10:15 am

  9. Alex J:

    Can anyone explain what happens when two open workbooks have macros with the same shortcut key? It looks like only one of the two will always run, but I’m not sure which one, and why.

    27 November 2007, 12:48 pm

  10. Julian:

    the shortcut is Ctrl j (lower case)
    I’ve got a utility to check what shortcuts I’ve already setup and there’s no clash.

    I’ve tried changing the shortcut to Ctrl J (ie Ctrl SHIFT J)
    and other combinations, but the macro stubbornly refuses to run this way.

    All my other shortcuts work fine.

    very puzzling.

    28 November 2007, 6:32 am

  11. Julian:

    Alex, download the add-in I refer to.
    You will see which key’s got multiple functions.

    http://www.xcelfiles.com/GetShortCutKeys.html

    29 November 2007, 11:26 am

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment