Solved: Can't see Reference as object

[nitt1995]

I am a VBA newbe trying to write a Excel Add-in for a project. This add-in takes vba modules that are created by the user in another program and imports them into a new workbook.

These imported modules contain calls to functions and subroutines that are located in the add-in. The only way I found for the module to call functions from the add-in is if the add-in is added as a Reference to the module's workbook.

I found a set of commands to add the reference to the workbook, and a set of commands to see if the reference already exists. However, if I want to remove the reference from the file, I am having trouble.

This is what I am trying to do:
Sub RemoveAddinRegistration()
Dim Msg As String
Dim Ref As Reference
For Each Ref In ActiveWorkbook.VBProject.References
If Ref.Name = "SASM_Tool" Then
ActiveWorkbook.VBProject.References.Remove (Ref)
End If
Next Ref
End Sub
It does not recognize Reference as a valid object. If I replace Reference with Object, I can succesfully find the reference, but the Remove command doesn't work (because it wants Ref to be a Reference, not an Object)

This code does work
Sub CheckAddinRegistration()
Dim Msg As String
Dim Ref As Object
Msg = ""
For Each Ref In ActiveWorkbook.VBProject.References
Msg = Msg & Ref.Name & vbCrLf
Msg = Msg & Ref.FullPath & vbCrLf & vbCrLf
Next Ref
MsgBox Msg
End Sub
I am working with Excel 2002 and VBA 6.3

Thanks!

[Killian]

Hi and welcome to VBAX :hi:

AFAIK, there is no "Reference" data type available so you would have to declare it as an Object. The "remove" method should still be available (although it won't come up as you type in the IDE) since it is still valid.

I tested the routine below (Excel 2003, VBA 6.3) that works fine for me (provided I have checked: Tools>Macro>Security "Trust access to Visual Basic Project"
Sub RemoveAddinRegistration()
Dim Ref As Object
'previously added a ref to "AcroIEHelper 1.0 Type Library"
For Each Ref In ActiveWorkbook.VBProject.References
If Ref.Name = "ACROIEHELPERLib" Then
ActiveWorkbook.VBProject.References.Remove Ref
End If
Next Ref
End Sub

[Zack Barresse]

Welcome to the board!


Two things:

1: An Object variable will not show any Intellisense. Hence why Remove does not show up as valid but still works.

2: Setting a variable as [b]Reference[b] type DOES work, but you need to set a reference (Tools | References) to [b]Microsoft Visual Basic for Applications Extensibility 5.3[b].

HTH

[nitt1995]

How do I get the VBA Extensibility library. I don't see this under Tools->References

[nitt1995]

Hi and welcome to VBAX :hi:

AFAIK, there is no "Reference" data type available so you would have to declare it as an Object. The "remove" method should still be available (although it won't come up as you type in the IDE) since it is still valid.

I tested the routine below (Excel 2003, VBA 6.3) that works fine for me (provided I have checked: Tools>Macro>Security "Trust access to Visual Basic Project"
Sub RemoveAddinRegistration()
Dim Ref As Object
'previously added a ref to "AcroIEHelper 1.0 Type Library"
For Each Ref In ActiveWorkbook.VBProject.References
If Ref.Name = "ACROIEHELPERLib" Then
ActiveWorkbook.VBProject.References.Remove Ref
End If
Next Ref
End Sub

Thanks for your help. I removed the quotation marks around Ref and it worked.

Thanks!

[Zack Barresse]

The reference is under Tools | References, and it is ..

Microsoft Visual Basic for Applications Extensibility 5.3