Category Archives: VBA

#Excel Super Links #156

 

3 ways to change an image based on a cell value

https://exceloffthegrid.com/automatically-change-picture/

Custom Connector to import Google Sheets with OAuth2 authentication in PowerBI

http://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/

Advancing intelligence, management, and security to empower the modern workplace

https://blogs.office.com/en-us/2017/09/25/advancing-intelligence-management-and-security-to-empower-the-modern-workplace/

The Environment concept in M for Power Query and Power BI Desktop, Part 1

http://ssbi-blog.de/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power-bi-desktop-part-1/

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

https://dhexcel1.wordpress.com/2017/09/22/generating-a-realtime-voice-alert-for-the-latest-magnitude-5-or-greater-earthquake/

 

Advertisements

#Excel Super Links #155

 

Forms for Excel, new experience for Excel survey in Office 365

https://techcommunity.microsoft.com/t5/Microsoft-Forms-Blog/Forms-for-Excel-new-experience-for-Excel-survey-in-Office-365/ba-p/109195

Highlight a Specific Data Point using Power BI

http://prathy.com/2017/03/27/

visualizing change via slopegraph

http://www.storytellingwithdata.com/blog/2017/9/19/visualizing-change

Reverse Geocoding Using Google API In VB

https://chandoo.org/forum/threads/reverse-geocoding-using-google-api-in-vb.18005/

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

https://dhexcel1.wordpress.com/2017/09/22/generating-a-realtime-voice-alert-for-the-latest-magnitude-5-or-greater-earthquake/

 

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.

AudioEarthquakeAlert1

In this figure is a picture of the earthquake model.

AudioEarthquakeAlert2

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

earthquake_audio­_alert

#Excel Super Links #154

 

Excel Magic Trick 1465: Conditional Formatting Holiday Dates with Red Fill & Word “Holiday”

https://www.youtube.com/watch?v=DkchPSWIwHE

Text Files in VBA

http://www.excelgaard.dk/Lib/Text%20Files/

Find level of Directories / Find String in Power BI

http://prathy.com/2017/02/1801/

Connecting to APIs with Power BI (Power Query)–Part 1 of 2

https://www.poweredsolutions.co/2017/01/24/connecting-to-apis-with-power-bi-power-query-part-1-of-2/

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

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

 

#Excel Super Links #153

 

Embed your Power BI report with predefined filters

https://www.kasperonbi.com/embed-your-power-bi-report-with-predefined-filters/

Working with implied units

https://newtonexcelbach.wordpress.com/2017/09/19/working-with-implied-units/

The Great Football Project Rides Again! (In Power BI)

https://powerpivotpro.com/2017/09/the-great-football-project-rides-again-in-power-bi/

Who Has My Workbook Open? [VBA]

https://colinlegg.wordpress.com/2016/07/31/who-has-my-workbook-open-vba/

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

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

 

#Excel Super Links #151

#Excel Super Links #151

Number.Mod rescue pack for Power BI and Power Query

http://www.thebiccountant.com/2017/09/13/number-mod-rescue-pack-power-bi-power-query/

Excel Hyperlinks Run Command Files

http://blog.contextures.com/archives/2017/09/14/excel-hyperlinks-run-command-files/

Setting up UDF Applications

https://newtonexcelbach.wordpress.com/2017/09/09/setting-up-udf-applications/

Excel Magic Trick 1464: Is Item In List? Formula to Check if Item is in a List on Separate Sheet

https://www.youtube.com/watch?v=hlnuyqlWaD8

File System Object

http://www.excelgaard.dk/Lib/FileSystemObject/