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

#stayhome

With the coronavirus crisis, like everyone, I stay at home. But after a couple of weeks, watching TV is a waste of time. Also, my scale ⚖️ tells me I need to exercise. But what can I do in my room? 🤨

Then, I remembered that an Excel feature allows you to make the application talk 😮And I had the idea to use Excel as a sports coach 😉. This is what I'm going to show you in this article.

Video to use Excel as a 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 can Excel help?

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

1. Create a random number between 1 and 4
2. Convert this number to a string
3. Ask Excel to pronounce this word
4. 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 to 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 instructions.

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

Repeat the sequence several times.

We must insert these command lines in a For ... Next loop to repeat the sequence of commands.

Copy the full code

Here is the full program code.

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

Related Articles

• Sudoku Solver with Excel
• Save Data Automatically with Excel Calendar