title: Working with arrays in VBA
body: <p>I'm having a bit of a problem working with arrays in VBA, where the same would be trivial in (almost) any other language:</p>
  
  <pre><code>Public Function getArray() As MyType()
      'do a lot of work which returns an array of an unknown length'
      'this is O(N^2) or something equally intensive so I only want to call this once'
  End Function
  
  Public Sub doSomething()
      Dim myArray() As MyType
      Set myArray = getArray() 'FAILS with "Cannot assign to array error"'
  End Sub
  </code></pre>
  
  <p>I think it might be that I need to define the length of the array in advance, or ReDim a dynamic array.  But I don't know the length of the returned array ahead of time, and I'd like to avoid calling the function twice:</p>
  
  <pre><code>Public Sub doSomething()
      Dim myArray(0 To UBound(getArray()) As MyType  'not entirely sure if this would work, but it involves calling getArray twice which I'd like to avoid
      Set myArray = getArray()
  End Sub
  </code></pre>
  
  <p>In C# or Java the equivalent would be:</p>
  
  <pre><code>public MyType[] getArray(){
      //do some work and return an array of an unknown length
  }
  
  public void doSomething(){
      MyType[] myArray;
      myArray = getArray();  //one line and I don't need to define the length of array beforehand
  }
  </code></pre>