Improving Animation Performance

[Tom Quist]

Greetings!

I have a fairly advanced object animation script that I am using to animate various objects in my workbook upon user interaction.

For example, I have an small image which serves as a button. The animation "macro" is assigned to that image. When a user clicks the button, the macro creates a new shape on top of the button (matching the dimensions of the button). This new shape - the "mask" - then "animates" from opaque to transparent (and ultimately is programatically deleted).

Overall, I'm very happy with the performance of this script. However, performance seems to vary based on a few different contexts. Here are my observations thus far:

1. On a relatively "clean" worksheet, animations are very fluid. By "clean", I mean there are no (or very few) other objects on the active sheet. The animated object itself does not flicker, nor does the screen flicker at the start/stop of the animation sequence.

2. On a "cluttered" worksheet, with a fair number of objects and cell data visible, the animation quality is quite bad - the target object flickers, animation speed slows down, etc.

I would have assumed that this was more or less an insurmountable problem due to Excel not having the resources to handle the animation on top of the "busy" screen. However, I found that if I ran the exact same code from the VBE (as opposed to assigning the macro to the object and triggering upon click), the animation worked beautifully, even on the "cluttered" screen.

Thus, I am trying to determine why the quality varies dependent upon whether the code is triggered by the object itself versus directly within the VBE - and how I can achieve the high quality results even when assigning the macro to the object.

The only difference that I can surmise thus far is that when I trigger the macro from the VBE, my Excel window is (obviously) not "active" or "in focus". Could this impact it's ability to redraw the screen more quickly? Is there a way to programatically simulate dropping the window's focus for a brief moment? While I'm not terribly comfortable with Windows API calls, I'm willing to try that path if it is necessary.

Thank you so much for your help with this vexing issue!

Tom

P.S. I am 99.9% sure that this issue has nothing to do with the "screenupdating" property. I've researched that to death and the animations work just fine on less cluttered screens.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I used code like this to spin some shapes. Each shape on the worksheet calls this macro when it is clicked.
I used the Windows Sleep function in the Kernel to get a fine time resolution and used DoEvents to let other things happen during the animation.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub xx()
    Dim myShape As Shape
    Set myShape = ActiveSheet.Shapes(Application.Caller)
    For i = 1 To 18
        DoEvents
        Sleep 10
        DoEvents
        If myShape.Rotation > 90 And myShape.Rotation < 270 Then
            myShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
        Else
            myShape.Fill.ForeColor.RGB = RGB(0, 255, 0)
        End If
        myShape.IncrementRotation 10
        myShape.ThreeD.IncrementRotationX 10
    Next
End Sub

The code spims the selected shape and changes colour at the same time.

 

[Tom Quist]

Hi Rick,

I am very sorry for the slow response to your response. I really appreciate your suggestion!

While this does look like a nice piece of code for animating shapes, it still has some limitations in terms of smoothness and responsiveness.

Since I initially wrote, I've been researching alternatives to animating on my worksheet. A couple of ideas have popped into my mind, though I'm not sure they're feasible.

1. Draw "over" the worksheet using a windows api (like GDI maybe, or GDI+)

2. Use a form as a "mask" layer. I have found ways to make transparent forms, but I'm not too far along in the process (i.e. animating a shape in a form, getting rid of the titlebar, etc).

3. Use a windows api to limit the area of the sheet that is redrawn during the animation cycle (thus reducing the strain on the system). Not sure if this is possible, but my research suggested it MAY be an option.

I'm really not too good with the windows api calls, but I'm learning. Any additional advice would be welcome!

Thanks again, and sorry for the delayed response!

Tom

 

[RickXL]

No problem.

I have to say that those spinning shapes are very smooth on my PC (Intel Q6600 so not new).

Apart from using the Sleep command I tend to shy away from Windows calls. If you want to do that why not write it in C in the first place and save all that overhead?

Whatever I think, you must do what is good for you.

May your animations be smooth!

Regards,

 

[Tom Quist]

Rick - thanks again for your reply.

I should clarify - the animation example you sent was indeed smooth on my PC as well. It was when I tried to alter it to my specific needs that it's performance couldn't quite meet my needs (admittedly, I may be doing it wrong!). The biggest problem I faced was the amount of time lapsing at the minimum sleep time. The animations I'm using ideally complete in about .2 seconds. For this time span, I'd like to have about 8-10 frames cycled. I couldn't seem to accomplish this even with a sleep time of 1. Is there a way to achieve smaller increments? It would be nice to specify the amount of time an animation should take so that it performed the same across machines of varying performance capabilities (currently, I manually adjust the number of "steps" for the animation until it fits). It would be great to say:

"Hey shape, you have .2 seconds to go from opaque to transparent, and I want you to show me as many frames as is possible given the capabilities of this machine."

In order to do this, I suspect you'd have to measure the time at each animation frame and determine whether you were "on schedule", "ahead of schedule", or "behind schedule" and then adjust to the scenario.

I think I'm going to have to do some testing. I may be able to get better time reads using the high res timer code I found on the web a while back (I can find it, but I suspect you know what I'm talking about).

I wish I knew how to code in C! I've never received formal training in coding - I just picked up VBA over the years thanks to trial and error and the fine folks on the web. I really enjoy it and wish I had more time to learn some new languages. In fact, I'd love to do some Android development.

Thanks again for your help, Rick. I appreciate ya.

Tom