Solution - Experienced Challenge 7: It’s Showtime!
This was an interesting challenge for us. We knew that there was no simple, straightforward way to implement a countdown timer in Microsoft Office PowerPoint. In addition, we assumed that there was a way to implement a countdown timer in Microsoft Office Excel; however, we weren’t positive that this could be done. So did we make sure and research this issue, and actually create a countdown timer, before we added this challenge to OfficePalooza? Boy, you guys don’t know us very well, do you?!?
The truth is, we were advertising this challenge long before we verified that you actually could create a countdown timer in Excel. Is that because we’re such experts at Visual Basic for Applications that it was 100% guaranteed that we would find a way to do this? Well, seeing as how you don’t know us very well, yes, yes it was ….
At any rate, whether it was through programming skill – or sheer dumb luck – we managed to come up with something that did the trick:
Sub StartCountdown()
On Error Resume Next
strPath = ActiveWorkbook.Path
strFile = strPath & "\experienced_challenge_7.pptx"
For i = 59 To 0 Step -1
x = i
If x < 10 Then
x = "0" & x
End If
Application.Cells(8, 3) = "0:" & x
DoEvents
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
DoEvents
Next
Application.Speech.Speak "It's showtime."
Set objPowerPoint = CreateObject("PowerPoint.Application")
Set objPresentation = objPowerPoint.Presentations.Open(strFile, False, False, False)
objPresentation.SlideShowSettings.Run.View
End Sub
This particular subroutine starts off in somewhat boring fashion. Part of the challenge was to launch a PowerPoint presentation – Experienced_challenge_7.pptx – in slide show mode. In order to do that we needed to know the path name to the .PPTX file. Because the file was stored in the same folder as our Excel file that was easy: all we had to do was determine the Path of the active workbook. The gives us the folder path (for example, C:\OfficePalooza). We then simply tacked the name of the PowerPoint presentation onto the end of that folder path. All that gets taken care of in these two lines of code:
strPath = ActiveWorkbook.Path
strFile = strPath & "\experienced_challenge_7.pptx"
Like we said, a little boring. But something that had to be done.
Things get a little spicier, and a little more interesting, after that. Our next step was to create a For Next loop that runs from 59 to 0 (that is, 59-58-57-56, etc.). We did that because our subroutine has to countdown from 60 seconds (1 minute) to 0 seconds. This seemed like an easy way to do that, even if that makes this a subroutine that works only for this particular challenge. (Or for any other presentation that requires a 60-second countdown.) Is that cheating? No, not really. After all, all’s fair in love. And in OfficePalooza.
After we set up the For Next loop we then execute these lines of code:
x = i
If x < 10 Then
x = "0" & x
End If
In line 1, we simply assign the current value of the counter variable i to a variable named x. (And yes, we also knocked ourselves out trying to come up with creative and clever variable names.) We then check to see if x is less than 10; if it is, we tack a 0 (zero) onto the front of the value.
Why in the world did we do that? Well, again, we’re striving for simplicity here. Rather than do some fancy formatting of our countdown values we decided to simply use the numbers as is: 59 is 59, 43 is 43, and 11 is 11. The only problem with that occurs when we hit single digits: after all, we want the seconds displayed as 09 and 08, not as 9 and 8. As you might have guessed, that’s why stick a 0 on the front of the single-digit numbers.
After we have the number we then use this line of code to update the onscreen countdown:
Application.Cells(8, 3) = "0:" & x
And once we’ve done that, we call the DoEvents function; that helps ensure that the screen gets updated even while our subroutine continues to run. Without DoEvents the subroutine would work: it would count down from 59 to 0 and then open the PowerPoint presentation. However, you wouldn’t see the countdown to take place on screen; in fact that screen wouldn’t change at all.
And that didn’t seem like a very interesting countdown, to say the least.
The first time through the loop that means we’ll set the onscreen countdown to 0:59. That’s great, except for one thing: now what? Well, now we need to wait one second. As it turns out, there are several different ways to pause an Excel subroutine for a set amount of time. We decided to use this way:
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
So what are we doing here? Well, in the first two lines we’re assigning the current hour and the current minute to a pair of variables; in line 3, we assign the current second plus 1 to a third variable. Why do we do that? Well, suppose the time is exactly 8:00 AM (8 hours, 0 minutes, and 0 seconds). In the fourth line above we then create a new time using the TineSerial function. And what is that new time: it’s one second after 8:00 AM (8 hours, 0 minutes, 1 second). That turns out to be exactly 1 second after the current time.
And that’s no coincidence: in the last line we use the Wait method to pause our subroutine until the time reaches 8 hours, 0 minutes, and 1 second. In other words, the script is going to wait 1 second and then resume. The net effect? We’ve paused the script for 1 second.
And after that 1-second pause is over we call DoEvents again, and then zip back to the top of the loop and do the whole thing all over again.
Sooner or later our counter variable will be equal to 0 and we’ll exit our For Next loop. When that happens the first thing we do is announce – through the computer speakers – that it’s showtime. That’s something we can do by calling the Speak method, a method that will read – out loud – any value that’s passed to it (one common use of this method is to read the contents of a spreadsheet aloud to users who might have a visual impairment):
Application.Speech.Speak "It's showtime."
Of course, if it is showtime that means that we need to start the PowerPoint presentation. That’s what this block of code is for:
Set objPowerPoint = CreateObject("PowerPoint.Application")
Set objPresentation = objPowerPoint.Presentations.Open(strFile, False, False, False)
objPresentation.SlideShowSettings.Run.View
In the first line we create an instance of the PowerPoint.Application object, and in line 2 we use the Open method to open Experienced_challenge_7.pptx. (As you might recall, the full path to that file is stored in the variable strFile.) Last, but surely not least, we call the View method (part of the slideShowSettings.Run option) to start the presentation in slide show mode.
Surprised that we were able to come up with a solution to this challenge? Trust us: you aren’t half as surprised as we were!