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