2

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:

  Function TestSize()

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

    ' Populate array....

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

  End Function

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

Also: would I face the same problem if I wrote the function in VB?

zondo
  • 19,901
  • 8
  • 44
  • 83
Richard H
  • 38,037
  • 37
  • 111
  • 138
  • 1
    Why would you need such high amounts of elements in an array? You could try to use a collection, maybe it supports more than 2^16 – gizlmo Apr 20 '16 at 12:35
  • 2
    Arrays with > 65,536 elements are no problem within the VBA code itself, it's the returning of the array to the spreadsheet that is the problem. – Richard H Apr 20 '16 at 12:40
  • Are you using excel 2003 or earlier or is your workbook saved as an .xls? – Scott Craner Apr 20 '16 at 13:28
  • 1
    Related: http://stackoverflow.com/q/11237195/11683 (not voting as duplicate because the solution will not work). – GSerg Apr 20 '16 at 13:43
  • @ScottCraner Excel 2007, is a .xlsm – Richard H Apr 20 '16 at 13:57
  • 1
    See the link by @GSerg. It explains that they did not fix the limitation of assigning the aray to the spreadsheet. It is not a VBA problem but excel 2007. – Scott Craner Apr 20 '16 at 14:01

2 Answers2

0

Break up the results into 4 array and return an array with 4 items. First code is to illustrate the split, and the 2nd bit of code is probably closer to how you'd actually want to do it (but more complicated):

Dim arr() as string, arrP1(1 to 50000) as string, arrP2(1 to 50000) as string, arrP3(1 to 50000) as string, arrP4(1 to 50000) as string

ReDim Preserve arr(1 To 200,000)

' Populate array....

'Break results into 4 array parts:
for a=1 to 50000
   arrP1(a) = arr(a)
next
for a=1 to 50000
   arrP2(a) = arr(50000+a)
next
for a=1 to 50000
   arrP3(a) = arr(100000+a)
next
for a=1 to 50000
   arrP4(a) = arr(150000+a)
next
'Finally, return a 4-item array containing the parts:
TestSize = Array(arrP1,arrP2,arrP3,arrP4)

If you arr() could be any size, then, you could do the above with a clever thing like this, using a multidimensional array. Untested:

redim preserve arrParts(ubound(arr())\50000, 50000)
for a=1 to ubound(arr()) step 50000
    for aa=1 to 50000
       arrParts(a\50000, a mod 50000) = arr(a)
    next
next
Vexen Crabtree
  • 339
  • 3
  • 16
  • Thanks but I think this creates a 2 dimensional array 4 x 50,000 right? As opposed to a concatenated 1 x 200,000 – Richard H Apr 20 '16 at 13:39
  • How are you going to call this from a sheet? – GSerg Apr 20 '16 at 13:43
  • Richard H - the 1st code block doesn't make a 2-dimensional array as such, but, it returns a single array with 4 items, but each item is really another array. The 2nd code block makes a real multidimensional array. Without knowing what the purpose of the output is, it is hard to know which way would be best or if the 2nd method would still error. – Vexen Crabtree Apr 20 '16 at 14:14
0

If your data is uniform enough you can sort it alphabetically, or by some other logical way, and let say each letter of the alphabet designate the grouping to be searched.

Dim A_array(50000), B_array(50000), C_array(5000)...etc.

If Left(SearchString,1) = d then

'Search D_array

End If

If the array records are structured with a delimiter like "//" such as in A_array(0) = "data1//data2//data3//data4" You can search the D_array only and when you find the required data, split the record on the delimiter, and extract what you need. This method actually makes for faster searches even if you are using smaller arrays. You can store data in as many categories (arrays) as your devious mind can create. As far as I know, the number of arrays is not limited in VBA.

Jeff
  • 1
  • 1