Using collections in VBA instead of dictionary

Question

How can I replicate the following code with VBA collections rather than dictionaries? (I cannot access from Tools / References... / "Microsoft Scripting Runtime" at work)

The idea is to clean up any unused styles in VBA (https://stackoverflow.com/a/8933399/19794809)

Public Sub DropUnusedStyles()

    Dim styleObj As Style
    Dim rngCell As Range
    Dim wb As Workbook
    Dim wsh As Worksheet
    Dim str As String
    Dim iStyleCount As Long
    Dim dict As New Scripting.Dictionary

    Set wb = ThisWorkbook ' choose this module's workbook

    MsgBox "BEGINNING # of styles in workbook: " & wb.Styles.Count

    ' dict := list of styles
    For Each styleObj In wb.Styles
        str = styleObj.NameLocal
        iStyleCount = iStyleCount + 1
        Call dict.Add(str, 0)    ' First time:  adds keys
    Next styleObj

    For Each wsh In wb.Worksheets
        If wsh.Visible Then
            For Each rngCell In wsh.UsedRange.Cells
                str = rngCell.Style
                dict.Item(str) = dict.Item(str) + 1     ' This time:  counts occurrences
            Next rngCell
        End If
    Next wsh  

    ' Try to delete unused styles
    Dim aKey As Variant
    On Error Resume Next    ' wb.Styles(aKey).Delete may throw error

    For Each aKey In dict.Keys

        ' display count & stylename
        '    e.g. "24   Normal"
        Debug.Print dict.Item(aKey) & vbTab & aKey

        If dict.Item(aKey) = 0 Then
            ' Occurrence count (Item) indicates this style is not used
            Call wb.Styles(aKey).Delete
            If Err.Number <> 0 Then
                Debug.Print vbTab & "^-- failed to delete"
                Err.Clear
            End If
            Call dict.Remove(aKey)
        End If

    Next aKey

    MsgBox "ENDING # of style in workbook: " & wb.Styles.Count

End Sub

Can you use late binding to get to the Scripting Runtime library? — PeterT, Aug 18 '22 at 15:04

j2associates · Accepted Answer · 2022-08-18T16:05:44.070

Try using late binding and see if that works.

Public Sub DropUnusedStylesWithDictionary()
    Dim styleObj As Style
    Dim rngCell As Range
    Dim wb As Workbook
    Dim wsh As Worksheet
    Dim str As String
    Dim iStyleCount As Long
    ''Dim dict As New Scripting.Dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Set wb = ThisWorkbook ' choose this module's workbook

    MsgBox "BEGINNING # of styles in workbook: " & wb.Styles.Count

    ' dict := list of styles
    For Each styleObj In wb.Styles
        str = styleObj.NameLocal
        iStyleCount = iStyleCount + 1
        Call dict.Add(str, 0)    ' First time:  adds keys
    Next styleObj

    For Each wsh In wb.Worksheets
        If wsh.Visible Then
            For Each rngCell In wsh.UsedRange.Cells
                str = rngCell.Style
                dict.Item(str) = dict.Item(str) + 1     ' This time:  counts occurrences
            Next rngCell
        End If
    Next wsh

    ' Try to delete unused styles
    Dim aKey As Variant
    On Error Resume Next    ' wb.Styles(aKey).Delete may throw error

    For Each aKey In dict.Keys

        ' display count & stylename
        '    e.g. "24   Normal"
        Debug.Print dict.Item(aKey) & vbTab & aKey

        If dict.Item(aKey) = 0 Then
            ' Occurrence count (Item) indicates this style is not used
            Call wb.Styles(aKey).Delete
            If Err.Number <> 0 Then
                Debug.Print vbTab & "^-- failed to delete"
                Err.Clear
            End If
            Call dict.Remove(aKey)
        End If

    Next aKey

    MsgBox "ENDING # of style in workbook: " & wb.Styles.Count
    Set dict = Nothing
End Sub

Tried this first and it works, thanks. 35k styles to 50 in a few minutes. — DQd, Aug 18 '22 at 17:41

score 0 · Answer 2 · answered Aug 18 '22 at 16:09

Here is code using Collections as the original poster requested.

Public Sub DropUnusedStylesWithCollections()
    Dim styleObj As Style
    Dim rngCell As Range
    Dim wb As Workbook
    Dim wsh As Worksheet
    Dim str As String
    Dim sKey As String
    Dim lIndex As Long
    Dim iStyleCount As Long
    Dim oKeys As New Collection
    Dim oValues As New Collection

    Set wb = ThisWorkbook ' choose this module's workbook

    MsgBox "BEGINNING # of styles in workbook: " & wb.Styles.Count

    ' oCollection := list of styles
    For Each styleObj In wb.Styles
        str = styleObj.NameLocal
        iStyleCount = iStyleCount + 1
        Call oKeys.Add(str)    ' First time:  adds keys
        Call oValues.Add(0)    ' First time:  initialize
    Next styleObj

    For Each wsh In wb.Worksheets
        If wsh.Visible Then
            For Each rngCell In wsh.UsedRange.Cells
                str = rngCell.Style
                Call UpdateValuesCollection(oKeys, oValues, str)     ' This time:  counts occurrences
            Next rngCell
        End If
    Next wsh

    ' Try to delete unused styles
    On Error Resume Next    ' wb.Styles(aKey).Delete may throw error

    For lIndex = 1 To oKeys.Count

        ' display count & stylename
        '    e.g. "24   Normal"
        Debug.Print oValues(lIndex) & vbTab & oKeys.Item(lIndex)

        If oValues.Item(lIndex) = 0 Then
            ' Occurrence count (Item) indicates this style is not used
            Call wb.Styles(lkeys.Item(lIndex)).Delete
            If Err.Number <> 0 Then
                Debug.Print vbTab & "^-- failed to delete"
                Err.Clear
            End If
            Call oKeys.Remove(oKeys.Item(lIndex))
        End If

    Next lIndex

    MsgBox "ENDING # of style in workbook: " & wb.Styles.Count
    Set oCollection = Nothing
End Sub

Private Sub UpdateValuesCollection(oKeys As Collection, oValues As Collection, sKey As String, Optional bCaseSensitive As Boolean = False)
    Dim lIndex As Long, lCount As Long
    Dim bMatched As Boolean
    
    For lIndex = 1 To oKeys.Count
        lCount = oValues.Item(lIndex) + 1
        bMatched = False
        If bCaseSensitive Then
            If oKeys.Item(lIndex) = sKey Then
                bMatched = True
            End If
        Else
            If StrComp(oKeys.Item(lIndex), sKey, vbTextCompare) = 0 Then
                bMatched = True
            End If
        End If
        If bMatched Then
            Call oValues.Remove(lIndex)
            Call oValues.Add(lCount, sKey)
            Exit For
        End If
    Next lIndex
End Sub

Error 457: This key is already associated with an element of this collection. For: Call oValues.Add(lCount,sKey) — DQd, Aug 18 '22 at 17:42

Tim Williams · Answer 3 · 2022-08-18T18:18:15.740

Another collection-based approach:

Public Sub DropUnusedStyles()

    Dim wb As Workbook, wsh As Worksheet, str As String, arr, i As Long
    Dim colStyles As New Collection, c As Range, styleObj As Style
    
    Set wb = ThisWorkbook ' choose this module's workbook
    'first scan all of the used styles
    For Each wsh In wb.Worksheets
        If wsh.Visible Then
            For Each c In wsh.UsedRange.Cells
                str = c.Style
                arr = KeyValue(colStyles, str) 'any existing collection entry?
                
                If IsEmpty(arr) Then
                    'first instance of this style
                    colStyles.Add Array(str, 1), str
                Else
                    'exists - remove and replace, incrementing count
                    colStyles.Remove str
                    colStyles.Add Array(str, arr(1) + 1), str
                End If
            Next c
        End If
    Next wsh
    
    'list found styles
    Debug.Print "----------Used Styles-------"
    For Each arr In colStyles
        Debug.Print Join(arr, " = ")
    Next arr
    
    'review all workbook styles
    Debug.Print "----------Review Styles-------"
    For i = wb.Styles.Count To 1 Step -1 'deleting, so step backwards
        Set styleObj = wb.Styles(i)
        str = styleObj.NameLocal
        arr = KeyValue(colStyles, str)
        If IsEmpty(arr) Then
            Debug.Print str & "   #Delete#"  'delete this style
            styleObj.Delete 'remove the unused style
        Else
            Debug.Print str & " has " & arr(1) & " uses"
        End If
    Next i
End Sub

'return a collection value from a key, or Empty if key doesn't exist
Function KeyValue(col As Collection, key As String) As Variant
    Dim v As Variant
    On Error Resume Next
    v = col(key)
    On Error GoTo 0
    KeyValue = v
End Function

Updated with a line to delete the styles which have not been used.... — Tim Williams, Aug 18 '22 at 18:18
...if you have 35k styles then I'd comment out the debug.print or that will slow down your run time — Tim Williams, Aug 18 '22 at 18:20

Using collections in VBA instead of dictionary

3 Answers3