Thursday 17 June 2010

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.

Thursday 3 June 2010

Do… Loop and For… Next in VBA

Loop statements are used to get Excel to repeat a piece of code a certain number of times. The number of times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this until there are no more rows of data).

The are several ways in which loops can be constructed, depending on the circumstances in which they are going to be used.  Quite often the wanted result can be obtained in different ways, depending on your preferences.

There are two basic kinds of loops, examples of both: Do…Loop and For…Next loops. The code to be repeated is placed between the key words.

ExamplePic1

For our first example open a new workbook and place the numbers 1 to 10 in cells A1 through to A10. 

We are going to loop down the list, adding 5 to the number in column ‘A’.

Now open the VBE, add a new module and start a new sub routine call MyLoopStatement.  Copy the code below into your new routine.

Public Sub MyLoopStatement()
    Range("A1").Select 

    Do While IsEmpty(ActiveCell) = False 
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)" 
        ActiveCell.Offset(1, 0).Select  LoopStatement2
    Loop
End Sub 

 

This routine loops down the list placing a formula in each of the cells to the right of the  activecell.  It will continue WHILE the active cell is not empty.

The next example uses Do until.

    Range("A1").Select 
    Do
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell)

This example loops until it finds a blank cell.  It is generally considered better practise to use Do Until, rather then Do While.  Both of the above examples runs the code within the loop at least once before testing whether to move on.  You could write the same Do Until / Do While loops in the following way. 

    Range("A1").Select
    Do Until IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Loop

Moving the test to the start of the loop means it is carried out before carrying out any of the instructions within the loop.  This could be useful if, for example, sometimes cells A1:A10 were blank.

if you know or can use VBA to find out how many times you want to repeat the code you can use a For… Next loop.

In the following example we know that we have 10 rows of data, so we can tell the code to loop 10 times.

    Dim l As Long
    Dim lRowCount As Long

    lRowCount = Range("A1").CurrentRegion.Rows.Count - 1
    ' You could also try
    ' lRowCount = Cells.SpecialCells(xlCellTypeLastCell).Row

    For l = 1 To lRowCount
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Next l

For… Next loops are particularly good a looping through objects.

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook

    For Each ws In wb.Worksheets
        Debug.Print ws.Name
    Next ws

The above code would be quicker then:

    Dim l As Long
    Dim wb As Workbook

    Set wb = ThisWorkbook

    For l = 1 To wb.Worksheets.Count
        Debug.Print wb.Worksheets(l).Name
    Next l

As always, I hope the above examples have been helpful.  If you have any questions or comments please do let us know.