Acest tutorial vă va învăța cum să creați formule de celule folosind VBA.
Formule în VBA
Folosind VBA, puteți scrie formule direct în intervale sau celule în Excel. Arată așa:
| 123456789 | Sub Formula_Example ()„Atribuiți o formulă codificată dur unei singure celuleGama ("b3"). Formula = "= b1 + b2"„Atribuiți o formulă flexibilă unei game de celuleInterval ("d1: d100"). FormulaR1C1 = "= RC2 + RC3"Sfârșitul Sub |
Există două proprietăți ale gamei pe care va trebui să le cunoașteți:
- .Formulă - Creează o formulă exactă (referințe de celule codificate cu duritate). Bun pentru adăugarea unei formule la o singură celulă.
- .FormulaR1C1 - Creează o formulă flexibilă. Bine pentru adăugarea de formule la o gamă de celule în care referințele la celule ar trebui să se schimbe.
Pentru formule simple, este bine să utilizați proprietatea .Formulă. Cu toate acestea, pentru orice altceva, vă recomandăm să utilizați Macro Recorder…
Înregistrare macro și formule de celule
Macro Recorder este instrumentul nostru de bază pentru scrierea formulelor de celule cu VBA. Puteți pur și simplu:
- Începeți înregistrarea
- Tastați formula (cu referințe relative / absolute, după cum este necesar) în celulă și apăsați Enter
- Opriți înregistrarea
- Deschideți VBA și revizuiți formula, adaptându-vă după cum este necesar și copiați + lipiți codul acolo unde este necesar.
Găsesc că este mult mai ușor pentru a introduce o formulă într-o celulă decât pentru a tasta formula corespunzătoare în VBA.
Observați câteva lucruri:
- Macro Recorder va folosi întotdeauna proprietatea .FormulaR1C1
- Recorderul macro recunoaște referințele absolute și cele referitoare la celule
Proprietatea VBA FormulaR1C1
Proprietatea FormulaR1C1 folosește referințarea celulei în stil R1C1 (spre deosebire de stilul A1 standard pe care sunteți obișnuit să îl vedeți în Excel).
Aici sunt cateva exemple:
| 12345678910111213141516171819 | Sub FormulaR1C1_Examples ()„Referință D5 (absolută)'= $ D $ 5Interval ("a1"). FormulaR1C1 = "= R5C4"„Referință D5 (relativă) din celula A1'= D5Gama ("a1"). FormulaR1C1 = "= R [4] C [3]"„Referință D5 (rând absolut, coloană relativă) din celula A1'= D $ 5Gama ("a1"). FormulaR1C1 = "= R5C [3]"„Referință D5 (rândul relativ, coloana absolută) din celula A1'= $ D5Interval ("a1"). FormulaR1C1 = "= R [4] C4"Sfârșitul Sub |
Observați că referințarea celulei în stil R1C1 vă permite să setați referințe absolute sau relative.
Referințe absolute
În notația standard A1, o referință absolută arată astfel: „= $ C $ 2”. În notația R1C1 arată astfel: „= R2C3”.
Pentru a crea o referință de celulă absolută utilizând tipul R1C1:
- R + numărul rândului
- C + numărul coloanei
Exemplu: R2C3 ar reprezenta celula $ C $ 2 (C este a treia coloană).
| 123 | „Referință D5 (absolută)'= $ D $ 5Interval ("a1"). FormulaR1C1 = "= R5C4" |
Referințe relative
Referințele de celule relative sunt referințe de celule care se „mută” atunci când formula este mutată.
În notația standard A1, acestea arată astfel: „= C2”. În notația R1C1, utilizați parantezele [] pentru a compensa referința celulei de celula curentă.
Exemplu: Introducerea formulei „= R [1] C [1]” în celula B3 va face referire la celula D4 (celula 1 rând de mai jos și 1 coloană la dreapta celulei cu formulă).
Utilizați numere negative pentru a face referință la celulele de deasupra sau la stânga celulei curente.
| 123 | „Referință D5 (relativă) din celula A1'= D5Interval ("a1"). FormulaR1C1 = "= R [4] C [3]" |
Referințe mixte
Referințele celulare pot fi parțial relative și parțial absolute. Exemplu:
| 123 | „Referință D5 (rândul relativ, coloana absolută) din celula A1'= $ D5Interval ("a1"). FormulaR1C1 = "= R [4] C4" |
Proprietatea VBA Formula
Când setați formule cu.Proprietate Formula veți folosi întotdeauna notația în stil A1. Introduceți formula exact așa cum ați face într-o celulă Excel, cu excepția înconjurat de ghilimele:
| 12 | „Atribuiți o formulă codificată dur unei singure celuleGama ("b3"). Formula = "= b1 + b2" |
Sfaturi pentru formula VBA
Formula cu variabilă
Când lucrați cu formule în VBA, este foarte frecvent să doriți să utilizați variabile în formulele de celule. Pentru a utiliza variabile, utilizați & pentru a combina variabilele cu restul șirului de formule. Exemplu:
| 1234567 | Sub Formula_Variable ()Dim colNum As LongcolNum = 4Gama ("a1"). FormulaR1C1 = "= R1C" & colNum & "+ R2C" & colNumSfârșitul Sub |
Cotații de formulă
Dacă trebuie să adăugați o cotație („) într-o formulă, introduceți cotația de două ori („ ”):
| 123 | Sub Macro2 ()Interval ("B3"). FormulaR1C1 = "= TEXT (RC [-1]," "mm / zz / aaaa" ")"Sfârșitul Sub |
O singură citată („) înseamnă pentru VBA sfârșitul unui șir de text. În timp ce o citată dublă („”) este tratată ca o citată în șirul de text.
În mod similar, utilizați 3 ghilimele („” ”) pentru a înconjura un șir cu ghilimele („)
| 12 | MsgBox "" "Folosiți 3 pentru a înconjura un șir cu ghilimele" ""'Aceasta va imprima fereastra imediată |
Atribuiți formula celulei la variabila șir
Putem citi formula într-o anumită celulă sau interval și o putem atribui unei variabile șir:
| 123 | „Atribuiți formula celulei la variabilăDim strFormula as StringstrFormula = Range ("B1"). Formula |
Moduri diferite de a adăuga formule la o celulă
Iată câteva exemple suplimentare despre cum să atribuiți o formulă unei celule:
- Alocați direct formula
- Definiți o variabilă de șir care conține formula
- Utilizați variabile pentru a crea formula
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()„Modalități alternative de a adăuga formula SUM'la celula B1'Dim strFormula as StringReduceți celula ca Rangeestompează de la rând ca gamă, până la rând ca gamăSetați celula = Interval ("B1")„Alocarea directă a unui șircell.Formula = "= SUM (A1: A10)"„Stocarea șirului la o variabilă'și atribuirea proprietății „Formula”strFormula = "= SUM (A1: A10)"cell.Formula = strFormula„Folosind variabile pentru a construi un șir'și atribuirea acestuia la proprietatea „Formula”fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaSfârșitul Sub |
Actualizați formulele
Ca memento, pentru a reîmprospăta formulele, puteți utiliza comanda Calculate:
| 1 | calculati |
Pentru a reîmprospăta o singură formulă, interval sau întreaga foaie de lucru utilizați. Calculați în schimb:
| 1 | Foi ("Sheet1"). Range ("a1: a10"). Calculați |
