Tag Archives: UDF

Bible Verse In Any Language Using #Excel by David Hager


I recently demonstrated what became a popular Excel technique – looking up a Bible verse using Excel’s web functions. On LinkedIn, I received a comment from Raul stating that he did not understand English, only Spanish. So, I decided to add language translating to the model. I tried to find a (free) way to do this directly from an API, but I could not. I turned instead to the translation technique I had already published which use a VBA procedure. I combined both of the methods as demonstrated in the links shown below.



You can read both of these articles to see the details of how each was constructed. Meanwhile, the key formula in cell H5 is:


where mString = IF(ISERROR(FIND(“/b>”,oString)),oString,MID(oString,FIND(“/b>”,oString)+3,255))

and oString = FILTERXML(WEBSERVICE(“http://labs.bible.org/api/?passage=”&TheBook&” “&TheChapter&”:”&TheVerse&”&type=xml”),”//text”)

and LangCode = INDEX(LanguageCodes,MATCH(Language,LanguageNames,0))

Use the dropdowns in H2:J2 to select verse and language.


Sometimes the query has to be run twice in order to work. I have not been able to solve this problem, so please run the query a 2nd time if the cell containing the verse is blank. Alternatively, if you click in the formula in H5 and press Enter, it should calculate as desired. The problem is likely due to the Excel web functions becoming confused during recalculation. I attempted to correct this by adding &T(NOW()) to the end of the formula in cell H5. It appeared to help, but I cannot guarantee it. You can also try pressing Ctrl-Alt-F9 for recalculation.

This should be useful to everyone worldwide.

You can download the file here.


#Excel Short and Sweet Tip #26 (Showing an UserForm With a Worksheet UDF) by David Hager


When looking at the vagaries of a Worksheet UDF, it appeared to me that anything viewed by Excel as an object could be invoked by the UDF. So, I thought, what is a large Excel object that may not work with this methodology? After a while, I thought of an Userform. Surely, I cannot show an Userform in this way. But, I WAS WRONG!

Open a new workbook and go to the Visual Basic Editor (VBE) and add an Userform. Then, add a standard module and add this code to it.

Function UForm()


End Function

Then enter this formula in cell A1.


And presto, the userform appears!

Obviously, any userform, including those of your elaborate design, can be shown in this way. To have this UDF run when a specific cell is recalculated (in this case, A2), you can use a formula like:


as shown in the following figure:


Have fun with this!

#Excel Short and Sweet Tip #25 (Random Sound – Worksheet UDF) by David Hager


Once again, I am borrowing from an Excel technique from John Walkenbach, in this case playing a .wav file in Excel.


I have modified his code by coverting it to a function procedure with one argument for running a specified wav file. Copy into a module in the VBE.

Private Declare Function PlaySound Lib “winmm.dll” _

Alias “PlaySoundA” (ByVal lpszName As String, _

ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0

Const SND_ASYNC = &H1

Const SND_FILENAME = &H20000

Function PlayWAV(fName As String)

PlayWAV = “”

WAVFile = fName & “.wav”

WAVFile = ThisWorkbook.Path & “\” & WAVFile

Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)

End Function

This procedure assumes that the wav files to be played are in the same folder as the workbook.

In order to play a random sound, I made these 2 defined name formulas.

soundlist ={“chicken”,”horse”,”kitten”,”owl”,”cow”}

rand_sound =INDEX(soundlist,INT(RAND()*COUNTA(soundlist))+1)

So, the .wav files starting with, in this example, (chicken, horse, kitten, owl and cow) must exist for this function to work. You must personalize this array so that this technique will work with your own wav files.

Now, enter this function in a worksheet cell


Each time that the worksheet is recalculated, a random sound will play. I hope that you will find this useful.

Automating Word and PowerPoint from #Excel with a Worksheet UDF by David Hager


I have been following Mark’s recent posts at https://exceloffthegrid.com/ about automation from Excel.



That got me thinking about the use of user-defined functions in automating/instatiating other applications.

As it turns out, I first demonstrated the ability of user-defined functions to be used in automating an app (in this case Mappoint) in 2005 in this article on Dick Kuseika’s web site.


In the comments of this article, Jan Karel Pieterse (http://www.jkp-ads.com/ ) showed that the same thing could be done with Microsoft Word. I am using his example here to show that it does work.

Here is the code:

Function WriteResultToWord(stest As String)

Dim oWdObj As New Word.Application

Application.Volatile False

oWdObj.Visible = True


oWdObj.ActiveDocument.Paragraphs.First.Range.InsertAfter ” ” & stest

End Function

The result of entering this formula in cell E2 (=WriteResultToWord(D1)) is to open Word and insert the text into the blank document, shown in the following figure.


The next step was to find out if this technique would work with any other application. To test this on Power Point, I used this great example from Chandoo’s site.


I simply changed the Sub routine to a Function, with little modification (see code in the example file).

So, entering =AddChartsPowerPoint() in a cell opens Power Point and adds two charts (see below).


In this article I shared the technique of automation using a UDF. I am sure that you will extend these ideas in your own work.

Here is the example file.


Enable #Excel Formulas to Give an Audio Result 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


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


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.


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.


The example file can be downloaded here.


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


When collecting new links to publish in my Excel Super Links series, I try not to reuse the link in another article. I have been using Windows Explorer (with Windows Indexing enabled) to search the folder where I store these files and look for any files that might contain that link in order to prevent this from occurring. What I wanted was a way to access the File Explorer from the Excel environment. The following procedure performs this task very nicely.

Function WinExplore(TheFolder As String)

On Error Resume Next

Shell “Explorer.exe ” & TheFolder, vbNormalFocus

End Function

This function can be entered in a worksheet cell and when recalculated will open Explorer at the desired folder.

I hope that you find this useful.


#Excel: Exciting New Features – Using a Worksheet UDF to Modify Shapes on a Worksheet by David Hager


I recently published the following article about using a worksheet UDF to modify a shape on the worksheet.


As a brief review, cells B2 and C2 use a data validation list to populate the desired shape and color. Cells G2 and G3 contain the ModifyShape and ModifyShapeColor UDFs.

I have added 2 new features to this powerful technique. The first feature is the ability to change the size of the shape. Entering a value in cells C7 and C8 on the ShapeTest worksheet will change the size of the shape. I have added data validation to those cells to restrict values to the 0.5-2.0 range.

The other feature is the ability to add text to the shape. By entering the text message in cell B13, the new text is added to the shape. The following figure show the layout for the worksheet.


Here is the code for the UDF with the added features.

Function ModifyShape(ShapeNumber, ShapeType, Optional Vis As Boolean = True)

Application.Volatile True

With ActiveSheet.Shapes(ShapeNumber)

.AutoShapeType = ShapeType

.Visible = Vis

.DrawingObject.Characters.Text = Worksheets(“ShapeTest”).Range(“b13”).Value

.Height = .Height * Worksheets(“ShapeTest”).Range(“c7”).Value

.Width = .Width * Worksheets(“ShapeTest”).Range(“c8”).Value

ModifyShape = “done”

End With

End Function

There are a few more features that I plan to add at a future date. Enjoy!

You can download the file here.


#Excel Exchange Rate UDF With Symbol Lookup by David Hager

A number of Excel UDFs used to be available which utilized the Yahoo Finance API. Then, Yahoo changed its protocol for that financial data source, which caused those procedures to stop working. However, Google still has its financial converters exposed. In particular, we are interested in obtaining the current exchange rate from one currency to another.

The main procedure for returning exchange rates from Google Finance came from this site.


To use the UDF, under Tools, References in the VBE, scroll down the list and check:

Microsoft WinHttp Services, version 5.1

A table of currency codes and symbols was obtained from this web site.


The 2nd argument of the UDF (DestCur) is used to lookup the currency symbol associated with the desired currency. The following lines of code illustrate how this is done. In the main module, the following variable is declared.

Global SymbolToLookup As String

This has to be a global variable since it is going to be used in an event procedure in a worksheet module.

The lookup table is on the CurrencySymbols worksheet. This code returns currency symbol to be applied.

SymbolToLookup = Application.WorksheetFunction.Index([CurrencySymbols!C2:C110], Application.WorksheetFunction.Match(DestCur, [CurrencySymbols!B2:B110], 0))


Note the syntax of the two ranges. This is a shorthand method of passing ranges to worksheet functions used in VBA.

Then, the SymbolToLookup variable is passed to the event procedure in the Convert worksheet module, which fires after the UDF is entered in a cell.

Private Sub Worksheet_Change(ByVal Target As Range)

Target.NumberFormat = “General” & SymbolToLookup

End Sub

The following figure shows the result using the UDF plus the event procedure.


The file can be downloaded here.


#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: Creating a Environmental Variables Table with the VBA ENVIRON Function UDF by David Hager

Is it useful to obtain information about the various aspects of your PC operating system. This can easily be done with the VBA ENVIRON function. Since it is a VBA function, it cannot be used directly in a worksheet cell. The following UDF encapsulates the ENVIRON function, and this function can be used on the worksheet.

Function Env(Lposition As Variant)

Env = Environ(LPosition)

End Function

It is important to note that the variable Lposition is declared as a Variant so that both numeric and built-in Excel strings can be used by this UDF. I could not find a list of the strings on the net, but in this example I will show you how to make a list. The values listed below are for my computer.

A1: =Env(ROW()) returns ALLUSERSPROFILE=C:\ProgramData

B1: =LEFT(A1,FIND(“=”,A1)-1) returns ALLUSERSPROFILE

C1: =Env(B1) returns C:\ProgramData

Note that in B1 is the built-in string argument used by C1.

You can download the file here.


When you do, fill A1:C1 down to row 40 on the worksheet to make the environmental variable table for your computer. Column B will contain the built-in strings. I hope that you find this useful.

Excel Short & Sweet Tip #8 (VBA Function to Return Zodiac Sign) by David Hager

Here is a VBA function procedure that will allow you to return the sign of the zodiac for a given data.

Copy/paste it into a general module in the VBE.

Public Function ZodiacSign(BirthDate As Date) As String

Dim iDayofYear As Integer

iDayofYear = DateDiff(“d”, CDate(“1/1/” & Year(BirthDate)), BirthDate) + 1

If Year(BirthDate) Mod 4 = 0 And iDayofYear > 59 Then

iDayofYear = iDayofYear – 1

End If

If iDayofYear < 20 Then

ZodiacSign = “Capricorn”

ElseIf iDayofYear < 50 Then

ZodiacSign = “Aquarius”

ElseIf iDayofYear < 81 Then

ZodiacSign = “Pisces”

ElseIf iDayofYear < 111 Then

ZodiacSign = “Aries”

ElseIf iDayofYear < 142 Then

ZodiacSign = “Taurus”

ElseIf iDayofYear < 173 Then

ZodiacSign = “Gemini”

ElseIf iDayofYear < 205 Then

ZodiacSign = “Cancer”

ElseIf iDayofYear < 236 Then

ZodiacSign = “Leo”

ElseIf iDayofYear < 267 Then

ZodiacSign = “Virgo”

ElseIf iDayofYear < 297 Then

ZodiacSign = “Libra”

ElseIf iDayofYear < 327 Then

ZodiacSign = “Scorpio”

ElseIf iDayofYear < 357 Then

ZodiacSign = “Sagittarius”


ZodiacSign = “Capricorn”

End If

End Function

When entered in a worksheet cell (=ZodiacSign(A1)), where A1 contains the birthdate in question, the correct sign of the zodiac will be returned. Note that this part of the function code

If Year(BirthDate) Mod 4 = 0 And iDayofYear > 59 Then

iDayofYear = iDayofYear – 1

End If

adjusts the day of year if the year of birth is a leap year.

#Excel Worksheet UDF that Adds a Comment to Any Cell by David Hager

There was a lot of interest in my post on modifying a shape with a worksheet UDF.


The original idea was posted in 2007. I seem to remember, though, that the use of a UDF to modify cells occurred before that time. The initial discovery was that a UDF could add a cell comment to ANY cell. I can’t find the original reference, but this technique was last documented at:


I have modified the the UDF shown in that article to add a timestamp feature.

Function AddComment(rng As Range, str As String) As String

If Not rng.Comment Is Nothing Then rng.Comment.Delete

TimeStamp = Date & ” ” & Time

If Len(str) Then rng.AddComment.Text str & ” ” & TimeStamp

rng.Comment.Visible = True

End Function

In the example workbook, I entered the AddComment function in cell D6, but the range argument can point to any cell. In fact “range formulas” can also be used.

The INDEX, OFFSET and INDIRECT Excel functions all return ranges, so any formulas built with these functions can be used in a UDF where a range argument is required. The following example uses the INDEX function.

=AddComment(INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)),”MAX value in NumRange”)

where NumRange is defined as =OFFSET(A$1,,,COUNTA($A:$A),) ‘auto-expanding range

In this example, the formula INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)) returns the range of the cell containing the max value of NumRange, and as such it can be used in the first argument of the UDF. So, as numbers are added to column A as shown in the figure


the function will add a timestamped comment to any cell in that range that is the max value.

Obviously, there are numerous and more complex examples that can be built using this technique. I hope that you will find this useful in your projects.

The example file can be downloaded here.


#Excel: Four Super Filter Techniques by David Hager

I wanted to improve a few design features and eliminate a few bugs in some of my related recent posts,

so I have combined the concepts demonstrated by the following 4 articles located at:





into a single workbook. Among the changes I did/did not make are:

  1. No change to the formula that given occurance number for records in a filtered list.
  2. I moved the formula list from above the filtered list to another worksheet.
  3. I fixed some issues with the VBA code for the Filter Criteria UDF. I then located it with the
  4. the static list on the other worksheet.
  5. I included the Advanced Filter search with a custom list with this model.

In moving the static calculated list to another worksheet, I had significant problems adjusting the formulas to work in their new location. For the formula:

I had to add the sheets names in the parts of the formula that point to the filtered list on Sheet1. Also, to create the correct positioning of the first formula(s) in the list (place on row 3 instead of row 2), I had to change:




The FilterCriteriaEnh function was amended to fix several faults – an error handler was added to fix the scenario where .Criteria2 did not exist and adding a line of code (Criteria2=.Criteria2) in case it did (see code in the example workbook).

The criteria UDF was relocated to the row above the static list. The following formula in A1 on the Static worksheet is:


Note that the range in the UDF argument points at the filtered list on Sheet1.


The list used for the advanced filter lookup is also located on the Static worksheet. As expected, when I activated the advanced filter the filter criteria UDF did not return a result, since no “filter” was applied to the list.

I hope that I have explained what is available with this new filter model, but if not, please go back and reread the 4 base articles.

You can download the (enhanced) file here.



Using #Excel VBA to Create a Filter Criteria User Defined Function by David Hager

Some of the very best Excel work in the early years was done by Stephen Bullen. His legacy Excel site still exists, but it has not been updated in many years.


One of Stephen’s creations was a “simple” VBA function to return the applied criteria of a filtered list to a worksheet cell (shown below).


Function FilterCriteria(Rng As Range) As String

‘By Stephen Bullen

Dim Filter As String

Filter = “”

On Error GoTo Finish

With Rng.Parent.AutoFilter

If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

With .Filters(Rng.Column – .Range.Column + 1)

If Not .On Then GoTo Finish

Filter = .Criteria1

Select Case .Operator

Case xlAnd

Filter = Filter & ” AND ” & .Criteria2

Case xlOr

Filter = Filter & ” OR ” & .Criteria2

End Select

End With

End With


FilterCriteria = Filter

End Function

The use of this function is illustrated in the following figure. The formulas are in row 1.


Here is another view with criteria applied to other columns.


It also shows a limitation that this function had. Since the .Criteria1 and Criteria2 properties are strings, when criteria is set for a date column, the string contains Excel’s “date number”, not the date formatted number displayed in the cells. In order to workaround this limitation, I amended the function as shown below.

Function FilterCriteriaEnh(Rng As Range) As String ‘Enhanced to handle date filters

‘By Stephen Bullen and David Hager

Dim Filter As String

Dim Criteria2 As String

Filter = “”

sFormat = Application.Index(Rng, 2).NumberFormat

‘On Error GoTo Finish

With Rng.Parent.AutoFilter

If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

With .Filters(Rng.Column – .Range.Column + 1)

If Not .On Then GoTo Finish

Filter = .Criteria1

If sFormat = “m/d/yyyy” Then

Filter = Left(Filter, InStr(Filter, OnlyDigits(Filter)) – 1) & _

Format(OnlyDigits(Filter), sFormat)

On Error GoTo Finish

Criteria2 = Left(.Criteria2, InStr(.Criteria2, OnlyDigits(.Criteria2)) – 1) & _

Format(OnlyDigits(.Criteria2), sFormat)

End If

Select Case .Operator

Case xlAnd

Filter = Filter & ” AND ” & Criteria2

Case xlOr

Filter = Filter & ” OR ” & Criteria2

End Select

End With

End With


FilterCriteriaEnh = Filter

End Function


Function OnlyDigits(s As String) As String

With CreateObject(“vbscript.regexp”)

.Pattern = “\D”

.Global = True

OnlyDigits = .Replace(s, “”)

End With

End Function


First, I needed to capture the format from the column is question to see if it was date formatted.

sFormat = Application.Index(Rng, 2).NumberFormat

If sFormat = “m/d/yyyy” Then

Filter = Left(Filter, InStr(Filter, OnlyDigits(Filter)) – 1) & _

Format(OnlyDigits(Filter), sFormat)

The change in the string for the Filter variable is made by the formula shown above. The OnlyDigits function used in the formula construction is not original, but I do not know the source. It puts the string back together with the date replacing the date system number.

I did the same thing for Criteria2, but it will not exist if a second criteria is not selected in the filter, so I had to add error handling for that scenario.

On Error GoTo Finish

Criteria2 = Left(.Criteria2, InStr(.Criteria2, OnlyDigits(.Criteria2)) – 1) & _

Format(OnlyDigits(.Criteria2), sFormat)

A final filter list example using this enhanced function is shown below.


You can download the file for this here.