Author Archives: David Hager

About David Hager

Retired. Now very active in Excel community. Contact me with job opportunities.

#Excel Super Links #80 – shared by David Hager

 

Using the ODDFPRICE Function in Excel

http://excelmooc.com/using-the-oddfprice-function-in-excel/

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

https://powerspreadsheets.com/create-excel-userforms/

What is Break-Even Point (BEP)?

https://exceldatapro.com/break-even-point/

How to Use Excel Wildcard Characters in Formulas

http://excelitch.com/how-to-use-excel-wildcard-characters-in-formulas/

See New and Exciting Excel Content at:

https://dhexcel1.wordpress.com/

#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

Else

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”)

GiveV1

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.

GiveV2

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.

https://dhexcel1.wordpress.com/

The example file can be downloaded here.

VoiceVar

#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

http://chandoo.org/wp/2014/06/18/top-10-excel-struggles-and-solutions/

Plotting Freeze-Thaw Data in Excel

https://newtonexcelbach.wordpress.com/2017/06/21/plotting-freeze-thaw-data/

Spreadsheet Skunkworks! Excel VBA Chess Challenge

http://tigerspreadsheetsolutions.co.uk/spreadsheet-skunkworks-chess/

Lookup multiple values in DAX

https://www.sqlbi.com/articles/lookup-multiple-values-in-dax/

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

https://dhexcel1.wordpress.com/2017/06/23/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:

https://www.youtube.com/watch?v=JztEKJ-XkCU

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

https://dhexcel1.wordpress.com/2017/06/24/excel-conditional-format-rows-in-one-list-that-are-in-another-list-by-david-hager/

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.

=ISERROR(FIND(CONCAT($A2:$E2),CONCAT($I$2:$M$6)))

You can see the results in the following figure.

 TableAntiCompareCF1

You can download the example file here.

TableAntiCompareCF

 

#Excel Super Links #78 – shared by David Hager

 

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

http://chandoo.org/wp/2011/09/06/top-10-tips-for-excel-vba/

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

http://www.clearlyandsimply.com/clearly_and_simply/2017/06/us-choropleth-map-by-county-per-state-a-4th-option.html

Excel Combo Box Number Problem

http://blog.contextures.com/archives/2017/06/22/excel-combo-box-number-problem/

Nested SUMX or DAX Query?

https://powerpivotpro.com/2015/08/nested-sumx-or-dax-query/

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

https://dhexcel1.wordpress.com/2017/06/22/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.

=FIND(CONCAT($A2:$E2),CONCAT($I$2:$M$4))

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.

 TableCompareCF1

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.

TableCompareCF

#Excel Super Links #77 – shared by David Hager

 

White Paper On “Planning A Power BI Enterprise Deployment”

https://blog.crossjoin.co.uk/2017/06/20/white-paper-on-planning-a-power-bi-enterprise-deployment/

Compare Tableau vs. Power BI

https://datachant.com/2017/06/20/compare-tableau-vs-power-bi/

US Choropleth Excel Map by County per State

http://www.clearlyandsimply.com/clearly_and_simply/2017/06/us-choropleth-map-by-county-per-state.html#more

Defining Variables in DAX Queries

https://www.sqlbi.com/articles/defining-variables-in-dax-queries/

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

https://dhexcel1.wordpress.com/2017/06/19/excel-short-and-sweet-tip-23-open-windows-file-explorer-with-worksheet-udf-by-david-hager/