CODEWITHSUNDEEP
vb.netexceldictionary
OfficeAddinDev
OfficeAddinDev

Reputation: 1125

Vb.Net Using Dictionaries with Excel Ranges as Keys

The following code is excerpted from a larger procedure (the surrounding code is not relevant). Can anyone explain why I am unable to get the second ContainsKey line to return True? Hint: try this on a worksheet with just a few populated cells to reduce looping.

        For Each ws As Excel.Worksheet In Wb.Worksheets
            Dim dic As New Dictionary(Of Excel.Range, String)
            rngUsed = ws.UsedRange
            For Each cell As Excel.Range In rngUsed
                dic.Add(cell, "test")
                'THE FOLLOWING TWO MESSAGES SHOULD DISPLAY THE SAME RESULT, BUT DO NOT.  WHY???
                MsgBox(dic.ContainsKey(cell)) 'Returns True
                MsgBox(dic.ContainsKey(ws.Range(cell.Address))) 'Returns False
            Next
        Next

UPDATE: I have added the following code and it seems to be working:

Dim dic As New Dictionary(Of Excel.Range, String)(New MyComparer()) 'replaces line from above

Class MyComparer
Implements IEqualityComparer(Of Excel.Range)
Public Function Equals1(ByVal x As Excel.Range, ByVal y As Excel.Range) As Boolean Implements System.Collections.Generic.IEqualityComparer(Of Excel.Range).Equals
    If x.Address(External:=True) = y.Address(External:=True) Then
        Return True
    Else
        Return False
    End If
End Function
Public Function GetHashCode1(ByVal obj As Excel.Range) As Integer Implements System.Collections.Generic.IEqualityComparer(Of Excel.Range).GetHashCode
    Return obj.Count.GetHashCode
End Function

End Class

Upvotes: 1

Views: 1067

Answers (2)

OfficeAddinDev
OfficeAddinDev

Reputation: 1125

Dim dic As New Dictionary(Of Excel.Range, String)(New MyComparer()) 'replaces line from above

Class MyComparer
Implements IEqualityComparer(Of Excel.Range)
Public Function Equals1(ByVal x As Excel.Range, ByVal y As Excel.Range) As Boolean Implements System.Collections.Generic.IEqualityComparer(Of Excel.Range).Equals
    If x.Address(External:=True) = y.Address(External:=True) Then
        Return True
    Else
        Return False
    End If
End Function
Public Function GetHashCode1(ByVal obj As Excel.Range) As Integer Implements System.Collections.Generic.IEqualityComparer(Of Excel.Range).GetHashCode
    Return obj.Count.GetHashCode
End Function

This is the solution. Note that GetHashCode used in this custom comparer is very slow, so if anyone has an idea to speed this up, I'd love to hear it. @competent_tech, I have to use objects for the keys, since there is no string representation for a range that is unique and not subject to change (like how an address changes when adding/deleting rows, for example).

Upvotes: 1

competent_tech
competent_tech

Reputation: 44941

When an object is used as the key for the dictionary, .Net uses the GetHashCode to generate the key that is used in the underlying hashtable. Since you are using two different objects, you will get different values.

See the MSDN documentation for more details.

A better approach would be to turn the range into a string representation and use that as the key.

Upvotes: 1

Related Questions