Как преодолеть зависимость проги от версии MS Office?

On Error resume next
  Err.clear
  Set runXL = GetObject(, "Excel.Application")
  If Err.Number<>0 Then
     Set runXL = GetObject("", "Excel.Application") ' тоже самое что и CreateObject в таком случае
  End If
  On Error GoTo 0

Adding and Deleting References Programmatically

The Visual Basic Editor References collection contains one Reference object per selected reference (Tools menu, References command) in a given project. You access the References collection by using the VBProject object's References property. I've created a reusable function that adds a reference to a project. The calling function need only provide the project name along with the full path to the reference, as follows:

Public Function AddReference(ByVal ProjectName As String, _
        ByVal ReferencePath As String) As Boolean

    ' Purpose: Adds a reference to the target project in the VBE.
    ' Accepts:
    '   ProjectName: Target project name.
    '   ReferencePath: Full path to the target reference.
    ' Returns:
    '   True if the reference was successfully added.
   
    Dim oVBE As VBIDE.VBE
    Dim oVP As VBIDE.VBProject
    Dim oRefs As VBIDE.References
    Dim oRef As VBIDE.Reference
    Dim bRefSet As Boolean
   
    On Error GoTo AddReference_Err
   
    Set oVBE = Application.VBE
    Set oRefs = oVBE.VBProjects(ProjectName).References
   
    bRefSet = False
   
    For Each oRef In oRefs
        ' Can't add reference more than once.
        If oRef.FullPath = ReferencePath Then
            bRefSet = True
        End If
    Next oRef
   
    If bRefSet = False Then
        oRefs.AddFromFile ReferencePath
    End If
   
AddReference_End:
    AddReference = True
    Exit Function
   
AddReference_Err:
    MsgBox "Error " & Err.Number & " in AddReference: " & Err.Description
    AddReference = False
       
End Function

Because you can't add a particular reference more than once, I needed to set and check a Boolean value in the preceding code that adds the reference only if it hasn't already been added.

You can call the AddReference function with code similar to the following:

Public Sub TestAddReference()

    ' Purpose: Tests the AddReference function.
   
    Const REF_PATH As String = "C:\WINDOWS\System32\scrrun.dll"
   
    ' Add a reference to the Microsoft Scripting Runtime.
    If AddReference("Normal", REF_PATH) = True Then
        MsgBox "Reference to '" & REF_PATH & "' has been added!"
    Else
        MsgBox "Reference to '" & REF_PATH & "' could not be added!"
    End If
   
End Sub

The preceding code added a reference to the Microsoft Scripting Runtime located at C:\WINDOWS\System32\scrrun.dll. The reference was added to the Normal project.
Note   In the preceding sample code and the TestDeleteReference procedure later in this article, the path to scrrun.dll may be different on your computer. Also, the first argument to the AddReference and DeleteReference procedure call may need to be changed to something other than Normal (for example, Microsoft Access doesn't include a default Normal project).

Similarly, I've created a reusable function that deletes a reference, which looks almost identical to the AddReference function, as follows:

Public Function DeleteReference(ByVal ProjectName As String, _
        ByVal ReferencePath As String) As Boolean

    ' Purpose: Deletes a reference from the target project in the VBE.
    ' Accepts:
    '   ProjectName: Target project name.
    '   ReferencePath: Full path to the target reference.
    ' Returns:
    '   True if the reference was successfully deleted.
   
    Dim oVBE As VBIDE.VBE
    Dim oVP As VBIDE.VBProject
    Dim oRefs As VBIDE.References
    Dim oRef As VBIDE.Reference
   
    On Error GoTo DeleteReference_Err
       
    Set oVBE = Application.VBE
    Set oRefs = oVBE.VBProjects(ProjectName).References
   
    For Each oRef In oRefs
        If oRef.FullPath = ReferencePath Then
            oRefs.Remove oRef
        End If
    Next oRef
   
DeleteReference_End:
    DeleteReference = True
    Exit Function
   
DeleteReference_Err:
    MsgBox "Error " & Err.Number & " in DeleteReference: " & Err.Description
    DeleteReference = False
   
End Function

You can call the DeleteReference function with code similar to the following:

Public Sub TestDeleteReference()

    ' Purpose: Tests the DeleteReference function.
   
    Const REF_PATH As String = "C:\WINDOWS\System32\scrrun.dll"
   
    ' Deletes the reference to the Microsoft Scripting Runtime.
    If AddReference("Normal", REF_PATH) = True Then
        MsgBox "Reference to '" & REF_PATH & "' has been deleted!"
    Else
        MsgBox "Reference to '" & REF_PATH & "' could not be deleted!"
    End If
   
End Sub

The preceding code deleted the reference to the Microsoft Scripting Runtime located at C:\WINDOWS\System32\scrrun.dll. The reference was deleted from the Normal project.