VBA: work around for the 65,536 array limit with user defined functions
<p>I have a user-defined function in VBA that needs to return more than 65,536 results as an array. However attempting to return more than 65,536 results in #VALUE:</p>

<pre><code>  Function TestSize()

    Dim arr() As String
    ReDim Preserve arr(1 To 200,000)  ' No problem creating and using array with &gt; 65,536 elements  

    ' Populate array....

    TestSize = arr()   ' Fails here, produces #VALUE,  if array &gt; 65,536

  End Function
</code></pre>

<p>Appreciate this is a long-standing problem. Does anyone know of a work around?</p>

<p>Also: would I face the same problem if I wrote the function in VB?</p>