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 SubHow 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 SubWhat is VBA
- VBA ERROR Handling
- VBA Automation Error (Error 440)
- VBA Error 400
- VBA Invalid Procedure Call Or Argument Error (Error 5)
- VBA Object Doesn’t Support this Property or Method Error (Error 438)
- VBA Object Required Error (Error 424)
- VBA Out of Memory Error (Error 7)
- VBA Overflow Error (Error 6)
- VBA Runtime Error (Error 1004)
- VBA Type Mismatch Error (Error 13)
I found a macro on the web to protect a worksheet with a password. It works fine, but when I save the file I get the message: run-time error ‘9’: subscription out of range. I have never programmed or used visual basic before and could use some help . Thank you
The macro is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1:Protect the sheet with a password
Sheets("Sheet1").protect Password:="btfd"
'Step 2: Save the workbook
ActiveWorkbook.Save
End Sub
asked Feb 20, 2014 at 22:06
«Subscript out of range» indicates that you’ve tried to access an element from a collection that doesn’t exist. Is there a «Sheet1» in your workbook? If not, you’ll need to change that to the name of the worksheet you want to protect.
answered Feb 20, 2014 at 22:10
Why are you using a macro? Excel has Password Protection built-in. When you select File/Save As… there should be a Tools button by the Save button, click it then «General Options» where you can enter a «Password to Open» and a «Password to Modify».
answered Feb 20, 2014 at 22:21
Gordon BellGordon Bell
13.2k3 gold badges45 silver badges64 bronze badges
When you get the error message, you have the option to click on «Debug»: this will lead you to the line where the error occurred. The Dark Canuck seems to be right, and I guess the error occurs on the line:
Sheets("Sheet1").protect Password:="btfd"
because most probably the «Sheet1» does not exist. However, if you say «It works fine, but when I save the file I get the message: run-time error ‘9’: subscription out of range» it makes me think the error occurs on the second line:
ActiveWorkbook.Save
Could you please check this by pressing the Debug button first?
And most important, as Gordon Bell says, why are you using a macro to protect a workbook?
answered Feb 21, 2014 at 9:17
Matteo NNZMatteo NNZ
11.9k11 gold badges52 silver badges89 bronze badges
|
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 |
Еще раз огромное спасибо!! |
|
Юрий М  Модератор Сообщений: 60743 Контакты см. в профиле |
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 Пользователь Сообщений: 23365 |
Ведь естественно — если файл закрыт, то при попытке его сохранения должна быть ошибка. |
|
Sanja Пользователь Сообщений: 14849 |
#17 16.03.2018 09:19:33
Вы же выше сами написали, что
Макрос написан именно так, что файл должен быть предварительно открыт Согласие есть продукт при полном непротивлении сторон. |
||||
|
Rjn Пользователь Сообщений: 6 |
А где и как исправить макрос, что бы он работал при закрытом файле? |
|
vikttur Пользователь Сообщений: 47199 |
1. Код в сообщении следует оформлять кнопкой <…> |
|
vsahno  Пользователь Сообщений: 42 |
#20 21.02.2019 19:08:28
У меня не были прописаны ПОЛНЫЕ ИМЕНА ФАЙЛОВ! — только название, без расширения: |
||
|
Дмитрий Минин  Пользователь Сообщений: 2 |
#21 21.03.2023 10:15:59 Добрый день!
Прикрепленные файлы
|
||
|
Существует ли лист «Sheet1» в активной книге? |
|
|
Дмитрий Минин Пользователь Сообщений: 2 |
#23 21.03.2023 11:00:04
Понял ошибку и уже исправил. |
||
- Индекс VBA Excel вне диапазона
Индекс 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
Sometimes when we write and attempt to run sub-procedures in Excel VBA, Excel VBA returns a message box with the error message “Runtime error 9: Subscript out of range.”
The “Subscript out of range” error happens when we reference a non-existent collection member or a non-existent array element.
By the error, Excel VBA is telling us, “I have not found what you are looking for.”
This tutorial gives examples of specific causes of this error and how to fix it.
Before fixing the “Subscript out of range” error, you must identify its cause.
When you press the Debug button on the error message box, Excel VBA takes you to the statement that caused the error. By examining the line of code, you should be able to tell what caused the error. We outline some of the causes and solutions below.
Cause #1: Referencing a Non-Existent Item in a Collection
We can only access members of collections within their delimited ranges.
Therefore, attempting to access a collection member outside the defined scope will cause the “Subscription out of range” runtime error.
Suppose we have only Sheet1 and Sheet2 in our active workbook.
If we write the following sub-procedure and press F5 to run it:
We get the “Subscript out of range” error:
The error occurs because the Sheet3 object is not present in the Sheets collection, which consists of all the worksheets and chart sheets in the active workbook.
Therefore the code is referencing a non-existent entity in the Sheets collection.
How to Fix It
You can fix this error in any of the following ways:
- Create the non-existent object; in this case, add Sheet3 to the workbook.
- Check the spelling of the name of the collection member you want to access and ensure it is correct.
- In the Excel VBA code, refer to an object present in the collection; in this case, you can refer to either Sheet1 or Sheet2.
- Use the For Each…Next loop instead of referencing specific collection members. The loop allows us to loop through collection members and repeat particular actions, such as unhiding worksheets.
Also read: Not Enough Memory to Complete This Action in Excel – How to Fix?
Cause #2: Attempting to Access a Closed Workbook
If you try to access a closed workbook, you get the “Subscript out of range” error.
For example, if we have a closed workbook called “Employees” on our computer, running the following sub-procedure will generate the “Subscript out of range” error.
This error is generated because a closed workbook is not part of the collection of workbooks.
All the open workbooks on your computer make up the workbooks collection.
How to Fix It
You can use any of the following methods to fix the error.
- Open the workbook you want to access, then run the sub-procedure.
- Use the For Each…Next loop to check whether the workbook you wish to access is open. The loop goes through all the available workbooks and matches the name of the workbook you want to access against each open workbook. If a match is found, the workbook is open otherwise; the workbook is closed.
Cause #3: Referencing a Non-existent Array Element
If you reference a non-existent array element in your code, VBA displays the “Subscript out of range” to indicate something wrong with the code.
For example, the following code refers to an array element 11, which is not in the array declaration.
When we attempt to run the code, we get the “Subscript out of range” error.
How to Fix It
You can use any of the following techniques to solve the problem:
- Verify the upper and lower bounds of the array in the array declaration. If the dimensions are different from what you intended, adjust them accordingly.
- Ensure that you only refer to the array elements in the array declaration.
- Use LBound and UBound functions to direct the access of redimensioned arrays. The LBound function returns the lower boundary of the array, which can be either 0 or 1. The UBound function returns the upper limit of the array, which is equivalent to the number of items in the array.
- If the array dimensions are declared variables, ensure the variable names are spelled correctly.
Cause #4: Not Specifying the Number of Elements in an Array
If you declare an array but do not specify the number of elements in the array and attempt to access an element in the array, VBA returns the “Subscript out of range” error.
For example, the following code results in the error:
How to Fix It
- Explicitly specify the number of elements in the array in the array declaration.
Cause #5: Misspelling the name of a Workbook
If you misspell the name of the workbook you want to access, Excel VBA will return the “Subscript is out of range error.”
For example, if you want to activate a workbook named “Employees.xlsx,” the following code will not activate the workbook but return an error:
Although the workbook is open, the sub-procedure does not activate it because its name needs to be corrected.
How to Fix It
- Check the spelling of the workbook’s name in the sub-procedure and ensure it is correct.
Cause #6: Specifying an Invalid Element
Sometimes when you use the shorthand form of a subscript, you may mistakenly specify an invalid element.
For example, the shorthand for ActiveSheet.Range(“C3”) is [C3]. If this shorthand form of the subscript refers to an invalid element, you will get the “Subscript is out of range error.”
How to Fix It
Use a valid index or name for the collection.
This tutorial has given reasons and solutions for the “Subscript out of range” error. This error happens when we reference a non-existent collection member or a non-existent array element.
Examples of specific causes include
- Referencing a worksheet or workbook not present in the collection.
- Misspelling object names in the code.
- Referencing a non-existent array element.
- Not specifying the number of elements in the array.
- Attempting to access a closed workbook.
- Specifying an invalid element.
The solutions to the error include using the For Each…Next construct, instead of referencing specific items in the code and specifying the elements in an array.
We hope you found the tutorial helpful.
Other articles you may also like:
- Using Application.GetSaveAsFilename in VBA in Excel
- Using Application.EnableEvents in VBA in Excel
- SetFocus in Excel VBA – How to Use it?
- How to Open Excel Files Using VBA
- #NAME? Error in Excel – How to Fix!
- #NUM! Error in Excel – How to Fix it?
- SPILL Error in Excel – How to Fix?