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 din Excel. Arată astfel:
Sub Formula_Example() 'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2" 'Assign a flexible formula to a range of cells Range("d1:d100").FormulaR1C1 = "=RC2+RC3"End Sub
există două proprietăți De gamă pe care va trebui să le cunoașteți:
- .Formula-creează o formulă exactă (referințe de celule greu codificate). Bun pentru adăugarea unei formule la o singură celulă.
- .FormulaR1C1-creează o formulă flexibilă. Bun pentru adăugarea de formule la o serie de celule în care referințele celulare ar trebui să se schimbe.
pentru formule simple, este bine să utilizați .Formula De Proprietate. Cu toate acestea, pentru orice altceva, vă recomandăm să utilizați Macro Recorder…
Macro Recorder și formule de celule
Macro Recorder este instrumentul nostru de scriere a 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 celula& apăsați enter
- Opriți înregistrarea
- deschideți vba și examinați formula, adaptându-vă după cum este necesar și copiind+lipind codul acolo unde este necesar.
mi se pare că este mult mai ușor să introduceți o formulă într-o celulă decât să tastați formula corespunzătoare în VBA.
observați câteva lucruri:
- Macro Recorder va folosi întotdeauna .Proprietatea FormulaR1C1
- Macro recorderul recunoaște referințele celulare Absolute vs.Relative
VBA proprietatea FormulaR1C1
proprietatea FormulaR1C1 folosește referențierea celulelor în stil R1C1 (spre deosebire de stilul standard A1 pe care sunteți obișnuiți să îl vedeți în Excel).
iată câteva exemple:
Sub FormulaR1C1_Examples() 'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4" 'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC" 'Reference D5 (Absolute Row, Relative Column) from cell A1 '=D$5 Range("a1").FormulaR1C1 = "=R5C" 'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"End Sub
observați că referențierea 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ță absolută a celulei folosind tipul de stil R1C1:
- R + Număr rând
- C + Număr coloană
exemplu: R2C3 ar reprezenta celula $C$2 (C este coloana a 3-a).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
referințe Relative
referințe relative de celule sunt referințe de celule care „se mișcă” atunci când formula este mutată.
în notația standard A1 arată astfel: „=C2”. În notația R1C1, utilizați paranteze pentru a compensa referința celulei din celula curentă.
exemplu: Introducerea formulei „= RC ” în celula B3 ar face referire la celula D4 (celula 1 rând de mai jos și 1 coloană din dreapta celulei formulei).
utilizați numere negative pentru a face referire la celulele de deasupra sau din stânga celulei curente.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
referințe mixte
referințele celulare pot fi parțial relative și parțial absolute. Exemplu:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
VBA Formula proprietate
la stabilirea formule cu .Formula proprietate veți folosi întotdeauna notație în stil A1. Introduceți formula la fel ca într-o celulă Excel, cu excepția înconjurat de citate:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA Formula sfaturi
Formula cu variabila
atunci când se lucrează cu formule în VBA, este foarte comun 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:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Formula Citate
dacă trebuie să adăugați un citat („) într-o formulă, introduceți cotația de două ori („”):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
un singur citat („) semnifică la VBA sfârșitul unui șir de text. În timp ce un citat dublu („”) este tratat ca un citat în șirul de text.
în mod similar, utilizați 3 ghilimele („””) pentru a înconjura un șir cu un ghilimele („)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
atribuiți Formula celulei variabilei String
putem citi formula într-o celulă sau interval dat și o atribuim unei variabile:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
diferite moduri de a adăuga formule într-o celulă
iată câteva exemple pentru cum să atribuiți o formulă unei celule:
- atribuiți direct Formula
- definiți o variabilă de șir care conține Formula
- variabile pentru a crea formula
Sub MoreFormulaExamples ()' Alternate ways to add SUM formula' to cell B1' Dim strFormula as String Dim cell as Range dim fromRow as Range, toRow as Range Set cell = Range("B1") ' Directly assigning a String cell.Formula = "=SUM(A1:A10)" ' Storing string to a variable ' and assigning to "Formula" property strFormula = "=SUM(A1:A10)" cell.Formula = strFormula ' Using variables to build a string ' and assigning it to "Formula" property fromRow = 1 toRow = 10 strFormula = "=SUM(A" & fromValue & ":A" & toValue & ") cell.Formula = strFormulaEnd Sub
refresh formule
ca un memento, pentru a reîmprospăta formule, puteți utiliza comanda calcula:
Calculate
pentru a reîmprospăta formula unică, gama, sau întreaga utilizare foaie de lucru .Calculați în schimb:
Sheets("Sheet1").Range("a1:a10").Calculate