Mpemba Speech Code

I don’t know why code is rendered so poorly in the comments of this blog. I wish I had the smarts to modify iGSyntax Hiliter to work in the comments section, but I don’t. Here’s the code that Mpemba posted in a comment to John’s Speech post. You should be able to copy it from here and paste it into the VBE without issue. Don’t forget to set a reference to Microsoft Speech Library (Tools > References).

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
            Set .voice = .GetVoices.Item(2) ‘Microsoft Sam
       End If
        .Rate = Rate
        .Volume = Volume
        .Speak Words
    End With
End Sub
Posted in Uncategorized

16 thoughts on “Mpemba Speech Code

  1. DK,

    Same compile error. It seems to stop at the debug.print.getVoices. The Speech object library reference is checked. There is a priority order in the References-VBA Project dialogue. Could this be out of order?
    If I try to run the SaySomething sub instead of the Test sub, I get a “Macros” dialogue that appears to want me to assign another macro to this subroutine. What does the debug.print line do?

    Thanks for being so patient,

  2. Sorry…That would be the compile error “method or data member not found”


  3. Brett: Make sure there’s a space between Debug.Print and .GetVoices.Count

    Or just delete that line entirely. It prints the number of voices to the immediate window. It’s not germain to the code anyway.

  4. DK,

    The space is there. If I remove it, the VBE puts it back. I deleted the line. Now it stops at Set .voice = .GetVoices and returns the same error while highlighting the .GetVoices.


    The comments were in green, but I replaced apostrophe anyway. No change.

    If I type ?speechLib.SpVoice.GetVoices.count in the immediate window, I get the same compile error, “Method or data member not found”

    I don’t think the GetVoices is a valid method for my computers. Is that possible?


  5. 2000, service pak 4 is the OS. I think Excel 2000. Thanks for the graphic. It looks just like it. I’ll try again.


  6. Same error. I didn’t have to replace the quotes to get past the “by ref argument mismatch” though.

  7. Just a thought Brett:
    Do you get “Voices” from Excel when you set it to read a range back to you?

    If no voice files are installed on your system then
    Voc.GetVoices will not exist
    debug.print Voc.GetVoices.count
    will fail too


  8. Mpemba,

    Yes. I’ve put the speak cells icon on the main toolbar. And the SayIt function from John W. works. I sure would love to get to play with a different voice, though. I’ll keep at it.


  9. I only tripped over this feature in Excel today while looking for another Applications method.
    Thanks to you guys for having already answered all my questions.
    I definitly have uses for this to hassle users who insist on leaving required fields empty!


  10. I cannot find the the library in my Tools/References. All I have is:

    Microsoft Direct Speech Recognition
    Microsoft Direct Speech Synthesis

    I am running Excel 2002 with SP3.

    What am I missing?



  11. ExcelMonkey

    I had to install the text-to-speach feature. I did it once by trying to show the greyed-out “text to speech” toolbar and once (another machine) by trying to call the SayIt UDF from John W. Perhaps it’s not available because you need to install it?? Just a guess.


  12. >I cannot find the the library in my Tools/References.
    >All I have is:

    On my PC (XP SP2; Excel 2002 the reference is set to:

    Microsoft Speech Object Library
    C:Program FilesCommon FilesMicrosoft SharedSpeechsapi.dll

    For that matter I don’t see either of:
    >Microsoft Direct Speech Recognition
    >Microsoft Direct Speech Synthesis


  13. I wrote a macro that does two things.

    1) Pulls down data from an IBM reflection program
    2) Manipulates that data in Excel, then turns it into a pretty pivot table.

    One Command I must use once I get the Data into excel is the Left Function for example
    Left (“dddd”, 2) ‘ returns dd

    However, when I reference the IBM reflection program (early binding I think) the IBM reflection program also has a left function, which does something different. So when I try to manipulate the Work sheet it uses the wrong left function. I put the IBM reflection object 8.0 as the lowest priority but it still uses the wrong function. Why isn’t it using the correct Function?

    I have been manually removing the reference from the worksheet then running the second macro. However the macro is useful and other people want to use it. I tried explaining how to change the reference and I get blank stares. This is OK as I always get blank stares but they are talking about putting me in charge of the Data now. If I cant fix this I will be forced the break my code and claim it doesn’t work anymore, due to an upgrade in the system, as I don’t want to spend an hour pulling down data every day.

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

Leave a Reply

Your email address will not be published.