array: subscript out of range

[rachel]

I'm playing around with code to import data from a huge delimited text file.
Since i'm learning as i go, i'm trying to take the code in pieces. Below is
what i have so far.

--------------
Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCount = 0
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")
Debug.Print Trim(strArray(iCount)) ' [just seeing if the data are
being read]
iCount = iCount + 1
Wend
Close lngInputFile
--------------

I get a "subscript out of range" error on the Debug.Print line. When i move
the cursor over strInText in the Line Input # line, it shows the first line
from the text file as I would expect it to look. But when i move the cursor
over the strInText in the strArray=Split line, it says = Empty. I'm guessing
this is why i get the "subscript out of range" error.

I don't know what could be causing this, but again, i'm learning as i go.
Help!

thanks
rachael

 

[rachel]

Oops... caught a spelling error:

Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")

Duh. Of course, that probably won't be the only problem i run into, but at
least I caught (belatedly) one thing...

rachael

 

[Jim Thomlinson]

You have not specified the size of your array. Try this to see if that is
your problem. If so we can look at a dynamically sized array...

Dim strArray(100) As String

 

[crispbd]

The default array base is 1, so try initializing the iCount variable t
1 instead of 0

 

[Jim Thomlinson]

You shouldn't get spelling errors VBA cna catch them for you add the line:

Option Explicit

At the top of your code. VBA can add this line for you automatically
whenever a new sheet or module is added by changing The VBA option

Tools -> Options -> Editor -> Require Variable Declarations

With this checked VBA will not run any code where any variables are not
declared or where there are spelling mistakes...

Hope this helps...

 

[rachel]

Thanks for the "Option Explicit" reminder. I know i should always do that.
Sigh...

I didn't specify the size of the array because ultimately i'm planning on
using it to import text files with varying number of fields. I don't want to
specify a size that might change.

rachael

You have not specified the size of your array. Try this to see if that is
your problem. If so we can look at a dynamically sized array...

Dim strArray(100) As String

I'm playing around with code to import data from a huge delimited text file.
Since i'm learning as i go, i'm trying to take the code in pieces. Below is
what i have so far.

--------------
Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCount = 0
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")
Debug.Print Trim(strArray(iCount)) ' [just seeing if the data are
being read]
iCount = iCount + 1
Wend
Close lngInputFile
--------------

I get a "subscript out of range" error on the Debug.Print line. When i move
the cursor over strInText in the Line Input # line, it shows the first line
from the text file as I would expect it to look. But when i move the cursor
over the strInText in the strArray=Split line, it says = Empty. I'm guessing
this is why i get the "subscript out of range" error.

I don't know what could be causing this, but again, i'm learning as i go.
Help!

thanks
rachael

 

[Jim Thomlinson]

To set up a dynamic aray you need to use the redim function. Assuming that
your only problem is the array size thing (test it by using a fixed size like
100) then you can impliment the redim function.

Thanks for the "Option Explicit" reminder. I know i should always do that.
Sigh...

I didn't specify the size of the array because ultimately i'm planning on
using it to import text files with varying number of fields. I don't want to
specify a size that might change.

rachael

You have not specified the size of your array. Try this to see if that is
your problem. If so we can look at a dynamically sized array...

Dim strArray(100) As String

I'm playing around with code to import data from a huge delimited text file.
Since i'm learning as i go, i'm trying to take the code in pieces. Below is
what i have so far.

--------------
Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCount = 0
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")
Debug.Print Trim(strArray(iCount)) ' [just seeing if the data are
being read]
iCount = iCount + 1
Wend
Close lngInputFile
--------------

I get a "subscript out of range" error on the Debug.Print line. When i move
the cursor over strInText in the Line Input # line, it shows the first line
from the text file as I would expect it to look. But when i move the cursor
over the strInText in the strArray=Split line, it says = Empty. I'm guessing
this is why i get the "subscript out of range" error.

I don't know what could be causing this, but again, i'm learning as i go.
Help!

thanks
rachael

 

[Jim Thomlinson]

Make the array dynamic like this... Note the word preserve will keep you from
losing the contents of the array when it is resized...

Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCount = 0
redim strArray(iCount + 1)
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")
Debug.Print Trim(strArray(iCount)) ' [just seeing if the data are
being read]
iCount = iCount + 1
redim preserve strArray(iCount + 1)

Wend
Close lngInputFile

Thanks for the "Option Explicit" reminder. I know i should always do that.
Sigh...

I didn't specify the size of the array because ultimately i'm planning on
using it to import text files with varying number of fields. I don't want to
specify a size that might change.

rachael

You have not specified the size of your array. Try this to see if that is
your problem. If so we can look at a dynamically sized array...

Dim strArray(100) As String

I'm playing around with code to import data from a huge delimited text file.
Since i'm learning as i go, i'm trying to take the code in pieces. Below is
what i have so far.

--------------
Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCount = 0
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")
Debug.Print Trim(strArray(iCount)) ' [just seeing if the data are
being read]
iCount = iCount + 1
Wend
Close lngInputFile
--------------

I get a "subscript out of range" error on the Debug.Print line. When i move
the cursor over strInText in the Line Input # line, it shows the first line
from the text file as I would expect it to look. But when i move the cursor
over the strInText in the strArray=Split line, it says = Empty. I'm guessing
this is why i get the "subscript out of range" error.

I don't know what could be causing this, but again, i'm learning as i go.
Help!

thanks
rachael

 

[rachel]

Thanks, Jim. Your advice is helpful.

I have a question, though - since the array size won't change as the text
file is being imported (the width of a row will be the same throughout the
file), will I still have to be concerned about preserving the contents of the
array? Right now, my code is reading the text file in one line at a time and
printing it out to one of the worksheets (see below). The code seems to work
ok now without Redim Preserve.

----------------
Sub ImportTest()
Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
Worksheets("Sheet1").Cells(iRowCount, 1).Resize(1, UBound(strArray, 1) +
1).Value = strArray
iRowCount = iRowCount + 1
Wend
Close lngInputFile
End Sub
----------------

I have a dynamic array b/c i won't necessarily know how many fields the text
file has before i import it (right now i'm just using a short test file now
to make sure the code actually works). If i can determine the number of
fields beforehand, i will make a static array. But for now, I'm keeping it
dynamic.

rachael

Make the array dynamic like this... Note the word preserve will keep you from
losing the contents of the array when it is resized...

Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCount = 0
redim strArray(iCount + 1)
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")
Debug.Print Trim(strArray(iCount)) ' [just seeing if the data are
being read]
iCount = iCount + 1
redim preserve strArray(iCount + 1)

Wend
Close lngInputFile

Thanks for the "Option Explicit" reminder. I know i should always do that.
Sigh...

I didn't specify the size of the array because ultimately i'm planning on
using it to import text files with varying number of fields. I don't want to
specify a size that might change.

rachael

You have not specified the size of your array. Try this to see if that is
your problem. If so we can look at a dynamically sized array...

Dim strArray(100) As String

:

I'm playing around with code to import data from a huge delimited text file.
Since i'm learning as i go, i'm trying to take the code in pieces. Below is
what i have so far.

--------------
Dim strInText As String
Dim strArray() As String
strSourceFile = "C:\ShortTest.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCount = 0
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInTest, "~")
Debug.Print Trim(strArray(iCount)) ' [just seeing if the data are
being read]
iCount = iCount + 1
Wend
Close lngInputFile
--------------

I get a "subscript out of range" error on the Debug.Print line. When i move
the cursor over strInText in the Line Input # line, it shows the first line
from the text file as I would expect it to look. But when i move the cursor
over the strInText in the strArray=Split line, it says = Empty. I'm guessing
this is why i get the "subscript out of range" error.

I don't know what could be causing this, but again, i'm learning as i go.
Help!

thanks
rachael

 

[Tom Ogilvy]

From Excel VBA Help:

"Because the default base is 0, the Option Base statement is never
required."