Visual Basic for Applications/Read Aloud Strings and Text

From Wikibooks, open books for an open world
Jump to navigation Jump to search

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