"Subscript out of range" error re array populated by the "split" function.

[gar]

My procedure reads a line from a text file, uses the "split" function to populate an array with the components of the line and then displays the first element of the array using "debug.print". This works as expected if it just processes the first line from the input file but if I enclose the action in a "Do" loop to process each of the lines in the file, in order, a "Subscript out of range" error is generated by the debug.print statement.

[Welshgasman]

And you are not going to show any code?

[Micron]

Yes code would have been good (if you used code tags for more than a few lines of code - # button on posting toolbar) but I'll take a guess.
Your loop exceeds the upper boundary of the array because you don't know that arrays are zero based and that a common way to loop is
For n = 0 to Ubound(myArray) - 1

[pbaldy]

a common way to loop is
For n = 0 to Ubound(myArray) - 1

I don't use arrays a lot but I think using the -1 will miss the last value. From help on UBound:

Returns a Long data type containing the largest available subscript for the indicated dimension of an array.

I don't include a -1 in my code and don't get errors. I agree with previous sentiments that seeing the code will likely reveal the problem.

[Micron]

I believe you are correct and that I was recalling code I was helping out with at Mr. Excel and was subtracting 1.
Not sure why for that particular case but am thinking 1 is usually only subtracted when dealing with a count of a zero based collection or if actually using the size of the array. I can't recall ever worrying about the size of an array.

[orange]

For info, --an aside--
I was using some code that was suggested to me. It was using the UBound of an array that was Arr(7)--- so included 0-7 ( 8 elements) and was looking for UBound to be an even number (divisible by 2). It failed the test since the UBound was 7. The code logic was really looking for a count of the number of elements in the array. UBound +1 in my case.

[gar]

Thanks to all who responded, here is the problematic code:

Public Sub bible_verses()


'This routine reads lines from a text file containing some part (e.g. a book) of the
'authorized version of the bible in which each verse is headed by a string with the
'format: "{<book number>:<chapter number>:<verse number>}". It then appends these
'data to the "bookname", "chapter", "verse" and "atext" fields of the t_verses table as
'appropriate.


Dim aline As String
Dim db As Database
Dim fs As FileSystemObject
Dim infile As File
Dim otable As Recordset
Dim s As TextStream
Dim sarray() As String
Dim tarray() As String
Dim uarray() As String
Dim statement As Recordset
Dim statement_path As String ' pathname of input file as received from bank.


Set db = CurrentDb

'Open input file


statement_path = "C:\Users\Geoffrey\OneDrive\Documents\religious\bi ble\3_john.txt"

Set fs = New FileSystemObject
Set infile = fs.GetFile(statement_path)
Set s = infile.OpenAsTextStream(1)

'Read lines from input file.


Do While Not s.AtEndOfStream
aline = s.ReadLine
If Mid(aline, 1, 1) = "{" Then
Debug.Print aline
sarray = Split(aline, "}")
Debug.Print sarray(0)
tarray = Split(sarray(0), "{")
Debug.Print tarray(0)
uarray = Split(tarray(0), ":")
Debug.Print uarray(0) ', uarray(1), uarray(2)
End If
Loop

s.Close


End Sub

The "subscript out of range" error message is evoked by the last "Debug.Print line; if I comment this out, the procedure works as expected

[Welshgasman]

Debug.Print Ubound(uarray())

And please use code tags when posting more than two lines of code.

[Micron]

And please use code tags when posting more than two lines of code.

Already asked but disregarded or not understood.

(if you used code tags for more than a few lines of code - # button on posting toolbar)

When that happens I usually won't bother to try to follow it.

[June7]

Also advising to post code between CODE tags.

A sample of data is also helpful. Either as text in post or attached file.

Code is not appending data anywhere, just printing to immediate window.

The last print assumes there are 3 elements in array. Is this a certainty?

[gar]

Thanks for your message.

1 - Regret to say that I don't know what "code tags" are.

2 - This is the complete input file:

The Third Epistle of John


{64:1:1} The elder unto the wellbeloved Gaius, whom I love in the truth.
{64:1:2} Beloved, I wish above all things that thou mayest prosper and be in health, even as thy soul prospereth.
{64:1:3} For I rejoiced greatly, when the brethren came and testified of the truth that is in thee, even as thou walkest in the truth.
{64:1:4} I have no greater joy than to hear that my children walk in truth.
{64:1:5} Beloved, thou doest faithfully whatsoever thou doest to the brethren, and to strangers;
{64:1:6} Which have borne witness of thy charity before the church: whom if thou bring forward on their journey after a godly sort, thou shalt do well:
{64:1:7} Because that for his name’s sake they went forth, taking nothing of the Gentiles.
{64:1:8} We therefore ought to receive such, that we might be fellowhelpers to the truth.
{64:1:9} I wrote unto the church: but Diotrephes, who loveth to have the preeminence among them, receiveth us not.
{64:1:10} Wherefore, if I come, I will remember his deeds which he doeth, prating against us with malicious words: and not content therewith, neither doth he himself receive the brethren, and forbiddeth them that would, and casteth [them] out of the church.
{64:1:11} Beloved, follow not that which is evil, but that which is good. He that doeth good is of God: but he that doeth evil hath not seen God.
{64:1:12} Demetrius hath good report of all [men,] and of the truth itself: yea, and we [also] bear record; and ye know that our record is true.
{64:1:13} I had many things to write, but I will not with ink and pen write unto thee:
{64:1:14} But I trust I shall shortly see thee, and we shall speak face to face. Peace [be] to thee. [Our] friends salute thee. Greet the friends by name.

3 - I didn't do anything more with the data until printing to the immediate window confirmed that it was correct.

4 - Pretty certain but I had assumed that the system would be able to cope if there had been more or fewer.

[June7]

CODE tags - click # icon on post edit toolbar, paste code between. Be aware the forum will usually combine first two lines into one and will have to edit to correct - at least it does in my posts. Don't know why that happens.

'Read lines from input file.
Do While Not s.AtEndOfStream
aline = s.ReadLine
    If Left(aline, 1) = "{" Then
        sarray = Split(aline, "}")
        uarray = Split(Mid(sarray(0), 2), ":")
        Debug.Print uarray(0), uarray(1), uarray(2), Trim(sarray(1))
    End If
Loop

[Welshgasman]

Joined in 2011 and does not know what code tags are?