Removing VBE reference via code (Updating an add-in)

[krinid]

How can I remove a VBE reference via code?

I'm trying to use this code, but it's spawning error 438, saying the object or method is not supported:
ThisWorkbook.VBProject.References.Remove (ThisWorkbook.VBProject.References(strFilename))

The whole description:
I'm creating a script to update an add-in currently in use AND referenced by open workbooks via VBE. So I need to:
a) disable add-in
b) delete VBE ref
c) update add-in
d) reassert VBE ref
e) reenable add-in

I've got parts a), c), d) and e) working okay, but b) seems to be throwing the error (as above).

This is the entire code snippet, in case they have some interdepencies:

strFilename = "ValidFileName.xla"
strFilePath = "C:\ValidFilePath\" & strFilename
AddIns(strFilename).Installed = False
ThisWorkbook.VBProject.References.Remove (ThisWorkbook.VBProject.References(strFilename))
AddIns.Add filename:=strFilepath
ThisWorkbook.VBProject.References.AddFromFile (strFilename)
AddIns(strFilename).Installed = True

 

[krinid]

FYI
Problem solved using the following correction to the line generating the error:

ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References(strRefName)

where strRefName is the VB project name (eg: by default VBProject).

I wonder which reference would be removed when there are several references with the same name. hmm

 

[VBAgent]

Hi,
AFAIK, referenced workbooks have to have unique VBA project names (Project Name in Tools > <ProjectName>, with default VBAProject in excel).

V

 

[krinid]

Looks like it's possible - I opened several new projects and they all had the name 'VBAProject', and they all appear as such in the VBE refs section (and thus checking one is ambiguous as you don't know which project you're referencing). Alternatively, I changed the project names all to "TEST" and it generated no errors.

 

[VBAgent]

Hi,
The References collection consists only of referenced (ticked) items, in opposite to available libraries shown in the dialog. What I meant, there is no problem to dereference a given library as it is not possible to duplicate library/project names in references ("Name conflicts with existing module, project or object library" message).

V