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
14?
Good one, Rob. I rarely export/import modules so I never noticed that.
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.
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
14?
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 ?
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?
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?
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
14?
It’s worked fine for weeks, but now does not invoke.
Any ideas anyone?
thanks, Julian
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
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
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.
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.
Alex, download the add-in I refer to.
You will see which key’s got multiple functions.
http://www.xcelfiles.com/GetShortCutKeys.html
Alex J,
Regarding your question “… 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 Iam not sure which one, and why…”
This answer might come a bit too late but on my system (Win XP Excel 2002) if two open workbooks have macros with the same shortcut key, the macro in the workbook that gets opened first will be executed. For example, if you have Workbooks A and B and you open Workbook A first, the macro in Workbook A will be run when you press the shortcut key. I’m guessing the reason is Excel only remembers the shortcut key in the first opened workbook, same shortcut key in the workbooks opened thereafter is ignored.
I have had a problem with my macros linked to shortcut when I tried to consolidate my macros that I have recorded over time into one single module.
The macros linked to shortcuts would not execute when the shortcut was run, however they appeared to be in order in VBA. They even had that “‘ Keyboard Shortcut: Ctrl+Shift+…”
The problem is that the hidden coding that designates the shortcut key is only available on export, so the solution is to export all your macros, assuming they are module1, module2 & module3. Create the My_Std_Macros module (or what ever you wish to call it) and export it – yes it’s blank, but will create the neccessary .bas file with the first line.
This will create 4 .bas files that can then be edited in notepad. Simply copy all lines BUT NOT the first line (this is important to exclude the first line as it defines the name of the module and I hate to think what would happen if you had multiple names in the same module) from module1, 2 & 3 into the My_Std_Macros, save it and close the files. No particular order is necessary.
Remove Module1, 2, 3 & My_Std_Macro without exporting, then import only the new My_Std_Macro and all your macros will come across with shortcut keys.
You can add shortcuts in the same manner, prior to closing & saving the My_Std_Macro.bas, you can add the command lines as follows, assuming the Sub is called “Hide_Gridlines” (no prize for guess what this macro does … lol) linked to Ctrl+Shift+G
Attribute Hide_Gridlines.VB_ProcData.VB_Invoke_Func = “G
14? as follows:
Sub Hide_Gridlines()
Attribute Hide_Gridlines.VB_Description = “Hides the Gridlines
Macro recorded 09/12/2008 by David Mulroney”
Attribute Hide_Gridlines.VB_ProcData.VB_Invoke_Func = “G
14?
Note it comes after the description.
The expression
‘ Keyboard Shortcut: Ctrl+Shift+G
can be added within the sub but it only for your purposes.
If you wish to link it to … say Ctrl+j, then it would be = “j
14? (i.e. lower case means no shift, upper case means shift. The ”
14? part is constant.
I hope this helps :)
Good luck and happy programming
Can shortcuts be added to a function or only to a sub?
I’m wanting to assign it to something that isn’t visible in the VB Macro window
Matt –
Use the ‘Private’ keyword:
Private Sub MyMacro()
and assign the shortcut to this.
Very helpful!!
I — unwittingly — or accidentally — Assigned ^I, and suddenly “Italics this” became
Thanks so much!!!
Wow! I was looking for this because I was trying to share a new blog I’d written about assigning short-cut keys in Excel, and discovered something I didn’t know about Excel VBA (doesn’t happen too often these days, as I get older).
The blog, if it’s helpful, is at http://www.wiseowl.co.uk/blog/s139/short-cut-key-macros-excel.htm .
How can I reassign macro shortcut keys to Alt-key combinations instead of Ctrl-key combinations without using VB?
You can use an external macro tool such as insight software’s macro express.
This can say “this key (whatever) does (these actions)
Then you can say “but only if the window on top is Excel”.
Not free, but one of the programs I’m more than happy to pay for . . .
[…] : Raccourcis macro Bonjour, Voici un lien (en anglais) sur le sujet : Daily Dose of Excel » Blog Archive » Macro Shortcut Keys En réponse a tes questions : 1) tu ne peux pas le faire a partir de VBE, tu dois sauvegarder ton […]
I’m late to the game posting to this ancient thread… But I thought everyone knew that the Proc_Data ‘Invoke’ attribute specified *two* things:
1: The shortcut key
2: The category a macro of function is listed under in the function wizard.
If you don’t want your function to languish in obscurity under ‘User Defined’, give it a meaningful category ID.
Here’s an example I posted, years ago, listing a modified ‘Date Add’ function so that it appears under ‘Date and Time’ in the function wizard (Category ‘\n2′ using the Proc_Data Invoke ordinals:
Public Function AddDate(ByVal DateString As String, _
Optional ByVal ReferenceDate As Date _
Optional Subtract As Boolean = False _
) As Date
ATTRIBUTE AddDate.VB_Description="Add a datestring of the form '1m', '10d' or '5y' to the reference date. \r\nBy default the reference date is the current date. \r\nInteger dates only: time expressed as fractional days is discarded. \r\nAll addition and subtraction uses Actual/Actual: no other date convention is implemented.
ATTRIBUTE AddDate.VB_ProcData.VB_Invoke_Func = " \n2"
Share and Enjoy.
I didn’t know the shortcut key was stored there. I use Application.OnKey in my Auto_Open, but if I stored the shortcut there, it would persist through any catastrophic errors.
Quite.
It’s annoyed me for years, that Microsoft never opened up an API for the Attributes layer of VBA. There’s a lot there that an intermediate-level developer could make good use of.
Unfortunately, the interface we’ve got – editing saved module files and reimporting them – is rather fragile. It’s very easy to lose an attribute by cutting-and-pasting a function or property; and you had better not have any code that relies on (say) specifying the ‘default property’ attribute of a class.
I thought we had this method to add a function to the Date & Time functions:
Sub M_snb()
Application.MacroOptions "F_nieuw", "Does the same trick", , , , , 2
End Sub