Description

This assignment uses VbaHwk1.xlsm workbook. Open it – you should see a “Sheet1” worksheet that contains sales figures from January to March for four cities. Using this worksheet, you need to record a macro that creates a Clustered Bar chart. After you finish recording the macro, you will have to clean and compact it.

1. First, practice how to create a Clustered Bar chart. Be sure that the “Sheet1” worksheet is the active worksheet. 

a. Select range A3:E6. 

b. On the Insert tab, click the Bar icon in the Charts group. 

c. Click the Clustered Bar chart type in the 2-D Bar section to insert a chart. Note: as you move the mouse over the palette, a Screen Tip appears that shows the name of the chart type. 

d. Click once on the empty space somewhere near the top-right corner of the chart area to make the chart object active. Click the Layout tab, click Chart Title in the Labels group, and then click Above Chart to create a title for the chart. e. Type Sales Figures for the title and then click once anywhere on the empty space somewhere near the top-right corner of the chart area. f. Now, we want to move the chart to a new location. Click the Design tab and then click the Move Chart in the Location group. At this point, the Move Chart window is open. Select the New sheet option and then type Sales Chart as the name of the chart sheet. Click the OK button to dismiss the Move Chart window. At this point, you should see a new Sales Chart sheet that contains the Clustered Bar chart object. 

2. After successfully creating the Clustered Bar chart, you can delete that Sales Chart sheet and then recreate it. Repeat the process of creating and deleting a Clustered Bar chart several times until you are satisfied and comfortable with the process. 

3. Be sure you delete the Sales Chart sheet from the workbook. Now, you record a macro that will capture the steps – 1(a)-(f) above – that you do to create the Clustered Bar chart. You can name the macro Macro1 or VbaHwk1 or any other suitable name. Be sure to stop recording after finishing the above steps. 

4. Test your macro to make sure it does what it is supposed to do. Before running the macro, be sure that you delete the existing Sales Chart sheet; otherwise, your macro will generate an error message because it would not be able to perform the task described in step 1(f) above. If your macro works as expected, save it.