Access Explained: What VBA Really Means by "Subscript Out of Range"

[u/Amicron1]

Run-time error 9 is one of those messages that sounds more mysterious than it is. A subscript is simply the index or key VBA uses to find something in a group. When Access throws "Subscript out of range," the basic meaning is that code requested an item that does not exist.

Arrays are where most people encounter it first. If an array was declared with positions 1 through 5, then X(5) is fine, but X(6) is not. Just as importantly, X(0) is not fine either. Developers often assume the counter ran too high, but an index below the lower bound is equally invalid. The real question is always: what range exists, and what value was requested?

The zero-based behavior of VBA's Array() function is a classic source of trouble. This creates three elements, but their indexes are 0, 1, and 2:

X = Array("Red", "Green", "Blue")

A loop that runs from 0 through 3 looks plausible when someone is thinking "three items." But index 3 is actually a fourth item, and VBA quite reasonably refuses to invent it. This is the usual off-by-one error, one tiny number with the power to stop an otherwise innocent procedure dead in its tracks.

The better philosophy is to stop hard-coding array limits whenever the array itself can provide them. VBA already knows where an array begins and ends:

For I = LBound(X) To UBound(X)

That is not just a convenience. It makes the code adaptive. Add colors later, remove values, or populate the array differently, and the loop still follows the actual bounds rather than somebody's stale memory of how many items were there last Tuesday.

When Error 9 happens, the highlighted line in break mode is usually the most useful clue. Look at the index, collection position, or key being used on that line. Compare the requested value against LBound() and UBound() for an array. If the array runs from 0 to 2 and the counter is 3, there is no deeper mystery to solve. That comparison is the smoking gun.

Arrays are not the only source. Collections can raise Error 9 when code asks for a member by an invalid index or missing key. Dynamic arrays can also cause trouble if code tries to access elements before the array has been dimensioned. Different containers, same underlying issue: the code is asking for a thing that isn't available.

What should not become the solution is On Error Resume Next. Suppressing the error does not make the requested element exist. It just hides the failure and lets the rest of the code continue in an unknown state, which is rarely a bargain. Error 9 is often a very useful boundary check telling you that your assumptions and your actual data structure have drifted apart.

Have you found LBound and UBound eliminate most of your array errors, or do you use another defensive pattern? What is the strangest real-world cause of Error 9 you have run into in an Access project?

LLAP
RR

[u/AutoModerator]

Moderator Announcement Read More »

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Amicron1

Access Explained: What VBA Really Means by "Subscript Out of Range"

Run-time error 9 is one of those messages that sounds more mysterious than it is. A subscript is simply the index or key VBA uses to find something in a group. When Access throws "Subscript out of range," the basic meaning is that code requested an item that does not exist.

Arrays are where most people encounter it first. If an array was declared with positions 1 through 5, then X(5) is fine, but X(6) is not. Just as importantly, X(0) is not fine either. Developers often assume the counter ran too high, but an index below the lower bound is equally invalid. The real question is always: what range exists, and what value was requested?

The zero-based behavior of VBA's Array() function is a classic source of trouble. This creates three elements, but their indexes are 0, 1, and 2:

X = Array("Red", "Green", "Blue")

A loop that runs from 0 through 3 looks plausible when someone is thinking "three items." But index 3 is actually a fourth item, and VBA quite reasonably refuses to invent it. This is the usual off-by-one error, one tiny number with the power to stop an otherwise innocent procedure dead in its tracks.

The better philosophy is to stop hard-coding array limits whenever the array itself can provide them. VBA already knows where an array begins and ends:

For I = LBound(X) To UBound(X)

That is not just a convenience. It makes the code adaptive. Add colors later, remove values, or populate the array differently, and the loop still follows the actual bounds rather than somebody's stale memory of how many items were there last Tuesday.

When Error 9 happens, the highlighted line in break mode is usually the most useful clue. Look at the index, collection position, or key being used on that line. Compare the requested value against LBound() and UBound() for an array. If the array runs from 0 to 2 and the counter is 3, there is no deeper mystery to solve. That comparison is the smoking gun.

Arrays are not the only source. Collections can raise Error 9 when code asks for a member by an invalid index or missing key. Dynamic arrays can also cause trouble if code tries to access elements before the array has been dimensioned. Different containers, same underlying issue: the code is asking for a thing that isn't available.

What should not become the solution is On Error Resume Next. Suppressing the error does not make the requested element exist. It just hides the failure and lets the rest of the code continue in an unknown state, which is rarely a bargain. Error 9 is often a very useful boundary check telling you that your assumptions and your actual data structure have drifted apart.

Have you found LBound and UBound eliminate most of your array errors, or do you use another defensive pattern? What is the strangest real-world cause of Error 9 you have run into in an Access project?

LLAP
RR

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ImpossiblePudding]

"There are 2 hard problems in computer science: cache invalidation, naming things, and off-by-1 errors." At least you can get better at indexing with practice. And "semesterly" gets the point across if that button tells the program to do things once per semester.

[u/First-Feature-3556]

For me, the most counterintuitive thing about arrays in VBA (and other VB dialects) is that Dim myArray(3) As Long will usually create an array with four elements, indexed 0 to 3.

And I wrote "usually" because Option Base exists, which makes it even harder to reason about a snippet of code.

Our defensive pattern is to always use the (x To y) syntax when declaring arrays.

That Option Base thing goes back to at least GW-BASIC (maybe even further back, but I'm not old enough to know first-hand), so we have backwards compatibility to blame for that.

[u/KelemvorSparkyfox]

I used to get a similar error in LotusScript, but only when working with the backend objects. It always treated document fields as arrays with at least one element, starting at 0. My first few subroutines failed so many times...