VB Simple Things

Some Tips on Doing Simple Things in Visual Basic Under Excel

This will try to help you do the following:

Bring up the 'Control Panel' in Excel. [This lets you get to most of what will be needed.]
Add a scroll bar (slider) and connect it to a cell in the spreadsheet.
Add a 'Label' to your sheet. [Can serve as a button, so that stuff happens when you click on it.]
View the code lying underneath the Label [There's a subrouting for when you click the Label.]
In the code underneath, how to

bring a cell value from the spreadsheet down into a variable in the code
put a variable value back into a cell on the sheet
put a formula into a cell on the sheet
add a time delay [handy if your animation is running too fast]
make a loop (just like the old days) running in basic
write one or two conditional statements (while, if)

To bring up the Control Panel (CP), go to View/Toolbars and click on 'Control Toolbox'
A panel will appear on the page or at the top among the other toolbars.
(You can drag it to the top bar if you want.
If it's on the top and you want to pull it down, drag on the 3 vertical bars at the left.)
Click on the architect's triangle. Its background will turn light blue, telling you you are in Design Mode.

On the control panel, there are a lot of items. The vertically separated triangles denote a 'scroll bar' or slider
The rectangle with an 'A' in it is for a 'label'. These are the two we will deal with here.

To put a scroll bar on your sheet, click once on the scroll bar, then move the mouse to a convenient spot on your spreadsheet and drag out a rectangular shape (later you can change the shape if it's not quite right).

Now right-click on the scroll bar and select Properties. A panel will open up, and among the properties you will find Linked Cell. Set this to a cell near your scroll bar, say, H5. The scroll bar has only integer values, with defaults from 0 to 32767. You might change Max to 500, so that the scrolling will go only from 0 to 500.

Now click the architect's triangle again to leave Design Mode. Drag the bar around inside the slider and watch the values in the cell change. The triangles on either end change the value by 1.

Suppose you wanted the slider to adjust a parameter between 5 and 20. Then in cell H6 you could put "=5+H5*15/500". Cell H6 should now move from 5 to 20 as you drag the scroll bar around.

The beauty of this scheme is that when a graph depends on the value in H6, the graph will change right before your eyes as you drag the slider around!

Put a label on the sheet in the same way: click on label icon, then drag over an area for the label. Right-click on the label and select Properties. Now under Caption you can write 'Click Here to Start'. For larger type, click Font, and then click on the triangle at the right with the three dots "...". Now you get to choose a font and style. You can also pick a background color, text alignment etc.

See the code underneath the label using one of two methods. One icon over from the architect's triangle is the one for View Code, having a little magnifying glass in it. Click on this and you will find an empty subroutine which says
Private Sub Label1_Click().

You get to put basic code in here which will execute whenever label 1 is clicked. (The other way to see the code is to right-click on the label and select View Code.)

You can get back to the spreadsheet proper via View/Object on the top bar.

Here is sample code with some explanation.

Private Sub Label1_Click()
these three lines are just remarks
Rem 'Click to begin'
Rem loop to increment time and then reverse
Rem velocity after a certain time.
dimension statements for variables
Dim j As Integer
Dim time As Single
Dim dtime As Single
Dim velocity As Single
Dim st As String
Dim swapped As Boolean
Dim delaytime As Single
Dim starttime As Single
A cell has been named 'delta_t'.
The value from this cell is brought into the variable dtime.
Range("delta_t").Select
dtime = ActiveCell.Value
Range("time").Select
time = ActiveCell.Value
swapped = False
The cell named 'v_x' is loaded with the value of 5
Range("v_x").Select
ActiveCell.Value = 5
Range("delaytime").Select
delaytime = ActiveCell.Value
For j = 1 To 100
Rem here is the time delay for each iteration
starttime = Timer
Do While Timer < starttime + delaytime
Loop
the time is kept current and displayed on the sheet
time = time + dtime
Range("time").Select
ActiveCell.Value = time
dtime is 0.01, so halfway through the loop, the value of v_x is reversed
If time > 0.5 And swapped = False Then
    Range("v_x").Select
    velocity = ActiveCell.Value
    ActiveCell.Value = -velocity
    swapped = True
End If
Next j
End Sub

If the cell has not been named, then we refer to it normally, e.g, Range("B5").Select.

To deal with a series of cells it could go like this:

    Put zero into cells E6..E16
    for i=1 to 10
        st:="E" & Format(i+5)
        Range(st).select
        ActiveCell.value =0
    Next i