Този урок ще ви научи как да създавате клетъчни формули с помощта на VBA.
Формули във VBA
Използвайки VBA, можете да пишете формули директно в диапазони или клетки в Excel. Изглежда така:
| 123456789 | Подформула_Пример ()„Задайте твърдо кодирана формула на една клеткаОбхват ("b3"). Формула = "= b1+b2"„Задайте гъвкава формула на диапазон от клеткиОбхват ("d1: d100"). Формула R1C1 = "= RC2+RC3"End Sub |
Има две свойства на Range, които трябва да знаете:
- .Формула - Създава точна формула (твърдо кодирани препратки към клетки). Подходящ за добавяне на формула към една клетка.
- . Формула 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 е третата колона).
| 123 | „Справка D5 (абсолютно)'= $ D $ 5Обхват ("a1"). Формула R1C1 = "= R5C4" |
Относителни препратки
Относителните препратки към клетки са препратки към клетки, които се „преместват“, когато формулата се премести.
В стандартната нотация A1 те изглеждат така: „= C2“. В нотация R1C1 използвате скоби [], за да изместите препратката към клетката от текущата клетка.
Пример: Въвеждането на формула „= R [1] C [1]” в клетка В3 ще се позовава на клетка D4 (клетката 1 ред по -долу и 1 колона вдясно от клетката с формула).
Използвайте отрицателни числа за справка на клетки над или вляво от текущата клетка.
| 123 | „Справка D5 (относителна) от клетка A1'= 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]," "mm/dd/yyyy" ")"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"). Изчислете |
