Excel can be your new sports coach because Excel can talk 😀😊

#stayhome

With the coronavirus crises, like everyone, I stay at home. But after a couple of weeks, watching TV is clearly a waste of time. And also my scale tells me that I need to do some exercises. But what can I do in my room ? 🤨

And then, I remember that an Excel feature allows you to make the application talk 😮 and I had the idea to use it as sports coach 😉. This is what I going to show you in this article.

Video to use Excel as coach

The purpose of the exercise is very useful for tennis players 🥎 or ping-pong players 🏓 . You have to touch the glass number when the computer says the number.

How Excel can help?

To use Excel as sports coach, I need to write a program (a macro) to state randomly a number between 1 and 4. For each value, Excel will say the number.

The program is based on only 4 concepts

• Create a random number between 1 and 4
• Convert this number to a string
• Ask Excel to pronounce this word
• Repeat the sequence X times

Manage a random number

The Rnd instruction (for random) will create a random number between 0 and 1 (1 excluded). Now, by multiplying the result of Rnd by 4, we will have a number between 0 and 3.99999

Then, with to the Int  instruction (for Integer), we will only keep the integer part of the random number. So we return 0, 1, 2 or 3. And if we add the value 1 to the result obtained, then we return a value between 1 and 4 .

RndNumber = Int (Rnd () * 4) + 1

Specificity of random numbers in VBA

However, the instruction Rnd is not sufficient. In VBA, you must "force" the random instruction to be recalculate. Otherwise, there is a big change to repro the same sequence many time.

To avoid this, you must add the Randomize instruction in your code.

Convert numbers to strings

Now it's important to convert the number into a string because the command which makes Excel speak must have a string as argument and not a number.

To do this, we will use the CHOOSE function of Excel in the VBA program like this

Application.WorksheetFunction.Choose ( RndNumber , "one", "two", "three", "four")

The function will evaluate the random number and then return the string according the value of the number.

Now, Excel must speak

The method to pronounce a sentence is

Application.Speech.Speak "your text"

Introduce a time delay

To be able to do the exercise, it's important to introduce a time delay of 1 second to give time to reach the glass 😉 In VBA, this can done with the following instruction

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

Repeat the sequence several times

To repeat the sequence of command, we must insert these command line in a For ... Next loop.

Copy the full code

Here is the full code of the program.

Sub Sport()
Dim i As Long
Dim RndNumber As Long
Dim TextNumber As String
Dim NbTime As Long
    NbTime = 10  'Change the number of loops
    Application.Speech.Speak "Ready?"
    Application.Wait (Now + TimeValue("00:00:02"))
    Application.Speech.Speak "Go"
    For i = 1 To NbTime 
        Randomize
        If i = NbTime Then
            Application.Speech.Speak "Last One"
        End If
        RndNumber = Int(Rnd() * 4) + 1
        TextNumber = Application.WorksheetFunction.Choose(RndNumber, "one", "two", "three", "four")
        Application.Speech.Speak TextNumber
        Application.Wait (Now + TimeValue("00:00:01"))
    Next
End Sub

How to use the code in Excel?

To make this code usable, you must copy the code in the Visual Basic Editor.

1. Open a new Excel workbook
2. Open the visual basic editor with the keyboard shortcut Alt + F11
3. Add a new module

4. Paste the code in the module

5. Close the Visual Basic Editor

Run the macro

1. From Excel, press the Alt + F8 keys
2. Press the Run button

Avoid 2 times the same number

If you don't want to have 2 times the same number in a row, we must introduce another there to check the current value and the previous ont. Here is the code

Sub Sport() Dim i As Long Dim RndNumber As Long Dim TextNumber As String Dim NbTime As Long Dim PreviousValue As Long NbTime = 10 'Change the number of loops Application.Speech.Speak "Ready?" Application.Wait (Now + TimeValue("00:00:02")) Application.Speech.Speak "Go" For i = 1 To NbTime Randomize Do RndNumber = Int(Rnd() * 4) + 1 Loop While PreviousValue = RndNumber PreviousValue = RndNumber TextNumber = Application.WorksheetFunction.Choose(RndNumber, "one", "two", "three", "four") Application.Speech.Speak TextNumber Application.Wait (Now + TimeValue("00:00:01")) Next End Sub