Индекс вне диапазона vba excel

Обновлено: 05.07.2024

Знакомство с объектной моделью Excel следует начинать с такого замечательного объекта, как Range . Поскольку любая ячейка - это Range , то без знания, как с этим объектом эффективно взаимодействовать, вам будет затруднительно программировать для Excel. Это очень ладно-скроенный объект. При некоторой сноровке вы найдёте его весьма удобным в эксплуатации.

Что такое объекты?

Мы собираемся изучать объект Range , поэтому пару слов надо сказать, что такое, собственно, " объект ". Всё, что вы наблюдаете в Excel, всё с чем вы работаете - это набор объектов. Например, лист рабочей книги Excel - не что иное, как объект типа WorkSheet . Однотипные объекты объединяют в коллекции себе подобных. Например, листы объединены в коллекцию Sheets . Чтобы не путать друг с другом объекты одного и того же типа, они имеют отличающиеся имена, а также номер индекса в коллекции. Объекты имеют свойства , методы и события .
Свойства - это информация об объекте. Часто эти свойства можно менять, что автоматически влечет изменения внешнего вида объекта или его поведения. Например свойство Visible объекта Worksheet отвечает за видимость листа на экране. Если ему присвоить значение xlSheetHidden (это константа, которая по факту равно нулю), то лист будет скрыт.
Методы - это то, что объект может делать. Например, метод Delete объекта Worksheet удаляет себя из книги. Метод Select делает лист активным.
События - это механизм, при помощи которого вы можете исполнять свой код VBA сразу по факту возникновения того или иного события с вашим объектом. Например, есть возможность выполнять ваш код, как только пользователь сделал текущим определенный лист рабочей книги, либо как только пользователь что-то изменил на этом листе.

Объекты Range

Range это диапазон ячеек. Минимум - одна ячейка, максимум - весь лист, теоретически насчитывающий более 17 миллиардов ячеек (строки 2^20 * столбцы 2^14 = 2^34).
В Excel объявлены глобально и всегда готовы к использованию несколько коллекций, имеющий членами объекты типа Range , либо свойства это же типа. Коллекции глобального объекта Application : Cells , Columns , Rows , а также свойства Range , Selection , ActiveCell , ThisCell .
ActiveCell - активная ячейка текущего листа, ThisCell - если вы написали пользовательскую функцию рабочего листа, то через это свойство вы можете определить какая конкретно ячейка в данный момент пересчитывает вашу функцию. Об остальных перечисленных объектов речь пойдёт ниже.

Работа с отдельными ячейками

Синтаксическая форма	Комментарии по использованию
Range (" D5 ") или [ D5 ]	Ячейка D5 текущего листа. Полная и краткая формы. Тут применим только синтаксис типа A1, но не R1C1. То есть такая конструкция Range (" R1C2 ") - вызовет ошибку, даже если в книге Excel включен режим формул R1C1. Разумеется после этой формы вы можете обратиться к свойствам соответствующей ячейки. Например, Range (" D5 ") .Interior.Color = RGB(0, 255, 0) .
Cells(5, 4) или Cells(5, "D")	Ячейка D5 текущего листа через свойство Cells . 5 - строка (row), 4 - столбец (column). Допустимость второй формы мало кому известна.
Cells(65540)	Ячейку D5 можно адресовать и через указание только одного параметра свойсва Cells . При этом нумерация идёт слева направо, потом сверху вниз. То есть сначала нумеруется вся строка (2^14=16384 колонок) и только потом идёт переход на следующую строку. То есть Cells(16385) вернёт вам ячейку A2 , а D5 будет Cells(65540) . Пока данный способ выглядит не очень удобным.

Работа с диапазоном ячеек

Синтаксическая форма	Комментарии по использованию
Range ( "A1:B4 ") или [ A1:B4 ]	Диапазон ячеек A1:B4 текущего листа. Обратите внимание, что указываются координаты верхнего левого и правого нижнего углов диапазона. Причём первый указываемый угол вполне может быть правым нижним, это не имеет значения.
Range(Cells(1, 1), Cells(4, 2))	Диапазон ячеек A1:B4 текущего листа. Удобно, когда вы знаете именно цифровые координаты углов диапазона.

Работа со строками

Синтаксическая форма	Комментарии по использованию
Range (" 3:5 ") или [ 3:5 ]	Строки 3, 4 и 5 текущего листа целиком.
Range (" A3:XFD3 ") или [ A3:XFD3 ]	Строка 3, но с указанием колонок. Просто, чтобы вы понимали, что это тождественные формы. XFD - последняя колонка листа.
Rows (" 3:3 ")	Строка 3 через свойство Rows . Параметр в виде диапазона строк. Двоеточие - это символ диапазона.
Rows(3)	Тут параметр - индекс строки в массиве строк. Так можно сослаться только не конкретную строку. Обратите внимание, что в предыдущем примере параметр текстовая строка " 3:3 " и она взята в кавычки, а тут - чистое число.

Работа со столбцами

Синтаксическая форма	Комментарии по использованию
Range (" B:B ") или [ B:B ]	Колонка B текущего листа.
Range (" B1:B1048576 ") или [ B1:B1048576 ]	То же самое, но с указанием номеров строк, чтобы вы понимали, что это тождественные формы. 2^20=1048576 - максимальный номер строки на листе.
Columns (" B:B ")	То же самое через свойство Columns . Параметр - текстовая строка.
Columns(2)	То же самое. Параметр - числовой индекс столбца. "A" -> 1, "B" -> 2, и т.д.

Весь лист

Синтаксическая форма	Комментарии по использованию
Range (" A1:XFD1048576 ") или [ A1:XFD1048576 ]	Диапазон размером во всё адресное пространство листа Excel. Воспринимайте эту таблицу лишь как теорию - так работать с листами вам не придётся - слишком большое количество ячеек. Даже современные компьютеры не смогут помочь Excel быстро работать с такими массивами информации. Тут проблема больше даже в самом приложении.
Range (" 1:1048576 ") или [ 1:1048576 ]	То же самое, но через строки.
Range (" A:XFD ") или [ A:XFD ]	Аналогично - через адреса столбцов.
Cells	Свойство Cells включает в себя ВСЕ ячейки.
Rows	Все строки листа.
Columns	Все столбцы листа.

Следует иметь в виду, что свойства Range , Cells , Columns и Rows имеют как объекты типа Worksheet , так и объекты Range . Соответственно в первом случае эти коллекции будут относиться ко всему листу и отсчитываться будут от A1 , а вот в случае конкретного объекта Range эти коллекции будут относиться только к ячейкам этого диапазона и отсчитываться будут от левого верхнего угла диапазона. Например Cells(2,2) указывает на ячейку B2 , а Range("C3:D5").Cells(2,2) укажет на D4 .

Также много путаницы в умы вносит тот факт, что объект Range имеет одноименное свойство range . К примеру, Range("A100:D500").Range("A2") - тут выражение до точки ( Range("A100:D500") ) является объектом Range , выражение после точки ( Range("A2") ) - свойство range упомянутого объекта, но возвращает это свойство тоже объект типа Range . Вот такие пироги. Из этого следует, что такая цепочка может иметь и более двух членов. Практического смысла в этом будет не много, но синтаксически это будут совершенно корректно, например, так: Range("CV100:GR200").Range("J10:T20").Range("A1:B2") укажет на диапазон DE109:DF110 .

Ещё один сюрприз таится в том, что объекты Range имеют свойство по-умолчанию Item( RowIndex [, ColumnIndex] ) . По правилам VBA при ссылке на default свойства имя свойства ( Item ) можно опускать. Кстати говоря, то что вы привыкли видеть в скобках после Cells , есть не что иное, как это дефолтовое свойство Item , а не родные параметры Cells , который их не имеет вовсе. Ну ладно к Cells все привыкли и это никакого отторжения не вызывает, но если вы увидите нечто подобное - Range("C3:D5")(2,2) , то, скорее всего, будете несколько озадачены, а тем временем - это буквально тоже самое, что и у Cells - всё то же дефолтовое свойство Item . Последняя конструкция ссылается на D4 . А вот для Columns и Rows свойство Item может быть только одночленным, например Columns(1) - и к этой форме мы тоже вполне привыкли. Однако конструкции вида Columns(2)(3)(4) могут сильно удивить (столбец 7 будет выделен).

Примеры кода

Типовые задачи

Перебор ячеек в диапазоне (вариант 1)

В данном примере организован цикл For. Next и доступ к ячейкам осуществляется по их индексу. Вместо parRange(i) мы могли бы написать parRange.Item(i) (выше это объяснялось). Обратите внимание, что мы в этом примере успешно применяем, как вариант с parRange(i,c) , так и parRange(i) . То есть, если мы применяем одночленную форму свойства Item , то диапазон перебирается по строкам ( A1 , B1 , C1 , A2 , . ), а если двухчленную, то столбец у нас зафиксирован и каждая итерация цикла - на новой строке. Это очень интересный эффект, его можно применять для вытягивания таблиц по вертикали. Но - продолжим!

Количество ячеек в диапазоне получено при помощи свойства .Count . Как .Item , так и .Count - это всё атрибуты коллекций, которые широко применяются в объектой модели MS Office и, в частности, Excel.

Ошибка VBA Subscript out of range возникает из-за того, что объект, к которому мы хотим получить доступ, не существует. Это тип ошибки в кодировании VBA и «Ошибка времени выполнения 9». Для написания эффективного кода важно понимать концепции, и еще более важно понимать ошибку вашего кода VBA для эффективной отладки кода.

Если вы и ваша ошибка кодирования не знаете, что это за ошибка, когда вас нет.

Врач не может давать лекарства своему пациенту, не зная о болезни. В то время как врач и пациент знают, что есть заболевание (ошибка), важно понимать заболевание (ошибку) как предоставление ей лекарства. Если вы полностью понимаете ошибку, гораздо проще найти решение.

Другая причина может заключаться в том, что на вашем компьютере нет стандартного принтера. Есть несколько решений, чтобы решить эту проблему Ошибка 9 во время выполнениятак что попробуйте все в правильном порядке.

Добавление принтера по умолчанию

Подключите принтер к компьютеру, подключите шнур питания и включите его.
Перейдите в «Пуск»> «Панель управления»> «Принтеры и факсы».
Щелкните «Добавить принтер» и следуйте инструкциям на экране, чтобы добавить принтер по умолчанию.

Проверьте массивы

Возможно, вы установили неверное значение для элемента массива. Поэтому рекомендуется перепроверить значение, которое вы определили для элемента массива, и убедиться, что оно правильное. Также обязательно проверьте декларацию таблицы и проверьте верхний и нижний пределы. Если размер таблиц был изменен, обязательно используйте функции LBound и UBound для условий доступа. Проверьте правильность имени переменной, если индекс является переменной.

Определить количество элементов

В некоторых случаях вы, возможно, не определили количество элементов в вашем коде, которые будут вызывать ошибку. Рекомендуется указывать количество элементов в таблице с помощью функций Dim или ReDim.

Как избежать ошибки «Индекс ошибки вне допустимого диапазона» в VBA

Избегайте использования функций, интегрированных в Excel: ActiveWorkbook, ActiveSheet и Selection: введите возвращаемые значения и предпочитайте квалифицированные выражения.

Ноябрьское обновление 2021:

Теперь вы можете предотвратить проблемы с ПК с помощью этого инструмента, например, защитить вас от потери файлов и вредоносных программ. Кроме того, это отличный способ оптимизировать ваш компьютер для достижения максимальной производительности. Программа с легкостью исправляет типичные ошибки, которые могут возникнуть в системах 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 для проектирования сетей и устранения неполадок.

Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.

Присваивается переменной диапазон ячеек с помощью оператора Set:

В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.

Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.

Адресация ячеек в диапазоне

К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.

Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

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

Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

Всех приветствую.
Может кто то раньше сталкивался с подобными выкрутасами ворда и знает где рыть? Ворд 2010, Винда 7, все последние обновления стоят. Имеется файл ворда формата doc полученный со стороны.
При установке значений размера бумаги и ориентации страниц, единых для всего документа(например А4, портрет) через форму "Параметры страницы" все изменения принимаются нормально.
Если запускать макрос, записанный при выполнении предыдущих изменений, он выдает ошибку 4608 "Значение лежит вне допустимого диапазона".
Проблемный файл прилагаю. Код самозаписанного макроса в файле.

Документ.rar 18.84 Кб, 6 скачиваний с 2016-09-09

You don't have the permssions to download the attachments of this post.

2 Ответ от Fck_This 12.09.2016 09:43:16

Всех приветствую.
Может кто то раньше сталкивался с подобными выкрутасами ворда и знает где рыть? Ворд 2010, Винда 7, все последние обновления стоят. Имеется файл ворда формата doc полученный со стороны.
При установке значений размера бумаги и ориентации страниц, единых для всего документа(например А4, портрет) через форму "Параметры страницы" все изменения принимаются нормально.
Если запускать макрос, записанный при выполнении предыдущих изменений, он выдает ошибку 4608 "Значение лежит вне допустимого диапазона".
Проблемный файл прилагаю. Код самозаписанного макроса в файле.

Может просто код выложите отдельно? ошибку выбивает при распаковке.

Спасибо можно перевести на WebMoney-кошелёк R378231864568 или на Яндекс-деньги 410015093172871

3 Ответ от mikkelle 13.09.2016 10:19:01

ошибку выбивает при распаковке.

Не знаю, проверил, все нормально скачивается и распаковывается.

Код самый незамысловатый. Привожу как его создала макрорекодер. Первую же ошибку выдает на строке
.LineNumbering.Active = False