Ši pamoka išmokys jus kurti langelių formules naudojant VBA.
Formulės VBA
Naudodami VBA, galite rašyti formules tiesiai į „Excel“ diapazonus arba langelius. Tai atrodo taip:
| 123456789 | Antrinė formulė_pavyzdys ()„Priskirkite koduotą formulę vienai ląsteleiDiapazonas ("b3"). Formulė = "= b1+b2"„Priskirkite lanksčią formulę langelių diapazonuiDiapazonas ("d1: d100"). FormulėR1C1 = "= RC2+RC3"Pabaiga Sub |
Turite žinoti dvi diapazono savybes:
- .Formula - Sukuria tikslią formulę (užkoduotos ląstelių nuorodos). Gera pridėti formulę prie vienos ląstelės.
- .FormulaR1C1 - Sukuria lanksčią formulę. Tinka pridėti formules į langelių diapazoną, kur turėtų pasikeisti langelių nuorodos.
Paprastoms formulėms gerai naudoti .Formula ypatybę. Tačiau viskam kitam rekomenduojame naudoti Makro įrašymo įrenginys…
Makro įrašymo įrenginys ir ląstelių formulės
„Macro Recorder“ yra mūsų įrankis, skirtas rašyti ląstelių formules naudojant VBA. Galite tiesiog:
- Pradėkite įrašymą
- Į langelį įveskite formulę (su santykinėmis / absoliučiomis nuorodomis) ir paspauskite „Enter“
- Sustabdyti įrašymą
- Atidarykite VBA ir peržiūrėkite formulę, pritaikykite pagal poreikį ir nukopijuokite+įklijuokite kodą ten, kur reikia.
Manau, kad yra daug lengviau įvesti formulę į langelį, nei įvesti atitinkamą formulę VBA.
Atkreipkite dėmesį į porą dalykų:
- Makro įrašymo įrenginys visada naudos savybę .FormulaR1C1
- Makro įrašymo įrenginys atpažįsta absoliučias ir santykines ląstelių nuorodas
VBA „FormulaR1C1“ nuosavybė
„FormulaR1C1“ ypatybė naudoja R1C1 stiliaus langelių nuorodas (priešingai nei standartinis A1 stilius, kurį esate įpratę matyti „Excel“).
Štai keletas pavyzdžių:
| 12345678910111213141516171819 | Sub formulėR1C1_Examples ()„Nuoroda D5 (absoliuti)'= 5 USDDiapazonas ("a1"). FormulėR1C1 = "= R5C4"„Nuoroda D5 (santykinė) iš langelio A1'= D5Diapazonas ("a1"). FormulėR1C1 = "= R [4] C [3]"„Nuoroda D5 (absoliuti eilutė, santykinis stulpelis) iš langelio A1'= 5 USDDiapazonas ("a1"). FormulėR1C1 = "= R5C [3]"„Nuoroda D5 (santykinė eilutė, absoliuti stulpelis) iš langelio A1'= D5 USDDiapazonas ("a1"). FormulėR1C1 = "= R [4] C4"Pabaiga Sub |
Atkreipkite dėmesį, kad R1C1 stiliaus langelių nuoroda leidžia nustatyti absoliučias arba santykines nuorodas.
Absoliučios nuorodos
Standartinėje A1 žymėjime absoliuti nuoroda atrodo taip: „= $ C $ 2“. R1C1 žymėjime jis atrodo taip: „= R2C3“.
Norėdami sukurti absoliučios ląstelės nuorodą naudodami R1C1 stiliaus tipą:
- R + eilutės numeris
- C + stulpelio numeris
Pavyzdys: R2C3 būtų ląstelė $ C $ 2 (C yra 3 stulpelis).
| 123 | „Nuoroda D5 (absoliuti)'= 5 USDDiapazonas ("a1"). FormulėR1C1 = "= R5C4" |
Santykinės nuorodos
Santykinės langelių nuorodos yra langelių nuorodos, kurios „juda“ perkėlus formulę.
Standartiniame A1 žymėjime jie atrodo taip: „= C2“. Naudodami žymėjimą R1C1, skliausteliuose [] pakeiskite langelio nuorodą nuo dabartinio langelio.
Pavyzdys: įvedus formulę „= R [1] C [1]“ B3 langelyje būtų nuoroda į langelį D4 (1 langelis žemiau ir 1 stulpelis dešinėje nuo formulės langelio).
Naudokite neigiamus skaičius, kad nurodytumėte langelius, esančius virš arba į kairę nuo dabartinio langelio.
| 123 | „Nuoroda D5 (santykinė) iš langelio A1'= D5Diapazonas ("a1"). FormulėR1C1 = "= R [4] C [3]" |
Mišrios nuorodos
Ląstelių nuorodos gali būti iš dalies santykinės ir iš dalies absoliučios. Pavyzdys:
| 123 | „Nuoroda D5 (santykinė eilutė, absoliuti stulpelis) iš langelio A1'= D5 USDDiapazonas ("a1"). FormulėR1C1 = "= R [4] C4" |
„VBA Formula Property“
Nustatydami formules naudodami.Formulės nuosavybė visada naudosite A1 stiliaus žymėjimą. Formulę įvedate taip pat, kaip ir „Excel“ langelyje, išskyrus apsuptas citatų:
| 12 | „Priskirkite koduotą formulę vienai ląsteleiDiapazonas ("b3"). Formulė = "= b1+b2" |
VBA formulės patarimai
Formulė su kintamuoju
Dirbant su formulėmis VBA, labai dažnai norima naudoti kintamuosius langelių formulėse. Norėdami naudoti kintamuosius, naudokite &, kad sujungtumėte kintamuosius su likusia formulės eilute. Pavyzdys:
| 1234567 | Sub formulė_variable ()Dim colNum As LongcolNum = 4Diapazonas ("a1"). FormulėR1C1 = "= R1C" & colNum & "+R2C" & colNumPabaiga Sub |
Formulės citatos
Jei prie formulės reikia pridėti citatą („), įveskite citatą du kartus („ “):
| 123 | Antroji makrokomanda ()Diapazonas ("B3"). FormulėR1C1 = "= TEKSTAS (RC [-1]," "mm/dd/yyyy" ")"Pabaiga Sub |
Viena citata („) VBA reiškia teksto eilutės pabaigą. Dviguba citata („“) yra laikoma citata teksto eilutėje.
Panašiai naudokite 3 kabutes („“ “), kad eilutė būtų supa kabutėje („)
| 12 | MsgBox "" "Naudokite 3, kad apsuptumėte eilutę kabutėmis" ""'Tai atspausdins tiesioginį langą |
Priskirkite langelio formulę eilutės kintamajam
Mes galime perskaityti formulę tam tikrame langelyje ar diapazone ir priskirti ją eilutės kintamajam:
| 123 | „Priskirti langelio formulę kintamajamDim strFormula kaip eilutėstrFormula = Diapazonas ("B1"). Formulė |
Skirtingi būdai pridėti formulę prie langelio
Štai dar keli pavyzdžiai, kaip langeliui priskirti formulę:
- Tiesiogiai priskirti formulę
- Apibrėžkite eilutės kintamąjį, kuriame yra formulė
- Naudokite kintamuosius kurdami formulę
| 12345678910111213141516171819202122232425 | Sub MoreFormulaEemples ()„Alternatyvūs SUM formulės pridėjimo būdai“į ląstelę B1'Dim strFormula kaip eilutėDim dimeris kaip diapazonaspritemdyti nuo eilutės kaip diapazonas, iki eilutės kaip diapazonasNustatyti langelį = diapazonas ("B1")„Tiesioginis eilutės priskyrimascell.Formula = "= SUM (A1: A10)"„Eilutės išsaugojimas kintamajame“ir priskiriant„ Formulės “nuosavybeistrFormula = "= SUMA (A1: A10)"ląstelė.Formula = strFormula„Kintamųjų naudojimas eilutei kurti“ir priskiriant jį„ Formulės “nuosavybeifromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")ląstelė.Formula = strFormulaPabaiga Sub |
Atnaujinti formules
Primename, kad norėdami atnaujinti formules, galite naudoti komandą Apskaičiuoti:
| 1 | Apskaičiuoti |
Norėdami atnaujinti vieną formulę, diapazoną ar visą darbalapį, naudokite. Vietoj to apskaičiuokite:
| 1 | Skaičiuoklės („Sheet1“). Diapazonas („a1: a10“). Apskaičiuokite |
