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
oWdObj.Visible = True
oWdObj.ActiveDocument.Paragraphs.First.Range.InsertAfter ” ” & stest
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.