Dumping the contents of a VBA array to a sheet
 

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

Dumping the contents of a VBA array to a sheet
 

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


"Dave" wrote in message
. au...
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

Dumping the contents of a VBA array to a sheet
 

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 wrote:
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

Dumping the contents of a VBA array to a sheet
 

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!

Dumping the contents of a VBA array to a sheet
 

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

Kirk Lingner wrote:
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!

Dumping the contents of a VBA array to a sheet
 

Where does the 1000 times enter into this?

Alan Beban

Kirk Lingner wrote:
Sorry for the vagueness.

Here's a snippet of code (it's taking the range and writing it out to a
file via the cut and paste method).

' *******
' Creates a shell of a file into which the values will be stored.
' Uses Cut-and-Paste logic.
' *******
.. v_master is the name of ThisWorkbook
.. v_fn is the .csv file that will be created
.. v_data is the range that will be written out

Sub CreateFile(ByVal v_master As String, ByVal v_fn As String, ByVal
v_data As Range)

' Remove prior version, if any.
' Error(53) will occur if file does not exist.
On Error Resume Next
Kill v_fn & ".csv"

' Create new file
Application.SheetsInNewWorkbook = 1
' By adding a new workbook, the new workbook becomes active.
Workbooks.Add

' Paste values
v_data.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False

' Rename, save and close
With ActiveWorkbook
.SaveAs _
FileName:=v_fn, _
FileFormat:=xlCSV, _
CreateBackup:=False
.Close _
SaveChanges:=False
End With

Workbooks(v_master).Activate

End Sub




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

Dumping the contents of a VBA array to a sheet
 

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


"Tom Ogilvy" wrote in message
...
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


"Dave" wrote in message
. au...
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