2023年11月29日发(作者：)

调试经验——VBA编程中遭遇“Runtime-error9”：

Subscriptoutof。。。

问题：

今天，为了实现⼀个看似⽐较简单的功能，结果调试VBA代码花费了我近3个⼩时，⼀杯咖啡从早上喝到现在还没喝完。

其中，遭遇了⼀个“Runtime-error '9'”: Subscript out of range错误，就耽误了很多时间。

问题代码：

With workbooks(fileName).VBProject

For Each objVbc In .VBComponents

Select Case

Case 1, 2, 3

Dim wkBook As Workbook '⽤于删除delivery⽂件中的VBA代码（模块及⼯作表）

Dim objVbc As Object '定义delivery⽂件中的VBA组件(VB Component)

Set wkBook = GetObject(Filename)

'删除delivery⽂件中的VBA代码 (⽤户⽆需看到这些代码)

With ect

For Each objVbc In .VBComponents

Select Case

Case 1, 2, 3

. .VBComponents()

Case Else

With dule

.DeleteLines 1, .CountOfLines

End With

End Select

Next

End With

看来，还得好好学习啊！

以下内容摘⾃VBA Reference Guide.

----------------------------------------------------------------------------------------------------------

Subscript out of range (Error 9)

Elements of arrays and members of collections can only be accessed within their defined ranges. This error has the

following causes and solutions:

You referenced a nonexistent array element.

The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions

assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the

UBound and LBoundUBoundLBound functions to condition array accesses if you're working with arrays that are redimensioned. If the

index is specified as a variable, check the spelling of the variable name.

You declared an array but didn't specify the number of elements. For example, the following code causes this error:

Dim MyArray() As Integer MyArray(8) = 234 ' Causes Error 9.

Visual Basic doesn't implicitly dimension unspecified array ranges as 0 – 10. Instead, you must use DimDim or ReDimReDim to

specify explicitly the number of elements in an array.

You referenced a nonexistent collection member.

Try using the NextNext construct instead of specifying index elements.

You used a shorthand form of subscript that implicitly specified an invalid element.

For example, when you use the !! operator with a collection, the !! implicitly specifies a key. For example,

objectkeynameobjectkeynamekeyname

!.value is equivalent to .item(().value. In this case, an error is generated if ).

represents an invalid key in the collection. To fix the error, use a valid key name or index for the collection.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).