Animated Graphs in Excel

Setting up an automatic step

Click on the developer tab, then the insert icon.  Choose the small box under the Active X controls, which inserts a Command Button.  (It is important that you choose the Active X Command Button rather than the Form Control.  In the second method, we will use the Form Controls.)

After you press the button, click on the spreadsheet.  A new Command button “CommandBut” will appear wherever you click.   This can be expanded to show the entire label “CommandButton21”.  The last numbers might be different, especially when you add more buttons.

Adding a command button

Adding a command button to Excel.

 

Make sure that the button is selected, and then click on “View Code”.  The Microsoft  Visual Basic for Applications will appear.  In the pull down menu, select the CommandButton21, then enter the code shown in the diagram or listed below.   (Note that Visual Basic uses ‘ to denote a comment.  Also note that there are no characters like ; to denote the end of a command.)

VBA Script for causing a single cell to step through a series of values.

VBA Script for causing a single cell to step through a series of values.

Here is the code

Private Sub CommandButton21_Click()

Dim PauseTime, Start
  Dim i As Integer
  i = 1
Do While i <= 100     ' Set number of steps.
  Sheet1.Cells.Range("A7").Value = i
    i = i + 1
    PauseTime = 0.1   ' Set duration 
                      ' typically equal to interval.
    Start = Timer     ' Set start time.
   Do While Timer < Start + PauseTime
      DoEvents        ' Yield to other processes.
   Loop
Loop
End Sub

Make sure that the cell number in the

Sheet1.Cells.Range(“A7”).Value = i

line refers to the cell you have denoted as your step number. Once you are finished, exit out of the code editor.

Next, let’s make things look a little better.