How to add and remove references using CATVBA

Let’s say you are exporting data from CATIA to Excel or CATIA to Word, but different coworkers have different versions of Excel (Excel 2010, Excel 2003, Excel, 2013, etc.). If your users have an earlier version of MS Excel than the person that last saved the application, then the MS Excel reference will become broken. If this is the scenario then you should develop your application using early binding, but then convert it to late binding and remove the MS Excel reference entirely before it is deployed. That way the reference will not be an issue. You must, of course, ensure that the code which automates MS Excel is compatible for all versions in use by the end users. I will get into early versus late binding more later but for now let’s pretend you already have an existing code using late binding.

A Reference in a VBA Project is a “pointer” to a type library or DLL file that defines various objects and entities and the properties and methods of those entities. You may need to check the reference libraries in CATIA programmatically to prevent any errors from happening and so your users don’t ever have to go into the VB editor. Or if there is a problem the user at least knows what to do to fix it.

The codes below show you how to:

• Check what version of Excel the user has
• Check what references are selected
• Remove a reference
• Add a reference from a file location
• Use a For Each Next loop

Sub CATMain()

Dim oExcel As Object 'late binding

Set oExcel = CreateObject("Excel.Application")

' retrieve VBE object, the root object that contains all other objects and collections represented in Visual Basic for Applications

Dim oVBE ' As VBE

Set oVBE = CreateObject("MSAPC.Apc").VBE

'Use the VBProjects collection to access the collection of projects

Dim project ' As VBProject

Set project = oVBE.ActiveVBProject

'count the number of references selected (to see them go to tools>references)

MsgBox "The total number of references selected is: " & project.References.Count

'if you want to loop through all the references to see which ones are selected you could use this.  Be careful though, if there are 100 references selected it will display 100 different message boxes.

Dim x As Integer

For x = 1 To project.References.Count

MsgBox project.References.Item(x).Description

Next

Dim Reference As Object

'----IF EXCEL VERSION IS 15.0------------------------check the version of Excel that the user has

If oExcel.Application.Version = "15.0" Then

MsgBox "Excel is version: " & oExcel.Application.Version

'since Excel should be version 15 we need to remove any references to 14, otherwise code may fail

 For Each Reference In project.References

       If Reference.Description = "Microsoft Excel 14.0 Object Library" Then

            

                project.References.Remove Reference

            

                MsgBox "Reference for Excel 14.0 found and removed."

            

             Else

          

       End If

Next

Dim i As Integer

i = 0

'Check to see if Excel 15.0 object library is already selected

  For Each Reference In project.References

       If Reference.Description = "Microsoft Excel 15.0 Object Library" Then

            

             i = i + 1

            Else

       End If

Next

If i > 0 Then

'Excel 15 reference already added

MsgBox "Excel 15 already added."

Else

'need to add excel 15

    project.References.AddFromFile "C:\Program Files\Microsoft Office\Office15\EXCEL.EXE"

'conversely, if you want to add 14 it might look like this:

' project.References.AddFromFile "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"

MsgBox "reference to 15 added"

End If

End If

End Sub

If there is a reference in the VB Project that refers to a library that VBA cannot find on the local machine, VBA marks that reference as MISSING in the References dialog box.

If the reference is already missing, you will probably not be able to remove it via VBA, but you can trigger a message box warning the user that he needs to manually remove the missing reference before the program can be run successfully.

Option Explicit

Sub RemoveRef()

Dim theRef As Variant, i As Long

Dim oVBE ' As VBE

Set oVBE = CreateObject("MSAPC.Apc").VBE

'Use the VBProjects collection to access the collection of projects

Dim project ' As VBProject

Set project = oVBE.ActiveVBProject

On Error Resume Next

For i = project.References.Count To 1 Step -1

Set theRef = project.References.Item(i)

If theRef.IsBroken = True Then

       project.References.Remove theRef

    End If

Next

If Err <> 0 Then

MsgBox "Missing ref encountered!" & "You will need to remove the reference manually.", vbCritical, "Unable to Remove Missing Reference"

End If

On Error GoTo 0

End Sub

Overall, the code could probably be better but I wanted to give a few examples on a couple of different things all at once and try to get you thinking. Have you ever had to do anything like this or had any trouble with references? Please let me know!