Animation

[Hölzl Otto]

I'v made a xy-diagramm with a single point. This point should move -
dependent of the x,y-values. These values are calculated by a Sub-procedure.
Calculatind and writig into the cells, which are the data-source of the
diagram-point, does well. But the point does'nt move. After the procedure
has finished, the point moves to the endpoint of the calculation.
Can somebody give me a hint, how I can manage the point to move?
Thanks
Otto H.

 

[Bernie Deitrick]

Otto,

Excel will only allow the screen to update when the macro is done. You can do this by successively calling the same macro: Excel
will update after each time the macro is called, so this will animate your point. Example code is below. Name the cell with the X
Value "XVal", the cell with the Y Value "YVal", and modify the steps (DeltaX and DeltaY) and limit - or use a separate formula for
each - as needed. For slower animation, increase the TimeValue string from "00:00:00" to "00:00:01".

HTH,
Bernie
Excel MVP

Sub AnimationToMovePoint()
DeltaX = 0.1
DeltaY = 0.1
Limit = 3

Range("XVal").Value = Range("XVal").Value + DeltaX
Range("YVal").Value = Range("YVal").Value + DeltaY

If Range("YVal").Value < Limit Then
Application.OnTime Now() + TimeValue("00:00:00"), "MovePoint"
End If
End Sub

 

[Bernie Deitrick]

Oops,

Application.OnTime Now() + TimeValue("00:00:00"), "MovePoint"
Should have been:

Application.OnTime Now() + TimeValue("00:00:00"), AnimationToMovePoint"

Also, you can do the macro this way, though with less control on timing: more steps will give longer animation time:

Sub AnimationToMovePoint()
DeltaX = 0.1
DeltaY = 0.1

For i = 1 To 20
Range("XVal").Value = Range("XVal").Value + DeltaX
Range("YVal").Value = Range("YVal").Value + DeltaY
DoEvents
Next i
End Sub