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.

Posted in Uncategorized

25 thoughts on “The Talking Worksheet

  1. >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. 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. 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. >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. Charlie,

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

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

  7. DK,

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

    Brett

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

  9. @ 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

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

  12. Mike,

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *