XL: How to Play a Warning Sound If Critical Value Is Exceeded

ID: Q149777

The information in this article applies to:

SUMMARY

You can use Microsoft Excel to continuously import data to a worksheet in real time by using third-party products or custom applications. For example, you can use DDE to import live data from a stock market wire service, or you can import data directly into Microsoft Excel from a remote sensor or device.

In these situations, there may be times that an audible alert is desired to notify you that a critical value has been reached or exceeded, thefore eliminating the need for you to continuously monitor Microsoft Excel.

In Microsoft Excel, a Visual Basic for Applications macro can be used to continuously monitor real-time or changing values in a worksheet, compare them against a specific criteria, and then sound an audible warning when that criteria has been met.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

In order to play a sound in a Visual Basic for Applications procedure, a Windows API function, sndPlaySound(), can be used to play a .wav file.

This macro assumes that a sound card capable of playing .wav files is installed. For information about how to programmatically determine if a sound card is currently installed, see the following Knowledge Base article: 

   ARTICLE-ID: Q123974
   TITLE     : VB 3: How to Detect If Your Computer Has a Sound Card
1. In a new Workbook, enter the following data in the appropriate cells: 

   A1: Sensor     B1: Temperature     C1: Critical Temp
   A2: 1          B2: 100             C2: 100
   A3: 2          B3: 150             C3: 125
   A4: 3          B4: 25              C4: 100
   A5: 4          B5: 85              C5: 50
   A6: 5          B6: 60              C6: 75
2. In a new module sheet, enter the following macro 

   'Declare API functions
   '32bit function for Windows NT and Windows 95
   Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA"
  (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

   '16 bit function for Windows 3.x
   Declare Function sndPlaySound Lib "MMSYSTEM.DLL" (ByVal lpszSoundName As
      String, ByVal wFlags As Integer) As Integer

   Sub SoundWarning()

   'Activate the worksheet containing the data
   Worksheets("sheet1").Activate

   'Select the range of cells to watch

   Range("b2:b6").Select

   'Loop through the selected cells
   For Each x In Selection

   'check if the value of each cell in the selected range in greater than
   'the value in cell to it's right.
   If x.Value > x.Offset(0, 1).Value Then

      'Call the appropriate sound API, depending on the Operating System.
         'If Windows NT or Win95 is being used
         If (Application.OperatingSystem Like "*32-bit*") Then
             Call sndPlaySound32("c:\win95\phaser.wav", 0)
             'If Excel is running on the Macintosh
             ElseIf (Application.OperatingSystem Like "*Mac*") Then
             MsgBox "This macro will not run on a Macintosh"
             'If Windows 3.x is being used
             Else
             Call sndPlaySound("c:\win95\phaser.wav", 0)
         End If
      End If
   Next x
End Sub

REFERENCES

"Microsoft Excel Visual Basic User's Guide"

"Developing Excel 95 Solutions with Visual Basic for Applications," by Eric Wells, Microsoft Press, 1995; Appendix C, pages 757-764

"Developing Microsoft Excel 5 Solutions," by Eric Wells, Microsoft Press, 1995; Appendix C, pages 599-601

Additional query words: 5.00 5.00c 7.00 

Keywords          :  
Version           : 5.00 7.00
Platform          : WINDOWS

Last Reviewed: May 19, 1999