Vba excel нельзя установить свойство formulaarray класса range

Обновлено: 07.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.

Я вставляю следующую формулу INDEX MATCH в набор ячеек, используя VBA.

При этом я сталкиваюсь с ошибкой 1004 - "Невозможно установить свойство FormulaArray класса Range"

Я вполне уверен, что это как-то связано со ссылкой на рабочую книгу вне текущей, так как она прекрасно работает, когда я ИНДЕКСИРУЕМ МАТЧ внутри той же книги.

Сокращенный вариант формулы (для ясности чтения) следующий:

2 ответа

Согласно моему комментарию, это сводится к ограничению длины формулы, которую вы можете использовать в VBA с FormulaArray , Здесь вы, вероятно, можете просто использовать версию без массива:

хотя это вернет последний соответствующий элемент, а не первый, если у вас есть несколько строк, соответствующих вашим критериям.

Как войти FormulaArray более 255 символов, использующих VBA

Похоже, что в этом случае была альтернативная стандартная формула, которая соответствовала требованиям оригинала FormulaArray , Однако могут быть случаи, для которых нет альтернативной формулы. Для этих случаев у меня есть следующий метод ввода FormulaArray с более чем 255 символов, использующих VBA.

Большую часть времени, когда FormulaArray длиннее 255 символов из-за длины references он содержит, как они могут относиться к длинным постоянным массивам, внешние рабочие книги с большими именами (как в этом случае) или рабочие таблицы с большими именами (также в этом случае). Метод заключается в замене этих длинных строк на более короткие, однако для FormulaArray (после замены) быть принятым как FormulaArray эти короткие строки должны также представлять действительные references ,

Согласно вышеизложенному, может быть, по крайней мере, три ситуации с references :

Длинные постоянные массивы: в этих случаях используйте Defined Names как описано здесь

Workbooks с большими именами и
Worksheets с большими именами

Для случаев 2 и 3 применяется тот же метод: использование коротких references указывая на временный Worksheet как временная замена.

Применение метода к этому случаю:

оригинал FormulaArray : использовать переменную sFmlArray держать формулу

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

Замените названия рабочей книги и листа в FormulaArray с соответствующей переменной:

Предполагая, что мы хотим войти так долго FormulaArray В диапазоне D7:D10 давайте присвоим его переменной

Используйте функцию ниже, чтобы добавить временную таблицу. Эта функция также обеспечивает временный reference использоваться в качестве замены в FormulaArray

Заменить в FormulaArray Длинные ссылки с коротким и введите временный FormulaArray в rFmlAry спектр

С FormulaArray на месте, заменить временную короткую references с оригинальными длинными

Окончательно удали временный Worksheet

Это вся процедура (в качестве теста добавлено несколько строк в конце для проверки результата)

Обсуждаем вопросы только по Excel VBA
(программирование макросов, скриптов, пользовательских функций и т.п.).
Приветствуются ссылки на ресурсы и справочную литературу по теме.

Вопросы по работе с MS Excel, не относящиеся к программированию, задаем в теме Excel FAQ

Обратите внимание, этот топик для помощи в изучении и использовании VBA. Посему запросы типа "Напишите мне такой-то макрос, я VBA не знаю и знать не хочу" не приветствуются.
Древняя мудрость: "Накорми голодного рыбой и он погибнет, научи его ловить рыбу и ты спасешь его."(R)

Рекомендации:
Если у Вас есть проблема, не решаемая стандартными средствами Excel (об этом можно уточнить здесь ) или требующая автоматизации, попробуйте для начала записать макрос самим Excel через меню Сервис (Tools) - Макрос (Macro) - Начать запись (Record New Macro). Подробнее здесь. В большинстве случаев получившийся код (Сервис-Макрос-Макросы-Изменить) Вас не удовлетворит, но подскажет, какие объекты-методы-свойства использовать.
Другой Ваш помощник - Просмотр объектов (Object Browser). Ну и встроення помощь (F1), естественно.

Рекомендуется к прочтению:

(для продвинутых)
(eng.) - тематический блог: советы по работе с Excel и прочие материалы
(eng.) - советы, трюки и уловки
(eng.) - весьма оживленный форум по Excel&VBA.

Для листа "А" (в редакторе VBA своенный щелчёк в менеджере объектов на листе, в который вводятся данные) прописываем следующее:

Если вывод жёстко в следующую за последней строку листа, то, как вариант:
resSheet.Cells(1&, 1&).End(xlDown).Offset(1&, 0&).FormulaR1C1 = _
"=SUM(" & part & "C1:" & part & "C3)"

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

ужас какой-то ничего не понимаю. а главное не понмиаю зачем нам это задуют. очень сомневаюсь что мне когда-нибудь придет в голову при работе с экселем писать что-нибудь в VBA.

Кстати, нашел несоответствие между русским 2007 и 2003: в первом работает =TRANSPOSE. а 2003 на нем спотыкается и требует =ТРАНСП Кажется, верно и обратное. И не только этой функции касается.

- вызывает исключение в английском ворде (ему требуется чтобы Label:="Figure"). И наоборот.

Только вчера я напоролся на указанную несовместимость русского 2003 (=ТРАНСП) и русского же 2007 (=TRANSPOSE) и решил пересесть на последний.

Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:

если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.

В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:

самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:

Dim oRange As Range

А на диапазон ячеек с A1 по D10 — так:

Dim oRange As Range

С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:

Set oRange1 = Worksheets("Лист1").Range("C1")

пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).

второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Cells(1, 4)

Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:

Set oRange = Range(Cells(1, 1), Cells(5, 3))

третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.

Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:

Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:

Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.

На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:

sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))

sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))

На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().

If Selection.Areas.Count > 1 Then

Debug.Print "Диапазон с несмежными областями"

Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:

Dim oRange, oRange2 As Range

Set oRange = Range(Cells(2, 2), Cells(5, 3))

Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2

Debug.Print oRange2.Address 'Так оно и есть

Точно такие же особенности у свойств Row и Rows, Column и Columns.

Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом

Dim oRange As Range

Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.

Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом

Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
Worksheets("Лист1").Activate
ActiveCell.Dependents.Select

Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.

Читайте также: