Wednesday, 8 March 2017

Use VBA.Collection instead of Scripting.Dictionary?

My code uses Scripting.Dictionary a lot, perhaps too much. Of late, I was wondering if I could wean myself off this dependency.

When called to explain some usages I say that I prefer it to VBA.Collection. When asked why I say I forgot and I really did forget. Lately, I've been using a VBA-JSON library module from Tim Hall. Tim's code excellently parses JSON in VBA to a nested structure of Scripting.Dictionaries and VBA.Collections.

Usually, I never use Collections but this code forced me to reacquaint and I found at least one problem in that I cannot set an item in a collection I must delete and re-add back into its position. This is a pain but here is some code.



Function ReplaceByIndex(ByVal col As VBA.Collection, ByVal idx As Long, ByRef vNew)
    
    If idx = 1 Then
        If col.Count = 1 Then
            col.Remove 1
            col.Add vNew
        Else
            '* nothing to go after so MUST use BEFORE
            col.Add vNew, , idx + 1
            col.Remove idx
        End If
    Else
        '* use After
        col.Remove idx
        col.Add vNew, , , idx - 1
    End If

End Function


No comments:

Post a Comment