# The Talking Worksheet

Most people probably know about this, but I just discovered it today. You can use Excel’s Speak method in a function. Simple example:

Function SayIt(txt)
Application.Speech.Speak (txt)
End Function

Then you can write a formula that vocalizes the contents of a cell whenever the sheet is calculated. Like:

=SayIt(A20)

Or, add a condition using and IF Function. In this case, you hear the lovely voice only if the sum of the values in column A exceed 25,000:

=IF(SUM(A:A)&gt;25000,SayIt(&quot;Goal Reached&quot;))

It’s possible that there are some very good uses for this. But I can’t think of any. But it’s kind of fun to play around with.

By the way, Excel’s text-to-speech feature first appeared in Excel 2002, so this won’t work with earlier versions.

Posted in Uncategorized

## 25 thoughts on “The Talking Worksheet”

1. Mpemba says:

>It’s possible that there are some very good uses for this.
>But I can’t think of any.
I use it for double-checking columns of figures I’ve just typed in.
That way you just read down the table of data while Excel reads the numbers to you. It’s much easier than looking back and forward.

A little routine I used a while ago (for learning my lines in drama :o)
On my PC there are only 3 voices (one female, two male).
This routine allows you to vary speed (rate) and volume of the string (Words) argument and even choose which of the three voices to use.

‘Values for the Rate property range from -10 to 10
‘Values for the Volume property range from 0 to 100

Well, it works for me!

Sub Test()
SaySomething “Hello”, “Him”, 10, 100
SaySomething “Hello”, “Her”, 10, 100
SaySomething “Hello”, “None”, -5, 100
End Sub

Sub SaySomething(Words as string, Person as string, Rate as long, Volume as long)
Dim Voc As SpeechLib.SpVoice
Set Voc = New SpVoice

With Voc
Debug.Print .GetVoices.Count

If Person = “Him” Then
Set .voice = .GetVoices.Item(0) ‘LH Michael
ElseIf Person = “Her” Then
Set .voice = .GetVoices.Item(1) ‘LH Michelle
Else
Set .voice = .GetVoices.Item(2) ‘Microsoft Sam
End If
.Rate = Rate
.Volume = Volume
.Speak Words
End With
End sub

2. Charlie III says:

I use the Excel Speak all the time. Since our office has gone papaerless, I highlight a row of numbers, click the button and listen while a voice tells me what I have entered. It is a check to verify the numbers I entered into the spreadsheet are correct. Before, when checking, I would always have to lift my head up to look at the monitor and down to look at the paper, up down up down. I was getting whiplash and eye strain. This feature saves on up down time and medical bills. The downside, the feature is a little slow and the voice will not tell you if the number is negative. Would a function overcome these issues? If so I would then use your function or modify to Speak on a selection. I would then use all the time.

3. Haffy says:

Thanks, J-Walk – that’s provided some entertainment!

And nice use, Charlie III – yes, it’s a bit slow, but it could be really useful

4. Mpemba says:

>The downside, the feature is a little slow and
>the voice will not tell you if the number is negative
On my PC (Excel 2002) -5 was read out as “Minus five” so it does deal with negative numbers.

Mpemba

5. Brett says:

Charlie,

The “minus” is dependent on the format for me; negative as () results in no “minus.”

6. How did they get Stephen Hawking to record all that?

7. It looks like the functions on the Text to Speech toolbar are only available on that toolbar. That is, they’re not on any menu. That seems odd to me.

8. Brett says:

DK,

I right clicked on the main menu, cutomize…commands dialogue,Tools category. I dragged the “speak cells” icon to the main menu.

Brett

9. Kruncher says:

Each week I run a lengthy process which generates a series of project reports.

Because I want to leave it alone to run and do other things, but also want to know how far along it is, I programmed it to speak at the completion of each report generation.

Now I just turn up the speakers a bit and listen for “Report 9999 May 11 2006 is complete”.

I also programmed a wizard form to appear when a workbook containing a collection of forms is opened. There’s a picture of a wizard on the form that speaks to the user asking “What do you want to do today?” It gets a laugh out of the users and seems to make them more comfortable with using the finance forms.

Kruncher

10. Now that’s a good idea, Kruncher. Hopefully, I’ll remember to use it.

Dick, my voice is a very sexy female and she sounds nothing like S. Hawking.

11. Kruncher says:

@ Mike, OMG that’s hilarious.

@ John, thanks very much. I’ll take that as high praise coming from you. I’ve just spent the last couple of months pouring over your 2003 Power Programming book. As I’m sure you’ve heard a million times, it’s a fantastic resource. Thanks for it.

Kruncher

12. Brett says:

Mpemba,

I get a BY REF error running Test(). What’s this mean?

Thanks,
Brett

13. Try setting a reference to the Microsoft Speech Object Library.

14. fzz says:

Make SayIt return TRUE, and use it for conditional formatting formulas. E.g., for cell A1,

=IF(ISBLANK(\$A\$1),SayIt(“Fill in A1, you maizy lore-on!”))

Would this be more annoying than flashing text?

15. MacroMan says:

I try to pick up chicks with this in StarBucks. Program it to say a few words to a chick near by, hehe, yup gotta try every angle.

16. Brett says:

Mike,

I found the library reference. It still returns “Compile error: ByRef argument type mismatch” This is a lot of fun, though.

17. You’ll have to retype in the quotes (“). That will fix your type mismatch error

18. Mpemba says:

>Mike Alexander Says:

>Try setting a reference to the Microsoft
>Speech Object Library.

Sorry, I forgot to mention that.
I just ASSumed that anyone capable of using VBA knew about setting references :o)

>You’ll have to retype in the quotes (“).
>That will fix your type mismatch error

Ah, that one. Yes, the code I posted was straight out of a working module but some of the punctuation gets translated in the Blog. Why is that?

Hahahah

OH, I noticed you used doevents.
I had those in too but deleted them (along with other peripheral stuff). They are useful for interrupting the routine.

M

19. Brett says:

Mike,

Thanks! That gets it further before hitting a comile error.

Mpemba,

Yep, I’m not a programmer. That’s why I don’t know what the next error means “Compile error: Method or data member not found” After OK-clicking, the SaySomething sub is highlighted in yellow and the debug.print.GetVoices is in blue. I’m probably in too deep on this one, I’ll head back to the beach. Thanks for taking me this far!

Brett

20. Phung says:

Thanks, It’s great.

Sub Test()
SaySomething “Hello”, “Him”, 10, 100
SaySomething “Hello”, “Her”, 10, 100
SaySomething “Hello”, “None”, -5, 100
End Sub

Sub SaySomething(Words as string, Person as string, Rate as long, Volume as long)
Dim Voc As SpeechLib.SpVoice
Set Voc = New SpVoice

With Voc
Debug.Print .GetVoices.Count

If Person = “Him” Then
Set .voice = .GetVoices.Item(0) ‘LH Michael
ElseIf Person = “Her” Then
Set .voice = .GetVoices.Item(1) ‘LH Michelle
Else
Set .voice = .GetVoices.Item(2) ‘Microsoft Sam
End If
.Rate = Rate
.Volume = Volume
.Speak Words
End With
End sub

Thanks, It’s great.

21. David Hager says:

This technique used to work on my computer with three voices. Now I can only get one to work. Any ideas?

22. Razor says:

David,
I imagine you’ve got an updated operating system and now only have one voice. Windows 7 only has one.

Posting code? Use <pre> tags for VBA and <code> tags for inline.