I want to turn Sub ParseNames into a function. The problem with using automation in user-defined functions is that the functions become extremely slow. Functions shouldn’t be slow. This particular use of automating Outlook really lends itself to being a function, however.
It would be nice if I could keep the Outlook Application variable live until the worksheet is finished recalculating. One use of the function would still be slow, but multiple uses would have the same overhead as one. That might be worth it.
One thought was to use the Worksheet_Calculate event to destroy the variable. Since the Calculate event runs after the calculation is complete, the variable would destroyed at the appropriate time. The downside to this is that you lose portability of the function. It would work in an add-in where you could control the public variable and have a class module to fire a calculate event for any worksheet that was calculated. But you couldn’t just copy the function into your project and have it work, you would have to have an add-in loaded.
Maybe getting rid of the Calculate event would work just as well. Does the variable really need to be destroyed when not in use? Probably, but I can’t think of a reason why. The function still loses a little of it’s portability in that you would need a module level variable to keep the Application object variable live for future function calls. The function I used to test this looks like:
Function ParseName(vFullName As Variant, _
Optional sName As String) As String
Dim olCi As Object
Dim rCell As Range
Dim sFullName As String
Const olDISCARD As Long = 1
Const olCONTACTITEM As Long = 2
If molApp Is Nothing Then
Set molApp = CreateObject(“Outlook.Application”)
End If
Set olCi = molApp.CreateItem(olCONTACTITEM)
If TypeName(vFullName) = “Range” Then
sFullName = vFullName.Value
Else
sFullName = vFullName
End If
olCi.FullName = sFullName
Select Case UCase(sName)
Case “FIRST”
ParseName = olCi.firstname
Case “MIDDLE”
ParseName = olCi.MiddleName
Case Else
ParseName = olCi.LastName
End Select
olCi.Close olDISCARD
Set olCi = Nothing
End Function
The function checks to see if the variable is properly set. If not, it uses CreateObject to instantiate Outlook which should remain available for future function calls. Omitting the Calculate event has the added benefit that Outlook doesn’t need to be instantiated for every recalc, just the first one where the function is present.
Using Excel 2000 on WinXP, closing the workbook removes the Outlook process every time, at least according to the Task Manager. I don’t have other operating systems available to test it on, however. So it seems that Excel does the proper clean up and I should have nothing to fear with this method. Yet, I’m still worried about it. What do you think?
Is the point here to demonstrate how to use automation inside of a function, or to actually parse a name? If it’s the latter, I think it would be much more efficient to write your own parsing code in VBA. Using automation just seems like overkill to m.
The point is the former, but I might disagree with you on efficiency. The final product would be more efficient, but to write code that parses it as effectively as Outlook does would take me about 100 years. Well, maybe only 50.
It may be that I give Outlook too much credit in this regard. It seems that for every exception that I code for, there’s always another one; or an exception to the exception.
I suppose so many exceptions are to be expected when you take something that Microsoft wrote for interactive GUI use and try to use it programmatically…
I think the only time you’ll see Excel fail to clean-up the outlook process is if Excel dies unexpectedly. When it crashes, it doesn’t necessarily have an opportunity to decrement the reference counts on the outlook COM object.
You could probably test the theory with the following command: “taskkill.exe /F /IM excel.exe”.
Hi Dick,
I’ve been toying with the same idea for some time now, but in the context of treating an XL workbook/sheet as a function.
It seems to work…kind of…in the limited testing I’ve done…but…
…but it violates so many documented and unwritten norms of XL/VBA programming that I’ve been reluctant to publicize the technique.
That said, the concept can be an awesome tool in one’s repertoire. An XL workbook serving as a function!