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
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: –
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.
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.
Как исправить ошибку во время выполнения 9
Как правило, практикующие ПК и сотрудники службы поддержки знают «Subscript out of range» как форму «ошибки во время выполнения». Когда дело доходит до программного обеспечения, как Windows Operating System, инженеры могут использовать различные инструменты, чтобы попытаться сорвать эти ошибки как можно скорее. Ошибки, такие как ошибка 9, иногда удаляются из отчетов, оставляя проблему остается нерешенной в программном обеспечении.
После установки программного обеспечения может появиться сообщение об ошибке «Elements of arrays and members of collections can only be accessed within their defined ranges.». Во время возникновения ошибки 9 конечный пользователь может сообщить о проблеме в Microsoft Corporation. Microsoft Corporation может устранить обнаруженные проблемы, а затем загрузить измененный файл исходного кода, позволяя пользователям обновлять свою версию. Чтобы исправить такие ошибки 9 ошибки, устанавливаемое обновление программного обеспечения будет выпущено от поставщика программного обеспечения.
Почему возникает ошибка времени выполнения 9?
В большинстве случаев вы увидите «Subscript out of range» во время загрузки Windows Operating System. Вот три наиболее распространенные причины, по которым происходят ошибки во время выполнения ошибки 9:
Ошибка 9 Crash — Ошибка 9 может привести к полному замораживанию программы, что не позволяет вам что-либо делать. Если данный ввод недействителен или не соответствует ожидаемому формату, Windows Operating System (или OS) завершается неудачей.
Утечка памяти «Subscript out of range» — если есть утечка памяти в Windows Operating System, это может привести к тому, что ОС будет выглядеть вялой. Есть некоторые потенциальные проблемы, которые могут быть причиной получения проблем во время выполнения, с неправильным кодированием, приводящим к бесконечным циклам.
Ошибка 9 Logic Error — логическая ошибка возникает, когда компьютер производит неправильный вывод, даже если вход правильный. Это видно, когда исходный код Microsoft Corporation включает дефект в анализе входных данных.
Subscript out of range проблемы часто являются результатом отсутствия, удаления или случайного перемещения файла из исходного места установки Windows Operating System. В большинстве случаев скачивание и замена файла Microsoft Corporation позволяет решить проблему. Мы также рекомендуем выполнить сканирование реестра, чтобы очистить все недействительные ссылки на Subscript out of range, которые могут являться причиной ошибки.
Распространенные сообщения об ошибках в Subscript out of range
Обнаруженные проблемы Subscript out of range с Windows Operating System включают:
- «Ошибка программы Subscript out of range. «
- «Ошибка программного обеспечения Win32: Subscript out of range»
- «Subscript out of range столкнулся с проблемой и закроется. «
- «Subscript out of range не может быть найден. «
- «Subscript out of range не может быть найден. «
- «Проблема при запуске приложения: Subscript out of range. «
- «Файл Subscript out of range не запущен.»
- «Отказ Subscript out of range.»
- «Неверный путь к приложению: Subscript out of range.»
Эти сообщения об ошибках Microsoft Corporation могут появляться во время установки программы, в то время как программа, связанная с Subscript out of range (например, Windows Operating System) работает, во время запуска или завершения работы Windows, или даже во время установки операционной системы Windows. Документирование проблем Subscript out of range в Windows Operating System является ключевым для определения причины проблем с электронной Windows и сообщения о них в Microsoft Corporation.
Причины ошибок в файле Subscript out of range
Проблемы Subscript out of range могут быть отнесены к поврежденным или отсутствующим файлам, содержащим ошибки записям реестра, связанным с Subscript out of range, или к вирусам / вредоносному ПО.
Более конкретно, данные ошибки Subscript out of range могут быть вызваны следующими причинами:
Subscript Out of Range
The ‘subscript out of range‘ error in Excel VBA occurs when you refer to a nonexistent collection member or a nonexistent array element.
Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.
Workbooks
The Workbooks collection in Excel VBA contains all the Workbook objects that are currently open.
1. The code line below closes sales.xlsm.
Result when there’s no open workbook with this name:
Note: to fix this ‘subscript out of range’ error (run-time error 9), open sales.xlsm before clicking the command button.
Worksheets
The Worksheets collection in Excel VBA contains all the Worksheet objects in a workbook. Our workbook has 3 worksheets.
1. The code line below tries to place the word Hello into cell A1 on the 4th worksheet.
Result when you click the command button on the sheet:
Explanation: the ‘subscript out of range’ error pops up because there’s no 4th worksheet. To fix this error, change the 4 to a 1, 2 or 3 (or insert a new worksheet by clicking the plus sign).
Array
An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.
1. The first code line below declares a String array with name Films. The array consists of five elements.
2. Next, we initialize each element of the array.
3. The final code line tries to display the 6th element using a MsgBox.
Dim Films(1 To 5) As String
Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"
Result when you click the command button on the sheet:
Explanation: the ‘subscript out of range’ error pops up because there’s no 6th element. To fix this error, change the 6 to a 1, 2, 3, 4 or 5 (or declare a String array with 6 elements).