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
- Download the xlsm file. There is one worksheet Sheet1
- Complete the exercise if necessary
- Click on the "Tick for Answer:" box (cell J1) to view the hidden solutions in columns G:I
- 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
- This example was developed in Excel 2016 Pro 64 bit.
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]