# Excel as a sports coach

Last Updated on 04/05/2024

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 .

### 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

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

### 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.

### 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.

## 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

# Excel as a sports coach

Last Updated on 04/05/2024

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 .

### 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

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

### 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.

### 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.

## 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