Monthly Archives: September 2017

#Excel Short and Sweet Tip #29: Inserting Icons Using a User-Defined Function

Disclaimer: You need the Excel version included in Office 365 for this technique to work.

The insertion of icons in Excel 2016 is accomplished from the ribbon by selecting Insert, Icons. There are a number of catagories to select from, as shown in ths figure.


However, recently I have been interested (obsessed?) with worksheet UDFs and their ability to invoke actions or shapes. In this case, I wanted to see if a UDF would insert an icon into the worksheet. This is the VBA function I made with help of the macro recorder. Place this in a general module in your worksheet.

Function MakeIcon(fName As String)

iString =        “

fileName=” & fName & “.svg”

ActiveSheet.Pictures.Insert iString

End Function

Now, enter the formula =MakeIcon(“Man”) in cell A1 and you will get the following result.


Unfortunately, there are several inherent Excel limitations that prevent the full utilization of this function. First, you have to know the correct name of the icon to produce it. It would be nice if Microsoft provided a list of the icon names, but I could not locate one. Then, I tried to get names by macro recording the insertion of multiple icons, but only the “last” selected icon URL is recorded. Even so, I hope that this technique is useful to you.

The example file can be downloaded here.


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.

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.

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.


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


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

MsgBox “No new earthquake > 5.0”


With Voc

If Person = “Him” Then

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

ElseIf Person = “Her” Then

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


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.


Get Latest Storm Information in #Excel Using Only An Address and Storm Name


I recently published this article about the direction and distance of a tropical system from an address.

Go back and read this article to understand the first part of the model associated with the address (entered manually in cell B1).

Originally I wanted to include the abilty to add the storm coordinates from an internet source but I could not find one, so manually entering them was necessary. However, later I stumbled across just the xml source I was looking for from the National Hurricane Center site at NOAA. It only gives the current information on a storm, so it gets “stepped on” with each new advisory. This information is for storm number 15 for the 2017 Altantic hurricane season, which hhappens to be Maria.

In order to access more than one system, I replaced the number with a defined name function called StormNum, which is then used as the URL in the WEBSERVICE function to return the desired xml document.


I also needed a list of the storms for 2017. One complication was that the NHC now gives Potential Tropical Cyclones a number like those given to all tropical lows. Then, if the PTC does not develop into a low pressure system, it will not show up in online lists, like for example, Wikipedia. However, I was able to find a site that did include PTCs and I used Power Query to get the table containing the desired information (see worksheet SysNames). I massaged the column containing the storm designations as shown in column G. The formula used for this is =IF(PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))=””,”Not Yet”,PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))). Shown below:


The source from the PQ M code is:


Now that I had the list of storms, I was able to construct the StormNum defined name formula as follows:


where Storm_Number =MATCH(DirectionCalc!$B$2,Storm_List,0). Cell B2 contains a data validation list with the storm names derived from the PQ.

Now, the FILTERXML function can extract various pieces of information from the xml doucment defined as Storm, as shown in the following figure.


For example, the storm latitude (in cell C4) is =FILTERXML(Storm,”//centerLocLatitude”).

The final formula for the storm message is:

=ROUND(Distance,0)&” miles “&TextDirection&” of “&Address&”, moving “&MID(FILTERXML(Storm,”//systemDirectionOfMotion”),1,FIND(“OR”,FILTERXML(Storm,”//systemDirectionOfMotion”))-1)&”at “&FILTERXML(Storm,”//systemSpeedMph”)&” mph.”

Remember, do no use this model in any corporate or commercial manner (only for personal use).

You can download the file here.


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 1st argument. By passing the cursor over the cell containing this formula, the function procedure is run. See:

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.

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


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.


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.


Using #Excel to Find How Far the Event is from Your Location

Note: It is important to note that this Excel model can be used any place in the world to look at the direction from storms or any other event.

 The destructive nature of hurricanes has been dominating the news recently in the Atlantic basin. But, many times it is difficult to get information about how far a storm is from your specific location. This excellent Excel technique will allow you to answer that question. All that you will need is your address (or an address of interest) and the current storm coordinates. Key parts of this technique were found at the following links.

In the worbook provided, enter your address information in cell B1and the storm coordinates in C4 and D4. The formulas used to calculate your result are:

LocationXML =WEBSERVICE(“”&Address&”+,+&sensor=false&#8221;)

Lat_1 =FILTERXML(LocationXML,”//result/geometry/location/lat”)




Direction =DEGREES(ATAN2(COS(RADIANS(Latitude_1))*SIN(RADIANS(Latitude_2))-SIN(RADIANS(Latitude_1))*COS(RADIANS(Latitude_2))*COS(RADIANS(Longitude_2-Longitude_1)),SIN(RADIANS(Longitude_2-Longitude_1))*COS(RADIANS(Latitude_2))))


The final formula for displaying the desired information (in cell A7) is:

=ROUND(Distance,0)&” miles “&TextDirection&” of “&Address

The result can be viewed here.


I hope that you find this useful. Download the example file:



#Excel Advanced Filter: Selectively Show Control Chart Data by Standard Deviation


For this technique, I am building it on the workbook made for the following article. Please download the example file and read the article, since the functionality is synergistic with it.

When viewing a control chart, it is useful to be able to view only data within set control limits. For example, you would like to view all data within one sigma of the mean. The technique described here allows you to do that for 1, 2, or 3 sigma. The key to accomplish this is Excel’s advanced data filter. The advanced filter uses a boolean formula to filter a table of data, starting at the first row of the table. The formula in A2 gives the desired result.


where SigmaKeep is a worksheet cell (G2) with a data validation list of 1,2,3. Cells D6 and G6 (and the corresponding columns) contain formulas that calculate the +1 and -1 sigma from the mean for the data in column B. The 2nd and 3rd sigma are for E6,H6 and F6,I6 respectively. The following figure shows the advanced filter dialog box and the input ranges required, where SigmaKeep is set at a value of 2.


After the filter is applied, note the difference in the data in the control chart versus the original in the first figure.


The only caveat to this technique is that the advanced filter has to be cleared (Data, Sort & Filter, Clear) before a different KeepSigma value can be applied. This can be used in conjunction with the removal of outliers as discussed in the original article.

The example file can be downloaded here.