Dynamic Multiple Excel Charts
To combine a dynamic chart and multiple charts, we use the following two previous posts.
Let's assume that we have data (2020/01, 2020/02, 2020/03) and a chart (2020/01) as a starting point as follows.
Expected Output
The following animation video shows how useful a dynamic chart is when data is added and how convenient generating multiple charts on the fly is.
This expected output combines two functionalities : dynamic and multiple charts. Now let's learn how to do this work.
Setting some Excel ranges for convenience
As we have one chart for the first inception date, we can make remaining charts for the second and third inception dates (2020/02, 2020/03) not manually but by Excel VBA macro. To use VBA macro, we name three ranges to allow for flexible cell shifts as follows.
| range | name |
|---|---|
| F2 | template_chart_name |
| F3 | number_of_chart |
| B5 | origin |
template_chart_name is the first chart name and number_of_chart is the number of chart which will be drawn. origin is the location from which relative location is calculated. In particular the first chart name can be found by clicking the first chart and see the box above A column.
Keeping Excel Chart format
It is easy to make an Excel chart but is time-consuming to set favorite format and style. In this case, the problem is that when we change data even without modifying format and style of a chart, that format and style is changed against our will.
To fix the style and format for a chart, we modify some setting.
|
File → Options → Advanced → Chart → deselect 'Properties follow chart data point for current workbook' |
Worksheet_Change macro function
When data is changed, our chart needs to reflect this change. For this purpose Worksheet_Change Excel macro function is used. This is called when some change is detected in the sheet.
|
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub |
It is to note that Worksheet_Change Excel macro function is defined not in a module but in its own sheet script as in the previous post.
Button1_Click macro function
Let's insert a button with a text as "Generate charts" on the sheet and link this button to Button1_Click macro function which will be implemented in the next. This function performs make 2nd, 3rd, ..., charts with the same format and style of 1st chart.
Of course, the first chart should be manually drawn as it is a starting point. In particular, the 1st chart is a dynamic chart which responds promptly when data is changed by Worksheet_Change macro function.
Excel VBA macro code
The following VBA macro code consists of Worksheet_Change() function and Button1_Click() function. Using these two functions, we can draw multiple dynamic charts. This code is inserted into not a module file but its own sheet script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 | Option Explicit ' update only template chart Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim RngX As Range, RngY As Range Dim ChtObj As ChartObject, Achart As ChartObject Dim ser As Series, Origin As Range Dim tcn As String, noc As Long Dim i As Long, n As Long, ncnt As Long Set ws = Application.ActiveWorkbook.ActiveSheet ' template chart name and ' # of charts will be added + 1 tcn = Range("template_chart_name") noc = Range("number_of_chart") ' template chart and origin Set ChtObj = ws.ChartObjects(tcn) Set Origin = ws.Range("origin") ' scan and delete any ChartSheets For Each Achart In ws.ChartObjects If Achart.Name <> tcn Then Achart.Delete End If Next With ChtObj For i = 1 To .Chart.SeriesCollection.Count Set ser = .Chart.SeriesCollection(i) ncnt = Origin.Offset(1, 0).End(xlDown).Row - Origin.Row Set RngX = ws.Range(Origin.Offset(1, 0), Origin.Offset(ncnt, 0)) ser.XValues = "=" & RngX.Address(False, False, xlA1, xlExternal) Set RngY = ws.Range(Origin.Offset(1, noc * (i - 1) + 1), _ Origin.Offset(ncnt, noc * (i - 1) + 1)) ser.Values = "=" & RngY.Address(False, False, xlA1, xlExternal) Set RngX = Nothing Set RngY = Nothing Set ser = Nothing Next i End With Origin.Select Set ws = Nothing Set ChtObj = Nothing Set Origin = Nothing End Sub Sub Button1_Click() Dim ws As Worksheet Dim RngY As Range, Origin As Range Dim ChtObj As ChartObject, Achart As ChartObject Dim ser As Series Dim tcn As String, noc As Long Dim i As Long, n As Long Set ws = Application.ActiveWorkbook.ActiveSheet ' template chart name and ' # of charts will be added + 1 tcn = Range("template_chart_name") noc = Range("number_of_chart") ' template chart and origin Set ChtObj = ws.ChartObjects(tcn) Set Origin = ws.Range("origin") ' location information of tcn Dim tlr As Long, tlc As Long, brc As Long tlr = ws.ChartObjects(tcn).TopLeftCell.Row tlc = ws.ChartObjects(tcn).TopLeftCell.Column brc = ws.ChartObjects(tcn).BottomRightCell.Column ' scan and delete any ChartSheets For Each Achart In ws.ChartObjects If Achart.Name <> tcn Then Achart.Delete End If Next 'generate 2nd, 3rd, .... charts For n = 2 To noc ' copy and paste template chart ChtObj.Activate ActiveChart.ChartArea.Copy 'paste (n+1)th chart near the nth chart Cells(tlr, brc + 1 + (n - 2) * (brc - tlc + 1)).Select ws.Paste 'Change Data With ActiveChart .Parent.Name = tcn & "_" & n .ChartTitle.Text = Origin.Offset(0, n).Text ' replace series data For i = 1 To .SeriesCollection.Count Set ser = .SeriesCollection(i) Set RngY = ActiveSheet.Range( _ Origin.Offset(1, noc * (i - 1) + n), _ Origin.Offset(ser.Points.Count, noc * (i - 1) + n)) ser.Values = "=" & RngY.Address(False, False, xlA1, xlExternal) Set RngY = Nothing Set ser = Nothing Next i End With Next n Origin.Select Set ws = Nothing Set ChtObj = Nothing Set Origin = Nothing End Sub | cs |
As soon as this VBA macro code is coded, we can find that a dynamic chart is working on the fly and it is possible to multiple charts by clicking a button. Output is the same as the animation video file which is shown at first.
Concluding Remarks
In this post we combined a dynamic chart and multiple charts by using Excel VBA macro. This kind of work will improve work efficiency and we can concentrate on more important works. \(\blacksquare\)
No comments:
Post a Comment