title: Declaring Arrays Dynamically in VBA
body: <p>I am trying to create <code>arrays</code> of specific length dynamically, so that I can use them in a bigger procedure. </p>
  
  <p><strong>Sample Data:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/DffWy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DffWy.png" alt="enter image description here"></a></p>
  
  <p>The below code using the <code>Dictionary</code> Gives me the Count and Unique File Extensions in the Data.</p>
  
  <p><strong>Code:</strong></p>
  
  <pre><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 &amp; ":" &amp; dict.Item(varKey)
  
  Next
  </code></pre>
  
  <p><strong>Result:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/Ki9FG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ki9FG.png" alt="enter image description here"></a></p>
  
  <p>What I am trying to do is create 3 (in this sample) arrays <code>pdf(4)</code>,<code>xlsx(3)</code>,<code>docm(1)</code>
  Using the results from Dictionary.</p>
  
  <p>But the line <code>Dim varkey(dict.Item(varKey)) As Variant</code> gives me <em>Compile Error</em>.</p>
  
  <blockquote>
    <p>Constant Expression Required</p>
  </blockquote>
  
  <p>Is there a way to do it ? I searched google for ways to achieve this, but with no luck.</p>
  
  <p>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.</p>
  
  <p>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.</p>