Monthly Archives: June 2017

#Excel Super Links #80 – shared by David Hager


Using the ODDFPRICE Function in Excel

Create Excel UserForms For Data Entry In 6 Easy Steps: Tutorial And Practical Example

What is Break-Even Point (BEP)?

How to Use Excel Wildcard Characters in Formulas

See New and Exciting Excel Content at:

#Excel: Get Audio Result For Any Excel Formula or Cell with Worksheet UDF by David Hager


Most people reading this article have seen how to document a formula with the N function. Here is an example.

=SUM(A1:A3)+N(“This formula sums the first 3 values in Column A”)

The technique presented here uses a similar concept (the result is not affected by the additional formula).

However, instead of documenting the formula, it adds the ability to provide an audio result each time the formula is recalculated. I am sure that the use of this UDF will find wide usage throughout the Excel community.

Here is the VBA Function code for doing this. Make sure to add the Microsoft Speech Object Library (sapi.dll version) under Tools, Reference for this to work.

Function GiveVocalResult(Optional Person As String = “Him”, Optional bVolatile As Boolean = False, _

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

Dim Voc As SpeechLib.SpVoice

Set Voc = New SpVoice

Dim sAddress As String

Application.Volatile bVolatile

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

sAddress = Application.Caller.Address

rResult = Evaluate(Mid(Range(sAddress).Formula, 1, InStr(1, Range(sAddress).Formula, “&Give”) – 1))

.Speak rResult

End With

End Function

After numerous attempts to create the desired UDF, I finally came up with a solution. It is based on these two line of VBA code.

sAddress = Application.Caller.Address

rResult = Evaluate(Mid(Range(sAddress).Formula, 1, InStr(1, Range(sAddress).Formula, “&Give”) – 1))

Obtaining the address containing the formula proved to be the right path. The use of the caller address in the Evaluate function afforded the result of the “first” formula. There are two examples that illustrate the use of the GiveVocalResult UDF. Note that all of the arguments of the function are optional.

In the first example, shown in the following figure, a formula that looks for the second largest value in a range “reads” the result in a female voice.

In D1   =LARGE(A1:A10,2)&GiveVocalResult(“Her”)


When values are changed in the range A1:A10, the result of the formula is vocalized. The use of “Her” in the first function argument changed the default value of Him to Her.

In the second example, the value in cell C2 is set by using a data validation drop down list. When changed, the formula in D2 (=C2&GiveVocalResult()) reads the result in a male voice.


The last 3 arguments in the function are all set with default values. bVolatile is set to False, Rate is set to 1 (can vary between -10 to 10, and Volume is set to 60 (can vary from 0 to 100). Experiment with the settings and enjoy.

Because of what I consider to be a groundbreaking technique, please reference my web site when you use it.

The example file can be downloaded here.


#Excel Super Links #79 – shared by David Hager

#Excel Super Links #79 – shared by David Hager

Top 10 things we struggle to do in Excel & awesome remedies for them

Plotting Freeze-Thaw Data in Excel

Spreadsheet Skunkworks! Excel VBA Chess Challenge

Lookup multiple values in DAX

Get Information from #Excel About the August 2017 Total Eclipse by David Hager

Excel: Conditional Format Rows in List 1 that are Not in List 2 by David Hager


I was watching one of Mike Girvin’s excellent Excel videos today. The technique demonstrated in the video was to use Power Query to extract items in list 1 that are NOT In list 2:

I realized that this was the opposite of the conditional formatting technique I had just published.

So, for the sake of completeness, I decided to modifyvmy technique to emulate Mike’s technique.

Only a minor modification of the the CF formula was neccesary to produce the opposite condition.


You can see the results in the following figure.


You can download the example file here.



#Excel Super Links #78 – shared by David Hager


My Top 10 Tips for Mastering VBA & Excel Macros [Part 5 of 5 – VBA Crash Course]

US Choropleth Map by County per State – a 4th Option

Excel Combo Box Number Problem

Nested SUMX or DAX Query?

Creating Dependent Lists from a Column Lookup in an #Excel List by David Hager


Excel: Conditional Format Rows in One List that are in Another List by David Hager


Here is the scenario. You have a long list with multiple columns and a second list containing records to look up in the first list. All of the records in each row of the first list must be compared with all of the records in the second list.

Here is the conditional formatting formula that will highlight the desired rows. Although it appears surprisingly simple, it took me quite a while to come up with this.


This formula looks for the concatenated row string in the long string from list 2 and if the FIND function finds the string, a value corresponding to the position of the string is returned. Any number of 1 and above is interpreted in CF as TRUE. Otherwise, the formula produces an error, which is interpeted as FALSE.

This CF formula was applied to list 1 starting at A2.

As you can see from the following figure, the expected rows in list 1 are highlighted.


Now, if your data happened to be all numbers in each column, a row could be hightlighted by coincidence due to a match in the large concat string. So, don’t use this technique with lists of that characteristic.

I am sure that you will find this technique useful.

You can download the example file here.


#Excel Super Links #77 – shared by David Hager


White Paper On “Planning A Power BI Enterprise Deployment”

Compare Tableau vs. Power BI

US Choropleth Excel Map by County per State

Defining Variables in DAX Queries

#Excel Short and Sweet Tip #23 (Open Windows File Explorer with Worksheet UDF) by David Hager