 |
|
|
|
|
In this, our last lesson in our Excel Level 3 course we will be delving further into the macro feature of Excel. You now now how to record and run both absolute and relative macros, and hopefully you now understand a little about the actual macro (VBA) language. In this lesson we are going to create a mini-application using macros and we will assign these macros to an object.
When you are creating an application or spreadsheet for yourself or someone else, the most efficient way to do this is to break the project down into small bite-sized chunks. You work on one chunk at a time, making sure that it is working properly before moving on to the next chunk. Once you have worked through all your chunks, you then pull them together. This will make your application much easier to comprehend in the long run, especially if you intend on making modifications or additions in the future.
The macro recorder can be used to create a mini-application very easily using macros, as an application is simply several related tasks that achieve a common and ultimate objective. For example you may wish to consolidate data from several sources to create a forecast or budget. This is the scenario that we are going to use in this lesson for setting up our mini-application through the macro recorder. We are going to create a consolidated budget forecast using different macros to sum data, average data and extract minimum and maximum values.
Open up the attached four workbooks and save them to your hard drive. Have a look at the data. We are going to consolidate the three divisions of Hawley's brewery to firstly find the total income for the year. You will notice that all four workbooks have the same layout, enabling us to easily use the consolidate command to create our summation macro.
Recording a Macro to Perform a Consolidation
Firstly, let's open WBDLesson10Level3-Macros1 only and click in cell A1 to make sure it is our active workbook. Now follow these steps:
Select Tools>Macro>Record New Macro
Call your macro SumDept
Assign a shortcut key to it
Store the macro in This workbook
Click OK
You will notice your Stop Recording toolbar appear. We do not want a relatively recorded macro at this stage, so we need to make sure that the Relative Reference tool on our toolbar is not pressed down. If it is, click on it to deactivate it.
Click in cell B4 which is where we are going to start our consolidation
Select Data>Consolidation
Once the Consolidate dialog box pops up in front of you, you will notice that under the Function: heading, the default is Sum. This is what we want, so no need to make any changes here.
Click the Browse button
You need to now locate the first of the three workbooks that we want to consolidate
Locate the workbook WBDLesson10Level3-Macros2 and double click on it.
You will notice that the file name and path will pop up in the Reference box
Now click after the exclamation mark of the file name WBDLesson10Level3-Macros2
Click the Collapse Dialog button to the right and highlight the range B4:E6
Now click on the Expand Dialog button to the right to expand your dialog box
Click Add
OK, we have added in one of our workbooks to consolidate, let's now add the other two:
Click the Browse button
You need to now locate the second workbook that we want to consolidate
Locate the workbook WBDLesson10Level3-Macros3 and double click on it.
You will notice that the file name and path will pop up in the Reference box
Now click after the exclamation mark of the file name WBDLesson10Level3-Macros3
Click the Collapse Dialog button to the right and highlight the range B4:E6
Now click on the Expand Dialog button to the right to expand your dialog box
Click Add
Click the Browse button for a final time
You need to now locate the third workbook that we want to consolidate
Locate the workbook WBDLesson10Level3-Macros4 and double click on it.
You will notice that the file name and path will pop up in the Reference box
Now click after the exclamation mark of the file name WBDLesson10Level3-Macros4
Click the Collapse Dialog button to the right and highlight the range B4:E6
Now click on the Expand Dialog button to the right to expand your dialog box
Click Add
All we need to do now to perform our summation consolidation is click the OK button and then click on the Stop Recording tool on our Stop Recording toolbar.
The next thing that we MUST do is to test our macro before we go any further, so lets do that now.
Make sure that WBDLesson10Level3-Macros1 is your active workbook.
Delete all data in the range B4:E6
Now run your macro from either the shortcut key that you assigned it to, or via Tools>Macro>Macros.
Save your workbook
OK, assuming that our first "chunk" is working correctly, we now want to go on and work out the average income across the divisions, so follow these steps:
Select Tools>Macro>Record New Macro
Call your macro AveDept
Assign a shortcut key to it
Store the macro in This workbook
Click OK
Again, we need to make sure this is absolute recorded macro, so we need to make sure that the Relative Reference tool on our toolbar is not pressed down. If it is, click on it to deactivate it.
Click in cell B4 which is where we are going to start our consolidation
Select Data>Consolidation
Once the Consolidate dialog box pops up in front of you, you will notice that under the Function: heading, the default is Sum. Lets change it to Average.
Click the Browse button
You need to now locate the first of the three workbooks that we want to consolidate
Locate the workbook WBDLesson10Level3-Macros2 and double click on it.
You will notice that the file name and path will pop up in the Reference box
Now click after the exclamation mark of the file name WBDLesson10Level3-Macros2
Click the Collapse Dialog button to the right and highlight the range B4:E6
Now click on the Expand Dialog button to the right to expand your dialog box
Click Add
You need to now locate the second workbook that we want to consolidate, so click on your Browse button again.
Locate the workbook WBDLesson10Level3-Macros3 and double click on it.
You will notice that the file name and path will pop up in the Reference box
Now click after the exclamation mark of the file name WBDLesson10Level3-Macros3
Click the Collapse Dialog button to the right and highlight the range B4:E6
Now click on the Expand Dialog button to the right to expand your dialog box
Click Add
Click the Browse button for a final time to locate the final workbook
Locate the workbook WBDLesson10Level3-Macros4 and double click on it.
Again, the file name and path will pop up in the Reference box
Now click after the exclamation mark of the file name WBDLesson10Level3-Macros4
Click the Collapse Dialog button to the right and highlight the range B4:E6
Now click on the Expand Dialog button to the right to expand your dialog box
Click Add
Click OK
Click the Stop Recording button, found on the Stop Recording toolbar.
Again, before you go any further, delete the data in A4:E6 of WBDLesson10Level3-Macros1 and test your macro. Once tested, save WBDLesson10Level3-Macros1.
If this works, then create the following macros following the steps above, assigning a shortcut key to each.
Macro Name: MaxDept
Consolidation Function: Maximum
Macro Name: MinDept
Consolidation Function: Minimum
Creating an Object to Run a Macro
OK, so now we know how to run a macro from either Tools>Macro>Macros or via a shortcut key, but there is a much better way. Macros can make life very easy for you, but only if you can remember the macro names, or the shortcut keys that you used to activate them. There is another way that you can run macros, and that is by assigning them to an object. An object is anything that you draw and place onto a worksheet, such as a square, a circle, a triangle, hexagon, line, text box etc. We are going to assign three of our macros to objects that can then be clicked to run them. The first thing that we need to do is to create our objects, so lets follow these steps and do that now:
Move your mouse up to the toolbar area of the screen (the top).
Click with your right mouse button
Select the Drawing toolbar with your left mouse button
Your drawing toolbar will appear in front of you. It usually docks itself at the bottom of your screen, but this is dependent on your computer's settings.
Click on the Text Box tool
Now click somewhere on your screen, either below or to the right of your data. This will create a text box.
You will see your cursor flashing inside the text box, waiting for your to type.
Type in the name Sum Dept
Now click on the border of your text box until it changes from a line with slashes to a fuzzy dotted line. Once you see the fuzzy line this means that any changes that you make to the text box will be made to the entire contents. You can make formatting changes such as changing the fill colour and the font colour via the toolbar when your text box is in this mode.
If you hold your left mouse button down when you are clicked on this fuzzy dotted line, you will be able to move your text box. You can also resize your text box by grabbing hold of the handles on the outside of the text box with your left mouse button.
Now that we have created and formatted our text box, we can assign our macro to it and use it to run our SumDept macro.
Make sure you can still see a fuzzy dotted line around your text box.
Right click on this fuzzy line
Select Assign Macro from the menu.
This will bring up your Assign Macro dialog box and in the white pane you should be able to see all the macros that you have created.
Click on SumDept if it does not already appear under the Macro Name: box.
Select OK
Now to test to make sure that our newly created macro button works:
Click somewhere else on your screen to deselect your text box.
Click on the text box Sum Dept to run your macro.
Now follow theses same steps to create text boxes and assign macros for the other MinDept and MaxDept macros that we have recorded.
Make sure your test your macros.
Assigning a Macro to a Toolbar
Just as easily as we can assign a macro to an object, it is very simple to assign a macro to a toolbar, follow these steps and we will assign our AvgDept macro to our standard toolbar.
Move your mouse up to the toolbar area of your screen
Click with your right mouse button
Select Customise with your left mouse button. This will bring up your Customise dialog box.
Under Categories: select Macros
Under Commands: on the right hand side of your dialog box, click on either Custom Menu Item or Custom Button and hold your left mouse button down.
Drag your selection up to your Standard toolbar to the location that you require, then let go of your mouse.
Now with your dialog box still on your screen, wave your mouse over either your Custom Menu Item or your Custom Button on your standard toolbar and click with your right mouse button.
Under the shortcut menu presented to you, you will see the very last option is Assign Macro
Click on this option with your left mouse button.
It will present you with your Assign Macro dialog box.
Click on the macro AvgDept to place it into your Macro Name: box
Select OK
Right click again over your Custom Menu Item or Custom Button and you will see the option Name: if you have select Custom Menu Item you can give your button a relevant name here.
You will also see the option Change Button Image if you have selected a Custom Button you can change it's look here.
Click Close to close down your dialog box.
Now as always test your macro.
Summary
As you can see, macros are an extremely powerful feature of Excel. You use macros from anything such as automating a series of key strokes, to creating mini-applications, like the one that we have done in this lesson. You need to remember to thoroughly test your macros before use, and remember that when you are recording them that ALL key strokes are recorded, even if you make errors. The next step for you now is to move into the area of Visual Basics for Applications, which is the language of macros. Once you understand the basics of VBA, you will be able to go in and modify your macros to make them faster, more efficient and do many more things than what we have learnt here.