This post is written specifically for people who uses excel a lot. There are a lot of tasks which are always repeated again and again when you work with excel. What if you can create a keyboard shortcut to all these repeatable tasks? I will teach you how to create your own one. (my repetive tasks might be different from yours). Second and last part of the series teaches how to edit the code. First part of the series teaches you how to record your repeatable task using macros. Please go through the first part if not you might be lost in the explanation below.
Uhide your Personal Macro Workbook
If you have followed through and done the first part, you will have know that your macro is actually stored in a location in your computer, just that it is not visible to you. Once you have saved the Personal Macro Workbook, excel will create PERSONAL.XLSB which is the baseline excel template that will be used when you create a new excel file. All your macros that you created and save in Personal Macro Workbook will be stored there. In order for us to modify the code of the macros that you recorded, you will need to unhide it. To do this, go to View Ribbon->Unhide->PERSONAL.XLSB->OK.
Doing so will open up the hidden Personal Macro Workbook.
Edit the macro
Now we will be able to edit the macros you have recorded. Go to Developer Ribbon->Macros->Select the Macro Name->Edit
Start editing macro
Now you should see a new window appearing under Microsoft Visual Basic for Applications and the code you see will be the one that you actually “written” based on the few clicks of a button earlier when recording macro.
How to edit this?
What you see on the window might look daunting in the first place especially if you have zero background in programming. But you can actually see that the code is written in plain english. Like for example the code i showed above, you can see what is doing is changing the font and the values are named similar if not the same as what can be shown in the excel. Even if they are slightly different, you should be able to determine the mapping by looking at what is displayed in excel. (See illustration below)
Note the key to amending the code is actually on the code Selection. Because most of your automation will be on what you have selected, so this portion of the code will be the most reused as automation(pressing your keyboard shortcut) will be relative to what is selected in exel and will be a good reference point for you to reuse and remember your shortcut. To know what code to put in, the best way is to record the macro and show its code.
Adding new Macros using code without recording
You do not need to do another recording in order to create the automation anymore. You can manually input the code to create the macro. You just need to place your code within a Sub Macro Name()
Create keyboard shortcut after adding new macros using code
Your shortcuts will not be convenient for use unless you set a keyboard shortcut to it. To do that, save your PERSONAL.XLSB and click on Macros. This will show all the macros you have recorded and written using code. Select the macro you want to create a keyboard shortcut and click Options…
Now you should be able to see the Macro Options window to set your keyboard shortcut (See Part 1 to know how to do it)
If you are feeling lazy to try out and create your own macro. You can use some of the below which i created to make my life easier. Let me know if you want some other automation using excel macro available, I will add to the below as well. You can just copy and paste everything below to your Microsoft Visual Basic for Applications Window and assign shortcut accordingly. I have named the macros so it is self explanatory what it does.
Hi, I am Yarn Then Cohos King. Welcome to my blog. I am someone who always look to find more effective or efficient ways of gettings things done. Now I am sharing these as well as my thoughts and ideas to benefit others via this blog.
You can contact me via:
or contacting me via the Guestbook