Excel Circles Movement

In this post we see how to move shapes in a worksheet. This can be useful to build animated spreadsheets, but we will rather create some fun games in Excel instead. It is more powerful than simply moving across the cells as we learned in the previous post. It is as simple as adding shapes in combination with timer events.

Time Events

We will need time events in milliseconds. If you still don’t know how to do that please check the previous post where it is explained in more detail (Excel Tron Game). The VBA code below shows the declaration of SetTimer for both 32bit and 64bit, and the different timer macros used to set time intervals in milliseconds and call the corresponding macro to move the shapes. Find the link to download the Excel Circle Movement file below at the bottom of this article to see and/copy the code.

 

The timer will call the procedure “move” in the main module every 20 milliseconds. That procedure will simply add or subtract to the x and y axis of the shape location. When the circle’s column or row reaches the border limit it will change the direction (rebound). Note that column or row plus diameter is taken when checking for right and bottom borders as diameter varies for each circle.

The position of the circle is determined by the parameters c for column, f for row, and d for diameter. However, these have been defined as arrays to capture the position of n circles.

Shape Object

In this example we are using the Excel oval shape in the form of a circle. In order to add a shape in excel we just need to use the AddShape method. We have defined a shape object in order to easily manipulate its properties. The shape object “circ” has the form of an array and will store all the circles moving in the active worksheet. The variables x, y, z will determine the position and dimensions of the shape.

 

 

  Sub AddCircle()

      Dim x As Integer, y As Integer, z As Integer

      n = n + 1

      Randomize

      x = Int((500 - 30 + 1) * Rnd + 30)

      y = Int((200 - 30 + 1) * Rnd + 30)

      z = Int((120 - 60 + 1) * Rnd + 60)

      circcolor = Int((50 - 2 + 1) * Rnd + 2)

      Set circ(n) = ActiveSheet.Shapes.AddShape(msoShapeOval, _

      x, y, z, z)

 

      With circ(n)

          .Name = "circ" & n

          .LockAspectRatio = msoTrue

          With .Line

              .Weight = 0

              '.ForeColor.SchemeColor = 3

          End With

           With .Fill

               .Solid

               .ForeColor.SchemeColor = n + 9

               .Transparency = 0.3

           End With

      End With

 

      c(n) = x

      f(n) = y

      d(n) = z

      Randomize

      posneg = Int((2 - 1 + 1) * Rnd + 1)

      If posneg = 2 Then cc(n) = 1 Else cc(n) = -1

      ff(n) = -cc(n)

  End Sub

 

 

In order to move the shape across the worksheet we just need to modify its .Top and .Left properties, which determine the position relative to the top and left edges of the worksheet. The easiest way to do so is by using the circle name (previously established) as follows:

 

  With ActiveSheet.Shapes("circ" & n)

.Top = f(nn)

.Left = c(nn)

  End With

 

This is very powerful as it works with any type of shape including text boxes. In this other post we use the same principle to build a really fun game using text boxes with random letters moving around the worksheet for the player to catch - Excel Falling Letters.

Download Excel Circles Movement