Visual Basic for Applications/Read Aloud Strings and Text

Summary

edit

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

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