Visual Basic for Applications/Read Aloud Strings and Text


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 NotesEdit

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 CodeEdit

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