# EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

## Excel Training Level 3 Lesson 15-Excel 97-2003

### << PREVIOUS LESSON  | BACK TO EXCEL Level 3 TRAINING INDEX

Download the associated  Workbook for this lesson

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:

1. Select Tools>Macro>Record New Macro

2. Call your macro SumDept

3. Assign a shortcut key to it

4. Store the macro in This workbook

5. Click OK

6. 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.

7. Click in cell B4 which is where we are going to start our consolidation

8. Select Data>Consolidation

9. 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.

10. Click the Browse button

11. You need to now locate the first of the three workbooks that we want to consolidate

12. Locate the workbook WBDLesson10Level3-Macros2 and double click on it.

13. You will notice that the file name and path will pop up in the Reference box

14. Now click after the exclamation mark of the file name WBDLesson10Level3-Macros2

15. Click the Collapse Dialog button to the right and highlight the range B4:E6

16. Now click on the Expand Dialog button to the right to expand your dialog box

OK, we have added in one of our workbooks to consolidate, let's now add the other two:

1. Click the Browse button

2. You need to now locate the second workbook that we want to consolidate

3. Locate the workbook WBDLesson10Level3-Macros3 and double click on it.

4. You will notice that the file name and path will pop up in the Reference box

5. Now click after the exclamation mark of the file name WBDLesson10Level3-Macros3

6. Click the Collapse Dialog button to the right and highlight the range B4:E6

7. Now click on the Expand Dialog button to the right to expand your dialog box

9. Click the Browse button for a final time

10. You need to now locate the third workbook that we want to consolidate

11. Locate the workbook WBDLesson10Level3-Macros4 and double click on it.

12. You will notice that the file name and path will pop up in the Reference box

13. Now click after the exclamation mark of the file name WBDLesson10Level3-Macros4

14. Click the Collapse Dialog button to the right and highlight the range B4:E6

15. Now click on the Expand Dialog button to the right to expand your dialog box

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.

1. Make sure that WBDLesson10Level3-Macros1 is your active workbook.

2. Delete all data in the range B4:E6

3. Now run your macro from either the shortcut key that you assigned it to, or via Tools>Macro>Macros.

4. 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:

1. Select Tools>Macro>Record New Macro

2. Call your macro AveDept

3. Assign a shortcut key to it

4. Store the macro in This workbook

5. Click OK

6. 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.

7. Click in cell B4 which is where we are going to start our consolidation

8. Select Data>Consolidation

9. 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.

10. Click the Browse button

11. You need to now locate the first of the three workbooks that we want to consolidate

12. Locate the workbook WBDLesson10Level3-Macros2 and double click on it.

13. You will notice that the file name and path will pop up in the Reference box

14. Now click after the exclamation mark of the file name WBDLesson10Level3-Macros2

15. Click the Collapse Dialog button to the right and highlight the range B4:E6

16. Now click on the Expand Dialog button to the right to expand your dialog box

18. You need to now locate the second workbook that we want to consolidate, so click on your Browse button again.

19. Locate the workbook WBDLesson10Level3-Macros3 and double click on it.

20. You will notice that the file name and path will pop up in the Reference box

21. Now click after the exclamation mark of the file name WBDLesson10Level3-Macros3

22. Click the Collapse Dialog button to the right and highlight the range B4:E6

23. Now click on the Expand Dialog button to the right to expand your dialog box

25. Click the Browse button for a final time to locate the final workbook

26. Locate the workbook WBDLesson10Level3-Macros4 and double click on it.

27. Again, the file name and path will pop up in the Reference box

28. Now click after the exclamation mark of the file name WBDLesson10Level3-Macros4

29. Click the Collapse Dialog button to the right and highlight the range B4:E6

30. Now click on the Expand Dialog button to the right to expand your dialog box

32. Click OK

33. 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:

1. Move your mouse up to the toolbar area of the screen (the top).

2. Click with your right mouse button

3. Select the Drawing toolbar with your left mouse button

4. 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.

5. Click on the Text Box tool

6. Now click somewhere on your screen, either below or to the right of your data.  This will create a text box.

7. You will see your cursor flashing inside the text box, waiting for your to type.

8. Type in the name Sum Dept

9. 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.

10. 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.

1. Make sure you can still see a fuzzy dotted line around your text box.

2. Right click on this fuzzy line

3. Select Assign Macro from the menu.

4. 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.

5. Click on SumDept if it does not already appear under the Macro Name: box.

6. Select OK

Now to test to make sure that our newly created macro button works:

1. Click somewhere else on your screen to deselect your text box.

2. Click on the text box Sum Dept to run your macro.

3. Now follow theses same steps to create text boxes and assign macros for the other MinDept and MaxDept macros that we have recorded.

4. 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.

1. Move your mouse up to the toolbar area of your screen

2. Click with your right mouse button

3. Select Customise with your left mouse button.  This will bring up your Customise dialog box.

4. Under Categories: select Macros

5. 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.

6. Drag your selection up to your Standard toolbar to the location that you require, then let go of your mouse.

7. 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.

8. Under the shortcut menu presented to you, you will see the very last option is Assign Macro

9. Click on this option with your left mouse button.

10. It will present you with your Assign Macro dialog box.

11. Click on the macro AvgDept to place it into your Macro Name: box

12. Select OK

13. 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.

14. You will also see the option Change Button Image if you have selected a Custom Button you can change it's look here.

15. 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.