Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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 "rachel" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
The default array base is 1, so try initializing the iCount variable t 1 instead of 0 -- crispb ----------------------------------------------------------------------- crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088 View this thread: http://www.excelforum.com/showthread.php?threadid=27637 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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 "Jim Thomlinson" wrote: 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 "rachel" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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. "rachel" wrote: 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 "Jim Thomlinson" wrote: 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 "rachel" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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 "rachel" wrote: 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 "Jim Thomlinson" wrote: 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 "rachel" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
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 "Jim Thomlinson" wrote: 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 "rachel" wrote: 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 "Jim Thomlinson" wrote: 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 "rachel" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
array: subscript out of range
From Excel VBA Help:
"Because the default base is 0, the Option Base statement is never required." -- Regards, Tom Ogilvy "crispbd" wrote in message ... The default array base is 1, so try initializing the iCount variable to 1 instead of 0. -- crispbd ------------------------------------------------------------------------ crispbd's Profile: http://www.excelforum.com/member.php...o&userid=10880 View this thread: http://www.excelforum.com/showthread...hreadid=276371 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what does 'Subscript Out of range' mean?? | Excel Worksheet Functions | |||
Subscript Out of Range | Excel Programming | |||
Redim 2D Array Subscript Out Of Range Error | Excel Programming | |||
Subscript out of range | Excel Programming | |||
Subscript Out of Range error in Array... | Excel Programming |