0

I found the following Macro to clean up a workbook of 60k+ styles: https://stackoverflow.com/a/8933399

Every time I run this macro Excel crashes with the error "The object invoked has disconnected from its clients" on the line For Each styleObj In wb.Styles. This causes styleObj to be Nothing (i.e. null) and crashes the script.

What am I doing wrong? the wb.Styles.Count lines work perfectly, however everything below that breaks. I am using Excel 2010.

Even simple code like this fails in the same way at the beginning of the for loop:

Sub KillCustomStyles()
Dim mpStyle As Style
    For Each mpStyle In ActiveWorkbook.Styles
        If Not mpStyle.BuiltIn Then
            mpStyle.Delete
        End If
    Next mpStyle
End Sub
VBA Pete
  • 2,656
  • 2
  • 24
  • 39
Christopher Sardegna
  • 98
  • 1
  • 9

2 Answers2

0

Had you stuck to the example you linked, it would have worked. If you do it the direct way...

For Each mpStyle In ActiveWorkbook.Styles
...
   mpStyle.Delete

...you are changing the very collection you are iterating through, i.e. your loop becomes invalid because you change its base.

Instead, you should create a separate collection of styles:

Sub KillCustomStyles()
Dim styleObj As Style
Dim dict As New Scripting.Dictionary    ' <- from Tools / References... / "Microsoft Scripting Runtime"
Dim aKey As Variant
Dim i As Integer
Dim Str As String

For Each styleObj In ActiveWorkbook.Styles
    Str = styleObj.NameLocal
    i = IIf(styleObj.BuiltIn, 1, 0) 'if builtin: 1, else 0
    Call dict.Add(Str, i)    ' First time:  adds keys
Next styleObj

For Each aKey In dict
    If dict.Item(aKey) = 0 Then '0: not builtin
        ActiveWorkbook.Styles(aKey).Delete
    End If
Next aKey

End Sub

This code is drawn almost 1:1 from the linked SO post. Look sharp and you see that first there is a separate collection filled with styles, you iterate over this collection, but delete from wb.Styles

LocEngineer
  • 2,847
  • 1
  • 16
  • 28
0

I think you forgot 'if mpstyle.locked = true' as I said in the original answer I am unsure what the .locked property is but this may be the reason it didn't work. Here is the code I use again:

Sub NukeStyles()
Dim tempstyle As Style

For Each tempstyle In ActiveWorkbook.Styles

If tempstyle.BuiltIn = False Then
   If tempstyle.Locked = True Then 'not sure what this is but I need it
      tempstyle.Delete
   End If
End If

Next tempstyle

End Sub 'NukeStyles

'--------------------------------------

LeasMaps
  • 300
  • 4
  • 14