Visual Basic for Applications/Read Aloud Strings and Text
Summary[edit | edit source]
This page contains Excel VBA code to read out the contents of strings; that is, text held in a string variable. It can be adapted for use elsewhere in MS Office.
Code Notes[edit | edit source]
Place the entire code listing into a Standard Module and save the file with an xlsm suffix. Run the various subs to see how the code works.
The VBA Code[edit | edit source]
Sub BasicExcelSpeech() 'Speaks the supplied string text in a default Excel voice 'Default voice is changed via Windows Control Panel 'Named Parameters of Speak(): 'Text: the text to read (Required) 'SpeakAsync:=0, waits until done, or with 1, code runs during play (Optional) 'SpeakXML:=0 , normal setting, or with 1, to ignore xml tags (Optional) 'Purge:=0 , normal play, or with 1, clears the present play (Optional) Application.Speech.Speak Text:="Hello", SpeakAsync:=0, SpeakXML:=0, Purge:=0 End Sub Sub testSpeakEachDigit() SpeakEachDigit "0123456789" End Sub Function SpeakEachDigit(sIn As String) As Boolean 'non API method 'uses excel's speak function to read a string, chara by chara 'one character at a time Dim n As Long, m As Long, sS As String Application.EnableSound = True For n = 1 To Len(sIn) DoEvents sS = Mid(sIn, n, 1) 'take one character Application.Speech.Speak sS, 0, 0, 0 Next n SpeakEachDigit = True End Function Sub testSetupSpeechVoice() 'Run this to test SetupSpeechVoice() Dim sTxt As String, nVoc As Integer, nSpd As Integer, nVol As Integer sTxt = "The quick brown fox jumps over the lazy dog 1234567890 times." nVoc = 1 'chosen voice 0 or 1 nSpd = 0 'speed of reading -10 to +10 nVol = 100 'volume level 0 to 100 SetupSpeechVoice sTxt, nVoc, nSpd, nVol End Sub Function SetupSpeechVoice(sText As String, Optional ByVal nVoices As Integer, _ Optional ByVal nRate As Integer, _ Optional ByVal nLoudness As Integer) As Boolean 'Selects voice using an index, rate of speech -10 to +10, 'and volume 0-100 for Speech.Speak() 'Needs a VBA editor reference to Microsoft Speech Object Library Dim voc As SpeechLib.SpVoice Set voc = New SpVoice 'avoid wrong choice of voice If nVoices > voc.GetVoices.Count - 1 Or nVoices < 0 Then MsgBox "Voice integer is out of range" Exit Function End If With voc Set .Voice = .GetVoices.Item(nVoices) .Rate = nRate .Volume = nLoudness .Speak sText End With SetupSpeechVoice = True End Function Sub ListAvailableVoices() 'run this to know the id of the available voices 'Needs a VBA editor reference to Microsoft Speech Object Library Dim n As Integer, sAccum As String Dim voc As SpeechLib.SpVoice Set voc = New SpVoice For n = 0 To voc.GetVoices.Count - 1 Set voc.Voice = voc.GetVoices.Item(n) sAccum = sAccum & " " & n & " - " & voc.Voice.GetDescription & vbCrLf voc.Speak "My voice index number is " & CStr(n) Next n MsgBox sAccum End Sub