Lesson 25: Creating Animation in Excel VBA 365

25.1 Bringing Your Excel Sheets to Life with Animation

While Excel VBA 365 is primarily known for data processing and complex calculations, it also offers powerful capabilities for creating engaging animations and simple games. These techniques can be valuable for creating interactive dashboards, educational tools, or just adding some fun to your spreadsheets.

Pro Tip: Animation in Excel VBA works by rapidly changing object properties (like position, color, or size) while using the DoEvents command to allow screen updates between changes.

25.2 Understanding the Core Animation Concepts

To create animations in Excel VBA, you'll need to understand these key elements:

Object Properties: The Left and Top properties control an object's position
Loops: Continuous movement is created using looping structures
DoEvents: Essential for smooth animation by allowing screen refreshes
Timing: Controlling the speed of animation through loop delays

Example 25.1: Horizontal Object Movement

This basic example demonstrates how to make an image move horizontally across your worksheet:

Private Sub CmsStart_Click()
repeat:
  With VBAProject.Sheet1.Image1
    .Left = .Left + 1  ' Move right by 1 pixel
    DoEvents           ' Allow screen to update
    If .Left > 200 Then .Left = 1  ' Reset position
  End With
  GoTo repeat
End Sub

Private Sub CmdReset_Click()
  With VBAProject.Sheet1.Image1
    .Left = 0  ' Return to starting position
  End With
End Sub

Practical Applications

This technique can be adapted for:

Progress indicators for long-running macros
Interactive dashboard elements
Simple game mechanics
Visual demonstrations of concepts

Example 25.2: Vertical Object Movement

Modifying the horizontal movement code to create vertical animation:

Private Sub StartButton_Click()
repeat:
  With VBAProject.Sheet1.Image1
    .Top = .Top + 1    ' Move down by 1 pixel
    DoEvents
    If .Top > 200 Then .Top = 1  ' Reset position
  End With
  GoTo repeat
End Sub

Example 25.3: Diagonal Movement

Combine both properties for diagonal movement:

Private Sub StartButton_Click()
repeat:
  With VBAProject.Sheet1.Image1
    .Top = .Top + 5    ' Move down
    .Left = .Left + 5  ' Move right
    DoEvents
    ' Reset when reaching boundaries
    If .Top > 200 Then .Top = 1
    If .Left > 200 Then .Left = 1
  End With
  GoTo repeat
End Sub

25.3 Advanced Example: Creating a Digital Slot Machine

This comprehensive example demonstrates how to build an interactive slot machine simulation using Excel VBA:

Implementation Steps

Create a UserForm with three Label controls for the slots
Add a "Spin" button and a "Quit" button
Use the Rnd function to generate random numbers
Implement a loop with DoEvents for the spinning effect
Add win condition checks

Key Features

Random number generation (1-9)
Animation simulation through rapid updates
Jackpot detection logic
User feedback with message boxes

Private Sub CommandButton1_Click()
  Dim x As Integer
  x = 0
  
  Do
    x = x + 2
    ' Update slot displays with random numbers
    Label1.Caption = Int(Rnd * 9) + 1
    Label2.Caption = Int(Rnd * 9) + 1
    Label3.Caption = Int(Rnd * 9) + 1
    DoEvents
  Loop Until x = 10000
  
  ' Check for wins
  If (Label1.Caption = 7) And (Label2.Caption = 7) And (Label3.Caption = 7) Then
    MsgBox "Congratulations! You hit the Jackpot!", vbInformation, "Winner!"
  ElseIf (Label1.Caption = Label2.Caption) Or _
         (Label1.Caption = Label3.Caption) Or _
         (Label2.Caption = Label3.Caption) Then
    MsgBox "You hit the Mini Jackpot!", vbInformation, "Winner!"
  End If
End Sub

Key Takeaways

DoEvents is essential for smooth animation in VBA
Left and Top properties control object positioning
Loops create continuous movement while DoEvents allows screen updates
Random numbers can simulate slot machines and other game elements
Conditional logic enables win detection and game mechanics

🔗 Related Resources

❮ Previous Lesson