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

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

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

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

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