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)>25000,SayIt("Goal Reached"))
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.
Mpemba:
>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
11 May 2006, 1:17 amSet .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
Charlie III:
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.
11 May 2006, 5:40 amHaffy:
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
11 May 2006, 5:55 amMpemba:
>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
11 May 2006, 6:07 amBrett:
Charlie,
The “minus” is dependent on the format for me; negative as () results in no “minus.”
11 May 2006, 6:52 amDick Kusleika:
How did they get Stephen Hawking to record all that?
11 May 2006, 7:17 amDick Kusleika:
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.
11 May 2006, 8:21 amBrett:
DK,
I right clicked on the main menu, cutomize…commands dialogue,Tools category. I dragged the “speak cells” icon to the main menu.
Brett
11 May 2006, 8:41 amKruncher:
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
11 May 2006, 10:32 amJohn Walkenbach:
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 May 2006, 11:01 amMike Alexander:
Thanks to John, I’ve wasted an hour of company time putting this together.
Excel Does Seinfeld!
Download it here.
www.datapigtechnologies.com/downloads/Excel_does_Seinfeld.xls
11 May 2006, 12:04 pmKruncher:
@ 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
11 May 2006, 12:42 pmBrett:
Mpemba,
I get a BY REF error running Test(). What’s this mean?
Thanks,
11 May 2006, 1:20 pmBrett
Mike Alexander:
Try setting a reference to the Microsoft Speech Object Library.
11 May 2006, 1:49 pmfzz:
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?
11 May 2006, 1:53 pmMacroMan:
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.
11 May 2006, 4:33 pmBrett:
Mike,
I found the library reference. It still returns “Compile error: ByRef argument type mismatch” This is a lot of fun, though.
11 May 2006, 6:47 pmMike Alexander:
You’ll have to retype in the quotes (”). That will fix your type mismatch error
11 May 2006, 6:51 pmMike Alexander:
I don’t know why I’m having so much fun with this.
This is one of the funniest things I’ve put together!
Download and play the script.
http://www.datapigtechnologies.com/downloads/Excel_Knock_Knock.xls
11 May 2006, 8:03 pmMpemba:
>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?
>Download and play the script.
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
12 May 2006, 12:39 amBrett:
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
12 May 2006, 7:40 amHarald Staff:
This makes Excel do the Rainman routine:
14 May 2006, 5:14 amhttp://www.phoenix5.org/humor/WhoOnFirstTEXT.html
Phung:
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.
11 November 2007, 10:36 am