Microsoft KB Archive/149777

{|
 * width="100%"|

-

The information in this article applies to:


 * 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"   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   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 