CODEWITHSUNDEEP
vbaexcel
kumquatwhat
kumquatwhat

Reputation: 315

Trying to initialise unknown number of variables

I'm trying to initialize dictionaries, and I don't know that I'll need 3 or 4 or 10 or whatever. So I tried the following: 

dim wb1 as workbook
set wb1 = ThisWorkbook
dim strdict as new scripting.dictionary
  For c = 1 To 65536        'for all rows
    If strdict.Exists(wb1.Sheets(1).Cells(1, 4)) Then 
    'if that string has already been logged ie this is not the first pass of the current string, log it
      strdict(wb1.Sheets(1).Cells(1, 4)) = strdict(wb1.Sheets(1).Cells(1, 4)) + 1
    Else
      strdict.Add wb1.Sheets(1).Cells(1, 4), 1                
      'if that string is not present, add it to the dictionary
      dim left(wb1.Sheets(1).cells(1,4), 3) & "log" as Scripting.dictionary
    End If
  Next c

I can't say that I was surprised that

dim left(wb1.Sheets(1).cells(1,4), 3) & "log" as Scripting.dictionary

didn't work, but is there a way to accomplish what I'm thinking of? Or is this something vba simply cannot do?

Upvotes: 0

Views: 427

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

I'm not seeing your need for multiple dictionaries based on the code you posted. Seems like you just want to get distinct values and counts from Col D?

EDIT: updated based on your explanation

dim wb1 as workbook
dim dictCount as new scripting.dictionary
dim dictSub as new scripting.dictionary
dim k, kSub

set wb1 = ThisWorkbook

For c = 1 To 65536 

    k = wb1.Sheets(1).Cells(c, 4)

    'seen this key before?
    If Not dictCount.Exists(k) Then           
        dictCount.Add k, 0
        dictSub.Add k, New Scripting.Dictionary
    End If  

    dictCount(k) = dictCount(k) + 1 'increment count

    'track sub strings
    kSub = Left(k, 3)
    If dictSub(k).Exists(kSub) Then
        'increment the count
        dictSub(k)(kSub)=dictSub(k)(kSub)+1 
    Else
        dictSub(k).Add kSub, 0
    End If                 


Next c

Upvotes: 2

Related Questions