VBA to Load Named Ranges for Dynamic Charts
In a post to the Microsoft Excel Charts Discussion Group earlier this week, the poster wanted a VBA routine that would update a series of charts on a click of a button. In response, it was suggested to create dynamic charts using named ranges as an alternative to using VBA .
I try to utilize dynamic charts as much as possible when I have to present the same data multiple times and when that data set consistently updates. Unfortunately, the initial setup of the chart can be tricky. The tricky parts involve saving the source formulas as named ranges and then entering those ranges into the chart. That being said, the use of VBA macros can help make process much easier.
For example, assume you want to make an embedded chart that automatically updates as you enter data into rows. Assuming the use of Excel 2003 and starting with the data set below:
First, go to Tools -> Macro -> Visual Basic Editor. In the Visual Basic Editor select Insert -> Module. Add the following macro into a module.
Sub UpdateRows()
Dim Cht As Chart
Dim StartRange As String
Dim TotalRange As String
Dim XVal As String
Dim YVal As String
Set Cht = ActiveChart
StartRange = Worksheets("Sheet1").Range("B4").Address
TotalRange = Worksheets("Sheet1").Range("B4:B20").Address
XVal = "=OFFSET(" & StartRange & ",0,-1,COUNT(" & TotalRange & "),1)"
YVal = "=OFFSET(" & StartRange & ",0,0,COUNT(" & TotalRange & "),1)"
ActiveWorkbook.Names.Add Name:="XVal", RefersTo:=XVal
ActiveWorkbook.Names.Add Name:="YVal", RefersTo:=YVal
Cht.SeriesCollection(1).XValues = "=" & ThisWorkbook.Name & "!" & "XVal"
Cht.SeriesCollection(1).Values = "=" & ThisWorkbook.Name & "!" & "YVal"
End Sub
Next, create an embedded column chart using the range B3:C9 of Sheet1 of your workbook as the source.
To run the macro, select the embedded chart (click once on the outside edge). Go to Tools -> Macro -> Macros -> UpdateRows -> Run.
The macro first loads the formulas "XVal" and "YVal" as defined names:
Next, it loads those defined names as sources for the chart:
And that's all there is to creating a simple dynamic chart.
If you need to change the source range for your chart, make the changes to the code below:
StartRange = Worksheets("Sheet1").Range("B4").Address
TotalRange = Worksheets("Sheet1").Range("B4:B20").Address
The example above presents data in rows. To use data in columns, change the "XVal" and "YVal" code above to these:
YVal = "=OFFSET(" & StartRange & ",0,0,1,COUNT(" & TotalRange & "))"
XVal = "=OFFSET(" & StartRange & ",-1,0,1,COUNT(" & TotalRange & "))"
