A new utility — Audio Alerts
Generating an audio alert
http://www.tushar-mehta.com/publish_train/xl_vba_cases/TM_Audio_Alerts.htm
A couple of weeks ago, I received an email request for a means to generate an audio alert in Excel. On the verge of clicking “Send mail” on my standard response pointing the way to the newsgroups, I thought of something intriguing.
So, before reading further, let me rephrase the original request. How does one generate a customized, meaningful, natural language alert in Excel? And, oh, don’t forget. Keep the process simple.
.
.
.
.
Here’s a hint: It uses a feature introduced with Excel 2002 that if you were like me evaluated and decided had little practical value.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Use Excel’s Text-to-Speech capability that shows up in the Object Model as the Speak method!
So, how does one keep it simple?
The VBA code contains the Speak method which it applies to each reference it finds in a table. This means the VBA code needs *no* maintenance.
The table (an Excel worksheet) lists all the cells to be monitored in the form of columns for: named range, worksheet name, and workbook name.
Each monitored cell should typically contain a condtional formula that yields a zero-length string, “”, for normal conditions and some message (e.g., “Warning: Sensor temperature is dangerously high”) for alert conditions.
Essentially, this creates a solution that allows anyone familiar with Excel to generate audio alerts.
Jan:
I t h i n k t h a t w o u l d b e r e a l l y a n n o y i n g i n a s t e p h e n h a w k i n s w a y.
6 October 2006, 7:25 amJan:
I admit it shows real, style but it would be terrible to have missed a
6 October 2006, 7:33 am“Reactor Overload - Core Meltdown imminent Click OK to continue ”
…warning due to a run time error type 13 owing to the text to speech dll not being referenced or something.
John Walkenbach:
I guess we have different definitions of “simple.” I’d just add this function:
Function SayIt(txt)
Application.Speech.Speak (txt)
End Function
Then use a formula like this:
=IF(C6E6,SayIt(”High voltage”),”"))
Your solution, of course, doesn’t require a macro (which is an advantage). But it has two disadvantages: It requires an add-in (which cannot be modifed or examined), and it requires an additional workbook that must be open at all times. And the whole thing falls apart if the user changes the filename, sheet name, or range name.
6 October 2006, 12:49 pmTushar Mehta:
{sheepish grin}
Hi John: Duh! Talk about overkill.
Another benefit to your approach is that the design is that much more transparent.
Of course, the function could be in an add-in removing macro warnings (at the expense of a link to the add-in file). And, the function could return the text as the function value.
Hopefully, I can still edit the original post.
6 October 2006, 5:32 pmJohn Walkenbach:
Sometimes we overlook the obvious. I’ve done it far too many times. One time I spent about 10 hours working on something that could be done with a pivot table in about 5 seconds.
And when will I learn that this software strips out angle brackets? That formula I posted got mangled.
6 October 2006, 6:20 pmTushar Mehta:
John: Yeah, I rarely regret such mistakes. The price of experimenting.
While the implementation was overkill, the idea of using Speak for natural language alerts is something I had not seen before.
One of the things that bugs me about real-time tracking of something (stock prices for example) is that one has to look to see what is happening. That means that the associated software program has to have a visible window. What a waste of real estate!
Yeah, most software nowadays can generate an automatic email but that requires checking for new email. Big f’ing help! If I could incorporate an audio alert into the system…
The angle brackets may have gotten messed up but your idea came through quite clearly.
7 October 2006, 6:06 amBrett:
John,
In your earlier post about Excel’s speech you also mentioned accessing a few different voices. As I recall, you had managed to get a nice female voice. I’m still stuck with Microsoft Sam. Is there a VBA reference other than the Speech object library I need to check?
Thanks,
9 October 2006, 6:19 amBrett
Jim Thomlinson:
Brett. I am not to sure if you can change the voice directly as you have asked. However go to Windows -> Control Panel -> Speech -> Text To Speech and then just select the voice you want…
10 October 2006, 4:24 pmBrett:
Jim. Thanks! I’ve ditched LH Michael for LH Michelle.
Brett
10 October 2006, 9:34 pmTushar Mehta:
OK. Changed my mind. The original design has value.
Of course, John (Walkenbach)’s suggestion of using a UDF has merit as both he and I have pointed out.
But, it turns out that the original design, with an additional table listing all cells to be monitored, has some major advantages.
First, since it doesn’t use a custom UDF, the design degrades gracefully. If the requisite add-in is missing from the system, the non-UDF design continues to work just without the audio alert. Essentially, the customer is back to where s/he started, with the visual alert (a conditional formula, conditional format, etc.).
Second, it works with any existing design. If someone has already developed a solution that includes a visual alert (a conditional formula, etc.) the audio alert can be added without any modification to the existing workbook(s). To monitor an additional cell, all one has to do is add a row to the table in TM Audio Alerts Monitor List.xls file.
So, both the non-UDF and the UDF have value.
12 October 2006, 10:53 am