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
Else
Set .voice = .GetVoices.Item(2) ‘Microsoft Sam
End If
.Rate = Rate
.Volume = Volume
.Speak Words
End With
End Sub
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
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,
Brett
Sorry…That would be the compile error “method or data member not found”
Brett
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.
Brett,
I bet you need to also fix the single quotes before each comment (i.e. ‘Microsoft Sam)
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.
Mike,
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?
Thanks,
Brett
Not possible. Make sure your Tools > References looks like this
http://www.dailydoseofexcel.com/blogpix/speechref.gif
or similar. I just pasted it into 2000 and 2003 and it worked without issue. What version are you using?
2000, service pak 4 is the OS. I think Excel 2000. Thanks for the graphic. It looks just like it. I’ll try again.
Brett
Same error. I didn’t have to replace the quotes to get past the “by ref argument mismatch” though.
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
and
debug.print Voc.GetVoices.count
will fail too
M
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.
Thanks,
Brett
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!
jt
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?
Thanks
EM
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.
Brett
>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
M
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.
Nat: Use