Updating Charts Using VBA Code

Several customers have asked for the ability to update the SPC for Excel charts using VBA alone.   They did not want to go through the input box that asks for which charts to update.  This is possible to do with the following types of charts created by SPC for Excel:

• Pareto diagrams
• Histograms
• Attribute Control Charts
• Variable Control Charts
• Process Capability Charts
• Scatter Diagrams
• Waterfall Charts

To use this option, create the chart initially in an Excel workbook.  Once the chart is created, you can use the code below to call the update function.  The program will find the new data and update the chart based on the options you have selected.  You can still change those options by selecting the “Options” icon from the SPC for Excel ribbon.

VBA Code for Version 5 and Version 6

Each chart has unique name in a workbook.   It is entered in the first user form when then chart was first created.   That name is usually the name on the worksheet tab, but you can move charts around in a workbook.  The name of the chart does not change though even if you move it from place to place. So, if you move it, the name may not correspond to the worksheet tab.  

To update an existing chart, you simply run the following routine (change 6 to 5 if you have version 5):

Sub UpdateMyCharts(MyChtName)

‘MyChtName is the name of the chart

Application.Run “spcforexcelv6.xlam!UpdateChartsBPI”, MyChtName 

End Sub

To update all charts in a workbook, use the follow code:  Application.Run “spcforexcelv6.xlam!UpdateAllChartsBPI”

VBA Code for Version 4

In the code below, replace “chartname” with the name of the worksheet tab containing the chart.

Pareto Diagrams
Application.Run “spcforexcelv4.xla!UpdateParetoBPI”, “chartname”

Histograms
Application.Run “spcforexcelv4.xla!UpdateHistogramBPI”, “chartname”

Attribute Control Charts (p, np, c and u)
Application.Run “spcforexcelv4.xla!UpdateAttributeChartBPI”, “chartname”

Variable Control Charts (Xbar-R, Xbar-s, X-mR, Xbar-R-R, Xbar-R-s, Run)
Application.Run “spcforexcelv4.xla!UpdateVariableChartBPI”, “chartname”

Process Capability
Application.Run “spcforexcelv4.xla!UpdateProcessCapabilityBPI”, “chartname”

Scatter Diagrams
Application.Run “spcforexcelv4.xla!UpdateScatterBPI”, “chartname”

Waterfall Charts
Application.Run “spcforexcelv4.xla!UpdateWaterfallBPI”, “chartname”