Declaring Arrays Dynamically in VBA

Question

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

Sample Data:

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:

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.

You can't dynamically declare a variable. Dim statements are not executable. That's why this sounds like an X-Y problem. — BigBen
– BigBen, Commented Aug 9, 2019 at 5:26
Is this what you are trying? 1. Dim varkey As Variant 2. Dim n as Long: n=Val(dict.Item(varKey)) 3. Redim varkey(n) — Siddharth Rout
– Siddharth Rout, Commented Aug 9, 2019 at 5:59
@SiddharthRout .. That's sort of what I am trying to Do. but here varkey is also a Variable. Maybe this is not possible, given the answer by BigBen — Mikku
– Mikku, Commented Aug 9, 2019 at 6:33
Dictionary of Collections might be what you are looking for. Dictionary of arrays is also possible, but it is not efficient to modify those arrays after adding to dictionary. — BrakNicku
– BrakNicku, Commented Aug 9, 2019 at 7:58

TinMan · Accepted Answer · 2019-08-09 08:13:43Z

2

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

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

edited Aug 9, 2019 at 8:13

answered Aug 9, 2019 at 8:07

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mikku Over a year ago

Is there a Way I can call Debug.Print Map(pdf).Count ? will it work ? If i just want to use the pdf .. I want to be able to call that directly without looping through the keys.. Like pdf(1) for 1st pdf file or pdf(ubound(pdf)) for last pdf file

Mikku Over a year ago

Cool .. They are working as an Array only that I have to add use Map("pdf") instead of pdf 😄

TinMan Over a year ago

You should watch: Excel VBA Introduction Part 39 - Dictionaries and read Excel VBA Dictionary – A Complete Guide

BigBen · Accepted Answer · 2019-08-09 14:50:02Z

1

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.

edited Aug 9, 2019 at 14:50

answered Aug 9, 2019 at 6:05

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Collectives™ on Stack Overflow

Declaring Arrays Dynamically in VBA

2 Answers 2

Result

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Result

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related