Tämä opetusohjelma opettaa sinulle, miten luoda solun kaavoja käyttämällä VBA.
kaavat VBA: ssa
käyttämällä VBA: ta voit kirjoittaa kaavat suoraan Vaihteluväleihin tai soluihin Excelissä. Se näyttää tältä:
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
on kaksi alueen ominaisuutta, jotka sinun tulee tietää:
- .Kaava-luo tarkka kaava (kova koodattu solu viittauksia). Hyvä lisäämällä kaava yhteen soluun.
- .Formula1c1-luo joustavan kaavan. Hyvä lisäämällä kaavoja erilaisia soluja, joissa soluviitteet pitäisi muuttua.
yksinkertaisiin kaavoihin on hyvä käyttää.Kaavaomaisuus. Kaikessa muussa suosittelemme kuitenkin käyttämään Makrotallenninta …
Makrotallenninta ja Solukaavoja
Makrotallennin on go-työkalumme solukaavojen kirjoittamiseen VBA: lla. Voit yksinkertaisesti:
- Aloita tallennus
- kirjoita kaava (tarvittaessa suhteellisilla/absoluuttisilla viittauksilla) soluun & paina enter
- Lopeta tallennus
- avaa VBA ja tarkista kaava, mukauttamalla tarpeen mukaan ja kopioimalla+liittämällä koodi tarvittaessa.
minusta on paljon helpompaa syöttää kaava soluun kuin kirjoittaa vastaava kaava VBA: han.
huomaa pari asiaa:
- Makrotallennin käyttää ainaFormulaR1C1-ominaisuus
- makron tallennin tunnistaa absoluuttiset vs. suhteelliset soluviitteet
VBA FormulaR1C1-ominaisuus
FormulaR1C1-ominaisuus käyttää R1C1-tyylistä soluviittausta (erotuksena tavallisesta A1-tyylistä, johon olet tottunut Excelissä).
tässä muutamia esimerkkejä:
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
huomaa, että R1C1-tyylinen soluviittaus mahdollistaa absoluuttisten tai suhteellisten viittausten asettamisen.
absoluuttiset viitteet
standardissa A1 notaatiossa absoluuttinen viittaus näyttää tältä: ”=$C$2″. R1C1-notaatiossa se näyttää tältä:”=R2C3”.
absoluuttisen soluviitteen luominen käyttäen R1C1-tyylistä tyyppiä:
- R + rivinumero
- C + Sarakenumero
esimerkki: R2C3 edustaisi solua $C$2 (C on 3.sarake).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
suhteelliset viitteet
suhteelliset soluviitteet ovat soluviitteitä, jotka ”liikkuvat”, kun kaavaa siirretään.
tavallisessa A1-notaatiossa ne näyttävät tältä: ”=C2”. In R1C1 notaatio, käytät suluissa offset solun viittaus nykyisestä solusta.
esimerkki: Syöttämällä formula_3 soluun formula_3 viitattaisiin soluun D4 (solu 1 rivi alla ja 1 sarake kaavan solun oikealla puolella).
käytä negatiivisia lukuja viitesoluihin nykyisen solun ylä-tai vasemmalla puolella.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
Sekaviitteet
soluviitteet voivat olla osittain suhteellisia ja osittain absoluuttisia. Esimerkki:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
VBA: n Kaavaomaisuus
asetettaessa kaavojaKaava ominaisuus käytät aina A1-tyylinen merkintä. Syötät kaavan aivan kuten olisit Excel-solussa, paitsi lainausten ympäröimänä:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA: n Kaavavihjeet
muuttujan kaava
kun työskentelet VBA: n kaavojen kanssa, on hyvin yleistä haluta käyttää muuttujia solun kaavojen sisällä. Muuttujien käyttämiseksi käytetään & muuttujien yhdistämiseksi muun kaavan merkkijonon kanssa. Esimerkki:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Kaavalainaukset
Jos sinun on lisättävä lainaus (”) kaavan sisällä, anna lainaus kahdesti (””):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
yksi lainaus (”) merkitsee VBA: lle merkkijonon loppua. Kun taas kaksinkertainen lainaus ( ”” ) käsitellään kuin lainaus sisällä merkkijono tekstiä.
vastaavasti, käytä 3 lainausmerkkiä ( ””) merkkijonon ympäröimiseen lainausmerkillä ( ” )
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Määritä Merkkijonomuuttujalle
voimme lukea kaavan tietyssä solussa tai alueella ja antaa sen muuttujalle:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
erilaisia tapoja lisätä kaavoja soluun
tässä on muutama muu esimerkki siitä, miten solulle voidaan antaa kaava:
- suoraan liittää kaava
- Määrittele merkkijonomuuttuja, joka sisältää kaavan
- käytä muuttujat, joilla luodaan kaava
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
Päivitä kaavat
muistutuksena, kaavojen päivittämiseksi voit käyttää laskentakomentoa:
Calculate
yhden kaavan, alueen tai koko laskentataulukon käytön päivittämiseksi .Laske sen sijaan:
Sheets("Sheet1").Range("a1:a10").Calculate