Category Archives: VBA

#Excel Super Links #45 – shared by David Hager

How To Transform Data by Example in Excel

http://www.howtoexcel.org/tips-and-tricks/how-to-transform-data-by-example/

RegEx UDF in Excel

https://sites.google.com/site/beyondexcel/project-updates/regexudf

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

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1101

Excel VBA Trivial Pursuit

http://www.dustinormond.com/blog/vba-trivial-pursuit/

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

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

https://www.kasperonbi.com/dynamic-format-using-dax/

Dynamic CAGR Calculation in DAX

https://powerbi.tips/2016/05/measures-calculate-cagr/

Animated Asynchronous Electric Mmotor Model in Excel

http://www.excelunusual.com/2017/01/asynchronous-electric-motor/

Excel Monthly Report Tutorial

https://andrewexcel.blogspot.jp/2017/05/monthly-report-tutorial.html

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

https://dhexcel1.wordpress.com/2017/05/20/copy-excel-chart-as-a-enhanced-metafile-picture-by-david-hager/

 

#Excel Super Links #42 – shared by David Hager

Creating Infographics with Excel, Part 1

http://www.thinkdatainsights.com/creating-info-graphics-with-excel-part-1/

Web Scraping Power BI Custom Visuals from Office Store

https://datachant.com/2017/05/19/web-scraping-part-3-power-bi-custom-visuals/

Grid of Random Integers in Excel

https://excelxor.com/2015/01/26/grid-of-random-integers/#more-4145

How to edit M-function Documentation Metadata

http://www.thebiccountant.com/2017/05/11/how-to-edit-m-function-metadata/

#Excel: Four Super Filter Techniques by David Hager

https://dhexcel1.wordpress.com/2017/04/15/excel-combining-and-refining-a-static-filter-list-filter-criteria-udf-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

http://analystcave.com/excel-get-geolocation-coordinates-of-an-address/

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

http://oco-carbon.com/coding/altitude-in-excel-google-elevation-api/

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.

ElevationFA2

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

ElevFA1

The file can be downloaded here.

ElevationFromAddress

#Excel Super Links #41 – shared by David Hager

Excel High Precision Engineering Chart #1

http://blog.excelhero.com/2010/08/24/excel_high_precision_engineering_chart_1/

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

https://powerpivotpro.com/2017/05/power-query-magic-part-1-always-good-references/

Exposing System Secrets with VBA and WMI API

https://sites.google.com/site/beyondexcel/project-updates/exposingsystemsecretswithvbaandwmiapi

Determining The Drive Type From Excel

http://spreadsheetpage.com/index.php/tip/determining_the_drive_type/

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

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

http://dailydoseofexcel.com/archives/2012/05/05/copy-chart-as-a-picture/#comments

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

Selection.Copy

 

OpenClipboard 0

lng = GetClipboardData(CF_ENHMETAFILE)

lng = CopyEnhMetaFileA(lng, var)

EmptyClipboard

CloseClipboard

DeleteEnhMetaFile lng

On Error GoTo 0

End If

End Sub

You can download the file containing this procedure here.

CopyChartAsPicture

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:

http://www.jkp-ads.com/articles/apideclarations.asp

#Excel Super Links #35 – shared by David Hager

Excel Code Cracker Game

http://www.andypope.info/fun/codecracker.htm

Excel VBA convert column of sentences to column of single words

http://stackoverflow.com/questions/20782832/vba-convert-column-of-sentences-to-column-of-single-words

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

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

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

https://wmfexcel.com/2015/07/11/sumc3-is-it-a-valid-formula-no-it-is-magical-indeed/

Averaging of Scientific Results in Excel

https://dhexcel1.wordpress.com/2017/02/03/averaging-of-scientific-results-in-excel/