Preliminary version (dated 9 October 2016)

Worksheet animation with audio


About the animation


The sample file is based on an exercise for the Excel OFFSET function, using both single cell, and multi-cell reference parameters.


To view the animation


  1. Download the xlsm file. There is one worksheet Sheet1
  2. Complete the exercise if necessary
  3. Click on the "Tick for Answer:" box (cell J1) to view the hidden solutions in columns G:I
  4. This will also expose the "Watch the MC8 Excel animation" button in rows 47:50

Animation MC8 - the VBA code



Code 1: Sub procedure AnimationMC8 provide an explanation of the answer to multi-cell offset question
' Module: [Sheet1 (Code)]
Private Sub AnimationMC8()
Dim r%, c%, h%, w%, i%
Dim ref As Range
Dim AppS As Speech
Dim Starttime As Date, endtime As Date

Starttime = Time

Set AppS = Application.Speech
Set ref = Range("Sheet1!C31:C35")

On Error GoTo ErrHandler:

' Syntax: OFFSET(reference, rows, cols, [height], [width])
' Animation Duration: 00:01:30

    Sheet1.Range("A26").Select
    ActiveWindow.ScrollRow = ActiveCell.Row

' Reset - in case ESC pressed
    With Range("I45")
        .Interior.Color = xlNone
        .Font.ColorIndex = xlAutomatic
        .Font.Bold = False
    End With
    Call UnloadMC8

    AppS.Speak ("This xlf animation, explains the answer to Exercise MC8.")
    Call ShowMC8

    AppS.Speak ("Located in row 45 of the worksheet")

        Range("I45").Interior.Color = 65535 ' Yellow
            Application.Wait Now + TimeValue("00:00:01")
        Range("I45").Interior.Color = xlNone
            Application.Wait Now + TimeValue("00:00:01")
        Range("I45").Interior.Color = 65535
    AppS.Speak ("The offset function has five arguments as shown in the on screen dialog box")
    AppS.Speak ("Reference, Rows and Columns, are compulsory.")
    AppS.Speak ("Height and width, in the square brackets, are optional.")

    AppS.Speak ("Now refer to the formula in cell I 45. It is highlighted in yellow.")

        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            With .Characters(31, 2) ' rows
                 .Font.FontStyle = "Bold"
                 .Font.Color = -16775961
            End With
        End With

    AppS.Speak ("The second argument, the offset row is -1 with respect to the reference argument.")

        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            With .Characters(34, 2) ' cols
                 .Font.FontStyle = "Bold"
                 .Font.Color = -16775961
            End With
        End With

    AppS.Speak ("The offset column is also -1 with respect to the reference argument.")

        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            With .Characters(37, 2) ' height
                 .Font.FontStyle = "Bold"
                 .Font.Color = -16775961
            End With
        End With

    AppS.Speak ("The offset height -2. That is, 2 rows up..")

        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            With .Characters(40, 2) ' width
                 .Font.FontStyle = "Bold"
                 .Font.Color = -16775961
            End With
        End With

    AppS.Speak ("And the offset width is -2, this means, two columns left.")

    Call UnloadMC8
            Application.Wait Now + TimeValue("00:00:02")

        ' clear red bold text
        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
        End With

    AppS.Speak ("I will now explain each of the parameters with respect to the reference range, G31 to G35.")
            Application.Wait Now + TimeValue("00:00:01")

        For i = 7 To 10
            ref.Offset(r, c).Borders(i).Weight = xlMedium
            ref.Offset(r, c).Borders(i).Color = RGB(255, 0, 0)  ' Red
            ref.Offset(r, c).Borders(i).LineStyle = xlContinuous
        Next i

    AppS.Speak ("Offset argument 1 is the reference, as shown by the range with the red border")

        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            With .Characters(19, 11) ' reference
                 .Font.FontStyle = "Bold"
                 .Font.Color = -16775961
            End With
        End With

            Application.Wait Now + TimeValue("00:00:02")

        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            With .Characters(31, 2) ' rows
                 .Font.FontStyle = "Bold"
                 .Font.Color = -16775961
            End With
        End With

    AppS.Speak ("Offset argument 2 is row -1, so the offset is one row up")

' rows ===
        For i = 7 To 10
            ref.Offset(r, c).Borders(i).LineStyle = xlNone
        Next i
            r = -1
        For i = 7 To 10
            ref.Offset(r, c).Borders(i).Weight = xlMedium
            ref.Offset(r, c).Borders(i).Color = RGB(255, 0, 0)
        Next i

            Application.Wait Now + TimeValue("00:00:02")

    AppS.Speak ("Offset argument 3 is column -1, so offset now moves one column left")

        With Range("I45")
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            With .Characters(34, 2) ' cols
                 .Font.FontStyle = "Bold"
                 .Font.Color = -16775961
            End With
        End With

' columns ===
        For i = 7 To 10
            ref.Offset(r, c).Borders(i).LineStyle = xlNone
        Next i
            c = -1
        For i = 7 To 10
            ref.Offset(r, c).Borders(i).Weight = xlMedium
            ref.Offset(r, c).Borders(i).Color = RGB(255, 0, 0)
        Next i
            Application.Wait Now + TimeValue("00:00:02")

    AppS.Speak ("The area with the red border is one row up and one column to the left of the green shaded reference")
    AppS.Speak ("Notice that the offset region has retained the dimensions of the reference, 5 rows by 1 column")
    AppS.Speak ("This offset region will now be resized to a height of -2 and a width of -2")
            ref.Offset(r, c).Resize(1, 1).Interior.Color = RGB(255, 165, 0) ' Orange
    AppS.Speak ("The top cell of the offset region, shown in orange color, will be the bottom cell after height -2 has been applied ")

    AppS.Speak ("Let's adjust the height first. This the the 4th argument. A negative value extends the offset in an upwards direction.")
            ref.Offset(r, c).Resize(1, 1).Interior.Pattern = xlNone

' height ===
        For i = 7 To 10
            ref.Offset(r, c).Borders(i).LineStyle = xlNone
        Next i
           h = -2
        For i = 7 To 10
            ref.Offset(r, c).Range("A1").Offset(h + 1, 0).Resize(Abs(h)).Borders(i).Weight = xlMedium
            ref.Offset(r, c).Range("A1").Offset(h + 1, 0).Resize(Abs(h)).Borders(i).Color = RGB(255, 0, 0)
        Next i
            Application.Wait Now + TimeValue("00:00:02")

' width ===
    AppS.Speak ("Offset argument r is width -2, so offset now extend one column left to form a 2 column region")
        For i = 7 To 10
            ref.Offset(r, c).Range("A1").Offset(h + 1, 0).Resize(Abs(h)).Borders(i).LineStyle = xlNone
        Next i
           w = -2
        For i = 7 To 10
            ref.Offset(r, c).Range("A1").Offset(h + 1, w + 1).Resize(Abs(h), Abs(w)).Borders(i).Weight = xlMedium
            ref.Offset(r, c).Range("A1").Offset(h + 1, w + 1).Resize(Abs(h), Abs(w)).Borders(i).Color = RGB(255, 0, 0)
        Next i
            Application.Wait Now + TimeValue("00:00:02")

    AppS.Speak ("The upper left cell has value 1, and the lower right cell has value 12")
            Application.Wait Now + TimeValue("00:00:02")

ErrHandler:
    AppS.Speak ("This excel animation was brought to you by excel at finance dot com.")
            Application.Wait Now + TimeValue("00:00:01")
    AppS.Speak ("Spoken by your microsoft windows voice. Thank you for watching.")
            Range("A29:E38").Borders.LineStyle = xlNone
            With Range("I45")
                .Interior.Color = xlNone
                .Font.ColorIndex = xlAutomatic
                .Font.Bold = False
            End With
            Call UnloadMC8

    endtime = Time
    Debug.Print "Duration: " & Format(Starttime - endtime, "hh:mm:ss")

End Sub