2. Add Multiple Tasks to a Macro
A Macro's real power is combining multiple tasks into a single command. In this lesson, we'll learn how to append additional tasks to the original Macro.
What happens when we record a Macro
- When you record a Macro, Excel interprets your actions as a block of code and saves it
- To see the code, you need to open the Visual Basic Editor
- When saving a Macro, you use the currently open workbook or the Personal.xlsb file
- Use the Personal.xlsb file if you want to use the Macro in multiple workbooks.
ALT + F8: Open Macros dialogue box
- You can also useALT + L, P, M
ALT + F11: Open / Close VBA editor
- You can also useALT + Q
ALT + L , R: Stop / Start recording Macro
ALT + H , H: Change background color
ALT + H , F, C: Change font color
ALT + H , B, S: Add outside borders to a cell
CTRL + SHIFT + %: Convert cell format to percentage
In the previous lesson we recorded a very simple macro that changed the background color of our cell to blue, colored the text blue and bolded the text. In this lesson we're going to extend the functionality of our macro to convert the cell format to percentage and put a border around the cell. Before we do that, however, let's understand exactly what happened when we recorded our first macro. In the Developer tab we can click on the Macros button or press the shortcut alt F8.
And this lists the macros that are available in the current Excel sheet.
As expected we have one macro called percentinput which we created in the previous lesson that's stored in the current Excel file.
As a result it's only available in this file. If you'd like to make the macro available across all files, then you should store the macro in a file called PERSONAL.XLSB which is a hidden program that Microsoft runs when Excel is opened.
On the right-hand side let's go to the Edit button.
And when I hit Edit, I few lines of VBA code appear on the screen in a window that's called the VBA Editor. What has happened is the following, as we record our macro on the screen, Excel is working underneath to convert our recording into VBA code. This includes the name of the macro at the top, a number of green lines which are comments that include our macro name, description and shortcut and underneath we have the code generated by our recording. Here it tells us that a cell has been selected, the following changes have been made to the background color, the following changes to the text color and the text has lastly been bolded.
For simple macros such as this example, there's normally no need to look at the code generated. However, for more complex macros, the recording doesn't always work immediately and some editing to the code may be required. Our current macro has three simple tasks included in it. To utilize the real power of macros, we can combine even more tasks into one command. Let's say I also want to add a border to the selected cell and convert the cell to the percentage format. So, let's close the VBA Editor with alt F11 and return to our Excel sheet.
I'll navigate to a new cell, record a new macro, call this macro finalinput, I give it a new shortcut, say shift Y, I'll store it in this workbook and in the description I'll add final percent input format and press OK.
My first task as before will be to go to the Home tab and change the background color, so I'll press H and select the required color.
Then I'll change the font color to the same blue we had before and I'll bold it with control B.
I'll now add a border with alt H B and then S for outside border and lastly I'll change it to the percentage format with control shift five.
I'll then return to the Developer tab and stop the macro.
Let's now test this macro to see if it's working correctly.
And as you can see, it adds a border, changes the text and the background to the correct colors and leaves my input in the correct format. Before wrapping up this lesson, let's go back and take a quick look at the code just generated by this new macro, so I'll press alt F11 to open up the VBA Editor. As you can see, a new macro has been created called finalinput with the description and the keyboard shortcut included. As before, we have a section of code for changing the background color and changing the font and bolding the font.
We now also have a new section related to the borders and as you can see Excel has a width end block of code for each of the cell's four borders. In the last line, the number format has also been changed to percentage. Even though we haven't covered any VBA coding yet, it's pretty easy to understand how some of these commands work. Let's say for example we'd like to add on a decimal point to our percentage format. I'll simply change this value to 0.0 and now returning to my Excel sheet and using the shortcut control shift Y this should give me 20.0% and it does. And what's more, I can apply this macro to the previous cells as well, control shift Y.
So, now without even knowing how to program, we've already begun to edit VBA code in Excel. Congratulations.