Category Archives: VBA

#Excel Super Links #45 – shared by David Hager

How To Transform Data by Example in Excel

RegEx UDF in Excel

Excel VBA get data from PDF file into Sheet(s) or Text file(s)

Excel VBA Trivial Pursuit

#Excel Short and Sweet Tip #15 (Using TEXTJOIN With Non-Contiguous Ranges) by David Hager


#Excel Super Links #43 – shared by David Hager

Dynamic format using DAX

Dynamic CAGR Calculation in DAX

Animated Asynchronous Electric Mmotor Model in Excel

Excel Monthly Report Tutorial

Copy #Excel Chart as an Enhanced Metafile Picture by David Hager


#Excel Super Links #42 – shared by David Hager

Creating Infographics with Excel, Part 1

Web Scraping Power BI Custom Visuals from Office Store

Grid of Random Integers in Excel

How to edit M-function Documentation Metadata

#Excel: Four Super Filter Techniques by David Hager

#Excel UDF Using Google API to Return the Elevation of an Address by David Hager

There has been a lot of interest in using the Google API in Excel VBA for geocoding.

This article demonstrated a method of returning latitude and longitude coordinates from an address

and this article showed how to return an elevation from latitude and longitude coordinates.

So, I decided to combine the techniques from both articles to create an UDF that would use an address to give the elevation of that location. The first line of code illustrates the function arguments.

Function ElevationFromAddress(address As String, Optional ToFeet As Boolean) As Double

An address string is the 1st argument and there is an optional 2nd boolean argument for coverting meters to feet. Please feel free to download the example file containing the complete code for the UDF

The Google API algorithm can recognize locations with limited or detailed address information. The first two records in the table are for locations at a considerable altitude. The next record is for a Baptist seminary in Fort Worth, Tx. The last records are both for The White House in Washington, D.C. Note that the values are different – the first record must be for the front gate while the generic address refers to the actual building. As shown in the figure, the formulas in column B use the 2nd optional argument to return the elevation in feet.


This function procedure requires a reference to Microsoft XML, v6.0, selected in the VBE under Tools, References, as shown here.


The file can be downloaded here.


#Excel Super Links #41 – shared by David Hager

Excel High Precision Engineering Chart #1

Power Query (M)agic Part 1: Always Have Good References

Exposing System Secrets with VBA and WMI API

Determining The Drive Type From Excel

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager


Copy #Excel Chart as a Enhanced Metafile Picture by David Hager

I am sharing here yet another great Excel technique from Rob van Gelder. This procedure allows you to click a chart in any open workbook and run the macro to convert the chart to a .emf file at the location of your choice. See:

The code from that article is shown here.

Declare Function OpenClipboard Lib “user32” (ByVal hwnd As Long) As Long

Declare Function CloseClipboard Lib “user32” () As Long

Declare Function GetClipboardData Lib “user32” (ByVal wFormat As Long) As Long

Declare Function EmptyClipboard Lib “user32” () As Long

Declare Function CopyEnhMetaFileA Lib “gdi32” (ByVal hENHSrc As Long, ByVal lpszFile As String) As Long

Declare Function DeleteEnhMetaFile Lib “gdi32” (ByVal hemf As Long) As Long

Const CF_ENHMETAFILE As Long = 14

Const cInitialFilename = “Picture1.emf”

Const cFileFilter = “Enhanced Windows Metafile (*.emf), *.emf”


Public Sub SaveAsEMF()

Dim var As Variant, lng As Long


var = Application.GetSaveAsFilename(cInitialFilename, cFileFilter)

If VarType(var) <> vbBoolean Then

On Error Resume Next



OpenClipboard 0

lng = GetClipboardData(CF_ENHMETAFILE)

lng = CopyEnhMetaFileA(lng, var)



DeleteEnhMetaFile lng

On Error GoTo 0

End If

End Sub

You can download the file containing this procedure here.


Important note: This solution will run correctly only on 32-bit systems – not 64-bit. If you really need it to work on 64-bit, you can try to modify the code based on the information located at:

#Excel Super Links #35 – shared by David Hager

Excel Code Cracker Game

Excel VBA convert column of sentences to column of single words

Excel Magic Trick 1419: RANKX DAX Function & More: Ranking Profit for Products within Manufacturer

=SUM(‘???’!C3) Is it a valid formula?? No. It is magical indeed!

Averaging of Scientific Results in Excel