Wikibooks

Visual Basic for Applications/Play a WAV File from VBA

< Visual Basic for Applications

Summary

edit
  • This code module plays sounds in Excel VBA using existing .wav files. Many such files can be found in the C:\Windows\Media folder, and can be made from text with a suitable VBA procedure.
  • The procedure used is an API. This API code has been tested for Win10 64 bit with Excel 2019 64 bit, and works well.

Notes on the Code

edit
  • The most general version that applies to all VBA uses an API function sndPlaySound. The code plays audio (WAV) files via the user's speakers as opposed to any internal sounders. Files must already exist on the user's PC for any sound that is played. In Excel, and when the user just needs to play strings, then the built-in Speak function is more convenient.
  • Notice that sndPlaySound makes use of a wait parameter. When assembling discrete sounds that are not intended to overlap, the function can be made to play its sound before allowing the next line of code to run. Conversely, to overlap a new note with the tail of an old note, as in the playing of piano keys, the next code line is allowed to run before the sound has completed. Setting the Wait parameter to False will cause the code to run on before completion.
  • Libraries of wav sound files can be downloaded from internet pages. They are generally organized for download in zipped files by category. That is to say, a set of spoken numbers, spoken letters, piano keys etc. If the ideal set is not available then there are several free text to wav file applications on the internet that can be downloaded and that allow sound files to be made from typed text, Readers might care to comment in the My discussion page about high quality file sets.
  • Quite complex constructions can be made by playing sound files in sequence. Possible applications include audio advice for the progress of long procedure runs, or sound punctuation of the type already used by Microsoft for the opening and closing of processes. Some individuals construct entire piano keyboards on user forms, complete with flats and sharps.
  • Readers who intend to use integers or letters for playback as a sequence might be interested to know:
    • The task of code writing is greatly simplified if the sound files' names start with the characters that they represent. For example, a file that expresses the sound for one is best called 1.wav, three as 3.wav, and similarly with the letter set. The reason is that a string character from code can be used to construct the name of the file to call. This allows single line calls in loops at times, compared to the accessing of lists of elaborately named files. See the procedure ReadIntegers for the method to use. Clearly, without the wav file set that goes with it, this procedure is of limited use to the reader.
    • The lead in and lead out times of sound files can make for a fairly halting delivery. To get around this, record a sound file with as much in the one file as possible, rather than depending on a sum of many to obtain the result.

VBA Code

edit 
Option Explicit

'Declaration for Win10 and Office 64 bit
Public Declare PtrSafe Function sndPlaySound Lib "winmm.dll" _
        Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
           ByVal uFlags As Long) As Long

Sub TestPlayWavFileAPI()
    'run this to play a sound wave (.wav) file
    
    Dim sPath As String
    
    'path to wave file - replace with your own
    sPath = "C:\Windows\Media\Ring08.wav"
    
    'test the no-wait feature
    PlayWavFileAPI sPath, False
    MsgBox "This message box appears during the sound"
    
    'test the wait feature
    PlayWavFileAPI sPath, True
    MsgBox "This message appears only after the sound stops"

End Sub

Function PlayWavFileAPI(sPath As String, Wait As Boolean) As Boolean
    'API declaration of sndPlaySound is modified for 64 bit windows
    'and tests well in Excel 2019 vba version 7.1.
    'For earlier versions it might be necessary to remove the word PtrSafe
    'from the declaration, or to consider another format.
    
    'make sure file exists
    If Dir(sPath) = "" Then
        Exit Function
    End If
    
    If Wait Then
        'hold up follow-on code until sound complete
        sndPlaySound sPath, 0
    Else
        'continue with code run while sound is playing
        sndPlaySound sPath, 1
    End If

End Function

See Also

edit
edit
Retrieved from "https://en.wikibooks.org/w/index.php?title=Visual_Basic_for_Applications/Play_a_WAV_File_from_VBA&oldid=4379370"