Dumping the contents of a VBA array to a sheet

[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

 

[Tom Ogilvy]

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

 

[Alan Beban]

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

 

[Dave]

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