Цей підручник навчить вас створювати формули клітин за допомогою VBA.
Формули у VBA
Використовуючи VBA, ви можете писати формули безпосередньо в діапазони або комірки в Excel. Виглядає це так:
| 123456789 | Підформула_приклад ()'Призначте формулу з жорстким кодуванням до однієї клітинкиДіапазон ("b3"). Формула = "= b1+b2"'Призначте гнучку формулу діапазону клітинокДіапазон ("d1: d100"). Формула R1C1 = "= RC2+RC3"End Sub |
Вам потрібно знати дві властивості діапазону:
- .Формула - Створює точну формулу (жорстко кодовані посилання на клітинки). Добре для додавання формули до однієї клітинки.
- . Формула R1C1 - Створює гнучку формулу. Добре підходить для додавання формул до діапазону клітинок, де посилання на клітинки мають змінюватися.
Для простих формул добре використовувати властивість .Formula. Однак для всього іншого ми рекомендуємо використовувати Макрореєстратор…
Макрореєстратор та формули комірок
Макрореєстратор-це наш інструмент для написання формул комірок з VBA. Ви можете просто:
- Почніть запис
- Введіть формулу (з відносними / абсолютними посиланнями) у клітинку та натисніть клавішу Enter
- Зупинити запис
- Відкрийте VBA та перегляньте формулу, адаптуючи її за необхідності та копіюючи+вставляючи код, де це необхідно.
Я знаходжу це набагато простіше щоб ввести формулу в клітинку, ніж набрати відповідну формулу у VBA.
Зверніть увагу на пару речей:
- Макрореєстратор завжди буде використовувати властивість .FormulaR1C1
- Макрореєстратор розпізнає абсолютні та відносні посилання на клітинки
Формула VBA R1C1 Властивість
Властивість FormulaR1C1 використовує посилання на клітинки у стилі R1C1 (на відміну від стандартного стилю A1, який ви звикли бачити в Excel).
Ось деякі приклади:
| 12345678910111213141516171819 | Підформула R1C1_Приклади ()'Посилання D5 (абсолютно)'= $ D $ 5Діапазон ("a1"). Формула R1C1 = "= R5C4"'Посилання D5 (відносне) з комірки A1'= D5Діапазон ("a1"). Формула R1C1 = "= R [4] C [3]"'Посилання D5 (абсолютний рядок, відносна колонка) з клітинки А1'= D $ 5Діапазон ("a1"). Формула R1C1 = "= R5C [3]"'Посилання D5 (відносний рядок, абсолютна колонка) з клітинки А1'= $ D5Діапазон ("a1"). Формула R1C1 = "= R [4] C4"End Sub |
Зауважте, що посилання на клітинку у стилі R1C1 дозволяє встановлювати абсолютні або відносні посилання.
Абсолютні посилання
У стандартному позначенні A1 абсолютна посилання виглядає так: “= $ C $ 2”. У позначенні R1C1 це виглядає так: “= R2C3”.
Щоб створити абсолютне посилання на клітинку за допомогою типу R1C1:
- R + Номер рядка
- C + Номер стовпця
Приклад: R2C3 представляє клітинку $ C $ 2 (C - це 3 -й стовпець).
| 123 | 'Посилання D5 (абсолютно)'= $ D $ 5Діапазон ("a1"). Формула R1C1 = "= R5C4" |
Відносні посилання
Відносні посилання на клітинки - це посилання на клітинки, які “переміщуються” під час переміщення формули.
У стандартному позначенні A1 вони виглядають так: “= C2”. У позначенні R1C1 ви використовуєте дужки [] для зміщення посилання на клітинку від поточної комірки.
Приклад: Введення формули “= R [1] C [1]” у клітинку B3 буде посилатися на клітинку D4 (комірка 1 рядок нижче та 1 стовпець праворуч від комірки формули).
Використовуйте від’ємні числа для посилання на клітинки вище чи ліворуч від поточної комірки.
| 123 | 'Посилання D5 (відносне) з комірки А1'= D5Діапазон ("a1"). Формула R1C1 = "= R [4] C [3]" |
Змішана література
Посилання на клітинки можуть бути частково відносними і частково абсолютними. Приклад:
| 123 | 'Посилання D5 (відносний рядок, абсолютна колонка) з клітинки А1'= $ D5Діапазон ("a1"). Формула R1C1 = "= R [4] C4" |
Властивість формули VBA
При встановленні формул за допомогою.Властивість формули Ви завжди будете використовувати позначення у стилі А1. Ви вводите формулу так само, як у клітинку Excel, за винятком оточення лапками:
| 12 | 'Призначте формулу з жорстким кодуванням до однієї клітинкиДіапазон ("b3"). Формула = "= b1+b2" |
Поради щодо формули VBA
Формула зі змінною
Під час роботи з формулами у VBA дуже часто хочеться використовувати змінні у формулах комірок. Щоб використовувати змінні, ви використовуєте & для об'єднання змінних з рештою рядка формули. Приклад:
| 1234567 | Sub Formula_Variable ()Dim colNum As LongcolNum = 4Діапазон ("a1"). Формула R1C1 = "= R1C" & colNum & "+R2C" & colNumEnd Sub |
Цитати формул
Якщо вам потрібно додати цитату (“) у формулу, введіть цитату двічі (“ ”):
| 123 | Додатковий макрос2 ()Діапазон ("B3"). Формула R1C1 = "= ТЕКСТ (RC [-1]," "мм/дд/рррр" ")"End Sub |
Одинарна лапка (“) означає для VBA кінець рядка тексту. Тоді як подвійні лапки (“”) розглядаються як цитати в рядку тексту.
Аналогічно, використовуйте 3 лапки (“” ”), щоб оточити рядок лапкою (“)
| 12 | MsgBox "" "Використовуйте 3, щоб оточити рядок лапками" ""'Це буде друкувати негайне вікно |
Призначити формулу клітинки змінній рядка
Ми можемо прочитати формулу в даній клітинці або діапазоні та призначити її рядковій змінній:
| 123 | 'Призначити змінній формулу клітиниЗатемнити strFormula як StringstrFormula = Діапазон ("B1"). Формула |
Різні способи додавання формул до клітинки
Ось ще кілька прикладів того, як призначити формулу клітині:
- Безпосередньо призначити формулу
- Визначте змінну рядка, що містить формулу
- Використовуйте змінні для створення формули
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()'Альтернативні способи додавання формули SUM'до комірки В1'Затемнити strFormula як StringЗатемнити клітинку як діапазонdim fromRow як Діапазон, toRow як ДіапазонВстановити клітинку = Діапазон ("B1")'Безпосереднє призначення рядкаcell.Formula = "= SUM (A1: A10)"'Збереження рядка до змінної'і присвоєння властивості "Формула"strFormula = "= SUM (A1: A10)"cell.Formula = strFormula'Використання змінних для побудови рядка'та присвоєння його властивості "Формула"fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaEnd Sub |
Оновити формули
Нагадуємо, що для оновлення формул можна скористатися командою Обчислити:
| 1 | Обчисліть |
Щоб оновити окрему формулу, діапазон або весь аркуш, скористайтеся.
| 1 | Аркуші ("Аркуш1"). Діапазон ("a1: a10"). Обчисліть |
