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.

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/03/lookup-a-bible-verse-using-excel-wo-vba-by-david-hager/

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

=Translate(mString,LangCode)&T(NOW())

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.

BibleVerseMultLanguages1

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.

BibleVerseMultLanguages

#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()

UserForm1.Show

End Function

Then enter this formula in cell A1.

=UForm()

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:

=A2&UForm()

as shown in the following figure:

xlSS026_1

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.

http://spreadsheetpage.com/index.php/tip/playing_sound_from_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

=PlayWAV(rand_sound)

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.

https://exceloffthegrid.com/controlling-word-from-excel-using-vba/

https://exceloffthegrid.com/controlling-powerpoint-from-excel-using-vba/

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.

http://dailydoseofexcel.com/archives/2005/04/25/automating-mappoint/

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.Documents.Add

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.

Automate1

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.

http://chandoo.org/wp/2011/08/03/create-powerpoint-presentations-using-excel-vba/

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

Automate2

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.

AutomateUDF

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

Else

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

GiveV1

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.

GiveV2

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.

https://dhexcel1.wordpress.com/

The example file can be downloaded here.

VoiceVar

#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.

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

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.

ModShape_NF1

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.

ModShape_NewFeatures