Addin opened but not loaded

[Guest]

Hi everyone,

When I open an addin (right-clic on its name | Open), the addin is opened
and I can see the code in VBE. But the Addin is not "installed". In VBE, I
cannot "close" it so that its name disappears from the VB environment. To do
that, I have to close Excel and reopen it which is not very convenient.

The problem is that I have many versions of a particular addin (in different
folders) which I have to modify. but I cannot open another addin with the
same name as one that is already loaded.

I know how to list the addins that are installed but is there a way to list
the addins that are listed in VBE? Also, is there a way to close such an
addin without closing Excel?

Thank you.

 

[Tom Ogilvy]

I believe by installed, you are refering to the the list in Tools=>Addins.
If you opened it, then it is as usable as any of the installed addins. To
close it, in the immediate window of the VBE

Workbooks("MyAddin.xla").Close True

would be one way.

 

[Guest]

Hi Tom,

This line of code does not work and gives an error: The index does not
belong to the selection.

When I say "installed", I do refer to the list in Tools=>Addins where a
check mark is visible. Those addins can easily be listed as the following
procedure does.

' ***********************************************************
Sub ListAddins()
Dim XL As Excel.Application
Dim ai As AddIn
Dim strMessage As String
Dim AddinInstalled() As String
Dim intI As Integer
Dim intJ As Integer

On Error Resume Next
Set XL = GetObject(, "Excel.Application")
If XL Is Nothing Then Exit Sub

strMessage = "List of all addins:" & vbCrLf & vbCrLf
intI = 0
intJ = 0
For Each ai In XL.Addins
intJ = intJ + 1
strMessage = strMessage & intJ & "- " & ai.Name & vbCrLf
If ai.Installed Then

' Add its name to a dynamic array.
intI = intI + 1
ReDim Preserve AddinInstalled(intI)
AddinInstalled(intI) = ai.Name
End If
Next

' Add the installed addins to the message.
strMessage = strMessage & vbCrLf & vbCrLf
strMessage = strMessage & "Addins installed: " & vbCrLf & vbCrLf
For intJ = 1 To intI
strMessage = strMessage & AddinInstalled(intJ) & vbCrLf
Next intJ

MsgBox strMessage
ReDim AddinInstalled(0)
Set XL = Nothing
End Sub
' ***********************************************************

When I open an addin, it is not necessarily installed, but it always appears
in the VBE Project Explorer's list. I want to know if it is possible to list
those projects' names and eventuelly to close one of them (there is no Close
command in the VBE menu). I tried this code that obviously does not solve my
problem.

' ***********************************************************
Sub TestVBE()
Dim intNbThings As Integer
Dim intI As Integer
Dim strMessage As String
intNbThings = Application.VBE.MainWindow.Collection.Count
strMessage = "Number of elements: " & intNbThings & vbCrLf & vbCrLf
For intI = 1 To intNbThings
strMessage = strMessage & intI & "- " & _
Application.VBE.MainWindow.Collection.Item(intI).Caption & vbCrLf
Next intI
MsgBox strMessage
End Sub
' ***********************************************************

Thank you for your help.

 

[Tom Ogilvy]

? Workbooks("MyAddin.xla").Name
myAddin.xla
? workbooks("MyAddin.xla").IsAddin
True
workbooks("MyAddin.xla").Close Savechanges:=False

final line worked fine for me.

 

[Guest]

Hi again Tom,

It effectively works fine in the Execution Window. But if I write a macro
like this one:

'************************************************************
Sub AddinClose()
Workbooks("AlgoLDT.xla").Close SaveChanges:=False
End Sub
'************************************************************
.... it gives an error (the name is spelled correctly). Is that normal?

With a macro, I can place a button on a toolbar that executes it...

Is it possible to list the projects displayed in the VBE Project Explorer
window?

Thank you for your time.

 

[Tom Ogilvy]

Sub CloseAddin()
Workbooks("Myaddin_aa.xla").Close SaveChanges:=False
End Sub

worked fine for me.

Sub ListProjectes()
Dim pr As VBProject
On Error Resume Next
For Each pr In ThisWorkbook.VBProject.Collection
Debug.Print pr.Name
Debug.Print " -- " & pr.Filename
Next
End Sub

Lists the project name (the part not in parens in the project explorer) and
the filename.

 

[Guest]

Hi Tom,

It does work in 3 out of 4 cases. In one particular case, the addin has an
Auto_Close macro that must be checked out. It remains in the VBE projects
list but cannot be accessed any more. I will investigate the case.

Thank you for your time and your precious help.

 

[Ric Payne]

It seems there is a difference between item.name and item.title

For example HTML.XLA is the "name" of the addin but "Internet
Assistant VBA" is its "title" and I found that if its name and title
were different it can only be installed if its Title is referred to.

To uninstall addins I used the following code which stored the name
and title in a sheet so that i could reinstall it when i closed the
workbook.

............
i=0
j=0
For Each Item In AddIns

If Item.Installed = True Then
On Error Resume Next
i = i + 1
J = J + 1
strAddName = Item.Name
Worksheets(shtName).Cells(i, 2) = Item.Name
Worksheets(shtName).Cells(i, 3) = Item.Title
Item.Installed = False

End If

Next Item

..............

Then I reinstalled with :

nRow = Worksheets(shtName).[a16384].End(xlUp).Row

For i = 1 To nRow
If Not IsEmpty(Worksheets(shtName).Cells(i, 2)) Then
On Error Resume Next
strAddName = Worksheets(shtName).Cells(i, 3) 'this is the
TITLE
AddIns(strAddName).Installed = True
Worksheets(shtName).Cells(i, 2).ClearContents
Worksheets(shtName).Cells(i, 3).ClearContents
End If
Next

On Error GoTo 0

........................

My code is clunky by the standards of the people who contribute here
but it seems to work.

Regards,
Ric Payne

 

[Dave Peterson]

Sometimes when workbooks close, the project is still displayed in the project
explorer.

It appears to be a bug (to me).

I've never seen it cause any trouble, but there have been lots of posts
discussing this "feature."

 

[Guest]

Hi Ric,

I checked out your Name and Title comment and you're right. It adds up to my
knowledge of addins.

Thank you very much.

It seems there is a difference between item.name and item.title

For example HTML.XLA is the "name" of the addin but "Internet
Assistant VBA" is its "title" and I found that if its name and title
were different it can only be installed if its Title is referred to.

To uninstall addins I used the following code which stored the name
and title in a sheet so that i could reinstall it when i closed the
workbook.

............
i=0
j=0
For Each Item In AddIns

If Item.Installed = True Then
On Error Resume Next
i = i + 1
J = J + 1
strAddName = Item.Name
Worksheets(shtName).Cells(i, 2) = Item.Name
Worksheets(shtName).Cells(i, 3) = Item.Title
Item.Installed = False

End If

Next Item

..............

Then I reinstalled with :

nRow = Worksheets(shtName).[a16384].End(xlUp).Row

For i = 1 To nRow
If Not IsEmpty(Worksheets(shtName).Cells(i, 2)) Then
On Error Resume Next
strAddName = Worksheets(shtName).Cells(i, 3) 'this is the
TITLE
AddIns(strAddName).Installed = True
Worksheets(shtName).Cells(i, 2).ClearContents
Worksheets(shtName).Cells(i, 3).ClearContents
End If
Next

On Error GoTo 0

........................

My code is clunky by the standards of the people who contribute here
but it seems to work.

Regards,
Ric Payne

Hi Tom,

It does work in 3 out of 4 cases. In one particular case, the addin has an
Auto_Close macro that must be checked out. It remains in the VBE projects
list but cannot be accessed any more. I will investigate the case.

Thank you for your time and your precious help.


In
VBE, I
way to
list