Excel Charts with different data and the same format and style
Problem
When we assume to have some data which has 3 dimensions : items, inception dates, pricing dates. For each date, we need to draw a combined graph (line and area) which describes movements of each items with respect to corresponding pricing dates. When the number of inception dates is 3, three charts are drawn.
At first, we can draw a chart for the inception date 2020m01 with each series of realized PL, cumulative PL, effective, and ineffective items at date 2020m01.
Expected Output
The following recording file shows how multiple charts are generated by clicking a button. As can be seen, modifications of a format or style are easily done to all charts without much effort.
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' |
Excel VBA macro code
The remaining job is to make 2nd, 3rd, ..., charts with the same format and style of 1st chart by Excel VBA macro code. Of course, the first chart should be manually drawn as it is a starting point. The following Excel VBA macro code does that job. This macro code is linked to the button ("Generate charts") on the sheet. By clicking this button, this macro runs.
In particular, when this macro runs, all charts except the first chart with the name of template_chart_name are deleted and regenerated again.
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 | Option Explicit 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 |
Running the above Excel VBA macro code produces the remaining 2nd and 3rd charts which are for 2020/02, 2020/03 data respectively.
By adding one more inception date (2020/04) from an additional issuance, we can also get multiple charts with four inception dates ranging from 2020/01 to 2020/04. Of course, a chart for 2020/01 should be made in advance.
Concluding Remarks
In this post we generate multiple Excel charts with the same format and style by using Excel VBA macro. Although the number of charts increases, it is easy to generate corresponding charts for each dates by modifying this code. \(\blacksquare\)
No comments:
Post a Comment