skulakowski
01-10-2006, 12:28 PM
What's the practical limit to the size of an array? I'm trying to set up an array of 380,000 rows by 148 columns (basically 6 sheets by 64,000 plus a few more on the 7th sheet). My Windows XP Professional PC is (supposed to be) very smart with 2 gig of RAM. How can I know how much memory is available? is in use? Are there other memory options that I can change to make this array work?
Sub GetChartData()
Dim SheetCount As Double
Dim SheetNames() As Variant
Dim i As Double
SheetCount = ActiveWorkbook.Sheets.Count
SheetCount = SheetCount - 1 'change number of extra sheets here
ReDim SheetNames(1 To SheetCount, 0 To 2) As Variant
TotalLoanCount = 0
For i = 1 To SheetCount 'for each sheet, count records and running total of records
SheetNames(i, 0) = ActiveWorkbook.Sheets(i).Name
Worksheets(SheetNames(i, 0)).Select
Range("A2").Select
Selection.End(xlDown).Select
SheetNames(i, 1) = ActiveCell.Row - 1
If i > 1 Then SheetNames(i, 2) = SheetNames(i, 1) + SheetNames(i - 1, 2) Else SheetNames(i, 2) = SheetNames(i, 1)
Range("A2").Select
Next i
TotalLoanCount = SheetNames(i - 1, 2)
Dim ChartingArray() As Double
Dim qRow As Double
Dim qOtherRow As Double
Dim p As Double
Dim n As Double
Dim StartRow As Double
Dim EndRow As Double
For i = 1 To SheetCount
If i = 1 Then
ReDim ChartingArray(0 To TotalLoanCount - 1, 0 To 147) As Double
StartRow = 0
Else: StartRow = SheetNames(i - 1, 2)
End If
etc. etc. etc.
next i
At the "ReDim ChartingArray(0 To TotalLoanCount - 1, 0 To 147) As Double" command, I get "run-time error 7, out of memory".
Anybody have any ideas? The ChartingArray data is really three sets of 48 plus three other fields. Only thing that I can think of is to run three passes through the data, handling each pass independently.http://vbaexpress.com/forum/images/smilies/banghead.gif
Sub GetChartData()
Dim SheetCount As Double
Dim SheetNames() As Variant
Dim i As Double
SheetCount = ActiveWorkbook.Sheets.Count
SheetCount = SheetCount - 1 'change number of extra sheets here
ReDim SheetNames(1 To SheetCount, 0 To 2) As Variant
TotalLoanCount = 0
For i = 1 To SheetCount 'for each sheet, count records and running total of records
SheetNames(i, 0) = ActiveWorkbook.Sheets(i).Name
Worksheets(SheetNames(i, 0)).Select
Range("A2").Select
Selection.End(xlDown).Select
SheetNames(i, 1) = ActiveCell.Row - 1
If i > 1 Then SheetNames(i, 2) = SheetNames(i, 1) + SheetNames(i - 1, 2) Else SheetNames(i, 2) = SheetNames(i, 1)
Range("A2").Select
Next i
TotalLoanCount = SheetNames(i - 1, 2)
Dim ChartingArray() As Double
Dim qRow As Double
Dim qOtherRow As Double
Dim p As Double
Dim n As Double
Dim StartRow As Double
Dim EndRow As Double
For i = 1 To SheetCount
If i = 1 Then
ReDim ChartingArray(0 To TotalLoanCount - 1, 0 To 147) As Double
StartRow = 0
Else: StartRow = SheetNames(i - 1, 2)
End If
etc. etc. etc.
next i
At the "ReDim ChartingArray(0 To TotalLoanCount - 1, 0 To 147) As Double" command, I get "run-time error 7, out of memory".
Anybody have any ideas? The ChartingArray data is really three sets of 48 plus three other fields. Only thing that I can think of is to run three passes through the data, handling each pass independently.http://vbaexpress.com/forum/images/smilies/banghead.gif