Disabled Addins

[ErikJan]

Declarations:

Code: Select all

Option Explicit
'
'Registry reading code based on examples here: www.vbforums.com/showthread.php?t=567903
'
Dim oReg As Object, Results() As String
Private Const HKEY_CURRENT_USER As Long = &H80000001
Private Const REG_BINARY = 3
Private Type RegData
    strKeyName As String
    strValueName As String
    strData As String
End Type
Dim Regd() As RegData

Find all disabled items and store keys and addin-names in array "Result"

Code: Select all

    Dim strPath As String, arrValues As Variant, strValue As Variant, arrTypes As Variant
    Dim i As Integer, j As Integer, Cnt As Integer, EJ As String, EJ_Flag As Integer
    '
    ReDim Regd(0)
    Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    strPath = "Software\Microsoft\Office\" & Application.Version & "\Excel\Resiliency\DisabledItems\"
    '
    oReg.enumvalues HKEY_CURRENT_USER, strPath, arrValues, arrTypes
    '
    If Not IsNull(arrValues) Then
        ReDim Preserve Regd(UBound(Regd) + UBound(arrValues) + 2)
        For i = 0 To UBound(arrValues)
            EJ = ""
            EJ_Flag = 0    'we look for ":\" (and there is a chr(0) in between)
            strValue = vbNullString
            Select Case arrTypes(i)
            Case REG_BINARY
                oReg.getbinaryvalue HKEY_CURRENT_USER, strPath, arrValues(i), strValue
                For j = 0 To UBound(strValue)
                    Select Case EJ_Flag
                    Case 0
                        EJ_Flag = IIf(strValue(j) = 58, 1, 0)
                    Case 1
                        EJ_Flag = IIf(strValue(j) = 0, 2, 0)
                    Case 2
                        EJ_Flag = IIf(strValue(j) = 92, 3, 0)
                    Case 3
                        If strValue(j) <> 0 Then EJ = EJ + Chr(strValue(j))    'Build the string
                    End Select
                Next
            End Select
            '
            Cnt = Cnt + 1
            ReDim Preserve Results(2, Cnt)
            Results(1, Cnt) = arrValues(i)
            Results(2, Cnt) = Extract(EJ)
        Next
    End If
    Set oReg = Nothing

Code could be smoother and shorter but it does work. In a separate routine, I can now delete the identified key from the registry (with some user interfacing etc.)

(The "extract" routine simply pulls the addin name from the string)

Code: Select all

Function Extract(Source As String) As String
    Dim i As String, j As Integer
    i = InStr(Source, ".xla")
    j = InStrRev(Source, "\")
    Extract = Mid(Source, j + 1, i - j - 1)
End Function

[HansV]

Thanks for sharing!

[ErikJan]

Slight problem... I declare the array like this:

Dim Results() As String

And resize the array when I get hits:

ReDim Preserve Results(2, Cnt)

And later I list (and process) results like this:

For i = 1 To UBound(Results)
Debug.Print Results(1, i), Results(2, i)
Next

However if the redim never fires, I get an error in the last (list) loop. How can I 'see' that?

[HansV]

Try to single-step through the code.

[ErikJan]

I know the problem... the UBound() give an error is the array never gets dimensioned... but how do I cope with that...

By the way... the for statement should be:

For i = 1 To UBound(Results,2)

[HansV]

You could use an error handler for that.

[ErikJan]

Someone reminded me that I never posted my code...

I've attached what worked for me; plse note that in my case it was always the same Add-in that got disabled so if the code finds any disabled Add-in, it assumes it is that one.

Excel needs to be re-started to re-load the addin (of course that can be done automatically as well).

[Jan Karel Pieterse]

Thanks!