Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > limit to possible array size?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 113
Default limit to possible array size?
I'm working on code that reads in a text file line by line, splits the data
by the delimiter and puts the string into an array, collects the fields i
want (i don't want them all) into another array, and writes the data i want
into a spreadsheet. The text file has 1134 fields. At some point, however,
the code bombs with a "subscript out of range error" on the text string
array. While trying to figure out what was going on, I found out that the
upper limit of the text string array was 1050 - well short of the 1134 fields
that are in the text file. Is there a size limit to arrays or am i having
some other kind of problem? I'll be the first to admit that i'm pretty much
winging it here.

The part of the code where i'm having difficulty is below.


' strArray contains the line of text from the text file (ubound = 1050)
' varArray has a list of the fields i want to import (taken from another
source)
' partArray is where the fields/data i want from strArray are stored

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the remaining fields in the
data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
ReDim Preserve partArray(UBound(partArray) + 1)
For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
Worksheets("Sheet2").Cells(iRowCount, 1).Resize(1, UBound(partArray, 1)
+ 1).Value = partArray
Wend
Close lngInputFile


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default limit to possible array size?
This isn't much help, but I created a text file with small strings in each
field.

I ran this version of your code:

Option Explicit
Sub testme()

Dim strSourceFile As String
Dim lngInputFile As Long
Dim iRowCount As Long
Dim strArray() As String
Dim strInText As String
Dim varArray(1 To 10) As Long
Dim i As Long
Dim fieldNum As Long

For i = 1 To 10
varArray(i) = i * 2
Next i

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the
'remaining fields in the data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

ReDim Preserve partArray(UBound(partArray) + 1)

For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR
OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

Worksheets("Sheet2").Cells(iRowCount, 1) _
.Resize(1, UBound(partArray, 1) + 1).Value = partArray
iRowCount = iRowCount + 1
Wend
Close lngInputFile
End Sub

And I got this from the immediate window while the code was paused:

?ubound(strarray)
2559

So I increased the size of each string:
?len(strarray(2323))
78
(2323 chosen at random)

All worked ok. I used xl2002 and win98.



rachel wrote:

I'm working on code that reads in a text file line by line, splits the data
by the delimiter and puts the string into an array, collects the fields i
want (i don't want them all) into another array, and writes the data i want
into a spreadsheet. The text file has 1134 fields. At some point, however,
the code bombs with a "subscript out of range error" on the text string
array. While trying to figure out what was going on, I found out that the
upper limit of the text string array was 1050 - well short of the 1134 fields
that are in the text file. Is there a size limit to arrays or am i having
some other kind of problem? I'll be the first to admit that i'm pretty much
winging it here.

The part of the code where i'm having difficulty is below.

' strArray contains the line of text from the text file (ubound = 1050)
' varArray has a list of the fields i want to import (taken from another
source)
' partArray is where the fields/data i want from strArray are stored

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the remaining fields in the
data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
ReDim Preserve partArray(UBound(partArray) + 1)
For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
Worksheets("Sheet2").Cells(iRowCount, 1).Resize(1, UBound(partArray, 1)
+ 1).Value = partArray
Wend
Close lngInputFile

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 113
Default limit to possible array size?
Unless I'm missing something, it looks like the only difference in your code
below is how you create varArray. So maybe that does narrow things down for
me... I'll keep playing around with it.

Thanks
rachael

"Dave Peterson" wrote:

This isn't much help, but I created a text file with small strings in each
field.

I ran this version of your code:

Option Explicit
Sub testme()

Dim strSourceFile As String
Dim lngInputFile As Long
Dim iRowCount As Long
Dim strArray() As String
Dim strInText As String
Dim varArray(1 To 10) As Long
Dim i As Long
Dim fieldNum As Long

For i = 1 To 10
varArray(i) = i * 2
Next i

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the
'remaining fields in the data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

ReDim Preserve partArray(UBound(partArray) + 1)

For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR
OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

Worksheets("Sheet2").Cells(iRowCount, 1) _
.Resize(1, UBound(partArray, 1) + 1).Value = partArray
iRowCount = iRowCount + 1
Wend
Close lngInputFile
End Sub

And I got this from the immediate window while the code was paused:

?ubound(strarray)
2559

So I increased the size of each string:
?len(strarray(2323))
78
(2323 chosen at random)

All worked ok. I used xl2002 and win98.



rachel wrote:

I'm working on code that reads in a text file line by line, splits the data
by the delimiter and puts the string into an array, collects the fields i
want (i don't want them all) into another array, and writes the data i want
into a spreadsheet. The text file has 1134 fields. At some point, however,
the code bombs with a "subscript out of range error" on the text string
array. While trying to figure out what was going on, I found out that the
upper limit of the text string array was 1050 - well short of the 1134 fields
that are in the text file. Is there a size limit to arrays or am i having
some other kind of problem? I'll be the first to admit that i'm pretty much
winging it here.

The part of the code where i'm having difficulty is below.

' strArray contains the line of text from the text file (ubound = 1050)
' varArray has a list of the fields i want to import (taken from another
source)
' partArray is where the fields/data i want from strArray are stored

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the remaining fields in the
data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
ReDim Preserve partArray(UBound(partArray) + 1)
For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
Worksheets("Sheet2").Cells(iRowCount, 1).Resize(1, UBound(partArray, 1)
+ 1).Value = partArray
Wend
Close lngInputFile

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 113
Default limit to possible array size?
I figured out what the problem was - it's the result of a quirk of the file i
was using to create the varArray() array. The code does seem to work the way
it should, it's just that the other varArray file i'm using has problems.

Thanks for your help!
rachael

"Dave Peterson" wrote:

This isn't much help, but I created a text file with small strings in each
field.

I ran this version of your code:

Option Explicit
Sub testme()

Dim strSourceFile As String
Dim lngInputFile As Long
Dim iRowCount As Long
Dim strArray() As String
Dim strInText As String
Dim varArray(1 To 10) As Long
Dim i As Long
Dim fieldNum As Long

For i = 1 To 10
varArray(i) = i * 2
Next i

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the
'remaining fields in the data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

ReDim Preserve partArray(UBound(partArray) + 1)

For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR
OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

Worksheets("Sheet2").Cells(iRowCount, 1) _
.Resize(1, UBound(partArray, 1) + 1).Value = partArray
iRowCount = iRowCount + 1
Wend
Close lngInputFile
End Sub

And I got this from the immediate window while the code was paused:

?ubound(strarray)
2559

So I increased the size of each string:
?len(strarray(2323))
78
(2323 chosen at random)

All worked ok. I used xl2002 and win98.



rachel wrote:

I'm working on code that reads in a text file line by line, splits the data
by the delimiter and puts the string into an array, collects the fields i
want (i don't want them all) into another array, and writes the data i want
into a spreadsheet. The text file has 1134 fields. At some point, however,
the code bombs with a "subscript out of range error" on the text string
array. While trying to figure out what was going on, I found out that the
upper limit of the text string array was 1050 - well short of the 1134 fields
that are in the text file. Is there a size limit to arrays or am i having
some other kind of problem? I'll be the first to admit that i'm pretty much
winging it here.

The part of the code where i'm having difficulty is below.

' strArray contains the line of text from the text file (ubound = 1050)
' varArray has a list of the fields i want to import (taken from another
source)
' partArray is where the fields/data i want from strArray are stored

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the remaining fields in the
data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
ReDim Preserve partArray(UBound(partArray) + 1)
For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
Worksheets("Sheet2").Cells(iRowCount, 1).Resize(1, UBound(partArray, 1)
+ 1).Value = partArray
Wend
Close lngInputFile

--

Dave Peterson

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default limit to possible array size?
Glad you found the problem.

rachel wrote:

I figured out what the problem was - it's the result of a quirk of the file i
was using to create the varArray() array. The code does seem to work the way
it should, it's just that the other varArray file i'm using has problems.

Thanks for your help!
rachael

"Dave Peterson" wrote:

This isn't much help, but I created a text file with small strings in each
field.

I ran this version of your code:

Option Explicit
Sub testme()

Dim strSourceFile As String
Dim lngInputFile As Long
Dim iRowCount As Long
Dim strArray() As String
Dim strInText As String
Dim varArray(1 To 10) As Long
Dim i As Long
Dim fieldNum As Long

For i = 1 To 10
varArray(i) = i * 2
Next i

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the
'remaining fields in the data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

ReDim Preserve partArray(UBound(partArray) + 1)

For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR
OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i

Worksheets("Sheet2").Cells(iRowCount, 1) _
.Resize(1, UBound(partArray, 1) + 1).Value = partArray
iRowCount = iRowCount + 1
Wend
Close lngInputFile
End Sub

And I got this from the immediate window while the code was paused:

?ubound(strarray)
2559

So I increased the size of each string:
?len(strarray(2323))
78
(2323 chosen at random)

All worked ok. I used xl2002 and win98.



rachel wrote:

I'm working on code that reads in a text file line by line, splits the data
by the delimiter and puts the string into an array, collects the fields i
want (i don't want them all) into another array, and writes the data i want
into a spreadsheet. The text file has 1134 fields. At some point, however,
the code bombs with a "subscript out of range error" on the text string
array. While trying to figure out what was going on, I found out that the
upper limit of the text string array was 1050 - well short of the 1134 fields
that are in the text file. Is there a size limit to arrays or am i having
some other kind of problem? I'll be the first to admit that i'm pretty much
winging it here.

The part of the code where i'm having difficulty is below.

' strArray contains the line of text from the text file (ubound = 1050)
' varArray has a list of the fields i want to import (taken from another
source)
' partArray is where the fields/data i want from strArray are stored

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
ReDim partArray(1)

' i want the first 130 fields and a subset of the remaining fields in the
data file.

For i = 1 To 130
partArray(i - 1) = strArray(i - 1)
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
ReDim Preserve partArray(UBound(partArray) + 1)
For i = 1 To UBound(varArray)
fieldNum = varArray(i)
partArray(i + 130) = strArray(fieldNum + 129) ''WHERE THE ERROR OCCURS
ReDim Preserve partArray(UBound(partArray) + 1)
Next i
Worksheets("Sheet2").Cells(iRowCount, 1).Resize(1, UBound(partArray, 1)
+ 1).Value = partArray
Wend
Close lngInputFile

--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Limit the size of a cell Joël Excel Discussion (Misc queries) 2 September 22nd 09 01:36 PM
Is there an array size limit for MMULT or MINVERSE in excel 2007? jimr315 Excel Worksheet Functions 4 February 7th 07 10:48 PM
Cell size? Or size limit for Text data type? CClem Excel Discussion (Misc queries) 0 April 21st 06 04:09 PM
How to find out the size of a variable-size array ? Adrian[_7_] Excel Programming 1 July 6th 04 09:12 AM


All times are GMT +1. The time now is 09:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement