Just to add, but if anyone has both XL97 and later versions installed (won't
apply to many) using -
Major:=0, Minor:=0
may add the Office97 Existensiblity library 5.0 instead of 5.3 for XL2000
and later.
The proc below is probably overkill but I found it useful to keep in my
respective Personal's. Helped me ensure I had a reference to the correct
library for whichever version I was working in. Also helped as annoyingly it
always seemed that the wrong version would be listed in the dropdown and
would need to browse and find the right one.
Note this was only for my development use and would not normally add the
reference in a distributed workbook. As Chip mentioned, other code may break
due to a temporary missing reference (though there are ways round that).
I only add a reference to the library to get the intellisense. Otherwise I
declare all object variables 'As Object', ie late binding to avoid the need
to add any reference at all.
Sub AddExtRef(wb As Workbook, Optional bRemoveRef As Boolean)
Dim bXL9plus As Boolean
Dim bWrongRef As Boolean
Dim objRef As Object
Dim objRefs As Object
#If VBA6 Then
bXL9plus = True
#End If
Set objRefs = wb.VBProject.References
On Error Resume Next
Set objRef = objRefs("VBIDE")
On Error GoTo 0
If Not objRef Is Nothing Then
If (bXL9plus <> (objRef.Minor = 3)) Or bRemoveRef Then
' incompatible ext library for current xl version
objRefs.Remove objRef
Set objRef = Nothing
End If
End If
If objRef Is Nothing And Not bRemoveRef Then
Set objRef = objRefs.AddFromGuid("{0002E157-0000-0000-C000-000000000046}", _
5, IIf(bXL9plus, 3, 0))
'If no need to cater for xl97 change the continuation line to 0,0)
End If
'C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB 5.3
xl9+
'C:\Program Files\Common Files\Microsoft Shared\VBA\Vbeext1.olb 5.0 xl97
End Sub
What I tend to do is select the project in the VBE, then paste following in
the Immediate, with the cursor at the end of the line hit enter at -
Call Application.Run("Personal.xls!AddExtRef", ThisWorkbook)
Regards,
Peter T
