Excel Dictionary not updating?

Question

I'm a relative newcomer to VB so am having real problems trying to understand how things fit together. I'm currently trying to use a global dictionary to store headers/column values so they can be accessed quickly when run (as the column numbers may change depending on content). However i'm struggling to make a dictionary work, it appears to add values but later in the code shows up as empty, i have no idea what i'm doing wrong and would appreciate any help.

Public dataHeaders As Dictionary


Public Function getCases()

Set dataHeaders = CreateObject("Scripting.Dictionary")

For i = 1 To 100
    If IsEmpty(Worksheets("DATA").Cells(1, i)) Then
        Exit For
    Else
        dataHeaders.Add Worksheets("DATA").Cells(1, i), i
    End If
Next


For i = 1 To 10

    For j = 1 To 750
        If Worksheets("Summary").Cells(1, i) = Worksheets("DATA").Cells(dataHeaders("Checker"), j) Then
            Worksheets("Summary").Cells(2, i) = Worksheets("Summary").Cells(2, i) + 1
        End If
    Next
Next

End Function

I have had the same issue. You can look up my question here: http://stackoverflow.com/questions/17601919/global-variable-loses-its-value — ApplePie, Dec 05 '13 at 14:33
Hey Sam, I was using a dictionary because i need to identify the columns with strings (is it a name, an address etc), I was under the impression arrays just used integers? But any help you may have would be appreciated! — user3070580, Dec 05 '13 at 14:42
Is this an Excel VBA question? I don't see a VB6 question here. — Bob77, Dec 05 '13 at 15:13

score 0 · Accepted Answer · answered Dec 05 '13 at 15:52

0

I suspect that your problem is either a casing issue or whitespace. To get rid of this issue, use the Trim and UCase (or LCase) to normalise your text before using it in a dictionary.

I tested the below code, and it outputs what I would expect..

Sub test()


Dim headers As Dictionary
Dim valueCount As Integer
Dim ws As Worksheet
Dim headerRange As Range

Set ws = Sheet1

Set headers = New Dictionary

'get last column on the right of our header row
valueCount = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column


Set headerRange = ws.Cells(1, 1).Resize(1, valueCount)

    Dim i As Long
    i = 1

    For Each cell In headerRange

    'Trim and convert to upper when assigning to array.
    headers.Add UCase(Trim(cell.Value)), i
    i = i + 1

    Next cell


     For Each Key In headers.Keys

        'Note the usage of Trim and UCase
        Debug.Print "item: " & Key & " Value : " & headers(UCase(Trim(Key)))
     Next Key


End Sub

answered Dec 05 '13 at 15:52

Sam

7,025
3
23
37

Thanks again Sam, unfortunately when i try to use that code, it outputs an error stating Object required (but doesn't bother highlighting the location of the problem). Don't suppose there is a way to import LUA code? Not too shabby with that! :D – user3070580 Dec 05 '13 at 20:41
Just change `Set headers = new Dictionary` to `Set headers = CreateObject("Scripting.Dictionary")`, that should fix it .The code doesn't error for me because i have the scripting library linked in my excel. (I figured you did too as you declare your headers as a Dictionary and not an Object) – Sam Dec 05 '13 at 20:56
@user3070580 Also, you don't really need my code, your code should work.. Just try using `UCase` and `Trim` in the way I demonstrated, see if that fixes your problem.. – Sam Dec 05 '13 at 21:01
1

Few tweaks here and there and it works, thanks a lot Sam! Needless to say i'll have many more questions, but as for this one, consider it solved :) – user3070580 Dec 05 '13 at 21:15

Excel Dictionary not updating?

1 Answers1