Dumping the contents of a VBA array to a sheet

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I've got a VBA array that is 65536 rows by 2 columns (I've truncated it to
fit into two columns) that I'm trying to dump to the A and B columns of a
spreadsheet. I'm getting a Runtime 1004 error a few seconds after the line
of code tries to execute the dump

Presumably I'm trying to dump too much data, if so how do I dump the array
in smaller chunks?

Cheers

Dave
 
Sub DumpArray()
Dim myArray(1 To 65536, 1 To 2) As Long
For i = 1 To 65536
For j = 1 To 2
myArray(i, j) = Fix(Rnd() * 10000 + 1)
Next
Next
Range("A1:B65536").Value = myArray
End Sub


worked fine for me, xl2000, SR1, Win 2K

Regards,
Tom Ogilvy
 
What version are you using? As Tom Ogilvy pointed out, the direct dump
works fine in xl2000. For earlier versions there may be a limit (I don't
have an eearlier version to test). If so, and if that limit is 5461
elements, and if the functions in the downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following might work on an array referred to by the variable "myArray";
I say "might" out of an abundance of caution--it works in xl2000, and I
expect it works in earlier versions.(Watch for word wrap in this posting.):

Set rng = Range("A1")
k = 1
For i = 1 To 68250 Step 2730
Range(rng(i, 1), rng(Application.Min(65536, 2730 * k), 2)).Value =
SubArray(myArray, 1, 2, i, 2730 * k)
k = k + 1
Next

Alan Beban
 
Hi Tom,

Yes I think my problem lies elsewhere as I just tried running my code below
without alterations on a different workbook and it worked fine. When the
code crashed on the other workbook I had two Dictionary objects in memory
containing 270,000 items, I'm going to experiment with clearing these,

Cheers

Dave

Redim ResultsArray(Output.Rows.Count,2)
Do While i < OutputRange.Rows.Count
i = i +1
ResultsArray(i,2) = DictKeys(i-1)
ResultsArray(i,1) = DictItems(i-1)
Loop
OuputRange.Values = MyArray
 
Back
Top