Индекс вне диапазона vba excel
The subscript out of range is one of the most common errors that VBA coders will see. It’s never fun to see any of your code break. Fortunately, the fix for this is usually painless.
tl;dr – The subscript out of range error usually means VBA cant find the Excel object that you’re trying to reference. Correct the reference name and your code should work.
Table of Contents/Navigation
(Click Header Below to Scroll To Section)
Why Do You Get The Error
Workbook - Subscript Out of Range
Worksheet - Subscript Out of Range
Arrays - Subscript Out of Range
Why The Subscript Out Of Range Error
Often times the VBA Subscript Out of Range error will be one of the first errors that you encounter. This can happen with either recorded macros or self written VBA.
What causes this error is the following: Your macro/VBA has some reference to an excel object. A lot of the time, this can be a workbook, worksheet or Range. If you’ve changed/deleted/moved your Excel object, VBA will throw this error. In human terms, this is saying:
I can’t find the thing your looking for.
To dive a little deeper, Excel creates a collection of every object. Think of a collection as a bookshelf containing a lot of different books. In this example, the bookshelf will be the Workbook collection and the books will be the individual Workbooks.
Your collection of workbooks includes all of the open workbooks that you have open on your PC. If you try to reference a workbook that is not in the collection, you will get a subscript out of range error.
This example extends to any Excel object that has a collection (worksheets, workbooks, ranges, tables etc)
In the examples below, I will give some common scenarios that will help you avoid or correct this common error.
Subscript Out of Range - Workbook
The workbook tends to be a common culprit of the error. There are many ways a workbook reference can generate this error. In the following sections we will discuss various scenarios where this is likely to pop up.
Misspelling A Workbook Name
As silly as this sounds, often times you will see the Subscript Out Of Range error because of a misspelled workbook name. Consider the following:
You have VBA that is trying to activate a workbook. The workbook name is “myData.xlsx”.
The code above will throw a subscript out of range error. Fortunately, you caught your mistake and you fix your code to properly reflect the correctly spelled workbook name. The following code will run without any issues.
Using an Incorrect Index Number
As mentioned in the beginning of this post, the subscript out of range error pops up when you refer to a workbook that is not contained within the workbooks collection, or list of open workbooks on your pc.
Therefore, if you only have two workbooks open but use a workbook index number of three, you will get the error.
Referring a Workbook That's Not Open
Similar to misspelling a workbook name, if you try to do any action with a workbook that’s not currently open, you will get a Subscript Out of Range error.
Subscript Out of Range - Worksheet
We now turn our attention to the subscript out of range as it relates to a worksheet.
The ways you will get this range are very similar to the workbook. Specifically, you can expect to see this error if you
- Reference a worksheet that does not exist
- Use an improper worksheet Index number
- Misspell a tab/worksheet name
Referncing a Worksheet That Does Not Exist
Imagine the situation where we have two tabs as shown below. We try to activate a tab/worksheet that does not exist.
We only have two tabs in our workbook. Specifically, we have the “Input” and the “Calculation” tabs.
Let’s take a look at the code below.
As you can see, we are trying to activate the “output” tab. Because this does not exist, Excel will throw a Subscript Out of Range error.
To avoid this issue, always make sure that the worksheet that you are referencing exists in the workbook that you are working with.
Using an Incorrect Index Number
If your workbook has two worksheets and you use a index value higher than two, Excel will throw the subscript out of range error. Please see the examples below.
Misspelling a Worksheet Name
Similar to referencing a worksheet that doesn’t exist, if you try to do any action with a worksheet that’s that doesn’t exist in the workbook that you’re working in, you will get a Subscript Out of Range error.
Subscript Out of Range - Arrays
As you get a little more advanced with your VBA programming, you will start to use arrays in your work. Arrays are great as they are a good way to organize lots of similar data in a single array variable. This allows us to avoid creating multiple variables for similar items.
Common Array Error
Generally your array has a set of dimensions. If you try to acces an item of your array outside of the predetermiend demensions you will get a subscript out of range error.
Consider the following snippet of code.
The code is declaring an array to hold three strings. The array is defined from myArray(0) to myArray(2).
In the second to last line we use the msgBox to display myArray(2) which doesnt throw an error. However, the last line show tries to use myArray(3) which is not defined. Hence the Subscript Out of Range error.
To avoid this issue, always make sure that the worksheet that you are referencing exists in the workbook that you are working with.
Arrays Errors and For Loops
Consider the example where we have a list of ten amounts that represent order totals.
We now want to use VBA to loop through our data assign each of these values to an item in an array. We will use the following code.
This code works perfecly up until the iteration where x=10 during the for loop.
Instead of looping 10 times, our code is actually looping 11 times. Remember that both the array and our loop started with 0 and went up to 9, giving us a total or 10 values.
Let’s see what Excel is showing us for the error.
Conclusion
This brings us to our conclusion on the Subscript Out of Range error. If I am coding am happen to run into a bug, most of the time I am glad to see it is this one.
This error is usually pretty easy to debug. It is often one of the first errors that you will see when changing a macro or writing VBA from scratch. It is frequently driven by renaming a tab/workbook, misspelling an object reference, looping one too many times in an array, or just referencing something that doesn’t exist.
Leave a comment down below and let me know if this article helped or if there is more quality content that I can add to benefit my readers.
In the mean time feel free to check out the rest of our free lessons that will take you to your next level of productivity and output.
Ошибка VBA Subscript out of range возникает из-за того, что объект, к которому мы хотим получить доступ, не существует. Это тип ошибки в кодировании VBA и «Ошибка времени выполнения 9». Для написания эффективного кода важно понимать концепции, и еще более важно понимать ошибку вашего кода VBA для эффективной отладки кода.
Если вы и ваша ошибка кодирования не знаете, что это за ошибка, когда вас нет.
Врач не может давать лекарства своему пациенту, не зная о болезни. В то время как врач и пациент знают, что есть заболевание (ошибка), важно понимать заболевание (ошибку) как предоставление ей лекарства. Если вы полностью понимаете ошибку, гораздо проще найти решение.
Другая причина может заключаться в том, что на вашем компьютере нет стандартного принтера. Есть несколько решений, чтобы решить эту проблему Ошибка 9 во время выполнениятак что попробуйте все в правильном порядке.
Добавление принтера по умолчанию
- Подключите принтер к компьютеру, подключите шнур питания и включите его.
- Перейдите в «Пуск»> «Панель управления»> «Принтеры и факсы».
- Щелкните «Добавить принтер» и следуйте инструкциям на экране, чтобы добавить принтер по умолчанию.
Проверьте массивы
Возможно, вы установили неверное значение для элемента массива. Поэтому рекомендуется перепроверить значение, которое вы определили для элемента массива, и убедиться, что оно правильное. Также обязательно проверьте декларацию таблицы и проверьте верхний и нижний пределы. Если размер таблиц был изменен, обязательно используйте функции LBound и UBound для условий доступа. Проверьте правильность имени переменной, если индекс является переменной.
Определить количество элементов
В некоторых случаях вы, возможно, не определили количество элементов в вашем коде, которые будут вызывать ошибку. Рекомендуется указывать количество элементов в таблице с помощью функций Dim или ReDim.
Как избежать ошибки «Индекс ошибки вне допустимого диапазона» в VBA
Избегайте использования функций, интегрированных в Excel: ActiveWorkbook, ActiveSheet и Selection: введите возвращаемые значения и предпочитайте квалифицированные выражения.
Обновление за май 2022 года:
Теперь вы можете предотвратить проблемы с ПК с помощью этого инструмента, например, защитить вас от потери файлов и вредоносных программ. Кроме того, это отличный способ оптимизировать ваш компьютер для достижения максимальной производительности. Программа с легкостью исправляет типичные ошибки, которые могут возникнуть в системах Windows - нет необходимости часами искать и устранять неполадки, если у вас под рукой есть идеальное решение:
Используйте интегрированные только один раз и только в большинстве внешних макросов (подпрограмм) и захватывайте при запуске макроса, например
set wkb = ActiveWorkbook
set wks = ActiveSheet
Набор соли = выбор
Во время и в макросах эти интегрированные имена не являются надежными, но возвращаемые значения записываются, например,
Установите wkb = Workbooks.add 'вместо Workbooks.add без ввода возвращаемого значения.
wkb.Activate 'вместо Activeworkbook.Activate
Также попробуйте использовать квалифицированные выражения, например
wkb.Sheets («Sheet3»). name = «foo» 'вместо Sheets («Sheet3»). name = «foo».
or
Установите newWks = wkb.Sheets.Add.
newWks.name = «bar» 'вместо ActiveSheet.name = «bar».
Используйте квалифицированные выражения, например
newWks.name = «bar» 'вместо «xyz.Select», за которым следует Selection.name = «bar».
Эти методы обычно работают лучше, дают менее запутанные результаты, более устойчивы к рефакторингу (например, перемещают строки кода внутри и между методами) и работают лучше во всех версиях Excel. Например, выбор изменяется по-разному от одной версии Excel к другой во время выполнения макроса.
Также обратите внимание, что вы, вероятно, обнаружите, что использование более квалифицированных выражений не требует активации почти такого же количества функций. (Это может означать, что экран меньше мигает для пользователя.) Таким образом, вся строка Windows (выражение). Activate может быть просто удален, а не заменен wkb. Активировать на всех.
Совет экспертов: Этот инструмент восстановления сканирует репозитории и заменяет поврежденные или отсутствующие файлы, если ни один из этих методов не сработал. Это хорошо работает в большинстве случаев, когда проблема связана с повреждением системы. Этот инструмент также оптимизирует вашу систему, чтобы максимизировать производительность. Его можно скачать по Щелчок Здесь
CCNA, веб-разработчик, ПК для устранения неполадок
Я компьютерный энтузиаст и практикующий ИТ-специалист. У меня за плечами многолетний опыт работы в области компьютерного программирования, устранения неисправностей и ремонта оборудования. Я специализируюсь на веб-разработке и дизайне баз данных. У меня также есть сертификат CCNA для проектирования сетей и устранения неполадок.
Моя проблема заключается в следующем, Сначала в строке If . у меня была ошибка, требующая объекта, теперь я исправил это (я думал), но теперь на следующей строке sheetSkills("B" & j). я получаю ошибку нижнего индекса вне диапазона. у кого-нибудь есть идеи? Я не могу понять это на самом деле
У вас есть рабочие листы с именами «MainPage» и «Skills» в активной книге, когда это выполняется? Это единственная потенциальная ошибка нижнего индекса, которую я вижу в этом коде — и i , и j ограничены циклом.
У меня есть рабочие листы с именами MainPage и Skills, да, так как называть их Sheet1 или Sheet 2 было бы неудобным кодом imho
Пожалуйста, обновите/добавьте текущий код, который вы пытаетесь. Я нигде не вижу wsSkills . Кроме того, если вы пытаетесь сослаться на диапазон в wsSkills , вам нужно использовать wsSkills.Range("B"&j) или wsSkills.Cells(j,2)
Ключевым словом было «активная» рабочая тетрадь. Когда вы используете Application.Worksheets("Whatever") , он ищет это имя рабочего листа только в активной книге. Если у вас открыто более одного окна, а в фокусе находится не тот, вы можете найти его, а можете и не найти. Смотрите ответ @urdearboy, чтобы узнать, как лучше подойти к этому.
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно.
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей.
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то.
Ответы 1
- Может быть ваш код ищет другую книгу? Попробуйте ThisWorkbook.Sheets
- Добавьте Option Explicit , чтобы принудительно объявить переменную (хотя это не является источником вашей проблемы)
- Наконец, передача значения должна быть Range.Value = Range.Value
Единственная другая ошибка, которую я вижу, заключается в том, что имена ваших листов не совпадают. Я запустил этот код без проблем (и ваш). Если ваше фактическое имя листа содержит запаздывающий пробел или какой-либо другой символ, который не очевиден на первый взгляд, это даст вам Subscript Out of Range , так как это должно соответствовать точный.
Вы можете использовать это, чтобы проверить, что имена ваших листов находятся. Если какая-либо из этих строк содержит ошибку, это означает, что VBA не может найти лист в вашей книге.
Я скопировал имена листов в активной книге, у меня также нет других открытых книг, а скопированные имена вставлены между " " внутри кода. Я использовал ваш код, а также свой собственный, и я продолжаю получать ту же ошибку, Подстрочный индекс вне диапазона. У меня есть оба листа, доступные в рабочей книге. «Передача значения», на которую вы ссылались, в основном представляет собой передачу имени в Cell Sheets("Skills").Range("B" & i) Итак, я думаю, это строка. Также я отредактировал букву столбца в последней ссылке Sheets("Skills").
Решение проверено! Ошибка лежала где-то в имени MainPage, я действительно понятия не имею, откуда взялась ошибка имени, но теперь я изменил это имя на «Main», и код работает ИДЕАЛЬНО!! Спасибо всем!
Содержание:
Что вызывает ошибку «Нижний индекс вне допустимого диапазона» в VBA?
- Несуществующий элемент: В некоторых случаях возможно, что вы упомянули элемент в команде, который не существует. Возможно, что нижний индекс больше или меньше, чем диапазон возможных нижних индексов, или размеры, возможно, не были назначены массиву в этот момент в приложении.
- Неопределенные элементы: Возможно, вы не указали количество элементов в своем коде. Важно, чтобы количество элементов в массиве определялось с помощью команд «Dim» или «ReDim».
- Неверный член коллекции: В некоторых случаях пользователь мог ссылаться на несуществующий член коллекции. Если на член коллекции сделана неверная ссылка, может возникнуть эта ошибка.
- Сокращенный сценарий: Возможно, вы использовали сокращенную форму подстрочного индекса и неявно указали недопустимый элемент. Важно использовать действительное имя ключа.
Теперь, когда у вас есть базовое представление о природе проблемы, мы перейдем к ее решениям. Обязательно реализуйте их в том порядке, в котором они представлены, чтобы избежать конфликтов.
Решение 1. Проверка массивов
Возможно, вы определили неверное значение для элемента Array. Поэтому рекомендуется дважды проверить значение, которое вы определили для элемента Array, и убедиться, что оно правильное. Также убедитесь, что вы проверили объявление массива и проверить верхняя и нижняя границы. Если размер массивов был изменен, обязательно используйте LBound и UBound функции для условного доступа. Проверьте написание имени переменной, если индекс является переменной.
Решение 2. Указание количества элементов
В некоторых случаях возможно, что вы не определили количество элементов в своем коде, из-за которых возникает ошибка. Рекомендуется указывать количество элементов в массиве с помощью Тусклый или ReDim функции.
Решение 3: изменение конструкции
Эта ошибка обычно возникает, когда пользователь указывает неправильный или несуществующий член коллекции. Поэтому вместо указания элементов индекса рекомендуется использовать «Для каждого… Далее»Построить.
Решение 4.Проверка ключевого имени и индекса
В некоторых случаях возможно, что вы использовали сокращенную форму нижнего индекса, и он указывает недопустимый элемент. Поэтому рекомендуется использовать действующий ключназвание и индекс для коллекции.
The subscript out of range is one of the most common errors that VBA coders will see. It’s never fun to see any of your code break. Fortunately, the fix for this is usually painless.
tl;dr – The subscript out of range error usually means VBA cant find the Excel object that you’re trying to reference. Correct the reference name and your code should work.
Table of Contents/Navigation
(Click Header Below to Scroll To Section)
Why Do You Get The Error
Workbook - Subscript Out of Range
Worksheet - Subscript Out of Range
Arrays - Subscript Out of Range
Why The Subscript Out Of Range Error
Often times the VBA Subscript Out of Range error will be one of the first errors that you encounter. This can happen with either recorded macros or self written VBA.
What causes this error is the following: Your macro/VBA has some reference to an excel object. A lot of the time, this can be a workbook, worksheet or Range. If you’ve changed/deleted/moved your Excel object, VBA will throw this error. In human terms, this is saying:
I can’t find the thing your looking for.
To dive a little deeper, Excel creates a collection of every object. Think of a collection as a bookshelf containing a lot of different books. In this example, the bookshelf will be the Workbook collection and the books will be the individual Workbooks.
Your collection of workbooks includes all of the open workbooks that you have open on your PC. If you try to reference a workbook that is not in the collection, you will get a subscript out of range error.
This example extends to any Excel object that has a collection (worksheets, workbooks, ranges, tables etc)
In the examples below, I will give some common scenarios that will help you avoid or correct this common error.
Subscript Out of Range - Workbook
The workbook tends to be a common culprit of the error. There are many ways a workbook reference can generate this error. In the following sections we will discuss various scenarios where this is likely to pop up.
Misspelling A Workbook Name
As silly as this sounds, often times you will see the Subscript Out Of Range error because of a misspelled workbook name. Consider the following:
You have VBA that is trying to activate a workbook. The workbook name is “myData.xlsx”.
The code above will throw a subscript out of range error. Fortunately, you caught your mistake and you fix your code to properly reflect the correctly spelled workbook name. The following code will run without any issues.
Using an Incorrect Index Number
As mentioned in the beginning of this post, the subscript out of range error pops up when you refer to a workbook that is not contained within the workbooks collection, or list of open workbooks on your pc.
Therefore, if you only have two workbooks open but use a workbook index number of three, you will get the error.
Referring a Workbook That's Not Open
Similar to misspelling a workbook name, if you try to do any action with a workbook that’s not currently open, you will get a Subscript Out of Range error.
Subscript Out of Range - Worksheet
We now turn our attention to the subscript out of range as it relates to a worksheet.
The ways you will get this range are very similar to the workbook. Specifically, you can expect to see this error if you
- Reference a worksheet that does not exist
- Use an improper worksheet Index number
- Misspell a tab/worksheet name
Referncing a Worksheet That Does Not Exist
Imagine the situation where we have two tabs as shown below. We try to activate a tab/worksheet that does not exist.
We only have two tabs in our workbook. Specifically, we have the “Input” and the “Calculation” tabs.
Let’s take a look at the code below.
As you can see, we are trying to activate the “output” tab. Because this does not exist, Excel will throw a Subscript Out of Range error.
To avoid this issue, always make sure that the worksheet that you are referencing exists in the workbook that you are working with.
Using an Incorrect Index Number
If your workbook has two worksheets and you use a index value higher than two, Excel will throw the subscript out of range error. Please see the examples below.
Misspelling a Worksheet Name
Similar to referencing a worksheet that doesn’t exist, if you try to do any action with a worksheet that’s that doesn’t exist in the workbook that you’re working in, you will get a Subscript Out of Range error.
Subscript Out of Range - Arrays
As you get a little more advanced with your VBA programming, you will start to use arrays in your work. Arrays are great as they are a good way to organize lots of similar data in a single array variable. This allows us to avoid creating multiple variables for similar items.
Common Array Error
Generally your array has a set of dimensions. If you try to acces an item of your array outside of the predetermiend demensions you will get a subscript out of range error.
Consider the following snippet of code.
The code is declaring an array to hold three strings. The array is defined from myArray(0) to myArray(2).
In the second to last line we use the msgBox to display myArray(2) which doesnt throw an error. However, the last line show tries to use myArray(3) which is not defined. Hence the Subscript Out of Range error.
To avoid this issue, always make sure that the worksheet that you are referencing exists in the workbook that you are working with.
Arrays Errors and For Loops
Consider the example where we have a list of ten amounts that represent order totals.
We now want to use VBA to loop through our data assign each of these values to an item in an array. We will use the following code.
This code works perfecly up until the iteration where x=10 during the for loop.
Instead of looping 10 times, our code is actually looping 11 times. Remember that both the array and our loop started with 0 and went up to 9, giving us a total or 10 values.
Let’s see what Excel is showing us for the error.
Conclusion
This brings us to our conclusion on the Subscript Out of Range error. If I am coding am happen to run into a bug, most of the time I am glad to see it is this one.
This error is usually pretty easy to debug. It is often one of the first errors that you will see when changing a macro or writing VBA from scratch. It is frequently driven by renaming a tab/workbook, misspelling an object reference, looping one too many times in an array, or just referencing something that doesn’t exist.
Leave a comment down below and let me know if this article helped or if there is more quality content that I can add to benefit my readers.
In the mean time feel free to check out the rest of our free lessons that will take you to your next level of productivity and output.
Читайте также: