Loss of macro function moving from Excel 98 to Excel 2004

[hanjohn]

Some macros that I have put together using Excel in Office for Mac 98
do not achieve the same effect when run in Excel 2004 for Mac.
I am a high school science teacher and I use these macros to
demonstrate scientific principles to students by moving excel shapes on
the screen, ie animations.
Consider this example that smoothly moves a shape, named "myshape" 100
points down the screen:

Dim myshape as shape, k as Integer
Set myshape = ActiveSheet.Shapes("myshape")
Do While k<100
myshape.IncrementTop 1
Calculate
k = k + 1
Loop

When run on PC or my old iMac (G3, OS 9.2) the named shape moves 100
points smoothly down the screen. When run on the school eMacs with
Excel 2004 the screen only shows the initial and final positions of the
shape. While the code is running the shape stays put at the initial
position and it then jumps to the final position when the Do Loop
finishes.
Application.ScreenUpdating = True makes no difference, since it is True
by default anyway.
Is there something else I can do to get my animations working on my
school's networked Macs?

 

[JE McGimpsey]

Some macros that I have put together using Excel in Office for Mac 98
do not achieve the same effect when run in Excel 2004 for Mac.
I am a high school science teacher and I use these macros to
demonstrate scientific principles to students by moving excel shapes on
the screen, ie animations.
Consider this example that smoothly moves a shape, named "myshape" 100
points down the screen:

Dim myshape as shape, k as Integer
Set myshape = ActiveSheet.Shapes("myshape")
Do While k<100
myshape.IncrementTop 1
Calculate
k = k + 1
Loop

When run on PC or my old iMac (G3, OS 9.2) the named shape moves 100
points smoothly down the screen. When run on the school eMacs with
Excel 2004 the screen only shows the initial and final positions of the
shape. While the code is running the shape stays put at the initial
position and it then jumps to the final position when the Do Loop
finishes.
Application.ScreenUpdating = True makes no difference, since it is True
by default anyway.
Is there something else I can do to get my animations working on my
school's networked Macs?

One way is to use DoEvents to force redraw of the screen:

Dim myshape As Shape, k As Integer
Set myshape = ActiveSheet.Shapes("myshape")
Do While k < 100
myshape.IncrementTop 1
Calculate
k = k + 1
DoEvents
Loop

One downside to this is that if there are no events to process, DoEvents
will wait for a period of time, so the movement of the shape is very
slow. However, if you move the mouse, say, while the code is running,
the shape will proceed smartly down the screen.

 

[hanjohn]

I'll give DoEvents a try tomorrow at school. I've not used it before so
I'll do a bit of experimenting. Do you have any idea as to why this
change in VBA function has been introduced? Is it something to do with
OS X compared with previous OS's? Is it an intentional change or what?
Should I contact Microsoft to get more info? Thanks for your help.
Ken Johnson

 

[JE McGimpsey]

Do you have any idea as to why this
change in VBA function has been introduced? Is it something to do
with OS X compared with previous OS's? Is it an intentional change or
what? Should I contact Microsoft to get more info?

I really don't know why the change. Perhaps it's part of the effort to
speed up VBA.

It's worth sending feedback to MacBU - use the Help/Send Feedback on
Excel menu item within XL.

I wouldn't hold your breath for a reply, though...

 

[hanjohn]

Thanks again, I'll give that a try.
Ken Johnson