This is the 3^{rd} post to explain in detail how my xlCube game application was constructed. To read the previous post, go to:

https://dhexcel1.wordpress.com/2018/01/20/xlcubesuper-an-excel-game-explained-part2/

This is the VBA routine I developed to add 3D cube ranges to the playing board of xlCubeSuper. It generates 7 3D ranges randomly with sizes ranging from 3x3x3 to 15x15x15.

Sub tdAddNamedRanges()

Randomize ‘ Initialize random-number generator

For s = 1 To 7

‘set x,y, and z values to fall within limits determined by cube size

x = 1 + Int(Rnd() * (20 – (2 * s)))

y = 1 + Int(Rnd() * (20 – (2 * s)))

z = 1 + Int(Rnd() * (20 – (2 * s)))

‘create R1C1 style formula strings

cShipStr = “='” & z & “:” & z + (2 * s) & “‘!R” & x & “C” & y & “:R” & x + (2 * s) & “C” & y + (2 * s)

centerShipStr = “='” & z + s & “‘!R” & x + s & “C” & y + s

‘create names for cube 3D references and the centers of each cube

matchVar = False

With ThisWorkbook.Names

.Add Name:=”cShip” & s, RefersToR1C1:=cShipStr, Visible:=False

.Add Name:=”centerShip” & s, RefersToR1C1:=centerShipStr, Visible:=False

End With

If s > 1 Then

For m = 1 To s – 1

If Names(“centership” & m).RefersTo = Names(“centership” & s).RefersTo Then

matchVar = True

End If

Next

End If

If matchVar Then

s = s – 1

End If

Next

End Sub

The first statement in the VBA procedure uses RANDOMIZE to create a numeric seed that will be used by the RND function to generate a random number.

Inside the For-Next statement, which sets the variable “s” to a value from 1 to 7, the xyz coordinates for each cube are generated as follows:

x = 1 + Int(Rnd() * (20 – (2 * s)))

y = 1 + Int(Rnd() * (20 – (2 * s)))

z = 1 + Int(Rnd() * (20 – (2 * s)))

The numbers generated by these fomulas keep the cube on the board, depending on the value of “s”. The z coordinate is for the worksheets included in tne board and the xy coordinates are for the cells.

Then, for each value of “s”, the 3D cubical range and the center of each cube are generated with these formulas in R1C1 format.

cShipStr = “='” & z & “:” & z + (2 * s) & “‘!R” & x & “C” & y & “:R” & x + (2 * s) & “C” & y + (2 * s)

centerShipStr = “='” & z + s & “‘!R” & x + s & “C” & y + s

When they are saved as defined name formulas using the following code,

With ThisWorkbook.Names

.Add Name:=”cShip” & s, RefersToR1C1:=cShipStr, Visible:=False

.Add Name:=”centerShip” & s, RefersToR1C1:=centerShipStr, Visible:=False

End With

the formulas are converted by Excel into A1 format.

Originally, this was all of the code for this process. What I had ignored what the fact that two different cubes could have the exact same center. During all of the testing/playing of this game over the years, that scenario was never recognized as having occurred. But the current version, with 7 cubes, had this happen several times in early testing. So, I came up with this solution to correct this issue.

If s > 1 Then

For m = 1 To s – 1

If Names(“centership” & m).RefersTo = Names(“centership” & s).RefersTo Then

matchVar = True

End If

Next

End If

If matchVar Then

s = s – 1

End If

A key part to this code is

Names(“centership” & m).RefersTo = Names(“centership” & s).RefersTo

which compares the currently added formula to each previous formula added. If this statement is TRUE, then matchVar is set to TRUE. Then,

If matchVar Then

s = s – 1

End If

which decrements “s” by 1, effectively rerunning the previously generated 3D formula until an unique center is produced.

The next post will discuss the worksheet formulas used in this game.

Pingback: xlCubeSuper An Excel Game Explained – Part4 | Excel For You