## How Many Squares

In my recent article How Many Squares? (1. Interactive Chart), I answered a question posed by my friend and colleague Carlos Barboza on LinkedIn: How Many Squares? Given the partial grid of dots shown, how many squares can you form using these dots as the vertices of the squares?

I made my own workbook, so I could easily keep track of squares. Then I set up a way to select and draw any of those squares. This interactive chart is described in How Many Squares? (1. Interactive Chart).

Then I thought it would be great to automate the process, using VBA to cycle through each of the squares in turn. You can download my workbook from the link below (actually it’s a zip file containing the workbook). The workbook contains a simple VBA procedure, which I’ll describe, so you will need to unblock the workbook in Windows to allow macros, then enable macros when you open the file.

Check out my recent article Unblocking and Enabling Macros if you need help unblocking this workbook.

## Automate Display of the Squares (VBA)

I put together a simple VBA procedure that will highlight each of the squares in turn, by changing the value of `ScrollNow`

in cell T2. Remember that cell T3 is called `ScrollMin`

and contains zero, and cell T4 is called `ScrollMax`

and contains 46. We need one more Name which I will call `Pause`

, to designate the pause in seconds between changing from one square to the next.

I selected cell L8, entered a value of 0.25, and named it “Pause”. I initially used a value of 1, but that cycled through the squares too slowly.

I also named the worksheet `wsDotsAndSquares`

in the VBA Editor’s Project Explorer.

`ScrollMin`

, `ScrollMax`

, `ScrollNow`

, and `Pause`

will be inputs into the VBA procedure. Here is the procedure:

```
Sub CycleThroughTheSquares()
Dim iMin As Long, iMax As Long
iMin = wsDotsAndSquares.Range("ScrollMin").Value2
iMax = wsDotsAndSquares.Range("ScrollMax").Value2
Dim tPause As Double
tPause = wsDotsAndSquares.Range("Pause").Value2
Dim rNow As Range
Set rNow = wsDotsAndSquares.Range("ScrollNow")
Dim iNow As Long
For iNow = iMin To iMax
rNow.Value2 = iNow
Dim t As Double
t = Timer
wsDotsAndSquares.Calculate
DoEvents
Do
DoEvents
If Timer - t > tPause Then Exit Do
Loop
Next
rNow.Value2 = 0
End Sub
```

`iMin`

and `iMax`

take the values of `ScrollMin`

and `ScrollMax`

; `tPause`

takes the value of the cell `Pause`

. `rMin`

is a reference to the cell `ScrollNow`

, not its value. The code loops from `iMin`

to `iMax`

, assigning the value `iNow`

to the cell `ScrollNow`

, which updates all of the formulas and thus the chart. `Timer`

is a function that returns the value from the computer’s clock, in seconds, with a resolution better than a hundredth of a second. We check the time at the beginning of the loop, then keep checking within a nested loop until the new `Timer`

value is greater than the initial value plus `tPause`

.

Then I added a Form Menu button. Go to Developer > Insert > Form Menu Button. Draw the button in the worksheet, then when the Assign macro dialog pops up, select the macro above and click OK.

Give your button a more descriptive caption (“Show Squares”) and resize and reposition it as desired. I put mine above the Pause cell near the chart.

Click the button, and the square number will increment from zero (no square) through 46, finally returning to zero, spending about 0.25 seconds (Pause) at each square.

## Display Squares in Random Order (VBA)

I wrote another VBA procedure that will show the charts in random order. It’s much like the first procedure, but I make use of some new Dynamic Array functions. I use `SEQUENCE`

to generate a list of numbers from 1 to `iMax`

. I use `RANDARRAY`

to generate a list of random numbers corresponding to this sequence. Then I use `SORTBY`

to sort the sequence according to the random numbers. The squares are displayed in the order that the square numbers appear in the randomly sorted sequence.

```
Sub CycleThroughTheSquaresInRandomOrder()
Dim iMax As Long
iMax = wsDotsAndSquares.Range("ScrollMax").Value2
Dim tPause As Double
tPause = wsDotsAndSquares.Range("Pause").Value2
Dim rNow As Range
Set rNow = wsDotsAndSquares.Range("ScrollNow")
rNow.Value2 = 0
Dim v As Variant, w As Variant, x As Variant
v = WorksheetFunction.Sequence(iMax)
w = WorksheetFunction.RandArray(iMax, , 0, 1, False)
x = WorksheetFunction.SortBy(v, w, 1)
Dim iNow As Long
For iNow = 1 To iMax
rNow.Value2 = x(iNow, 1)
Dim t As Double
t = Timer
wsDotsAndSquares.Calculate
DoEvents
Do
DoEvents
If Timer - t > tPause Then Exit Do
Loop
Next
rNow.Value2 = 0
End Sub
```

You can assign this macro to the original Show Squares button, or add a new button.

## Make an Animated GIF

How can I talk about animated charts without showing an animated image? How Many Squares? (3. Animated GIF) shows how easy it is to create an animated GIF that cycles through all of our squares.

## Leave a Reply