Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
does an array contain contents of cell | New Users to Excel | |||
#N/A if cell contents not part of an array | Excel Discussion (Misc queries) | |||
Compare row contents w/Sumproduct or Array formula? | Excel Worksheet Functions | |||
How do I concatenate the contents of an array in Excel? | Excel Worksheet Functions | |||
Randomize the order of the contents of an array | Excel Programming |