Microsoft Excel VBA - Play a sound based on a condition

Using Microsoft Excel's IF() function and a sprinkle of VBA you can play a sound when a condition is met.

Chances are you may have used Microsoft Excel's CONDITIONAL FORMATTING feature.

(HOME -- CONDITIONAL FORMATTING)

 In the above example, cell A2 is conditionally formatted to turn red if the cell value is greater than 200.

Conditional Formatting is a great feature. But, adding SOUND into the mix will increase the impact the message can have on the user of the document.

Creating a User Defined Function (UDF)

Microsoft Excel is full of all sorts of built in functions. The most popular one being, SUM(). My favorite function is the IF(). There are roughly 400+ built in functions found within Excel.

Not only can you take advantage of the built in functions, but Excel also allows you to build you own custom functions or User Defined Functions (UDF).

By building your own custom function you can specify exactly what the function will do and the type of output it will return back to the user.

Function PlayBeep() As String
    Beep
    PlayBeep = ""
End Function
 

The above simple function, called PlayBeep, will play a sound when called from within an Excel Worksheet.

Once the function is created in the VBA window it can then be used just like any other function within an Excel worksheet. In this case, when I type =PlayBeep() in a cell within a worksheet and press the Enter key, a simple beep sound is played. Now, what exactly is the function doing?

The first and last line are the standard opening and closing lines of creating a custom function.

Function PlayBeep() As String

This line, above, lets Excel know you are creating a function called PlayBeep and it returns a string value.

End Function

The above line just lets Excel know where the function ends.

Everything between those two lines is what the function will perform. In this case, two simple lines of code:

Beep = Plays a default system beep

PlayBeep = "" = Once the beep is played then the function returns an empty string. In reality it could return anything string value. Perhaps, you want it to play a sound if an incorrect valued is found as well as return the text "Invalid Value".

Function PlayBeep() As String
    Beep
    PlayBeep = "Invalid Value"
End Function

Incorporating the IF() Function

Now that a custom function has been created it can now be used in the worksheet.

Using an IF() function, a condition can be specified. If the condition is true then the PlayBeep() function can then be called to play the beep.

 If cell A2 is greater than 200, then beep. Otherwise, return an empty string or some other value.

Other Methods for the IF condition

With a little more VBA you could also incorporate the conditional check in the function itself.

Function PlayBeep(PaymentAmt As Long) As String
    If PaymentAmt > 200 Then
        Beep
        PlayBeep = "Invalid Value"
    Else
        PlayBeep = "Valid Value"
    End If
End Function

Download the file to practice on your own: VBAPlaySound-01