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.