Cell Animation in Excel
Basic Principles

You can generate graphics for Excel games in many way - with the use of worksheet cells, shapes, bitmaps embedded in a worksheet or an Userform. In this section I will discuss some practical issues with creating animations via altering worksheet cells.

There are two basic ways of creating Cell Animation: worksheet cells may serve as tiles with a cell representing a single sprite (with a given background, font and value) like in my Calculor's Dungeon:

Alternatively, cells may serve as pixels, when an animated sprite is made of a range of cells with colored interiors. Snake games in Excel lie somewhere in between - snake body if formed by a group of cells, but for the purpose of animation you alter each cell separately, like in Colo's Excel Snake:

Below, I list several very basic but useful principles for anyone who would like to develop Cell Animations.

1. Forget about ScreenUpdating = False.

This principle contradicts some tips you may find in available guides about speeding up VBA code. Typically you disable screen updating when you write information to a worksheet. It makes perfects sense in situations like this:

Application.ScreenUpdating = False
 For Xi=1 to 1000
  Selection.EntireRow.Delete
 Next Xi
Application.ScreenUpdating = True

But Cells Animation are a totally different concept. When you turn ScreenUpdating on, the entire screen is refreshed - it is relatively time consuming process (remember, in animation every millisecond counts) and it causes the whole screen to blink.

This "refreshing blink" is hardly noticeable when you see it once, but repeated in animation it could be very annoying. And we don’t want Excel Games to have those common epilepsy warning, do we?

To sum up, turning ScreenUpdating On / Off may prove useful for some title screens and so on, but it is a totally useless method to refresh the frames of animation.

2. No Copy - Paste!

This is a very basic principle, well known by most VBA developers. When you copy a range manually, you need to select it, copy and paste. There is no need do this things in Excel VBA. Instead of:

Range("A1:B2").Select
Selection.Copy
Range ("A5").Select
ActiveSheet.Paste

Use:

Range("A1:B2").Copy Range("A5")

It is much faster, simpler and you do not bother with selecting cells, which could likely disrupt the animation effect. Of course, instead of Range("…") syntax, it is much more convenient to use range object variables.

For single cells, modern PCs should be able to perform even 500 Range(...).Copy operations per second.

3. Range().Copy vs With Range()

Actually, when you alter only particular cells, working with Range Object is significantly faster than Copying a row. The code below:

With Range("A1")
 .Value= Range("b5").Value
 .Interior.ColorIndex= Range("b5").Interior.ColorIndex
End With

May be almost twice as fast as copying a range that contains single cell:

Range("B5").Copy Range("A1")

However, reading the properties of a Range Object is also relatively time consuming in VBA, so you could speed the code even more by using pre-defined values if able:

With Range("a1")
 .Value = "X"
 .Interior.ColorIndex = 33
End With

This difference in performance is significantly magnified if you work with not visible cells (hidden or outside the range visible on the screen). Range().Copy operation performed "outside the screen" is accelerated by about 25%, but the speed of With Range() operation could be tripled - you could alter more than 3000 cells in a second!

4. Economies of Scale

Sometimes simple arithmetic is enough to estimate the efficient use of resources, for example, if you like to travel 200 miles, you will use approximately twice the amount of fuel spent on a 100 miles travel. But sometimes this arithmetic fails: the price of airline tickets are very loosely correlated with the distance to fly. Excel cell animation is also ruled by more sophisticated mechanics.

Accessing the worksheet cells in VBA is relatively time consuming and this process has significant constant component (e.g. component independent on the properties of accessed cells). Compare this two examples:

For Xi = 1 to 100
 Range("a11").Copy Range("a1")
Next Xi

And:

Range("a11:i20").Copy Range ("a1")

Both pieces of code copy one hundred cells in total, but the second will be up to 25 times faster! Thus, copying a single range of hundred cells is only about four times slower than copying a single cells - these are huge "economies of scale".

Merging objects to reduce the number of Copy operations is a crucial way to optimize the speed of cell animation. For example, in my Calculor’s Dungeon, movement of critter or missile consists of a single Copy operation of a two-cells range containing the sprite and "empty space". Like in example below for critter heading up:

Best result may by achieved by combining the principles listed above. One can perform all animation for individual sprites outside the visible part of the screen with very fast With Range() operations, and then copy entire game area with one Range().Copy operation benefiting from the Excel economies of scale.

5. Peculiarities

6. Challenges

So far, Cell Animations were based only on colored interiors or cells containing a single character. However, some attempts to use multiple character in a cell, each formatted individually, may prove useful. Below you can see a screenshot presenting this kind animation. Maybe it is a good idea for a new game?

You can download an exemplary workbook with this animation:

Download Wingdings Warrior Animation (13kb)

Wingdings are cool, aren’t they?