CODEWITHSUNDEEP
arraysexcelvbadictionary
Mikku
Mikku

Reputation: 6654

Declaring Arrays Dynamically in VBA

I am trying to create arrays of specific length dynamically, so that I can use them in a bigger procedure.

Sample Data:

enter image description here

The below code using the Dictionary Gives me the Count and Unique File Extensions in the Data.

Code:

Dim dict As New Scripting.Dictionary

For Each cel In Range("B1:B8")
    I = 1
    If Not dict.Exists(cel.Text) Then
        dict.Add cel.Text, I

    Else
        temp = dict(cel.Text) + 1
        dict.Remove cel.Text
        dict.Add cel.Text, temp
    End If
Next cel

For Each varKey In dict.Keys

Debug.Print varKey & ":" & dict.Item(varKey)

Next

Result:

enter image description here

What I am trying to do is create 3 (in this sample) arrays pdf(4),xlsx(3),docm(1) Using the results from Dictionary.

But the line Dim varkey(dict.Item(varKey)) As Variant gives me Compile Error.

Constant Expression Required

Is there a way to do it ? I searched google for ways to achieve this, but with no luck.

Basically what I want is to use these different extension names to declare Arrays. But these extension names will vary so I need to declare them dynamically. Array should have same name as the Extension.

So pick the name from sheet or from Dictionary and declare that as Array of a specified Length. Length can be Redim'ed afterwards also, but the main problem is declaring them from a variable.

Upvotes: 2

Views: 617

Answers (2)

BigBen
BigBen

Reputation: 50008

I'm not sure exactly what the task at hand is, but this is an X-Y problem, if I understand your comments.

Dim statements - declarative statements - are not executable. This is regardless of the type (String, Long, Variant array, whatever.) Your question title might have been bit misleading in that regard, since it seems like essentially you're trying to dynamically declare variables - the fact they are arrays is coincidental.

You can avoid the compile error by ReDimming an array based on the count from your dictionary, but you can't come up with a dynamic list of variables.

Upvotes: 1

TinMan
TinMan

Reputation: 7759

As BrakNicku commented a Dictionary of Dictionaries will get you the answer that you want.

Sub PrintExtensionCount()
    Dim Cell As Range
    Dim Map As New Scripting.Dictionary, subMap As New Scripting.Dictionary

    For Each Cell In Range("B1:B8")
        If Not Map.Exists(Cell.Value) Then Map.Add Cell.Text, New Dictionary

        Set subMap = Map(Cell.Value)
        subMap.Add Cell.Offset(0, -1).Value, vbNullString
    Next

    Dim Key As Variant

    For Each Key In Map
        Set subMap = Map(Key)
        Debug.Print Key; ":"; subMap.Count
    Next

End Sub

Result

Immediate Window Result

Not to confuse things but I like to use a Dictionary of ArrayList.

Sub PrintExtensionCount()
    Dim Cell As Range
    Dim Map As New Scripting.Dictionary, list As Object

    For Each Cell In Range("B1:B8")
        If Not Map.Exists(Cell.Value) Then Map.Add Cell.Text, CreateObject("System.Collections.ArrayList")

        Set list = Map(Cell.Value)
        list.Add Cell.Offset(0, -1).Value
    Next

    Dim Key As Variant

    For Each Key In Map
        Set list = Map(Key)
        Debug.Print Key; ":"; list.Count
    Next

End Sub

Upvotes: 2

Related Questions