Generating a “Realtime” Voice Alert for the Latest Magnitude 5 or Greater Earthquake

 

I recently published an article about getting information on the latest earthquake of magnitude 5 or greater.

https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/

Please read this article to see how the core model was constructed.

One problem with this model is that since Excel’s web functions are non-volatile, a formula containing those functions must be recalculated by reentering the formula. I decided that an easier way was needed to trigger an update. I also recently published an article which utilized the hyperlink rollover technique.

https://dhexcel1.wordpress.com/2017/09/15/highlighting-words-in-an-excel-list-using-the-hyperlink-rollover-method/

I figured that this might be a good way to trigger a recalculation. And, since I was going to use a VBA function to be called from the hyperlink formula, I thought that adding audio functionality would be useful as well. Here is the hyperlink rollover formula used (in cell D5, named Recalculate). Since a rollover is required, the technique is not truly realtime.

=IFERROR(HYPERLINK(EarthQuakeAlert(),”Recalculate”),”Recalculate”)

And, here is the VBA function called by “rolling over” (passing the cursor over) that cell.

Function EarthQuakeAlert(Optional Person As String = “Him”, _

Optional Rate As Long = 1, Optional Volume As Long = 80)

Static xlApp As New Excel.Application

Dim Voc As SpeechLib.SpVoice

Set Voc = New SpVoice

Dim sAddress As String

‘Application.Volatile True

xlApp.CalculateFull

If Range(“d1”).Value = Range(“b3”).Value Then

MsgBox “No new earthquake > 5.0”

Else

With Voc

If Person = “Him” Then

Set .voice = .GetVoices.Item(0) ‘male

ElseIf Person = “Her” Then

Set .voice = .GetVoices.Item(1) ‘female

Else

End If

.Rate = Rate

.Volume = Volume

.Speak “New Earthquake Alert! ” & Range(“b5”).Value

End With

Range(“d1”).Value = Range(“b3”).Value

End If

EarthQuakeAlert = “Recalculate”

Set xlApp = Nothing

End Function

In order to use SpeechLib.SpVoice in the code, the correct reference (from Tools, References) must be added to the VBE as shown in the following figure.

In this figure is a picture of the earthquake model.

I hope that you find this useful. You can download the file here.

earthquake_audio­_alert

Excel Super Links 1-150

This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Excel Super Links 1-150

 

 

Highlighting Actual Words in an #Excel List Using the Hyperlink Rollover Method

The Hyperlink Rollover technique was discovered by Jordan Goldmeier. It uses the HYPERLINK function with a VBA function procedure as the 1^st argument. By passing the cursor over the cell containing this formula, the function procedure is run. See:

http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html

And, this function, unlike normal UDFs, can modify the Excel worksheet.

I have been trying for quite some time to develop a way to highlight (format) cells in a list that contain words. It turns out that there is a bug (feature?) in the VBA expression Application.CheckSpelling that prevents its use in an UDF. For example, if I wanted to conditionally format cell A1 to highlight a string that is a word, you might expect that the following UDF could be used as a CF formula.

Function IsWord(WordRange As Range)

IsWord = Application.CheckSpelling(WordRange)

End Function

Well, it does not work. The problem is documented at the following link.

https://stackoverflow.com/questions/10776191/spellcheck-a-single-word-in-excel-function

I tried numerous methods to find something that would work. I won’t bore you with the details, but I used a lot of time on this without success. I even used the Hyperlink Rollover method, and it still did not work. Finally, in the last comment in the link shown above, I found that an early binding process was needed. When added to the regular UDF for use in conditional formatting, it still did not work, but it did work with Hyperlink Rollover. Here is the UDF developed to highlight words:

Function IsWord(WordRange As Range)

Static xlApp As New Excel.Application

For Each cRange In WordRange

If xlApp.CheckSpelling(cRange) Then

With cRange.Font

.Color = -16776961

.Bold = True

End With

End If

Next

IsWord = 0

Set xlApp = Nothing

End Function

By using this UDF with this technique, the following formula entered In cell D1 creates the Hyperlink Rollover location.

=IFERROR(HYPERLINK(IsWord(A1:A20),”Format Words”),”Format Words”)

After passing the cursor over this cell, the result can be viewed in the following figure.

I decided to add another word to that range in cell A9. Without “rolling over”, this cell now showed that A9 contained a word.

I was rather amazed by this. I could not find an example of this in any previous Hyperlink Rollover, but I might have missed seeing it. What I believe is occurring is that any change in the range used in the IsWord function serves as the same action as a rollover. I did verify that this condition persists even after the workbook is closed and reopened.

So…

I decided to use a dynamic range as the argument in the IsWord function, as shown below.

=IFERROR(HYPERLINK(IsWord(OFFSET(A1,,,COUNTA(A:A),)),”Format Words”),”Format Words”)

By adding several more cells with strings, the figure below shows the results.

I hope that you find this technique useful.

You can download the example file here.

IsWord