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

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0

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:

Q123974 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.00c

Keywords :
Version : WINDOWS:5.0,7.0
Platform : WINDOWS
Issue type :