Today, in order to implement a seemingly simple function, it turned out that it took me nearly 3 hours to debug the VBA code, and I still haven’t finished a cup of coffee since I drank it in the morning.
Among them, encountered a “Runtime-error ‘9’” : Subscript out of range error, which delayed a lot of time.
With workbooks(fileName).VBProject For Each objVbc In .VBComponents Select Case objVbc.Type Case 1, 2, 3 .VBComponents.Remove .VBComponents(objVbc.Name) Case Else With objVbc.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next End With
That’s the problem
This sentence. No error will be reported after modification to the following code:
Dim wkBook As Workbook 'VBA code (modules and worksheets) used to delete the delivery file. Dim objVbc As Object 'Defining a VBA Component in a delivery file (VB Component) Set wkBook = GetObject(Filename) 'Remove VBA codes from delivery files (users do not need to see these codes) With wkBook.VBProject For Each objVbc In .VBComponents Select Case objVbc.Type Case 1, 2, 3 .VBComponents.Remove .VBComponents(objVbc.Name) Case Else With objVbc.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next End With
|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 LBound 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:
Visual Basic doesn’t implicitly dimension unspecified array ranges as 0 – 10. Instead, you must use Dim or ReDim to specify explicitly the number of elements in an array. You referenced a nonexistent collection member. Try using the For Each… Next 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, object!keyname. value is equivalent to object.item(keyname).value. In this case, an error is generated if keyname 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).
- Error in *** : subscript out of bounds [How to Solve]
- Go Slice Error: panic:runtime error:index out of range  with length 0 [Solved]
- [Solved] electron-builder Package mac Error: panic: runtime error: index out of range
- [Solved] MindSpore Error: ValueError: `padding_idx` in `Embedding` out of range
- Android Studio Error: String index out of range 0 [How to Solve]
- How to Solve “RIP Address Out Of Range” Error
- C++ Error: terminating with uncaught exception of type std::out_of_range: vector Abort trap: 6
- [Solved] TensorFlow severing Container Creat Error: failed: Out of range: Read less bytes than requested
- Excel VBA on error resume next error handling
- ValueError: Floating point image RGB values must be in the 0..1 range.
- Bin File Programming Failed, could not find core in coresight setup, erase error
- [Solved] MindSpore Error: Should not use Python in runtime
- Zchunk error: Unable to find multipart download range error in dnf update
- [Solved] vue.runtime.esm.js?2b0e:619 [Vue warn]: Error in render: “TypeError: Cannot read property ‘length‘
- [Go] Solve panic: runtime error: invalid memory address or nil pointer dereference in golang
- [Solved] Runtime Library Install error 0x80070652 – another installation operation is in progress
- [Solved] Tsingsee green rhinoceros video pedestrian intelligence detection test error panic: runtime error
- Runtime error prompt in Android Studio: arrayadapter requires the resource ID to be a textview problem
- .net socket programming error [How to Solve]
- [Solved] R Language Error: Error in file(out, “wt“) : cannot open the connection