Use Excel VBA to create and update charts

In my day job, I use Excel every day that I am working. I have a number of spreadsheets that I have created, and others that have been imported from other departments and organisations.

These spreadsheets for the main part utilise charts to visualise the metrics contained within the data. The charts that I normally use are line charts and pie charts.

The line charts are used to plot:-

• Monthly weights and costs of disposing of waste within the organisation
• Monthly appraisal data

The pie charts are used to plot:-

• Categories of the weights of waste collected.
• Categories of appraisal data

The spreadsheets that I use for waste for the most part only need to be updated because the data is added to spreadsheets and charts that have already been created.

The spreadsheet I use for appraisals has to be created from data that is downloaded every week, and as a result I must create a brand new pie chart for that data on a weekly basis.

Get Crystal X’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This blog post, therefore, will demonstrate how to

• Update a chart
• Create a pie chart.

The screenshot below shows the user how to update a cluster chart by clicking onto the button to the right. I have tried that program with both a line chart and a clustered column chart, but the user can try it out with other chart designs if he so wishes:-

The psuedocode for the program, written in Excel VBA is:-

1. Define the variables of cells_to_chart, rng, and chObj.
2. Prompt the user for the range of cells to update.
3. Set up an error protocol. If the user does not enter a range of cells, the program will give the user an error message and stop.
4. If the chart is an embedded chart inside the worksheet, the program will update it and exit.
5. If the chart is a chart on another sheet, the program will update it and exit.
6. If there is no chart on the spreadsheet then a message will advise the user of this and the program will end.

The screenshot below shows the user how to create a pie chart from data that has been entered into a spreadsheet. The user only needs to click onto a button to accomplish this task. It is more automated to create a pie chart in one keystroke than use Excel’s menu system:-

The psuedocode for this program, written in Excel VBA, is:-

1. Define the variables ws, rng, chartObj, and cells_to_chart.
2. Set ws as being the active sheet.
3. Prompt the user for the range of cells to create a pie chart on.
4. If the range of cells to plot the chart on is empty, the program will stop.
5. Set up an error protocol. If the user does not enter a range of cells, the program will give the user an error message and stop.
6. The chart will be initialised with the size, type, title, and data labels.
7. When the pie chart has been created the program will end.

It is worth noting that once the charts have been created or amended, the user can go in and manually make changes, such as modifying the title or colours.

I have created a code review for this blog post and it can be viewed here:- https://youtu.be/1qci-fIr_Qs