Ребята, всем привет! 👋
Продолжаем изучать VBA Excel. Ранее мы уже говорили об ошибках возникающих при запуске процедур Excel VBA:
- Ошибка 1004 VBA - ошибка, определяемая приложением или объектом
- Несоответствие типа VBA (ошибка времени выполнения 13). Часть 1
- Несоответствие типа VBA (ошибка времени выполнения 13). Часть 2. Исправляем ошибки
- Как использовать VBA при возобновлении работы с ошибкой Next и при ошибке Goto 0
- Ошибка компиляции VBA
В продолжение темы рассмотрим еще одну: ошибку Run time error 9, примеры конкретных причин ошибки и способы ее устранения.
✍️ Если вы только начинаете осваивать VBA Excel мы уверены, каждый может найти для себя что-то новое!
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
На практике, возможны случаи когда вы пытаетесь запустить процедуру в Excel VBA, а получаете окно сообщения об ошибке Run time error 9 (Ошибка времени выполнения 9), что означает - подстрочный индекс вне диапазона”.
Данная ошибка (Run time error 9) возникает, когда мы ссылаемся на несуществующий элемент коллекции или несуществующий элемент массива. Иными словами Excel сообщает нам: “Я не нашел то, что вы ищете”.
Прежде чем исправлять ошибку Run time error 9 (как и любую другую) мы должны определить ее причину, а потому рассмотрим подробно некоторые причины и пути решения.
⏩ Причина № 1: Ссылка на несуществующий элемент в коллекции
Предположим, у нас есть только Лист1 и Лист2 в нашей активной рабочей книге:
Если мы напишем следующий код:
и нажмем F5, чтобы запустить ее. Мы получаем ошибку:
♦ Причина ошибки: Ошибка возникает из-за отсутствия объекта Лист3 в коллекции Sheets, которая состоит из всех листов и листов диаграмм в активной рабочей книге, а выше рассмотренный код ссылается на несуществующий объект в коллекции Sheets.
Поэтому попытка доступа к элементу коллекции за пределами определенной области приведет к ошибке времени выполнения “Run time error 9”.
📝 Как исправить?
🔔 Что важно! Мы можем получить доступ только к элементам коллекций в пределах их ограниченных диапазонов.
Вы можете исправить эту ошибку любым из следующих способов:
- Создайте несуществующий объект (в рассматриваемом примере следует добавить Лист3 в рабочую книгу);
- Проверьте правильность написания имени элемента коллекции, к которому вы хотите получить доступ, и убедитесь, что оно правильное.
- В коде Excel VBA обратитесь к объекту, присутствующему в коллекции; в этом случае вы можете ссылаться либо на Лист1, либо на Лист2.
- Используйте цикл For Each...Next вместо ссылки на конкретные элементы коллекции. Цикл позволяет нам перебирать элементы коллекции и повторять определенные действия, такие как отображение листов.
⏩ Причина № 2: Попытка доступа к закрытой книге
При попытке получить доступ к закрытой книге так же выдается ошибка “Run time error 9”.
Например, если у нас на компьютере закрыта рабочая книга с именем “Отчет”, выполнение следующего кода:
приведет к появлению ошибки “Run time error 9”.
♦ Причина ошибки:
🔔 Что важно! Все открытые рабочие книги на Вашем компьютере составляют коллекцию workbooks.
Приведенная выше ошибка возникает из-за того, что закрытая рабочая книга не является частью коллекции рабочих книг:
📝 Как исправить?
Вы можете использовать любой из следующих методов для исправления ошибки.
- Откройте книгу, к которой вы хотите получить доступ, затем запустите подпрограмму.
- Используйте цикл For Each ...Next, чтобы проверить, открыта ли книга, к которой вы хотите получить доступ. Цикл перебирает все доступные книги и сопоставляет имя книги, к которой вы хотите получить доступ, с каждой открытой книгой. Если найдено совпадение, книга открыта в противном случае; книга закрыта.
⏩ Причина № 3: Ссылка на несуществующий элемент массива
Например, следующий код ссылается на элемент массива 11, которого нет в объявлении массива:
Когда мы пытаемся запустить код, мы получаем ошибку “Run time error 9”.
♦ Причина ошибки: Если вы ссылаетесь на несуществующий элемент массива в своем коде, VBA отображает “Run time error 9”, чтобы указать на что-то неправильное в коде:
📝 Как исправить?
Для решения проблемы можно использовать любой из следующих методов:
- Проверьте верхнюю и нижнюю границы массива в объявлении массива. Если размеры отличаются от того, что вы предполагали, отрегулируйте их соответствующим образом.
- Убедитесь, что вы ссылаетесь только на элементы массива в объявлении массива.
- Используйте функции LBound и UBound для прямого доступа к массивам с измененными размерами.
Функция LBound возвращает нижнюю границу массива, которая может быть либо 0, либо 1.
Функция UBound возвращает верхний предел массива, который эквивалентен количеству элементов в массиве.
- Если измерения массива объявлены переменными, убедитесь, что имена переменных написаны правильно.
⏩ Причина № 4: Не указано количество элементов в массиве
Например, следующий код приводит к ошибке:
🔔 Что важно! Если вы объявляете массив, но не указываете количество элементов в массиве и пытаетесь получить доступ к элементу в массиве, VBA возвращает ошибку “Run time error 9”.
📝 Как исправить?
- Явно укажите количество элементов в массиве в объявлении массива.
⏩ Причина № 5: Ошибка в написании названия рабочей книги
Например, если вы хотите активировать книгу с именем “Отчет.xlsx ,” следующий код не активирует рабочую книгу и возвращает ошибку:
♦ Причина ошибки: В данном случае неправильно указано имя книги, к которой необходимо получить доступ:
Поэтому Excel возвращает сообщение “Run time error 9” хотя рабочая книга открыта, код не активирует ее, поскольку ее имя указано не верно и его необходимо исправить.
📝 Как исправить?
- Проверьте написание имени рабочей книги в коде и убедитесь, что оно правильное.
⏩ Причина № 6: Указание недопустимого элемента
Иногда при использовании сокращенной формы подстрочного индекса вы можете ошибочно указать недопустимый элемент.
Например, сокращение для ActiveSheet.Range (“C3”) равен [C3]. Если эта сокращенная форма подстрочного индекса ссылается на недопустимый элемент, вы получите сообщение “Run time error 9”.
📝 Как исправить?
- Используйте допустимый индекс или имя для коллекции.
В дополнение темы...
см. так же:
А на этом сегодня все. 👏 Теперь вы знаете как исправить ошибку Run time error 9. Я надеюсь, что вы нашли этот урок полезным.
Продолжение следует...
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Сэкономьте сумасшедшее количество времени, используя VBA. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
Понравился урок!? не забываем оставлять комментарий 📝Нам очень важна Ваша обратная связь 💝
И конечно же, за лайк 👍 благодарочка 💖 и респект 🤝 каждому!
