Monthly Archives: May 2017

#Excel Short and Sweet Tip #19 (Invaluable Excel Speller) by David Hager

Do you know about those kids toys that spells out the name of an object and then says the word? Well, you can do the same thing in Excel. This article is about the use of Speech.Speak in VBA, and this process provides a good demonstration of its use. Place this event procedure in a Worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

textInCell = Target.Value

If textInCell = “” Then Exit Sub

For n = 1 To Len(textInCell)

If Asc(Mid(textInCell, n, 1)) > 64 And Asc(Mid(textInCell, n, 1)) < 91 Then

Application.Speech.Speak “Capital ” & Mid(textInCell, n, 1)

Else

Application.Speech.Speak Mid(textInCell, n, 1)

End If

Next

Application.Speech.Speak (“spells ” & textInCell)

End Sub

Then, select a cell on the worksheet and if it contains a word it will spell the word and then say it. In the example file, A1 contains the word “Elephant”. If that cell is selected, it will be spelled out starting with “capital E” followed by the rest of the letters and then says “spells Elephant”.

You can download the file here. It also contains a greeting upon opening the workbook.

Welcome

#Excel Short and Sweet Tip #18 (Toggle Formatting on Worksheet) by David Hager

You might at some point want to view a section of a workbook without any user-applied formatting. It turns out that this is easy to do through conditional formatting. For this example, A4:G10 has data that has a simple table format applied, as shown below.

 Toggle1

There is a Data Validation list option in cell B2, with the list in A1:A2 (On, Off). Then, a conditional format is applied to A4:G10 with the formula =IF($B$2=”Off”,1,0).

 Toggle3

The important part of this process is selecting formatting options that look “normal”, such as no fill, no borders and a black font. When the Off value is selected, the formatting of the table “disappears”, as shown below.

 Toggle2

You can download the example file here.

ToggleFormatting

#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager

Most of the published methods that convert a numeric grade to a letter grade either use a lookup table or do not account for + or – scores. The following formula overcomes both of those limitations. For the following formula, the numeric grade is in cell A5.

=IF(A5>=90,”A”,IF(A5>=80,”B”,IF(A5>=70,”C”,IF(A5>=60,”D”,”F”))))&IF(A5>98,”+”,IF(A5<59,””,IF(OR(RIGHT(A5,1)={“1″,”2″,”0″}),”-“,IF(OR(RIGHT(A5,1)={“8″,”9″}),”+”,””))))

 

#Excel: Project Tracking Workbook by David Hager

This workbook is one of many Excel projects I have worked on over the years. I am sharing it here for the first time. I am not going to discuss how this application works but rather offer it up to the user as both a useful tool and an opportunity to explore the formulas used in its construction. It is important to note that the workbook is completely unprotected and contains no VBA code.
The ProjSetup worksheet is for data input. Column A is for the names of the projects to track. Column B is for the starting date of the project and Column C is for the finish date of the project. Column D is the priority of the project – low, medium or high. A Data Validation list allows for those values to be selected, and conditional formatting determines the color. Column F has an option box to select between days and weeks. The following figure shows these features.

xlProj1

The ProjTimeDisplay worksheet shows the project time schedules. This was constructed with Excel formulas and conditional formatting. Feel free to dig in to see how it works. Here is a figure to visualize the output.

xlProj2

The project workbook can be downloaded here.

xlProj

#Excel Short & Sweet Tip #16 (Multiple Delimiters with TEXTJOIN for Custom Formatting) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

it shows an example at the bottom of the article where the 1st argument of the TEXTJOIN function uses multiple delimiters. I knew that multiple delimiters could be used, but I could not think of an example where that might be useful. Then, I decided that it did have a useful purpose if it resulted in a desirable display result. So the first example is:

=TEXTJOIN(A7:D7, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula =CHAR(10).

As you can see in the figure, each record appears to be on a single line in the cell (F9).

TJMD1

This same technique can be enhanced further in this way.

=TEXTJOIN(A8:D8, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula

=CHAR(10)&REPT(“-“,CELL(“width”,H9)+NOW()*0)&CHAR(10).

With the TEXTJOIN formula example in cell H9, the appearance of the created string makes it very easy to see (in the following figure) each record.

TJMD2

The example file can be downloaded here.

TJ_MultDelimiters

#Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager

In a conversation with Bill Jelen about the TEXTJOIN function, he mentioned whether I had tried using 3D references as arguments. I said that I had not tried native 3D ranges, and I did not believe it would work. Well, I was wrong (again). It does work, in spite of there being no documentation from Microsoft about that ability. The figure below shows the result in cell D2 of using this formula:

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2)

TJ3D2.PNG

I continue to be amazed by the myriad of formula solutions made possible by the TEXTJOIN function. In a recent article where I demonstrated the use of non-contiguous ranges

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

I now realize that the same concept can be used with 3D ranges. (Here is the bonus!) In fact, that 3D range can be in ANOTHER workbook, as shown below.

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2,[TEXTJOIN_Native3DB.xls]Sheet1:Sheet2!A1:A2)

TJ3D1.PNG

The example file can be downloaded here (Note: I did not include the external file, and I put a tilde in front of that formula)

TEXTJOIN_Native3D

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

#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