Home / VBA / VBA Subscript Out of Range Runtime Error (Error 9)
Subscript Out of Range Error (Run Time: Error 9) occurs when you refer to an object or try to use a variable in a code that doesn’t exist in the code, in that case, VBA will show this error. As every code that you write is unique, so the cause of the error would be.
In the following example, you have tried to activate the “Sheet1” which is an object. But as you can see in the workbook no worksheet exists with the name “Sheet1” (instead you have “Sheet2”) so VBA show “Subscript Out of Range” to notify you that there’s something wrong with the code.
Subscript Out of Range
There could be one more situation when you have to face the error “Subscript Out of Range Error” when you are trying to declare a dynamic array but forget to use the DIM and ReDim statement to redefine the length of the array.
Now in the above code, you have an array with the name “myArray” and to make it dynamic we have initially left the array length blank. But before you add an item you need to redefine the array length using the ReDim statement.
And that’s the mistake we have made in the above code and VBA has returned the “Script Out of Range” error.
Sub myMacro()
Dim myArray() As Variant
myArray(1) = "One"
End Sub
How Do I Fix Subscript Out of Range in Excel?
The best way to deal with this Subscript Out of Range is to write effective codes and make sure to debug the code that you have written (Step by Step).
When you run a code step by step it is easy for you to know on which line of that code you have an error as VBA will show you the error message for Error 9 and highlight that line with yellow color.
The other thing that you can do is to use an “Error Handler” to jump to a specific line of error when it happens.
In the following code, we have written a line to activate the sheet but before that, we have used the goto statement to move to the error handler. In the error handler, you have a message box that shows you a message with the Err. Description that an error has occurred.
So, when you run this code and the “Sheet1” is not in the workbook where you are trying to activate it. It will show you a message box just like below.
And if the “Sheet1” is there then there won’t be any message at all.
Sub myMacro()
Dim wks As Worksheet
On Error GoTo myError
Sheets("Sheet1").Activate
myError:
MsgBox "There's an error in the code: " & Err.Description & _
". That means there's some problem with the sheet " & _
"that you want to activate"
End Sub
More on VBA Errors
Type Mismatch (Error 13) | Runtime (Error 1004) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure Call Or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400
Содержание
- Индекс выходит за пределы допустимого диапазона (ошибка 9)
- Поддержка и обратная связь
- Subscript out of range (Error 9)
- Support and feedback
- VBA Subscript Out of Range Runtime Error (Error 9)
- Subscript Out of Range (Run time: Error 9)
- Subscript Out of Range
- How Do I Fix Subscript Out of Range in Excel?
- VBA Subscript Out of Range
- Excel VBA Subscript Out of Range
- What is Subscript out of Range Error in Excel VBA?
- Why Does Subscript Out of Range Error Occur?
- VBA Subscript Error in Arrays
- How to Show Errors at the End of the VBA Code?
- Recommended Articles
Индекс выходит за пределы допустимого диапазона (ошибка 9)
К элементам массива и коллекции можно обращаться только в пределах их допустимых диапазонов. Эта ошибка имеет следующие причины и способы решения:
Вы обратились к несуществующему элементу массива. Возможно, заданный индекс выходит за пределы диапазона допустимых индексов или размеры массива не соответствуют параметрам, присвоенным на данном этапе приложения. Проверьте верхнюю и нижнюю границы, заданные при объявлении массива. Используйте функции UBound и LBound, чтобы обуславливать доступ к массивам, если вы работаете с массивами, которые имеют другие измерения. Если индекс указан как переменная, проверьте правильность имени переменной.
Массив был объявлен без определения числа элементов. Например, ниже показано сообщение об ошибке, полученное при запуске такого кода:
Visual Basic неявно измеряет неопределенные диапазоны массивов как 0 — 10. Вместо этого необходимо использовать Dim или ReDim, чтобы явно указать число элементов в массиве.
Вы обратились к несуществующему элементу коллекции. Вместо указания индексов попробуйте обработать элементы массива с помощью конструкции For Each. Next.
Вы использовали сокращенную форму индекса, который неявно указывал недопустимый элемент. Например, если вы используете ! оператор с коллекцией — ! неявно указывает ключ. Например, объект!ключевоеИмя. значение эквивалентно объекту. элемент (ключевоеИмя). значение. В этом случае возникает ошибка, если ключевоеИмя обозначает недопустимый ключ в коллекции. Чтобы исправить ошибку, используйте правильное имя ключа или индекс для коллекции.
Для получения дополнительной информации выберите необходимый элемент и нажмите клавишу F1 (для Windows) или HELP (для Macintosh).
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Источник
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 are 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).
Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Источник
VBA Subscript Out of Range Runtime Error (Error 9)
Subscript Out of Range (Run time: Error 9)
Subscript Out of Range Error (Run Time: Error 9) occurs when you refer to an object or try to use a variable in a code that doesn’t exist in the code, in that case, VBA will show this error. As every code that you write is unique, so the cause of the error would be.
In the following example, you have tried to activate the “Sheet1” which is an object. But as you can see in the workbook no worksheet exists with the name “Sheet1” (instead you have “Sheet2”) so VBA show “Subscript Out of Range” to notify you that there’s something wrong with the code.
Subscript Out of Range
There could be one more situation when you have to face the error “Subscript Out of Range Error” when you are trying to declare a dynamic array but forget to use the DIM and ReDim statement to redefine the length of the array.
Now in the above code, you have an array with the name “myArray” and to make it dynamic we have initially left the array length blank. But before you add an item you need to redefine the array length using the ReDim statement.
And that’s the mistake we have made in the above code and VBA has returned the “Script Out of Range” error.
How Do I Fix Subscript Out of Range in Excel?
The best way to deal with this Subscript Out of Range is to write effective codes and make sure to debug the code that you have written (Step by Step).
When you run a code step by step it is easy for you to know on which line of that code you have an error as VBA will show you the error message for Error 9 and highlight that line with yellow color.
The other thing that you can do is to use an “Error Handler” to jump to a specific line of error when it happens.
In the following code, we have written a line to activate the sheet but before that, we have used the goto statement to move to the error handler. In the error handler, you have a message box that shows you a message with the Err. Description that an error has occurred.
So, when you run this code and the “Sheet1” is not in the workbook where you are trying to activate it. It will show you a message box just like below.
And if the “Sheet1” is there then there won’t be any message at all.
Источник
VBA Subscript Out of Range
Excel VBA Subscript Out of Range
Subscript out of range is an error we encounter in VBA when we try to reference something or a variable that does not exist in a code. For example, suppose we do not have a variable named x. Then, if we use the MsgBox function on x, we will encounter a “Subscript out of range” error.
VBA “Subscript out of range” error occurs because the object we are trying to access does not exist. It is an error type in VBA coding VBA Coding VBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. read more , a “Run Time Error 9.” It is important to understand the concepts to write efficient code. It is even more important to understand the error of your VBA code Error Of Your VBA Code VBA error handling refers to troubleshooting various kinds of errors encountered while working with VBA. read more to debug the code efficiently.
If you make a coding error and do not know what that error is when you are gone.
A doctor cannot give medicine to his patient without knowing what the disease is. Doctors and patients both know there is a disease (error), but it is more important to understand the disease (error) rather than give medicine to it. If you can understand the error perfectly, it is much easier to find the solution.
Similarly, this article will see one of the important errors we regularly encounter, i.e., the “Subscript out of range” error in Excel VBA.
Table of contents
You are free to use this image on your website, templates, etc., Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: VBA Subscript Out of Range (wallstreetmojo.com)
What is Subscript out of Range Error in Excel VBA?
For example, if you are referring to the sheet, not the workbook, then we get Run-time error ‘9’: “Subscript out of range.”
If you click on the “End” button, it will end the sub procedure. If you click on “Debug,” it will take you to the line of code where it encountered an error, and help will take you to the Microsoft website page.
Why Does Subscript Out of Range Error Occur?
As we said, as a doctor, it is important to find the deceased before thinking about the medicine. VBA “Subscript out of range” error occurs when the line of code does not read the object we entered.
For example, look at the below image. We have three sheets: Sheet1, Sheet2, and Sheet3.
Now in the code, we have written the code to select the sheet “Sales.”
Code:
If we run this code using the F5 key or manually, we will get the Run-time error ‘9’: “Subscript out of range.”
It is because we tried accessing the worksheet object “Sales,” which does not exist in the workbook. It is a run time error because it occurred while running the code.
Another common subscript error is when we refer to the workbook, which is not there. For example, look at the below code.
Code:
The above code says variable WB should be equal to the workbook “Salary Sheet.xlsx.” As of now, this workbook is not open on the computer. If we run this code manually or through the F5 key, we will get Run time error 9: “Subscript out of Range.“
It is due to the workbook we are referring to which is either not open or does not exist at all.
VBA Subscript Error in Arrays
When you declare the array as the dynamic array, and if you don’t use the word DIM or REDIM in VBA REDIM In VBA The VBA Redim statement increases or decreases the storage space available to a variable or an array. If Preserve is used with this statement, a new array with a different size is created; otherwise, the current variable’s array size is changed. read more to define the length of an array, we usually get the VBA “Subscript out of range” error. For example, look at the below code.
Code:
In the above, we have declared the variable as an array but have not assigned a start and ending point. Rather, we have assigned the first array the value of 25.
If we run this code using the F5 key or manually, we will get Run time error ‘9’: “Subscript out of Range.”
To fix this issue, we need to assign the length of an array by using the “ReDim” word.
Code:
This code does not give any errors.
How to Show Errors at the End of the VBA Code?
If you do not want to see the error while the code is up and running but needs an error list at the end, then you need to use the “On Error Resume” error handler. For example, look at the below code.
Code:
As we have seen, this code will throw Run time error 9: “Subscript out of range” in Excel VBA. But we must use the error handler On Error Resume Next in VBA On Error Resume Next In VBA VBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error. read more while running the code. So, we will not get any error messages. Rather, the end message box shows me the error description like this.
You can download the Excel VBA Subscript Out of Range Template here:- VBA Subscript Out of Range Template
Recommended Articles
This article has been a guide to VBA Subscript Out of Range. Here, we learned the Error called “Subscript out of range” (Run-time error’9′) in Excel VBA, along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –
Источник
tgg Пользователь Сообщений: 12 |
Добрый вечер знатоки. Простой макрос стал прерываться ошибка runtime error 9 subscript out of range, долго искал причину.. а оказалось дело в следующем. При открытии другой Книги, или работая в другой книге в момент когда запускаются макросы (2 шт.каждые 60сек) в Книге1 и вылетает error Изменено: tgg — 16.03.2018 10:48:28 |
А где собственно вопрос? С уважением, |
|
tgg Пользователь Сообщений: 12 |
#3 27.03.2015 20:31:46 На строке With Worksheets(«Лист1») всё и происходит!
Изменено: tgg — 31.03.2015 22:50:24 |
||
Казанский Пользователь Сообщений: 8839 |
#4 27.03.2015 20:46:12 Начало второй процедуры:
Аналогично переделайте все квадратные скобки. |
||
tgg Пользователь Сообщений: 12 |
Вот в чём вопрос?? Изменено: tgg — 31.03.2015 22:50:35 |
1. Worksheets(«Лист1») — без указания принадлежности к книге, относится к активной в момент запуска макроса книге. Видимо, в ней нет листа Лист1. |
|
tgg Пользователь Сообщений: 12 |
Еще раз огромное спасибо!! |
Юрий М Модератор Сообщений: 60390 Контакты см. в профиле |
tgg, два момента: |
tgg Пользователь Сообщений: 12 |
#9 19.06.2015 22:03:22 Доброго времени суток! Не прошло и полгода …. Я к Вам с поклоном и вопросом. http://www.planetaexcel.ru/forum/?FID=8&PAGE_NAME=read&TID=30902 ), с той лишь разностью, что работает с диапазоном — If Not Intersect(ActiveCell, Range(«E18:E27»)) Is Nothing Then. Вот собственно сам макрос:
Но старая песня, опять при открытии другой книги excel этот макрос зачем-то срабатывает и встаёт на 2 строке. |
||
Johny Пользователь Сообщений: 2737 |
Когда открывается книга, то она становится активной, и поэтому Ваш диапазон Range(«E18:E27») относится уже к ОТКРЫТОЙ книге. There is no knowledge that is not power |
tgg Пользователь Сообщений: 12 |
Пробовались разные варианты, это первый вариант макроса, с указанием листа и принадлежности к книге. Но результат всегда был один и тот же. |
Johny Пользователь Сообщений: 2737 |
#12 19.06.2015 22:20:38
Ну так покажите эти «разные» варианты. There is no knowledge that is not power |
||
tgg Пользователь Сообщений: 12 |
Так они ведь не работают как надо! |
Rjn Пользователь Сообщений: 6 |
#14 16.03.2018 09:08:30 Добрый день!
|
||
Rjn Пользователь Сообщений: 6 |
В чем ошибка??? |
Hugo Пользователь Сообщений: 23137 |
Ведь естественно — если файл закрыт, то при попытке его сохранения должна быть ошибка. |
Sanja Пользователь Сообщений: 14837 |
#17 16.03.2018 09:19:33
Вы же выше сами написали, что
Макрос написан именно так, что файл должен быть предварительно открыт Согласие есть продукт при полном непротивлении сторон. |
||||
Rjn Пользователь Сообщений: 6 |
А где и как исправить макрос, что бы он работал при закрытом файле? |
vikttur Пользователь Сообщений: 47199 |
1. Код в сообщении следует оформлять кнопкой <…> |
vsahno Пользователь Сообщений: 42 |
#20 21.02.2019 19:08:28
У меня не были прописаны ПОЛНЫЕ ИМЕНА ФАЙЛОВ! — только название, без расширения: |
||
Subscript out of range is an error we encounter in VBA when we try to reference something or a variable that does not exist in a code. For example, suppose we do not have a variable named x. Then, if we use the MsgBox function on x, we will encounter a “Subscript out of range” error.
VBA “Subscript out of range” error occurs because the object we are trying to access does not exist. It is an error type in VBA codingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, a “Run Time Error 9.” It is important to understand the concepts to write efficient code. It is even more important to understand the error of your VBA codeVBA error handling refers to troubleshooting various kinds of errors encountered while working with VBA. read more to debug the code efficiently.
If you make a coding error and do not know what that error is when you are gone.
A doctor cannot give medicine to his patient without knowing what the disease is. Doctors and patients both know there is a disease (error), but it is more important to understand the disease (error) rather than give medicine to it. If you can understand the error perfectly, it is much easier to find the solution.
Similarly, this article will see one of the important errors we regularly encounter, i.e., the “Subscript out of range” error in Excel VBA.
Table of contents
- Excel VBA Subscript Out of Range
- What is Subscript out of Range Error in Excel VBA?
- Why Subscript Out of Range Error Occurs?
- VBA Subscript Error in Arrays
- How to Show Errors at the End of the VBA Code?
- Recommended Articles
You are free to use this image on your website, templates, etc., Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA Subscript Out of Range (wallstreetmojo.com)
What is Subscript out of Range Error in Excel VBA?
For example, if you are referring to the sheet, not the workbook, then we get Run-time error ‘9’: “Subscript out of range.”
If you click on the “End” button, it will end the sub procedure. If you click on “Debug,” it will take you to the line of code where it encountered an error, and help will take you to the Microsoft website page.
Why Does Subscript Out of Range Error Occur?
As we said, as a doctor, it is important to find the deceased before thinking about the medicine. VBA “Subscript out of range” error occurs when the line of code does not read the object we entered.
For example, look at the below image. We have three sheets: Sheet1, Sheet2, and Sheet3.
Now in the code, we have written the code to select the sheet “Sales.”
Code:
Sub Macro2() Sheets("Sales").Select End Sub
If we run this code using the F5 key or manually, we will get the Run-time error ‘9’: “Subscript out of range.”
It is because we tried accessing the worksheet object “Sales,” which does not exist in the workbook. It is a run time error because it occurred while running the code.
Another common subscript error is when we refer to the workbook, which is not there. For example, look at the below code.
Code:
Sub Macro1() Dim Wb As Workbook Set Wb = Workbooks("Salary Sheet.xlsx") End Sub
The above code says variable WB should be equal to the workbook “Salary Sheet.xlsx.” As of now, this workbook is not open on the computer. If we run this code manually or through the F5 key, we will get Run time error 9: “Subscript out of Range.“
It is due to the workbook we are referring to which is either not open or does not exist at all.
VBA Subscript Error in Arrays
When you declare the array as the dynamic array, and if you don’t use the word DIM or REDIM in VBAThe VBA Redim statement increases or decreases the storage space available to a variable or an array. If Preserve is used with this statement, a new array with a different size is created; otherwise, the current variable’s array size is changed.read more to define the length of an array, we usually get the VBA “Subscript out of range” error. For example, look at the below code.
Code:
Sub Macro3() Dim MyArray() As Long MyArray(1) = 25 End Sub
In the above, we have declared the variable as an array but have not assigned a start and ending point. Rather, we have assigned the first array the value of 25.
If we run this code using the F5 key or manually, we will get Run time error ‘9’: “Subscript out of Range.”
To fix this issue, we need to assign the length of an array by using the “ReDim” word.
Code:
Sub Macro3() Dim MyArray() As Long ReDim MyArray(1 To 5) MyArray(1) = 25 End Sub
This code does not give any errors.
How to Show Errors at the End of the VBA Code?
If you do not want to see the error while the code is up and running but needs an error list at the end, then you need to use the “On Error Resume” error handler. For example, look at the below code.
Code:
Sub Macro1() Dim Wb As Workbook On Error Resume Next Set Wb = Workbooks("Salary Sheet.xlsx") MsgBox Err.Description End Sub
As we have seen, this code will throw Run time error 9: “Subscript out of range” in Excel VBA. But we must use the error handler On Error Resume Next in VBAVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error.read more while running the code. So, we will not get any error messages. Rather, the end message box shows me the error description like this.
You can download the Excel VBA Subscript Out of Range Template here:- VBA Subscript Out of Range Template
Recommended Articles
This article has been a guide to VBA Subscript Out of Range. Here, we learned the Error called “Subscript out of range” (Run-time error’9′) in Excel VBA, along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –
- Declare Global Variables in VBA
- VBA AutoFill
- Clear Contents in VBA
- Excel VBA UCase Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
Sub Statistika() SearchingPath = Application.GetOpenFilename("Ôàéëû Microsoft Office Excel (*.xls), *.xls", _ Title:="Ïîèñê ôàéëîâ äëÿ áàçû äàííûõ (Car*.xls)") 'Ìåõàíèçì âûäåëåíèÿ ïóòè ê ïàïêå èç ïîëíîãî ïóòè ê ôàéëó k = InStr(1, StrReverse(SearchingPath), "") SearchingPath = Left(SearchingPath, Len(SearchingPath) - k + 1) Application.ScreenUpdating = False 'Íîâûé ñïîñîá çàäàíèÿ ïàïêè ñ ôàéëàìè 'With Application.FileDialog(msoFileDialogFolderPicker) ' .Show ' SearchingPath = .SelectedItems(1) 'End With Set DataCell = Workbooks("statistika").Sheets("Ñóììû").Range("A1") With Application.FileSearch .LookIn = SearchingPath '.Filename = SearchingPath .Filename = "Car*.xls" .Execute NumOfFiles = .Execute For i = 1 To .FoundFiles.Count 'ïðîâåðêà íàõîæäåíèÿ îáðàáîòàííîãî ôàéëà â áàçå äàííûõ Do 'ìåõàíèçì âûäåëåíèÿ íàçâàíèÿ ôàéëà èç ïîëíîãî ïóòè ê íåìó FullName = .FoundFiles(i) k = InStr(1, StrReverse(FullName), "") Path = Left(FullName, Len(FullName) - k + 1) Filename = Right(FullName, k - 1) 'DataCell.Range("I2").FormulaR1C1 = _ ' "=MID(R[0]C[-1],4,2)&"".""&MID(R[0]C[-1],6,2)&"".99""" 'DataCell.Range("G1").Copy Set NameCell = Workbooks("statistika").Sheets("Ñóììû").Range("C9900") Do Set NameCell = NameCell.Offset(1, 0) s = NameCell.Address d = NameCell.Value If NameCell.Value = Filename Then 'MsgBox ("Ôàéë " & FileName & " óæå îáðàáàòûâàëñÿ") i = i + 1 End If Loop Until IsEmpty(NameCell.Offset(0, -1)) Or (NameCell.Value = Filename) Loop Until IsEmpty(NameCell.Offset(0, -1)) Or i > .FoundFiles.Count If Not (i > .FoundFiles.Count) Then Workbooks.Open (.FoundFiles(i)) 'Sheets("Car").Activate Set CarCell = Sheets("Car").Range("A1") 'Íàõîæäåíèå ïåðâîé ïóñòîé ÿ÷åéêè â òàáëèöå Set DataCell = Workbooks("statistika").Sheets("Ñóììû").Range("A1") Do Set DataCell = DataCell.Offset(1, 0) Loop Until IsEmpty(DataCell.Range("B1")) And IsEmpty(DataCell.Range("C1")) s = DataCell.Address With DataCell 'Ïåðåíîñ "øàïêè" (îáùèõ äàííûõ) â áàçó .Range("C1").Value = Filename .Range("B1").FormulaR1C1 = _ "=MID(R[0]C[1],4,2)&"".""&MID(R[0]C[1],6,2)&"".07""" .Range("G1").Copy 'ñôîðìèðîâàííàÿ ñòðîêà ÿâëÿåòñÿ øàáëîíîì îáùèõ äàííûõ. Îíà óäàëÿåòñÿ Set ShablonRow = _ Workbooks("statistika").Sheets("Ñóììû").Range(DataCell.Row & ":" & DataCell.Row) 'Ïåðåíîñ äàííûõ î òîâàðå â áàçó Set CarCell = Workbooks(Filename).Sheets("Car").Range("A1") Do If Not (CarCell.Column > 20) Then Set CarCell = CarCell.Offset(0, 1) Else Set CarCell = CarCell.Offset(1, -20) End If Loop Until (CarCell.Value = "òîâàð") Or (CarCell.Row > 50) If Not (CarCell.Value = "òîâàð") Then MsgBox ("ÿ÷åéêè ñî çíà÷åíèåì ""òîâàð"" â ôàéëå " & Filename & " íåò") Else Set BegCell = CarCell s = CarCell.Address Do If Not (CarCell.Column > 20) Then Set CarCell = CarCell.Offset(0, 1) Else Set CarCell = CarCell.Offset(1, -20) End If If IsEmpty(CarCell) Then n = n + 1 Else n = 0 End If s = CarCell.Address Loop Until (CarCell.Value = "Âñåãî:") Or (n > 20 * 20) If Not (CarCell.Value = "Âñåãî:") Then MsgBox ("ÿ÷åéêè ñî çíà÷åíèåì ""Âñåãî:"" â ôàéëå " & Filename & " íåò") Else Set EndCell = CarCell s = EndCell.Address Set CarCell = Workbooks(Filename).Sheets("Car").Range("A1") Set CarCell = BegCell.Offset(1, -1) 'ñòðîêà ïîñëå BegCell, ñòîëáåö À Do With CarCell 'ïîñòàâùèê s = CarCell.Address If Not IsEmpty(.Range("B1")) And ((.Range("B1").Font.ColorIndex = 3) Or _ (.Range("B1").Font.ColorIndex = 10) Or (.Range("B1").Font.ColorIndex = 43)) And _ IsEmpty(.Range("A1")) Then vFirma = .Range("B1").Value Set CarCell = CarCell.Offset(1, 0) s = CarCell.Address bool = False Do With CarCell If Not IsEmpty(.Range("H1")) And ((.Range("H1").Font.ColorIndex = 3) Or _ (.Range("H1").Font.ColorIndex = 10) Or (.Range("H1").Font.ColorIndex = 43)) _ And IsEmpty(.Range("A1")) Then vSumma = .Range("H1").Value Set DataCell = DataCell.Offset(1, 0) ShablonRow.Copy DataCell.Range("1:1").PasteSpecial DataCell.Range("F1").Value = vSumma DataCell.Range("G1").Value = vFirma bool = True Set CarCell = CarCell.Offset(-2, 0) s = CarCell.Address End If Set CarCell = CarCell.Offset(1, 0) s = CarCell.Address End With Loop Until bool End If Set CarCell = CarCell.Offset(1, 0) s = CarCell.Address End With Loop Until CarCell.Row = EndCell.Row - 1 End If 'âñåãî: End If 'òîâàð End With ShablonRow.Delete shift:=xlUp .Filename = .FoundFiles(i) Workbooks(Filename).Close SaveChanges:=False 'MsgBox ("Çàêîí÷åíà îáðàáîòêà ôàéëà " & .FileName) 'Workbooks("statistika").Save End If ' not(i > .FoundFiles.Count) Next i 'MsgBox ("Îáðàáîòêà äàííûõ çàêîí÷åíà") Application.ScreenUpdating = True End With End Sub |
Индекс VBA Excel вне диапазона
Индекс VBA вне диапазона или, как правило, известен как ошибка времени выполнения 9, возникает, когда мы выбираем такую ячейку, лист или книгу, которые на самом деле не соответствуют диапазону или критериям, определенным в Excel. Как будто мы выбрали диапазон из 100 ячеек или столбца, и мы вызвали значения, хранящиеся в 120 ячейках того же столбца. Это означает, что мы выходим за пределы диапазона, чтобы выбрать и вызвать значения, которые не входят в наши определенные критерии. Когда возникает такая ситуация, мы получаем сообщение «Ошибка выполнения 9» во время компиляции или запуска кода. Сообщение об ошибке VBA Subscript out of Range поможет нам исправить ошибку, связанную с диапазоном, выбранным в Excel.
Пример индекса VBA Excel вне диапазона
Ниже приведены различные примеры VBA Subscript вне диапазона в Excel.
Вы можете скачать этот VBA Subscript вне шаблона Excel здесь — VBA Subscript вне шаблона Excel
Индекс VBA вне диапазона — пример № 1
Сначала рассмотрим простой пример. Для этого нам нужно перейти в окна VBA и добавить новый модуль, выбрав пункт меню «Вставка», как показано ниже.
Мы получим белое пустое окно модуля. Это где мы должны сделать работу кодирования.
Теперь напишите «Подкатегория выполняемой функции», для лучшей практики сохраните имя функции в «Подкатегории», как мы делали здесь для VBA Subscript вне диапазона.
Код:
Sub Subscript_OutOfRange1 () End Sub
Здесь, в Excel, у нас есть только один лист с именем «Лист1», как показано ниже.
Но мы напишем код для выбора листа, который даже не добавлен, и посмотрим, что произойдет.
Теперь перейдите в окно VBA и напишите Sheets (2), а затем выберите функцию Select, как показано ниже. Это означает, что мы выбираем последовательность листов 2- й позиции с помощью функции выбора.
Код:
Sub Subscript_OutOfRange1 () Sheets (2). Выберите End Sub
Теперь скомпилируйте полный код или сделайте это шаг за шагом, чтобы узнать, какая часть кода является ошибкой. Поскольку у нас есть только одна строка кода, мы можем напрямую запустить код, нажав кнопку воспроизведения под строкой меню. Мы получим сообщение об ошибке « V-Time error 9, Subscript out of range » в VBA, как показано ниже.
Это показывает, что мы пытаемся выбрать тот лист, который не существует. Если мы добавим новый лист или изменим последовательность листов в коде со 2- го на 1- й, то мы можем получить успешный запуск кода. Давайте добавим еще один лист и посмотрим, что произойдет.
Теперь снова запустите код. И поскольку мы не увидели никакой ошибки, это означает, что наш код завершает успешный запуск.
Индекс VBA вне диапазона — пример № 2
В другом примере мы снова увидим простой код активации рабочего листа. Для этого снова напишем код. Начните писать Подкатегорию в названии выполняемой функции или в любом другом имени, как показано ниже.
Код:
Sub Subscript_OutOfRange2 () End Sub
Теперь с помощью Worksheet мы активируем Sheet1, как показано ниже.
Код:
Sub Subscript_OutOfRange2 () Рабочие листы ("Sheet1"). Активировать конечную Sub
Теперь скомпилируйте полный код и запустите. Мы заметим, что не появилось сообщение об ошибке, что означает, что выполнение кода прошло успешно. Теперь давайте поместим пространство между «Листом 1»
Снова скомпилируйте и запустите код.
Как мы видим выше, даже если наш полный процесс и способ написания кода верны, но мы взяли правильное имя листа как «Лист 1». Который на самом деле не имеет места между «Лист1».
Это показывает, что все еще есть шансы получить ошибку, если не написать или написать правильное имя листа или имя книги.
Индекс VBA вне диапазона — пример № 3
В этом примере мы увидим, как выбор неправильного диапазона массива может создать и показать ошибку времени выполнения 9. Начать снова писать подкатегорию в имени выполняемой функции, как показано ниже.
Код:
Sub Subscript_OutOfRange3 () End Sub
Теперь с помощью DIM определите массив любого размера и передайте его в строку или целое число. Что зависит от того, что мы хотим хранить в массиве, числа или текст.
Здесь мы рассмотрели массив 2 × 3 как String, как показано ниже.
Код:
Sub Subscript_OutOfRange3 () Dim SubArray (2, 3) As Sub String End Sub
Таким образом, он сформирует таблицу для 2 строк и 3 столбцов, и мы можем хранить любые значения в соответствии с нашими потребностями. Поскольку мы выбрали строку, то мы будем рассматривать текст или алфавиты в нем.
Теперь во второй строке кода выберите созданный массив, но с дополнительным или большим столбцом и назначьте текст как ABC или любой другой текст по вашему выбору. Здесь мы выбрали массив 2 × 5, как показано ниже.
Код:
Sub Subscript_OutOfRange3 () Dim SubArray (2, 3) As String SubArray (2, 5) = ABC End Sub
Теперь скомпилируйте и запустите код. Как мы можем видеть на скриншоте ниже, мы получили сообщение об ошибке VBA Subscript of Range в Run-time error 9.
Причина получения этой ошибки заключается в том, что мы выбрали неверный диапазон массива в пределах 2 дополнительных столбцов от 2 × 3 до 2 × 5, что выходит за пределы кода. Теперь, если мы снова выберем правильный диапазон массива как 2 × 3 и посмотрим, что произойдет.
После компиляции и запуска кода. Мы увидим, что не получили никакой ошибки, что означает, что наш код был успешно выполнен.
Плюсы Excel VBA Subscript вне диапазона
- Индекс VBA вне диапазона позволяет нам узнать, что за ошибка произошла. Так что мы можем конкретно найти решение полученного кода ошибки.
- Поскольку индекс VBA выходит за пределы диапазона «Ошибка времени выполнения 9», очень полезно знать, какая ошибка произошла в Excel.
То, что нужно запомнить
- Рекомендуется использовать подкатегорию в имени выполняемой функции с последовательностью кода, чтобы ее было легко отслеживать.
- Сохраните файл как Macro-Enabled Workbook, чтобы избежать потери написанного кода.
- Если у вас огромные строки кода, лучше скомпилировать каждую строку кода одну за другой, нажав клавишу F8. Этот метод компилирует каждый шаг кода, чтобы мы могли сразу узнать, какая часть кода действительно имеет ошибку с первого раза.
Рекомендуемые статьи
Это было руководство по Excel VBA Subscript вне диапазона. Здесь мы обсудили причину возникновения ошибки VBA Subscript out of Range (Ошибка времени выполнения 9), а также некоторые практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи —
- Как исправить ошибку VBA 1004?
- Понимание ошибок в Excel
- Полное руководство по VBA при ошибке
- Использование функции IFERROR Excel
When running a VBA procedure, you might receive this error message
“Run-time error ‘9’, Subscript out of range”. In this article, we will talk about the possible reasons for the occurrence of this error and the ways to fix it.
According to our recent review, there are four categories that can trigger this error:
- Referring to a nonexistent element
- Wrong collection member
- Unidentified element
- Shorthand script
We are going to take a look at each of these categories and show you how to generate this error and share the best way to fix it.
Referring to a nonexistent element
In some cases, you might have referenced an element (a workbook, worksheet, array, etc.) in the code that doesn’t exist. It is also possible that the subscript is either smaller or larger than the range of possible subscripts.
You referred to a nonexistent Worksheet
To generate this error, open the VBA editor and write the following code to select the sheet “customers” in Module1.
Sub Test1() Sheets("customers").Select End Sub
If you run the code, you will have the error message.
The reason for this error is the fact that there is no sheet named “customers” in the workbook. Our workbook has three sheets named respectively Sheet1, Sheet2 and Sheet3.
To fix the error, create a worksheet named “customers” or rename any of the three existing sheets and run the macro again.
You referred to a Workbook that is not opened on your computer
If you run the code below, you will have the error code because you will be referring to a workbook that is not opened on your computer.
Sub Macro3( B ) Dim Wbk As Workbook Set Wbk = Workbooks("Employees.xls") End Sub
Wrong collection member
The subscript may be larger or smaller than the range of possible subscripts.
To generate this error, run the following code:
Sub Macro( ) Dim PriceArray(5 To 10 ) As Integer PriceArray(3) = 20 End Sub
Or
Sub Macro( ) Dim PriceArray(5 To 10 ) As Integer PriceArray(15) = 20 End Sub
The error will be triggered after running any of the two macros above because the PriceArray has been dimensioned from 5 to 10. But the code is referring to a subscript of index 3 (lower than 5) and 15 for the second macro (larger than 10). Elements of arrays and members of collections can only be accessed within their defined ranges.
That is why it is important to check the declaration of the array to verify it’s upper and lower bounds. It is also advisable to use the UBound and LBound functions to condition array accesses if you are working with arrays that are redimensioned.
Unidentified element
You declared an array but didn’t specify the number of elements
The following code will generate the error:
Sub Macro2( ) Dim PriceArray( ) As Integer PriceArray(5) = 20 End Sub
The error occurs because when declaring the array named PriceArray, the number of elements was not specified. In fact, Visual Basic does not implicitly dimension unspecified array range as 0 to 10.
To avoid the error, you must explicitly specify the number of elements in the array as in the code below:
Sub Macro3( ) Dim PriceArray(0 To 10 ) As Integer PriceArray(5) = 20
Shorthand script
You might have used a shorthand form of the subscript and it specifies an invalid element. Therefore, it is recommended that you use a valid key name and index for the collection.
For example [A10] is the shorthand for ActiveSheet.Range(A10). This means that instead of writing ActiveSheet.Range(A10).Select you can just write [A1].Select
If you don’t have a good knowledge of shorthand scripting, avoid using it because it can generate many errors, not just the “Subscript is out of range” error.
The aim of this article was to explain the possible reasons (the list is not exhaustive) for the occurrence of the “Subscript is out of range” error. Through a step by step tutorial, you have been able to fix the error. We hope it has been useful.
Excel VBA Subscript out of Range
VBA Subscript out of Range or majorly knows as Run-Time Error 9 happens when we select such cell or sheet or workbook which actually does not come under range or criteria defined in Excel. It is like we have selected the range of 100 cells or a column and we have called out the values stored in 120 cells of the same column. Which means that we are going out of range to select and call out the values which are not in our defined criteria. When this kind of situation happens, we get a “Run-Time Error 9” message while compiling or running the code. VBA Subscript out of Range error message guides us to rectify the error which is related to the range we have selected in Excel.
Example of Excel VBA Subscript out of Range
Below are the different examples of VBA Subscript out of Range in Excel.
You can download this VBA Subscript out of Range Excel Template here – VBA Subscript out of Range Excel Template
VBA Subscript out of Range – Example #1
We will first consider a simple example. For this, we need to go to VBA windows and add a new module by going in Insert menu option as shown below.
We will get a white blank window of Module. This is where we need to do coding work.
Now write Subcategory of performed function, for best practice keep the name of a function in Subcategory, as we did here for VBA Subscript out of Range.
Code:
Sub Subscript_OutOfRange1() End Sub
Here in excel, we have only one sheet named as “Sheet1” as shown below.
But we will write a code to select a sheet which is not even added and see what happens.
Now go to VBA window and write Sheets(2) followed by Select function as shown below. Which means, we are selecting Sheet sequence of 2nd position with Select function.
Code:
Sub Subscript_OutOfRange1() Sheets(2).Select End Sub
Now compile the complete code or do it step by step to know which part of the code is an error. As we have only one line of code, we can directly run the code by clicking on the play button below the menu bar. We will get an error Message saying “Run-Time error 9, Subscript out of range” in the VBA as shown below.
This shows that we are trying to select that sheet which doesn’t exist. If we add a new sheet or change the sheet sequence in code from 2nd to 1st then we may get a successful code run. Let’s add another sheet and see what happens.
Now again run the code. And as we did not see any error, which means our code completes the successful run.
VBA Subscript out of Range – Example #2
In another example, we will see again a simple code of activating a Worksheet. For this again we will write the code. Start writing the Subcategory in the name of a performed function or in any other name as shown below.
Code:
Sub Subscript_OutOfRange2() End Sub
Now with the help of Worksheet, we will activate Sheet1 as shown below.
Code:
Sub Subscript_OutOfRange2() Worksheets("Sheet1").Activate End Sub
Now compile the complete code and run. We will notice there is no error message been popped-up which means code run is successful. Now let’s put the space in between “Sheet 1”
Again compile and run the code.
As we can see above, even if our complete process and way of writing the code are correct but we have taken in correct sheet name as “Sheet 1”. Which in reality has no space between “Sheet1”.
This shows, there are the still chances of getting an error if do not spell or write correct sheet name or workbook name.
VBA Subscript out of Range – Example #3
In this example, we will see how choosing incorrect Array range may create and show Run-time error 9. Start writing Subcategory again in the name of the performed function as shown below.
Code:
Sub Subscript_OutOfRange3() End Sub
Now with the help of DIM define an Array of any size and gives it to String or Integers. Which depends, what we want to store in Array, numbers or text.
Here we have considered an array of 2×3 as String as shown below.
Code:
Sub Subscript_OutOfRange3() Dim SubArray(2, 3) As String End Sub
By this, it will form a table for 2 rows and 3 columns and we can store any values as per our need. As we have selected String then we will consider text or alphabets in it.
Now in the second line of code, select the created array but with an extra or more column and assign a text as ABC or any other text as per your choice. Here, we have selected an Array of 2×5 as shown below.
Code:
Sub Subscript_OutOfRange3() Dim SubArray(2, 3) As String SubArray(2, 5) = ABC End Sub
Now compile and run the code. As we can see in below screenshot, we got a VBA Subscript out of Range error message of Run-time error 9.
Reason for getting this error is because we have selected an incorrect Array range within 2 extra columns from 2×3 to 2×5, which is beyond the limit of code. Now if we again select the correct range of array as 2×3 and see what happens.
After compiling and running the code. We will see we did not receive any error which means our code run was successful.
Pros of Excel VBA Subscript out of Range
- VBA Subscript out of Range allows us to know what kind of error has happened. So that we can specifically find the solution of the obtained error code.
- As VBA subscript out of range ‘Run-time error 9’ is quite useful in knowing what kind of error has occurred in excel.
Things to Remember
- It is recommended to use Subcategory in the name of the performed function with a sequence of code so that it would be easy to track it properly.
- Save the file as Macro-Enabled Workbook to avoid losing written code.
- If you have huge lines of code then it is better to compile each line of code one by one by pressing F8 key. This method compiles each step of code so that we can directly know which portion of code actually has the error in the first go.
Recommended Articles
This has been a guide to Excel VBA Subscript out of Range. Here we discussed why VBA Subscript out of Range error occurs (Run-time Error 9) along with some practical examples and downloadable excel template. You can also go through our other suggested articles –
- VBA IsError
- VBA Get Cell Value
- VBA On Error
- VBA XML