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”)
Set olCi = molApp.CreateItem(olCONTACTITEM)
If TypeName(vFullName) = “Range” Then
sFullName = vFullName.Value
sFullName = vFullName
olCi.FullName = sFullName
Select Case UCase(sName)
ParseName = olCi.firstname
ParseName = olCi.MiddleName
ParseName = olCi.LastName
Set olCi = Nothing
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?