Making a Worksheet Talk Using Excel 2007 & VBA: Tech Tutorial

You might not have noticed, but Excel has a text-to-speech generator. You also may not have known that you can use VBA to control the output of the generator. This is a funny little tool to play around with and can bring excitement to your spreadsheets.

I often use this when developing analysis spreadsheets to be used in company sales meetings. It lightens the moods after my audience is about overload on numbers, statistics, and charts. Just imagine going over a sales projection when Microsoft’s usual synthesized voice comes on saying “$2,000 until sales’ goal reached!” A new type of enthusiasm comes over my audience.

The method that makes this possible is the Speak method. When applied to a range it will speak the cells in order by row or column. You can specify what direction you want the method to operate in by using:

expression.Speak(SpeakDirection)

This can only be used if it is applied to the ‘Range’ object.

The object that I will be dealing with in this article is the ‘Speech’ object. There are several different uses for it, and you can manipulate it in several different ways. In the example used for this article, we will first create a function that will define the Speak method. Then, we will pass the text that will be spoken to the method based on an expression.

First, the function:

Function TalkIt(txt)

Application.Speech.Speak (txt)

TalkIt = txt

End Function

If you have any programming or VBA experience, it isn’t too difficult for you to decipher exactly what the function is doing. If you are new to VBA, I will break it down for you. In the first phrase we declared a new function, named it TalkIt, and then stated that we will be passing a variable to it.

Function TalkIt(txt)

By doing this, you can’t call the function TalkIt without sending all the parameters to it. Then, we called on the Speak method by starting with the Speech property of the Application object.

Application.Speech.Speak (txt)

Finally, before closing the function, we set a value to the TalkIt function. This way when the function code executes, it will seek the value of the variable ‘txt’ before completing the code.

Now that the function is defined, we will pass the appropriate value to it to speak. You can call the function to execute many different ways. You can either do it in a certain area of the code or from a ‘click’ procedure of a button. In this example, I will have the spreadsheet compare a couple of cells and then use the TalkIt function from the click of a button.

Private Sub Button_1()

Dim intTotal As Integer

intTotal = WorksheetFunction.Sum(Cells.Range(“B3”, “B14”))

TalkIt (intTotal)

End Sub

Okay, now for some explaining. Using the ‘click’ event of the cmdCalculate button, we first defined the variable ‘intTotal’ as an integer. Then, we assigned the sum of the cells A1:A10 as the value of the variable. Finally, we called the TalkIt function we created earlier and passed the value of the intTotal variable to it. If you put numbers in your spreadsheet in all of the cells from A1 to A10, you will hear the voice say the total of the cells.

Another way you can do it is to have the synthesized voice say a phrase based on the calculations of the expression. It is fairly simple; all you have to do is add a couple lines of code to the cmdCalculations_Click sub procedure.

Private Sub cmdCalculate_Click()

Dim intTotal As Integer

‘declare a new variable to hold text

Dim txtTotal As String

intTotal = WorksheetFunction.Sum(Cells.Range(“A1”, “A10”))

‘use to the if…else statement to control the value of ‘txtTotal variable

If intTotal < 25000 Then

txtTotal = “Goal Not Reached”

Else

txtTotal = “Goal Reached”

End If

TalkIt (txtTotal)

End Sub

For the sample above, if the sum of A1:A10 is greater than 25000, you will hear the synthesized voice say, “Goal Reached”; if not, then it will say “Goal not reached”. Play around with the numbers in the cells to hear both of the different responses.

Play around with the Speak method to bring your Excel worksheet to life. If nothing else, while dealing with long and drawn-out code in the spreadsheet, you can have it notify you if the code was executed correctly.