Check Your Premises

August 26, 2008 – 12:47 pm by JP

When you are using early bound code for your own personal applications, it's easy to ensure that the proper object libraries are referenced: Simply go to Tools > References and check off the libraries you need. Then you can code using fully qualified references like "Excel.Range" and "Outlook.Application".

If you are writing code for others, however, there's no guarantee that the libraries will be referenced or even installed. For example, if your code uses ADO to utilize an Access database, your code needs to check if ADO is referenced and available before making any attempt to use ADO code.

I've written previously about version checking (see Make your Office add-in version-independent); here are some sample subs that can make it happen. This first sub will list the references from the current workbook directly onto the worksheet, so you can see the names of each project. I saw this code somewhere else, I think it was Colo's Excel Junk Room, but I wasn't able to find a link, so I rolled my own. The Name Property is what we're most concerned with; it's how we are going to identify references later on in the "live" code.

plain text version

VBA:

Sub ListProjectReferences()
Dim NextRow As Long
Dim NextCol As Long
Dim lCount As Long
Dim objRef As VBIDE.Reference
Dim rng As Excel.Range
Set rng = Range("A1")
NextRow = 0
NextCol = 1
With Application.VBE.ActiveVBProject.References
For lCount = 1 To .Count
Set objRef = .item(lCount)
Debug.Print objRef.Name & " " & objRef.FullPath
Cells(NextRow + 1, NextCol).Value = objRef.Name
Cells(NextRow + 1, NextCol + 1).Value = objRef.FullPath
NextRow = NextRow + 1
Next lCount
End With
ExitProc:
Set rng = Nothing
Set objRef = Nothing
End Sub

The above code requires a reference to the Microsoft Visual Basic for Applications Extensibility library. As an alternative, you can change "Dim objRef As VBIDE.Reference" to "Dim objRef As Object" and it will work without the reference. The name and path of each currently referenced object library is written to the worksheet, starting in cell A1. Go ahead and test this out by setting a few references to other libraries and re-running the code to see the Name and the full path.

Now that you have the names of each reference, you can loop through the collection quickly to see if the appropriate libraries needed for your application are already referenced. Then you can either move on to the purpose of the code, or create the object library references if they didn't already exist.

For example, if we needed a reference to Outlook and DAO for our application, we run the code above and learn that, if referenced, the names are "Outlook" and "DAO" respectively, for each object library. Here's some startup code that would check if they are already referenced. We loop through the references and check the name, and if it matches, we use two Boolean variables to store the result.

plain text version

VBA:

Private Sub Workbook_Open()
Dim lCount As Long
Dim strRefName As String
Dim bIsOutlookLibReferencedAtStartup As Boolean
Dim bIsDAOLibReferencedAtStartup As Boolean
' check if each needed object library is already referenced
With Application.VBE.ActiveVBProject.References
For lCount = 1 To .Count
strRefName = .item(lCount).Name
If strRefName = "Outlook" Then
bIsOutlookLibReferencedAtStartup = True
ElseIf strRefName = "DAO" Then
bIsDAOLibReferencedAtStartup = True
End If
Next lCount
End With
End Sub

If our Boolean variable is False, then the needed reference doesn't already exist, so we need to add it. We'll continue with Outlook only. We'll use a separate single-purpose sub called AddVBRef (below) and pass the reference name we want to add as an argument. If successful, we use another boolean variable to remember that we referenced Outlook, so we can de-reference it later.

plain text version

VBA:

Dim bWeStartedOutlook As Boolean
If Not bIsOutlookLibReferencedAtStartup Then
' Outlook was not already referenced when we started
If Not AddVBRef("Outlook") Then
MsgBox "This add-in requires a reference to Outlook object library." & _
" Please make sure that Outlook is installed on your" & _
"computer and that you can set a reference to it manually.", vbCritical
ThisWorkbook.Close False
Exit Sub
Else
' we were able to successfully reference Outlook
bWeStartedOutlook = True
End If
End If

plain text version

VBA:

Private Function AddVBRef(strRefName As String) As Boolean
' short sub that takes argument and adds references whatever
' reference is passed to it, returns True/False to calling sub
AddVBRef = False
Dim OutlookStartFolder As String
With Application.VBE.ActiveVBProject.References
Select Case strRefName
Case "Outlook"
OutlookStartFolder = OFCStartFolder("Outlook")
Select Case Application.Version
Case Is <10 ' Office 2000
On Error Resume Next
.AddFromFile OutlookStartFolder & "\msoutl9.olb"
On Error GoTo 0
If Err = 0 Then AddVBRef = True
Exit Function
Case Is>= 10 ' Office 2002-2007
On Error Resume Next
.AddFromFile OutlookStartFolder & "\msoutl.olb"
On Error GoTo 0
If Err = 0 Then AddVBRef = True
Exit Function
End Select
End Select
End With
End Function

Right now AddVBRef only adds a reference to Outlook; you would add additional Case statements, using the Name you want to add. Also keep in mind that it only works for Office programs; the function calls another function, OFCStartFolder which returns the install path for whatever Office program you pass to it:

plain text version

VBA:

Function OFCStartFolder(strProgram As String, Optional lVersion As Long) As String
' from Jon Peltier, slightly modded by Jimmy Pena 8-11-08
'
' strProgram can be "Excel", "Word", "Access", "PowerPoint", "Outlook", "Publisher"
' lVersion can be 2007, 2003, 2003, 2000 - if blank, it looks for highest version available
'
Dim wsh As Object
Dim i As Integer
Dim sReg As String
Dim lVerNum As Long
If lVersion <> 0 Then
' We are looking for a specific version's install folder, i.e. if someone has multiple versions
' and we want to hook a specific version.
' However, the point of an add-in is that we don't know what version the end user has, so
' this part of the IF loop should rarely be used.
Select Case lVersion
Case 2007
lVerNum = 12
Case 2003
lVerNum = 11
Case 2002
lVerNum = 10
Case 2000
lVerNum = 9
Case Else
MsgBox "Please specify a valid version."
GoTo ExitProc
End Select
sReg = "HKLM\SOFTWARE\Microsoft\Office\" & lVerNum & ".0\" & strProgram & "\InstallRoot\Path"
Set wsh = CreateObject("Wscript.Shell")
On Error Resume Next
OFCStartFolder = wsh.RegRead(sReg)
On Error GoTo 0
Else
' no version was specified, just find highest version
Set wsh = CreateObject("Wscript.Shell")
For i = 12 To 9 Step -1 ' (12 = 2007, 11 = 2003, 10 = 2002, 9 = 2000)
sReg = "HKLM\SOFTWARE\Microsoft\Office\" & CStr(i) & ".0\" & strProgram & "\InstallRoot\Path"
On Error Resume Next
OFCStartFolder = wsh.RegRead(sReg)
On Error GoTo 0
If Len(OFCStartFolder)> 0 Then Exit For
Next i
End If
End Function

And here is a companion sub that removes any reference passed to it. Again we are passing the Name Property here. We'll need this in case we referenced the object library, we must clean up after ourselves and remove that reference if our code is removed or uninstalled.

plain text version

VBA:

Private Function RemoveVBRef(strRefName As String)
' short sub that takes argument and de-references
' whatever reference is passed to it
Dim lCount As Long
With Application.VBE.ActiveVBProject.References
For lCount = 1 To .Count
If .item(lCount).Name = strRefName Then
.Remove .item(lCount)
Exit Function
End If
Next i
End With
End Function

Which would be used as follows in our shutdown code:

plain text version

VBA:

If bWeStartedOutlook Then RemoveVBRef ("Outlook")

Remember, bWeStartedOutlook would only be true if we manually set a reference to Outlook with the AddVBRef function, which itself would only be run if Outlook wasn't already referenced at startup.

One thing I should also mention is that the purpose of much of the code above is to check if we can use early bound code. If you are going to use late-bound code, you won't need much of it (just a check of the install path to see if the program is installed before we can reference any of its objects).

Oh and if anyone was interested, here is the computer I ended up buying: Gateway

Enjoy,
JP

Share and Enjoy:

Print This Post |

Email This Post | Permalink |

Subscribe now!

Filed Under: AddIns, Excel, VBA
Tags: add-in, Excel

This post has 75 views since August 26, 2008 – 12:47 pm.

2 Responses to “Check Your Premises”
Re "OFCStartFolder", it's dangerous to ask a user which version of anything they are using. What I would do is accept their best guess for version number, check that the reg key exists, if so they got lucky and the code can proceed, if not do the registry search for the possible versions.

Alternatively you could check the registry for available versions first, then let the user select, with the default being the most recent.

By Jon Peltier on Aug 27, 2008
The OFCStartFolder function was meant to be tweaked by the programmer, it wouldn't really be something the end user would access. I just added the Select Case in there to make it more flexible and to demonstrate the available options. I mentioned in the code comment that for the most part, you wouldn't know which version is highest version installed on someone else's computer, so that part of the code isn't something you would want to use often.

Thx,
JP

By JP on Aug 27, 2008