Slow plot in Excel
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:
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.
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.
