What is Macro?
Macro is a set of actions that are intended to be repeating all over again. Macro is not a word just for Excel, you can find many other software help users record a set of actions for Windows or even for specific games. The first Macro I used was EZ Macro about 15 years ago, which helped me repeat a set of actions in an online game endlessly in order to get my skill points up. Today I still use keyboard and mouse that can record Macro.
Excel is very kind to have a built-in Record Macro function, which allows you to record actions and turn them into Excel VBA code. You can quickly learn VBA by studying how the code is written for each action, but not everything can be recorded. For example, if you want to type 1 in A1, 2 in A2, 3 in A3, etc until A1000, you still need the programming knowledge to do the loop automatically, instead of recording the action manually.
Assign hotkey to Macro in workbook
If you create a Macro from “Record a Macro“, you can assign a hot key to a Macro, but the combination must include CTRL.
You can ignore the restriction of CTRL combination in VBA using OnKey Method, and you can add more than 2 keys as hotkey.
Assign hotkey to Macro using OnKey Method
Syntax of OnKey Method
Application.OnKey(Key, Procedure)
Key – A string indicating the key to be pressed
| Key | Code |
| BACKSPACE | {BACKSPACE} or {BS} |
| BREAK | {BREAK} |
| CAPS LOCK | {CAPSLOCK} |
| CLEAR | {CLEAR} |
| DELETE or DEL | {DELETE} or {DEL} |
| DOWN ARROW | {DOWN} |
| END | {END} |
| ENTER (numeric keypad) | {ENTER} |
| ENTER | ~ (tilde) |
| ESC | {ESCAPE} or {ESC} |
| HELP | {HELP} |
| HOME | {HOME} |
| INS | {INSERT} |
| LEFT ARROW | {LEFT} |
| NUM LOCK | {NUMLOCK} |
| PAGE DOWN | {PGDN} |
| PAGE UP | {PGUP} |
| RETURN | {RETURN} |
| RIGHT ARROW | {RIGHT} |
| SCROLL LOCK | {SCROLLLOCK} |
| TAB | {TAB} |
| UP ARROW | {UP} |
| F1 through F15 | {F1} through {F15} |
The above table and the below table are used to make a combination of “key” parameter. Hotkey must not be empty in order for the hotkey to work.
| To combine keys with | Precede the key code by |
| SHIFT | + (plus sign) |
| CTRL | ^ (caret) |
| ALT | % (percent sign) |
Procedure – Name of the Macro (Sub Procedure)
Example 1 – Excel VBA Assign hotkey to Macro
Assume that you have already written or recorded a Macro called Macro1. To assign hotkey Crtl+Shift+k to Macro1, we find the the code that represents “Ctrl” and “Shift” from the above tables, and below is the code we will use.
Application.Onkey "^+k", "Macro1"
To apply this code work in a worksheet, ALT+F11 > Double click on ThisWorkbook > select Workbook > Select Open
(To know more about Workbook_Open Event, click here)
Insert the below code
Private Sub Workbook_Open() Application.OnKey "^+k", "Macro1" End Sub
Because this event is triggered when workbook is open, you must close the workbook and then reopen it for the hotkey to work. Although this code is executed once at the moment workbook is opened, the short key works during workbook is open.
Example 2 – Override Ctr+V to Paste as Unicode
This thread was originally posted in Microsoft Community and as answered by me.
Question
I copy/paste information from web pages to Excel on a regular basis. Currently, I am forced to copy text from web page, right click the cell in Excel, select “Paste Special…” from the context menu, the select “Unicode text” from the listed options. Very cumbersome, and it slows me down. Likewise the option of using Ctrl-V, then hitting the icon to bring up the Paste Options, and clicking on the icon representing “Match Destination Formating”.
To put this another way… I almost NEVER paste formatting from the web to an Excel spreadsheet… it happens so rarely, I’d be willing to go through the extra steps in that rare circumstance.
How to set this so I can Ctrl-C selected text from a web page, and Ctrl-V into a cell in Excel, to DEFAULT to Unicode text without source formatting?
I’ve tried following the directions for various earlier versions of Excel, but it’s still cumbersome. I’m looking for ease-of-use, straight-forward functionality for my purposes, not fingers-tied-in-knots.
Answer
Actually you can override Ctrl+V behavior in VBA
Alt+F11 > Insert Module > Copy and paste the below code
Sub pasteVal() ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _ DisplayAsIcon:=False End Sub
This time copy the below code in “ThisWorkBook” instead of new Module
Private Sub Workbook_Open() Application.OnKey "^v", "pasteVal" End Sub
Now you can use Ctrl+V to paste as Unicode
Outbound References
https://msdn.microsoft.com/en-us/library/office/ff197461%28v=office.15%29.aspx