LESSON WORKBOOKS: Level 3 Lesson 10A 2007.xlsx
Level 3 Lesson 10B 2007.xlsx
Level 3 Lesson 10C 2007.xlsx
Level 3 Lesson 10D 2007.xlsx
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, because all an application is 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, unzip the attached file and open all four files. Ensure your active workbook is Level 3 Lesson 10A 2007 and click in cell A1. Now follow these steps:
Highlight the range B4:E6.
Select the Developer tab and under Code options select Record Macro
Call your macro SumDept
Assign a shortcut key to it
Store the macro in This workbook
Click OK
We do not want a relatively recorded macro at this stage, so we need to make sure that the Relative Reference button on the ribbon is not pressed down. If it is, click on it to deactivate it.
Select the Data tab and under Data options select 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.
You need to now locate the first of the three workbooks that we want to consolidate
Locate the workbook Level 3 Lesson 10B 2007 and select it, then highlight the range B4:E6
You will notice that the file name and path will pop up in the Reference box
Click Add
OK, we have added in one of our workbooks to consolidate, let's now add the other two:
Locate the workbook Level 3 Lesson 10C 2007 and and highlight the range B4:E6.
Click Add
Locate the third workbook that we want to consolidate
Locate the workbook Level 3 Lesson 10D 2007 and and highlight the range B4:E6.
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 button on the ribbon.
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 Excel Lesson 10 Macros 1 is your active workbook.
Delete all data in the range B4:E6
Now run your macro from the shortcut key that you assigned it to.
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:
Ensure you are in Level 3 Lesson 10A 2007. Select the Data tab and under Data options select Consolidation. Remove
Highlight the range B4:E6.
Select the Developer tab and under Code options select Record Macro
Call your macro AvgDept
Assign a shortcut key to it
Store the macro in This workbook
Click OK
We do not want a relatively recorded macro at this stage, so we need to make sure that the Relative Reference button on the ribbon is not pressed down. If it is, click on it to deactivate it.
Select the Data tab and under Data options select Consolidation.
Once the Consolidate dialog box pops up in front of you, you will notice that under the Function: heading, the default is Sum. Change it to Average.
You should see the three ranges in the three different workbooks still in the All References dialog, so you do not need to reselect them.
Click OK.
Again, before you go any further, delete the data in B4:E6 of Level 3 Lesson 10A 2007 and test your macro. Once tested, save your workbook.
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 the Macros dialog, found under Code options on the Developer tab 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).
Go to the Insert tab and under Illustrations options select Shapes
Click on one of the rectangles and create the shape of a button.
Right click on the shape and select Edit Text
You will see your cursor flashing inside the text box, waiting for your to type.
Type in the name Sum
Now right click on the border of your text box
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 to run your macro.
Now follow theses same steps to create text boxes and assign macros for the Minimum and Maximum macros we have recorded.
Make sure your test your macros.
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.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.