CODEWITHSUNDEEP
excelvbadictionary
Victor Lalonde
Victor Lalonde

Reputation: 27

Accessing individual cell values in ranges stored in VBA dictionaries

I have a table in Excel that I get my dictionary ID in column A and right now I am getting the dictionary key from column B with the Cell.Offset(0,1) you can see in the code below.

My problem is when I paste the dictionary key (in another workbook) I would like to get more than column B (like column B to V), but I use the value of column B for comparison purposes.

So if I store more columns in the key value (as a range for example) I can paste all of them, but I don't have access to my value of column B and my test* doesn't work anymore.

*The test is to get the latest iteration of the duplicated item ID in my table. It's what is in the Else of the IF condition.

 Public Function GetLatestRevisions(SearchRng As Range) As Object
    Dim dict            As Object
    Dim Cell            As Excel.Range
    Dim RevisionInDict  As Long
    Dim Revision        As Long

    Set dict = CreateObject("Scripting.Dictionary")
    If SearchRng.Columns.Count > 1 Then Exit Function

    For Each Cell In SearchRng
        If Not dict.Exists(Cell.Value) Then
            dict.Add Cell.Value, Cell.Offset(0, 1).Resize(1, 21)
        Else
            RevisionInDict = ConvertTextToNumeric(dict(Cell.Value))
            Revision = ConvertTextToNumeric(Cell.Offset(0, 1))
            If Revision > RevisionInDict Then dict(Cell.Value) = Cell.Offset(0, 1)
        End If
    Next

    Set GetLatestRevisions = dict
End Function

Upvotes: 0

Views: 992

Answers (1)

Tim Williams
Tim Williams

Reputation: 166401

You are mixing two different ways of adding to/updating your dictionary, and they have different results:

dict.Add v, Cell.Offset(0, 1).Resize(1, 21)

dict(Cell.Value) = Cell.Offset(0, 1)

The first line adds Cell as a Range object.

In the second case you add the Value of the cell and not the cell itself.

So, your dictionary may be inconsistent - some values will be Ranges, and some will not.

To illustrate the problem:

Dim dict, c As Range
Set dict = CreateObject("scripting.dictionary")

Set c = Range("A1:B1") 'value = "Hello"|"World"

dict.Add "A", c
Debug.Print TypeName(dict("A")) '>> Range
Debug.Print dict("A")(1)        '>> Hello
Debug.Print dict("A").Cells(2)  '>> World (a little more explicit)

dict("A") = c                   'adds c.Value, not c itself
Debug.Print TypeName(dict("A")) '>> Variant()
Debug.Print dict("A")(1, 1)     '>> Hello
Debug.Print dict("A")(1, 2)     '>> World

'Using Set can correct the problem
Set dict("A") = c               'correctly adds c as Range  
Debug.Print TypeName(dict("A")) '>> Range ***
Debug.Print dict("A")(1)        '>> Hello
Debug.Print dict("A").Cells(2)  '>> World

Fixed-up function:

Public Function GetLatestRevisions(SearchRng As Range) As Object
    Dim dict            As Object
    Dim Cell            As Excel.Range, v
    Dim RevisionInDict  As Long
    Dim Revision        As Long

    Set dict = CreateObject("Scripting.Dictionary")
    If SearchRng.Columns.Count > 1 Then Exit Function

    For Each Cell In SearchRng
        v = Cell.Value
        If Not dict.Exists(v) Then
            dict.Add v, Cell.Offset(0, 1).Resize(1, 21)
        Else
            RevisionInDict = ConvertTextToNumeric(dict(v).Cells(1).Value)
            Revision = ConvertTextToNumeric(Cell.Offset(0, 1).Value)
            If Revision > RevisionInDict Then
                Set dict(v) = Cell.Offset(0, 1).Resize(1, 21)
            End If
        End If
    Next

    Set GetLatestRevisions = dict
End Function

Upvotes: 2

Related Questions