nitt1995
06-16-2006, 06:25 AM
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!
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!