Playing a sound file using VBA

I recently received a request from a client to add a specific sound when displaying a message box warning.  I knew I’d done this before, and from memory knew it was fairly simple.  So, after ploughing through some old code I came across the code I have put below.  I hope it proves useful to you.

The code uses a call to an API, below.  The API code needs to be copied and pasted to the top of your module code.

' This is required to play a .wav sound with VBA
Public Declare Function sndPlaySound32 _
    Lib "winmm.dll" _
    Alias "sndPlaySoundA" ( _
        ByVal lpszSoundName As String, _
        ByVal uFlags As Long) As Long

Below is the routine which plays the sound, this can be placed in a utilities module and called by various routines, each passing in the name of the sound file you want to be played.

Windows has a folder full of .wav files, they can be found here; C:\Windows\Media.  The routine below allows you to pass in the full file path of a music file, or just the filename.  If you pass in a filename the routine assumes you want to play a Windows default sound and guesses the file path.

Public Sub PlaySound(ByVal SoundFileName As String)
    If Dir(SoundFileName, vbNormal) = "" Then
        ' SoundFileName is not a file. Get the file named by
        ' SoundFileName from the Windows\Media directory.
        SoundFileName = Environ("SystemRoot") & "\Media\" & SoundFileName
        If InStr(1, SoundFileName , ".") = 0 Then
            ' If SoundFileName does not have a .wav extension, add one.
            SoundFileName = SoundFileName & ".wav"
        End If
        If Dir(SoundFileName, vbNormal) = vbNullString Then
            ' Can't find the file. Just use a beep.
            Beep
            Exit Sub
        End If
    Else
        ' SoundFileName is a file, Use it.
    End If
    ' Play the sound, before continuing code
    sndPlaySound32 SoundFileName, 0&
    ' Play sound and continue code.
    sndPlaySound32 SoundFileName, 1&)
End Sub

I have included two lines at the bottom of the code, you can comment out which-ever you choose not to use.  The first plays the sound before allowing the code to continue, the second plays the code and allows the code to continue.  This means you can play a specific sound as you display a message box for example.  Have a play, see which way suits you best.

As always, if you have any questions do let us know via the comments section.

Current User and Current PC

In this post I thought I'd show two very useful functions which use Windows API calls, the username and PC name functions.  Windows API calls allow you to get information or features from the operating system which Excel and Access do not have.
The first step in using an API call is to tell VBA the function exists, where to find it, what arguments it takes and what data type it returns. This is done using the Declare statement, such as the example below.

Private Declare Function GetUserNameA _
  Lib "advapi32.dll" _
  (ByVal lpBuffer As String, nSize As Long) As Long

This statement tells the VBA interpreter that there is a function called GetUserNameA located in the file advapi32.dll that takes two arguments, the first a string and the second a Long value and returns a Long value. Once defined, we can call GetUserNameA in exactly the same way as if it is the VBA function:

  lng = GetUserNameA(strUserName, intLength)

The Function below uses the GetUserNameA API function and turns the results into a string which shows the current users Windows login.

Public Function CurrentUser() As String
'-- used to get current user's login id --
    Dim strUserName As String
    Dim lngLength As Long
    Dim lngResult As Long
    ' Set up the buffer
    strUserName = VBA.String(255, 0) 
    lngLength = 255
    ' Make the call 
    lngResult = GetUserNameA(strUserName,  lngLength)
    ' Assign the value
    CurrentUser = Left(strUserName, _
        InStr(1, strUserName, VBA.Chr(0)) - 1)
End Function

You can add this this code to any normal VBA module within either Excel or Access.  The API ‘Private Declare Function’ should be placed at the top of the module, before any other Sub Routines or Functions.
The current PC function, below, works in much the same way.

Private Declare Function GetComputerNameA Lib "kernel32" _
    (ByVal lpBuffer As String, nSize As Long) As Long

Public Function CurrentPC() As String
'-- used to get current user's login id --
    Dim strPCName As String
    Dim lngLength As Long
    Dim lngResult As Long
    ' Set up the buffer
    strPCName = VBA.String(255, 0)
    lngLength = 255
    ' Make the call
    lngResult = GetComputerNameA(strPCName, lngLength)
    ' Assign the value
    CurrentPC = Left(strPCName, _
        InStr(1, strPCName, VBA.Chr(0)) - 1)
End Function

Google / Yahoo as always will be able to provide more information on using API calls, this site is also probably worth a read.
I’ll add a sorry about the colour scheme, guess that’s what happens when you choose black as a background.