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
Recent Posts
Archive
Tags