which add-ins?

I got a little fed up poking around trying to work out which version of which addins are loaded where.

I found some great starter code here (thanks guys). And added some stuff.

Its here so anyone else can use it (and I can find it wherever I happen to be), and if you have any suggestions then please leave a comment.

Sub ListAddins()
Dim oAddin As AddIn
Dim oCOMAddin As COMAddIn
Dim wb As Workbook
Dim sAddins As String
Dim RegisteredFunctions As Variant
Dim i As Integer
Dim j As Integer
Dim sd As String: sd = “|”
Dim p As Object
sAddins = “Standard Add-ins” & vbCrLf & “================” & vbCrLf
For Each oAddin In Application.AddIns
sAddins = sAddins & oAddin.Name & sd & oAddin.FullName & sd & oAddin.Installed & vbCrLf
Next oAddin

sAddins = sAddins & vbCrLf & “Standard wb Add-ins” & vbCrLf & “================” & vbCrLf
For Each wb In Application.Workbooks
‘If wb.IsAddin Then
sAddins = sAddins & wb.Name & sd & wb.FullName & sd & vbCrLf
‘End If
Next wb

sAddins = sAddins & vbCrLf & “COM Add-ins” & vbCrLf & “============” & vbCrLf
For Each oCOMAddin In Application.COMAddIns
sAddins = sAddins & oCOMAddin.progID & sd & oCOMAddin.Description & sd & oCOMAddin.Connect & vbCrLf
Next oCOMAddin

sAddins = sAddins & vbCrLf & “Xll Add-ins” & vbCrLf & “============” & vbCrLf
RegisteredFunctions = Application.RegisteredFunctions

If Not IsNull(RegisteredFunctions) Then
For i = LBound(RegisteredFunctions) To UBound(RegisteredFunctions)
For j = 1 To 3
sAddins = sAddins & RegisteredFunctions(i, j) & sd
Next j
sAddins = sAddins & vbCrLf
Next i
‘do nowt
End If

sAddins = sAddins & vbCrLf & “VBA Projects Add-ins/wbs” & vbCrLf & “============” & vbCrLf

For Each p In Application.VBE.VBProjects
sAddins = sAddins & p.FileName & vbCrLf

‘MsgBox sAddins
Debug.Print sAddins

End Sub

I can’t imagine for a minute that will copy out right, but I’ll load a proper version on Codematic when I have incorporated all your suggestions.

I know for example if you have a few market data apps loaded the xll reg functions list will exceed the immediate window capacity – so I just comment that out.

Improvement suggestions welcome, especially with code…



4 Responses to “which add-ins?”

  1. ross Says:

    I didnt know that people coded in power point!

  2. Nicholas Hebb Says:

    You must have a lot of add-ins to need this. I have a few tools that make debug printing a lot easier.

    The first is a StringBuilder class that lets you call Add() for each line and ToString() to return them all concatentaed with vbCrLf’s. Let me know if you’re interested and I’ll post it.

    The second is just a function to replace C style \n with vbCrLf:

    Public Function nl(ByVal sText As String) As String
    Const newLine As String = vbCrLf
    sText = Replace$(sText, ” \n “, newLine)
    nl = Replace$(sText, “\n”, newLine)
    End Function

    The two Replace() calls are for padded and un-padded \n’s. Simple, yet so time saving.

    The third is if you are really lazy:

    Public Function dp(Optional sText As String = “”)
    Debug.Print sText
    End Function

  3. Simon Says:

    Ross, Didn’t we cover that at the conf. :-)

    Nick – you wrapped debug.print?? I thought I was lazy…

    (actually I do have something similar so that I can show msgboxes during dev then switch to debug.print in production.)
    please do post your stringbuilder, its always handy to see other approaches.

  4. Nicholas Hebb Says:

    It’s true, I am lazy. The dp() sub is in with my VBA functions, but the Debug.Print wrapper I use in VB6 (dbp() – below) is more thorough and has a side benefit when used with MZTools. MZTools has a “Procedure Callers” tool, so if I’ve littered a call stack with debug.print’s it makes it easy to see where my debug calls are coming from in case I need to focus on an area and comment some out.

    As for the StringBuilder class, DDoE covered on this topic today, and I posted mine in the comments:

    Public Sub dbp(ParamArray vOut() As Variant)
    Dim i As Integer
    Dim sOut As String
    sOut = “”
    If DEBUG_MODE Then
    If UBound(vOut) < 0 Then
    sOut = vOut(0)
    For i = 1 To UBound(vOut)
    sOut = sOut & vbTab & vOut(i)
    Debug.Print sOut
    End If
    End If
    End Sub

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: