Tag: visualization

It takes a click or two to create a chart in Excel, but it was not always that easy.

Once upon a time, in the days of Commodore 64, Sinclair ZX81 and Spectrum, Amstrad/Schneider 464/6128, and BBC Micro, charts had to be programmed using PLOT(x,y) or a direct POKE to the screen-mapped memory. Sounds tedious, but it was a definite improvement over the IBM 1130 with a drum plotter which had to be fed punch cards; drum and pen movement were controlled separately in incremental mode. Well, I am the same age as Fortran IV.

The machines were slow, so one could always see the chart being plotted dot by dot. That is what I miss these days—for some charts (scatter, polar) it is useful to slow down plotting so one can actually see the line being plotted.

Here is a small VBA program to animate a scatter chart by dynamically changing (increasing) the chart’s data source range—row by row every time increment (tick).

In a VBA module:

Option Explicit 
Private Declare Function GetTickCount Lib "kernel32" () As Long 
'
Public Sub AnimateChart(SheetName As String, _
                    DataRangeName As String, _
                    Optional ChartIndex As Long = 1, _
                    Optional Tick As Long = 50)
    '
    ' Comments  :  Animates chart by dynamically changing the 
    '               data source range
    '
    ' Parameters:  SheetName - name of the sheet the chart is on.
    '              DataRangeName - named range for the data,
    '                              includes header.
    '              ChartIndex - 1 for the first chart on the sheet,
    '                           2 for the second etc..
    '              Tick - update interval;
    '                     suggest: 50, 100, 150
    '
    ' Modified  :  2008-06-29
    '
    On Error GoTo PROC_ERR
    Dim Chrt As Chart
    Dim rngSrc As Range
    Dim rngRef As Range
    Dim NumberOfPoints As Long
    Dim CurrentPoint As Long
    Dim OldPoint As Long
    Dim Strt As Long
    Dim rw As Long
    Set Chrt = Worksheets(SheetName).ChartObjects(ChartIndex).Chart
    Set rngRef = ThisWorkbook.Names(DataRangeName).RefersToRange
    NumberOfPoints = rngRef.Rows.Count - 1
    CurrentPoint = 0 'current animation point
    'clear the chart (show first point only)
    rw = 2
    OldPoint = 1  'first point is in the second row of the range
    Set rngSrc = rngRef.Resize(rw)
    Chrt.SetSourceData Source:=rngSrc, PlotBy:=xlColumns
    DoEvents 'update screen
    Strt = GetTickCount 'remember start tick count
    Do
        CurrentPoint = CLng((GetTickCount - Strt) / Tick)
        If CurrentPoint  > OldPoint Then
            rw = rw + 1
            Set rngSrc = rngRef.Resize(rw)
            Chrt.SetSourceData Source:=rngSrc, PlotBy:=xlColumns
            OldPoint = CurrentPoint
        End If
        DoEvents
    Loop While (CurrentPoint <= NumberOfPoints)
PROC_EXIT:
    Set rngSrc = Nothing
    Set rngRef = Nothing
    Set Chrt = Nothing
    Exit Sub
PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
End Sub 

In a Worksheet module, the button’s click event—cbtAnimate_Click()—prepares data for a specific chart and calls AnimateChart(). Chart data should be defined as a named range which is passed via the DataRangeName parameter.

Private Sub cbtAnimate_Click()
    cbtAnimate.Caption = "busy..."
    AnimateChart SheetName:=Me.Name, _
                DataRangeName:="Lissajous_Data", _
                ChartIndex:=1, _
                Tick:=50
    cbtAnimate.Caption = "Animate"
End Sub

You can download the workbook with examples for a Lissajous curve, a logarithmic spiral, and a rose curve.