✅ Формулы Excel VBA - полное руководство

Содержание

Формулы в VBA
Регистратор макросов и формулы ячеек
Свойство VBA FormulaR1C1
Свойство формулы VBA
Советы по формулам VBA

Из этого туториала Вы узнаете, как создавать формулы ячеек с помощью VBA.

Формулы в VBA

Используя VBA, вы можете писать формулы прямо в диапазоны или ячейки в Excel. Это выглядит так:

123456789

Подформула_Пример ()'Назначьте жестко запрограммированную формулу одной ячейкеДиапазон ("b3"). Формула = "= b1 + b2"'Назначьте гибкую формулу диапазону ячеекДиапазон ("d1: d100"). Формула R1C1 = "= RC2 + RC3"Конец подписки

Вам необходимо знать два свойства Range:

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

Для простых формул можно использовать свойство .Formula. Однако для всего остального мы рекомендуем использовать Макро регистратор…

Регистратор макросов и формулы ячеек

Macro Recorder - это наш незаменимый инструмент для написания формул ячеек с помощью VBA. Вы можете просто:

Начать запись
Введите формулу (с относительными / абсолютными ссылками, если необходимо) в ячейку и нажмите клавишу ВВОД.
Остановить запись
Откройте VBA и просмотрите формулу, при необходимости адаптируя и копируя + вставляя код там, где это необходимо.

Я нахожу это намного легче для ввода формулы в ячейку, чем для ввода соответствующей формулы в VBA.

Обратите внимание на пару вещей:

Средство записи макросов всегда будет использовать свойство .FormulaR1C1.
Регистратор макросов распознает абсолютные и относительные ссылки на ячейки

Свойство VBA FormulaR1C1

Свойство FormulaR1C1 использует ссылки на ячейки в стиле R1C1 (в отличие от стандартного стиля A1, который вы привыкли видеть в Excel).

Вот некоторые примеры:

12345678910111213141516171819

Подформула R1C1_Examples ()Ссылка D5 (абсолютная)'= $ D $ 5Диапазон ("a1"). FormulaR1C1 = "= R5C4"'Ссылка D5 (относительная) из ячейки A1'= D5Диапазон ("a1"). FormulaR1C1 = "= R [4] C [3]"'Ссылка D5 (абсолютная строка, относительный столбец) из ячейки A1'= D $ 5Диапазон ("a1"). FormulaR1C1 = "= R5C [3]"'Ссылка D5 (относительная строка, абсолютный столбец) из ячейки A1'= $ D5Диапазон ("a1"). FormulaR1C1 = "= R [4] C4"Конец подписки

Обратите внимание, что ссылки на ячейки в стиле R1C1 позволяют устанавливать абсолютные или относительные ссылки.

Абсолютные ссылки

В стандартной записи A1 абсолютная ссылка выглядит так: «= $ C $ 2». В нотации R1C1 это выглядит так: «= R2C3».

Чтобы создать абсолютную ссылку на ячейку с использованием типа R1C1:

R + номер строки
C + номер столбца

Пример: R2C3 будет представлять ячейку $ C $ 2 (C - третий столбец).

123	Ссылка D5 (абсолютная)'= $ D $ 5Диапазон ("a1"). FormulaR1C1 = "= R5C4"

Относительные ссылки

Относительные ссылки на ячейки - это ссылки на ячейки, которые «перемещаются» при перемещении формулы.

В стандартной записи A1 они выглядят так: «= C2». В нотации R1C1 скобки [] используются для смещения ссылки на ячейку от текущей ячейки.

Пример: ввод формулы «= R [1] C [1]» в ячейку B3 будет ссылаться на ячейку D4 (ячейка 1 строка ниже и 1 столбец справа от ячейки формулы).

Используйте отрицательные числа для ссылки на ячейки выше или слева от текущей ячейки.

123	'Ссылка D5 (относительная) из ячейки A1'= D5Диапазон ("a1"). FormulaR1C1 = "= R [4] C [3]"

Смешанные ссылки

Ссылки на ячейки могут быть частично относительными и частично абсолютными. Пример:

123	'Ссылка D5 (относительная строка, абсолютный столбец) из ячейки A1'= $ D5Диапазон ("a1"). FormulaR1C1 = "= R [4] C4"

Свойство формулы VBA

При установке формул с расширением.Формула Свойство вы всегда будете использовать нотацию в стиле A1. Вы вводите формулу так же, как в ячейку Excel, только в кавычках:

12	'Назначьте жестко запрограммированную формулу одной ячейкеДиапазон ("b3"). Формула = "= b1 + b2"

Советы по формулам VBA

Формула с переменной

При работе с формулами в VBA очень часто требуется использовать переменные в формулах ячеек. Чтобы использовать переменные, используйте & для объединения переменных с остальной частью строки формулы. Пример:

1234567

Подформула_Переменная ()Dim colNum As LongcolNum = 4Диапазон ("a1"). FormulaR1C1 = "= R1C" & colNum & "+ R2C" & colNumКонец подписки

Формула Котировки

Если вам нужно добавить цитату («) в формулу, введите цитату дважды (« »):

123	Дополнительный макрос 2 ()Диапазон ("B3"). FormulaR1C1 = "= ТЕКСТ (RC [-1]," "мм / дд / гггг" ")"Конец подписки

Одиночная кавычка («) означает для VBA конец строки текста. В то время как двойные кавычки («») обрабатываются как кавычки в строке текста.

Точно так же используйте 3 кавычки («» »), чтобы заключить строку в кавычки («).

12	MsgBox "" "Используйте 3, чтобы заключить строку в кавычки" ""'Это немедленно распечатает окно

Назначить формулу ячейки строковой переменной

Мы можем прочитать формулу в данной ячейке или диапазоне и присвоить ее строковой переменной:

123	'Назначить формулу ячейки переменнойDim strFormula как строкаstrFormula = Range ("B1"). Формула

Различные способы добавления формул в ячейку

Вот еще несколько примеров того, как назначить формулу ячейке:

Непосредственно назначить формулу
Определите строковую переменную, содержащую формулу
Используйте переменные для создания формулы

12345678910111213141516171819202122232425

Sub MoreFormulaExamples ()'Альтернативные способы добавления формулы СУММ'в ячейку B1'Dim strFormula как строкаТусклая ячейка как диапазонdim fromRow как Range, toRow как RangeУстановить ячейку = Диапазон ("B1")'Непосредственное присвоение строкиcell.Formula = "= СУММ (A1: A10)"'Сохранение строки в переменной'и присвоение свойству "Формула"strFormula = "= СУММ (A1: A10)"cell.Formula = strFormula'Использование переменных для построения строки'и присвоив его свойству "Формула"fromRow = 1toRow = 10strFormula = "= СУММ (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaКонец подписки