vba Add Item to SortedList causes an "Automation Error"-CodePudding

Why the following code causes an error: -2146233079(80131509).

Sub testSortedList()
    Dim list
    Set list = CreateObject("System.Collections.SortedList")
    list.Add 1978340499, "a"
    list.Add 1, "b"
End Sub

CodePudding user response：

They keys of the list needs to be of same data type. You add two different data types, the first is of type Long, the second of type Integer and this throws the error message *failed to compare two elements..."

Simplest work around: Append an & to the 1, this will force VBA to store your constant as a Long:

list.Add 1&, "b"

Or use variables for your key values:

Dim key as Long
key = 1
list.Add key, "b"