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

 

[Kirk Lingner]

Gentlemen:

You may have touched on a problem I'm experiencing (xl97, SR-2, Office
2000).

I have a workbook, rather large, many sheets, that dumps a range to a
.csv file. It does this 1000 times.

About half way through or less (never at the same point), the screen
goes blank and task manager says Excel not responding. No error nessage
is generated. Sometimes it works, sometimes it doesn't.

The range I'm dumping is 1500x20.

I have another workbook that dumps a range 1500x6--same problem.

Is there a limit?

Anything anyone can suggest would be appreciated.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Alan Beban]

When people talk about "dumping" an array to a range, they mean
transferring a la something like

Range("whatever").Value = MyArray rather than lloping to transfer one
element at a time.

What do you mean by "dumps a range to a csv file"?

Alan Beban

 

[Alan Beban]

Where does the 1000 times enter into this?

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