Giacomo Brogi (1822–1881) — “Rome — Church of Chiesa della Trinità de’ Monti”. Catalogue # 3651.

Step charts in MS Excel

a few VBA code lines for making step charts easier in MS Excel

Step charts aims at showing a quantity which has discrete variations. An example of step chart is shown below (source : @WSJGraphics, interesting twitter account for data vizualisation addicts).

MS Excel has no native chart type for representing this data. If you try to chart the number of shares of Exxon Mobil, MS Excel will interpolate the values between two discrete variations, thus showing a wrong information.

The Peltier Tech Blog gives an interesting article on this topic, explaining how representing properly this kind of data.

I thought it could be useful to automatize it with a VBA macro. The idea unedrlying the code is very simple :

  • put the graph in chronological order i.e. set the horizontal axis to date
  • duplicate the values of the data : put two values at each date of variation: the first one for the value before the variation and the second one for the value after the variation.

The code of the macro is below. It duplicates data in a new worksheet and automatically generates a step chart. The macro also allows to

  • represent multiple quantities on the same step chart, even if these quantities have different variation moments,
  • choose the type of chart (line, area, stacked area).

Representing FED and BCE interest rate is very easy with this VBA macro !


I would be happy to receive your comment on this article. You can contact me at rodolphe.gintz@gmail.com.


Public Sub construit_graph_crenaux(zone As Range, Optional type_graphique As String)
Dim nom_generique, nom_onglet As String
Dim i, j, taille As Integer
onglet_source = zone.Worksheet.Name
‘determining the coordinates and dimensions of the data range
v_offset = zone.Row — 1
h_offset = zone.Column — 1
v_size = zone.Rows.Count — ligne_titre
h_size = zone.Columns.Count — 1
Application.ScreenUpdating = False
‘ adding a new worksheet
Sheets.Add
nom_generique = “DataGraphCrenaux”
nom_onglet = nom_generique
i = 0
If teste_existence_onglet(nom_onglet) Then
do
i = i + 1
nom_onglet = nom_generique & i
Loop While teste_existence_onglet(nom_onglet)
End If
nombre_graph_map = i
onglet_donnees = nom_onglet
ActiveSheet.Name = onglet_donnees
‘ duplicating the data
For i = 1 To v_size
If i = 1 Then
For j = 1 To h_size + 1
Cells(i + 1, j).FormulaR1C1 = “=’” & onglet_source & “’!R[“ & v_offset — 1 + ligne_titre & “]C[“ & h_offset & “]”
Next j
Else
Cells(2 * i — 1, 1).FormulaR1C1 = “=’” & onglet_source & “’!R” & v_offset + ligne_titre + i & “C” & h_offset + 1
Cells(2 * i, 1).FormulaR1C1 = “=R[-1]C”
For j = 1 To h_size
If Sheets(onglet_source).Cells(v_offset + ligne_titre + i — 1, h_offset + j + 1).Value = “” Then
Cells(2 * i — 1, j + 1).FormulaR1C1 = “=R[-1]C”
Else
Cells(2 * i — 1, j + 1).FormulaR1C1 = “=’” & onglet_source & “’!R” & v_offset + ligne_titre + i — 1 & “C” & h_offset + j + 1
End If

If Sheets(onglet_source).Cells(v_offset + ligne_titre + i, h_offset + j + 1).Value = “” Then
Cells(2 * i, j + 1).FormulaR1C1 = “=R[-1]C”
Else
Cells(2 * i, j + 1).FormulaR1C1 = “=’” & onglet_source & “’!R” & v_offset + ligne_titre + i & “C” & h_offset + j + 1
End If
Next j
End If
Next i
If ligne_titre = 0 Then
Cells(1, 1) = “date”
For j = 2 To h_size + 1
Cells(1, j) = “y-item “ & j — 1
Next j
Else
For j = 1 To h_size + 1
Cells(1, j).FormulaR1C1 = “=’” & onglet_source & “’!R[]C[]”
Next j
End If
Charts.Add
With ActiveChart
If nombre_graph_map = 0 Then .Name = “GraphCreneaux” Else .Name = “GraphCreneaux” & nombre_graph_map
If type_graphique = “” Then .ChartType = xlLine Else .ChartType = type_graphique
.SetSourceData Source:=Sheets(onglet_donnees).Range(Sheets(onglet_donnees).Cells(1, 1), Sheets(onglet_donnees).Cells(2 * v_size, h_size + 1))
End With
End Sub
Public Function teste_existence_onglet(nom As String) As Boolean
Dim i As Integer
teste_existence_onglet = False
For i = 1 To Worksheets.Count
If Worksheets(i).Name = nom Then
teste_existence_onglet = True
End If
Next i
End Function