title: EXCEL VBA Error: "Compile Error: Expected Array"
body: <p>Can anyone help me?</p>
  
  <p>I have been getting a compile error (...: "Expected Array") when dealing with arrays in my Excel workbook. </p>
  
  <p>Basically, I have one 'mother' array (2D, Variant type) and four 'baby' arrays (1D, Double type). The called subroutine creates the publicly declared arrays which my main macro ends up using for display purposes. Unfortunately, the final of the baby arrays craps out (giving the "Compile Error: Expected Array"). Strangely, if I remove this final baby array ('final' - as in the order of declaration/definition) the 2nd to last baby array starts crapping out. </p>
  
  <p>Here is my code:</p>
  
  <pre><code> Public Mother_Array() as Variant, BabyOne_Array(), BabyTwo_Array(), BabyThree_Array(), BabyFour_Array() as Double 'declare may other variables and arrays, too
  
  Sub MainMacro()
      'do stuff
  
     Call SunRaySubRoutine(x, y)
  
      'do stuff
  
      Range("blah") = BabyOne_Array: Range("blahblah") = BabyTwo_Array
      Range("blahbloh" = BabyThree_Array: Range("blahblue") = BabyFour_Array
  
  End Sub
  
  Sub SunRaySubRoutine(x,y)
      n = x * Sheets("ABC").Range("A1").Value + 1
  
      ReDim Mother_Array(18, n) as Variant, BabyOne_Array(n), BabyTwo_Array(n) as Double
      ReDim BabyThree_Array(n), BabyFour_Array(n) as Double
  
      'do stuff
  
      For i = 0 to n
  
          BabyOne_Array(i) = Mother_Array(0,i)
          BabyTwo_Array(i) = Mother_Array(2,i)
          BabyThree_Array(i) = Mother_Array(4,i)
          BabyFour_Array(i) = Mother_Array(6,i)
      Next        
  
  End Sub
  </code></pre>
  
  <p>I have tried to declare all arrays as the Variant type, but to no avail. I have tried to give BabyFour_Array() a different name, but to no avail. </p>
  
  <p>What's really strange is that even if I comment out the part which makes the BabyFour_Array(), the array still has zero values for each element. </p>
  
  <p>What's also a bit strange is that the first baby array never craps out (although, the 2nd one crapped out once (one time out of maybe 30).</p>
  
  <p>BANDAID: As a temporary fix, I just publicly declared a fifth dummy array (which doesn't get filled or Re-Dimensioned). This fifth array has no actual use besides tricking the system out of having the "Compile Error: Expected Array".</p>
  
  <p>Does anyone know what's causing this "Compile Error: Expected Array" problem with Excel VBA?</p>
  
  <p>Thanks,</p>
  
  <p>Elias</p>