 # Excel as a sports coach

Last Updated on 28/11/2021 by Frédéric LE GUEN

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

### 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.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
1. Paste the code in the module
1. 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

# Excel as a sports coach

Last Updated on 28/11/2021 by Frédéric LE GUEN

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

### 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.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
1. Paste the code in the module
1. 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