1

Animate In Excel

When creating a logo for my next site, I wanted to do it completely in Excel and then I later decided to animate in Excel.

The logo itself is relatively straightforward using Excel’s shapes.

The image below shows the final logo and also the shapes used to make it.

I used Excel’s Drawing Tools to format the shapes making up the logo.

The only change I had to make to the default shapes was to the top of the rectangles making up the Xs.

The two rectangles on the left are vertical.  The first one is my edited one and the second one is a standard rectangle.  You can see that the top of them are different.

The two rectangles on the right are the same ones but rotated.

The first one is my edited one and as you can see, the top of it now looks flat.

To do this, I clicked on the shape and edited points from the drawing tools options.

My original plan was to create the logo in Excel and then animate it in Flash, which is what I would normally animate in.

I was asked if I could animate in Excel and I took that as a challenge and accepted it.

Before continuing, you should at least know the basics of Excel VBA and macros.

If this is totally new to you then I would advise reading through the basics in the series starting with Introduction to Macros.

 

Animate In Excel

Traditional animation works by showing separate images so fast that we see it as a moving image.

That is what we are going to to when we animate in Excel.

We will position our shapes (including text) and move them slightly, then move them slightly again and so on until they are in the final position.

This will be done so quickly that it will (hopefully) look like a fluid motion when finished.

 

To work through this post, create shapes and set them up yourself to help follow how this macro works.

 

Animate In Excel: The Set-Up

Each of our shapes currently have a relatively vague name like “Rectangle 5” or “TextBox 3”.

To make them easier to identify and to make our code easier to read, we will give them more meaningful names.

• Click on a shape
• In the NameBox give it a more meaningful name.

I have named my shapes:

1. Top_Text
2. Mid_Text
3. Bottom_Text
4. Front_X
5. Back_X
6. Horiz_Bar
7. Horiz_Arrow

 

Animate In Excel: X&Y Positions

For each shape (and text box) we will assign an X and Y value.  This will be the position of the shape on screen and our macro will change the position to move them on screen.

X is the horizontal position.

The higher the number, the more to the right the shape will be.  Likewise, the lower the value the more to the left the shape will be.

Y relates to the vertical axis.

The higher the number, the lower down the shape will be and the lower the value, the higher up the shape will be.

Think of the top left of the sheet as being X=0 and Y=0.

As the values increase the shape moves further from this point.

The Y position will be the “Top” property of our shape and the X position will be the “Left” property.

 

Animate in Excel: Get The X&Y Positions

Before we can change the position of our shapes, we need to know where we want them to end up.

I would advise setting up your image away from row 1 and column A.  This is so that you have enough room on either side of the final image to move your shapes.  In my case, my image is around column S and row 35.

We will create a macro now that will get the X and Y position of each shape in its final position and then assign those values to columns A and B.

• Note the names of all your shapes
• Set up your final image.
• Insert a module
• Create a macro as below:

Sub getShapePositions()
Sheet1.Range(“A1”).Value = Sheet1.Shapes(“Front_X”).Top
Sheet1.Range(“B1”).Value = Sheet1.Shapes(“Front_X”).Left
Sheet1.Range(“A2”).Value = Sheet1.Shapes(“Back_X”).Top
Sheet1.Range(“B2”).Value = Sheet1.Shapes(“Back_X”).Left
Sheet1.Range(“A3”).Value = Sheet1.Shapes(“Horiz_Bar”).Top
Sheet1.Range(“B3”).Value = Sheet1.Shapes(“Horiz_Bar”).Left
Sheet1.Range(“A4”).Value = Sheet1.Shapes(“Horiz_Arrow”).Top
Sheet1.Range(“B4”).Value = Sheet1.Shapes(“Horiz_Arrow”).Left
Sheet1.Range(“A5”).Value = Sheet1.Shapes(“Top_Text”).Top
Sheet1.Range(“B5”).Value = Sheet1.Shapes(“Top_Text”).Left
Sheet1.Range(“A6”).Value = Sheet1.Shapes(“Mid_Text”).Top
Sheet1.Range(“B6”).Value = Sheet1.Shapes(“Mid_Text”).Left
Sheet1.Range(“A7”).Value = Sheet1.Shapes(“Bottom_Text”).Top
Sheet1.Range(“B7”).Value = Sheet1.Shapes(“Bottom_Text”).Left
End Sub

This gets the Top and Left (X&Y) values for each of our shapes and assigns them to cells starting in A1 and B1.

Our starting point is our ending point.

Now that we know where we want to end our animation we can work out where we want to start it from.

I have chosen 300 as the number of “frames” I want to go through.

You can easily and quickly change this number to test the results for yourself.

This means that I will also be changing the Top and Left values of my shapes by 300 where applicable.

 

Animate In Excel: Set Up A Timer

Add the macro below to your module.

“duration_ms” will be assigned in our main macro and this will be how long the macro delays before looping again.

When the timer reaches the duration we set it will loop again and repeat until it reaches the point we tell it to stop.

Sub my_timeout(duration_ms As Double)
Start_Time = Timer
Do
DoEvents
Loop Until (Timer – Start_Time) >= duration_ms
End Sub

 

Animate In Excel: Animate

It was at this point that I decided not to animate the text boxes by changing their positions, but rather to have them fade in instead.

This will lead to two types of animation, one will be a change to the position of shapes and the other will be a change to the transparency of the text boxes.

Create a new macro in your module

• I called mine “Logo_Animation”.
• The variable “repeat_count” will be used to help with animating the position of shapes.
• The variable “repeat_count2” will be used to help with animating the fading in of the text boxes.
• Both will initially be set to zero.
• Set the transparency of the 3 texts to be transparent (this is a value of 1).

Sub Logo_Animation()
repeat_count = 0
repeat_count2 = 0
Sheet1.Shapes(“Top_Text”).TextFrame2.TextRange.Font.Fill.Transparency = 1
Sheet1.Shapes(“Mid_Text”).TextFrame2.TextRange.Font.Fill.Transparency = 1
Sheet1.Shapes(“Bottom_Text”).TextFrame2.TextRange.Font.Fill.Transparency = 1

• Our repeat_count starts as zero.
• In the first run of our loop we make it 1 (repeat_count+1 is the same as 0+1).
• The position of our shapes will be 300 from our original position (Front_X left ends at 769 so we will start it at 469).  Change the position values to suit where you want the shapes to be moving from.
• The current value of repeat_count is added/subtracted from the starting positions of our shapes to assign the new location.
• The duration of our timeout is set as 0.01.
• The repeat_count value will now change to 2 (repeat_count was 1 and is now added to 1).
• The position of our shapes will change based on the new repeat_count value
• This will continue until repeat_count is equal to 300 at which point the macro is finished running.

Do
DoEvents
repeat_count = repeat_count + 1
Sheet1.Shapes(“Front_X”).Top = 350
Sheet1.Shapes(“Front_X”).Left = repeat_count + 469
Sheet1.Shapes(“Back_X”).Top = 650 – repeat_count
Sheet1.Shapes(“Back_X”).Left = repeat_count + 449
Sheet1.Shapes(“Horiz_Bar”).Top = 490
Sheet1.Shapes(“Horiz_Bar”).Left = 1060 – repeat_count
Sheet1.Shapes(“Horiz_Arrow”).Top = repeat_count + 189
Sheet1.Shapes(“Horiz_Arrow”).Left = repeat_count + 519
my_timeout (0.01)
Loop Until repeat_count = 300

• repeat_count2 will be used to gradually make the text more opaque.
• After the 3rd line below, the value is 1.  This is divided by 100 to give .01 and deducted from the starting transparency value.  This will be 1% opaque or 99% transparent depending on what way you look at it.
• For the second iteration of our loop, repeat_count will be 2 and our transparency becomes 98%.
• This continues until repeat_count2 is 100.  At which time the transparency will be 0% (1 – 100% = 0).

Do
DoEvents
repeat_count2 = repeat_count2 + 1
Sheet1.Shapes(“Top_Text”).TextFrame2.TextRange.Font.Fill.Transparency = 1 – repeat_count2 / 100
Sheet1.Shapes(“Mid_Text”).TextFrame2.TextRange.Font.Fill.Transparency = 1 – repeat_count2 / 100
Sheet1.Shapes(“Bottom_Text”).TextFrame2.TextRange.Font.Fill.Transparency = 1 – repeat_count2 / 100
my_timeout (0.01)
Loop Until repeat_count2 = 100
End Sub

Animate In Excel: Try It Yourself

Test this macro yourself.

Change the timing, change the finishing point of your loop

Change how quickly the shapes change position or the transparency changes.

These settings can open up a lot of possibilities for your animation.

In the next post I will show you another form of animation in Excel, using a chart and random values to give the impression of a talking robot.