Ta vadnica vas bo naučila ustvarjati celične formule z uporabo VBA.
Formule v VBA
Z uporabo VBA lahko formule zapišete neposredno v obsege ali celice v Excelu. Izgleda takole:
| 123456789 | Pod Formula_Primer ()"Dodelite trdo kodirano formulo eni celiciRazpon ("b3"). Formula = "= b1+b2""Dodelite prilagodljivo formulo vrsti celicRazpon ("d1: d100"). Formula R1C1 = "= RC2+RC3"End Sub |
Morate vedeti dve lastnosti Range:
- .Formula - Ustvari natančno formulo (trdno kodirane sklice na celice). Dobro za dodajanje formule v eno celico.
- .FormulaR1C1 - Ustvari prilagodljivo formulo. Dobro za dodajanje formul v vrsto celic, kjer bi se morale sklice na celice spremeniti.
Za preproste formule je dobro uporabiti lastnost .Formula. Za vse ostalo pa priporočamo uporabo Makro snemalnik…
Makro snemalnik in celične formule
Makro snemalnik je naše orodje za pisanje celičnih formul z VBA. Lahko preprosto:
- Začni snemati
- Vnesite formulo (po potrebi z relativnimi / absolutnimi referencami) v celico in pritisnite enter
- Ustavi snemanje
- Odprite VBA in preglejte formulo, po potrebi se prilagodite in kopirajte+prilepite kodo, kjer je to potrebno.
Se mi zdi veliko lažje za vnos formule v celico kot za vnos ustrezne formule v VBA.
Upoštevajte nekaj stvari:
- Makro snemalnik bo vedno uporabljal lastnost .FormulaR1C1
- Makro snemalnik prepozna absolutne in relativne referenčne celice
Lastnost formule VBAR1C1
Lastnost FormulaR1C1 uporablja sklicevanje na celice v slogu R1C1 (v nasprotju s standardnim slogom A1, ki ste ga navajeni videti v Excelu).
Tu je nekaj primerov:
| 12345678910111213141516171819 | Pod formula R1C1_Primeri ()„Referenca D5 (absolutno)'= $ D $ 5Razpon ("a1"). Formula R1C1 = "= R5C4"„Sklic D5 (relativno) iz celice A1'= D5Razpon ("a1"). Formula R1C1 = "= R [4] C [3]"„Sklic D5 (absolutna vrstica, relativni stolpec) iz celice A1'= 5 USDRazpon ("a1"). Formula R1C1 = "= R5C [3]"„Sklic D5 (relativna vrstica, absolutni stolpec) iz celice A1'= D5 USDRazpon ("a1"). Formula R1C1 = "= R [4] C4"End Sub |
Upoštevajte, da sklicevanje na celice v slogu R1C1 omogoča nastavitev absolutnih ali relativnih referenc.
Absolutne reference
V standardnem zapisu A1 je absolutna referenca videti tako: "= $ C $ 2". V zapisu R1C1 izgleda tako: “= R2C3”.
Če želite ustvariti referenco absolutne celice z uporabo tipa R1C1:
- R + Številka vrstice
- C + Številka stolpca
Primer: R2C3 bi predstavljal celico $ C $ 2 (C je tretji stolpec).
| 123 | „Referenca D5 (absolutno)'= $ D $ 5Razpon ("a1"). Formula R1C1 = "= R5C4" |
Relativne reference
Relativne sklice na celice so sklice na celice, ki se "premaknejo", ko se formula premakne.
V standardnem zapisu A1 izgledajo tako: "= C2". V zapisu R1C1 uporabite oklepaje [] za odmik sklica celice od trenutne celice.
Primer: Vnos formule »= R [1] C [1]« v celico B3 bi se skliceval na celico D4 (celica 1 vrstica spodaj in 1 stolpec desno od celice formule).
Za sklicevanje na celice nad ali levo od trenutne celice uporabite negativna števila.
| 123 | „Sklic D5 (relativno) iz celice A1'= D5Razpon ("a1"). Formula R1C1 = "= R [4] C [3]" |
Mešane reference
Sklicevanje na celice je lahko delno relativno in delno absolutno. Primer:
| 123 | „Sklic D5 (relativna vrstica, absolutni stolpec) iz celice A1'= D5 USDRazpon ("a1"). Formula R1C1 = "= R [4] C4" |
Lastnost formule VBA
Pri nastavljanju formul z.Lastnost formule vedno boste uporabili zapis v slogu A1. Formulo vnesete tako, kot v Excelovo celico, razen obdani z navedki:
| 12 | "Dodelite trdo kodirano formulo eni celiciRazpon ("b3"). Formula = "= b1+b2" |
Nasveti za formulo VBA
Formula s spremenljivko
Pri delu s formulami v VBA je zelo pogosto, da želite uporabiti spremenljivke v formulah celic. Če želite uporabiti spremenljivke, uporabite & za kombiniranje spremenljivk z ostalim nizom formule. Primer:
| 1234567 | Pod Formula_Variable ()Dim colNum As LongcolNum = 4Razpon ("a1"). Formula R1C1 = "= R1C" & colNum & "+R2C" & colNumEnd Sub |
Citati formule
Če morate v formulo dodati citat (“), ga dvakrat vnesite (“ ”):
| 123 | Podmakro 2 ()Razpon ("B3"). Formula R1C1 = "= BESEDILO (RC [-1]," "mm/dd/llll" ")"End Sub |
En sam citat (“) pomeni za VBA konec niza besedila. Medtem ko se dvojni narekovaj (“”) obravnava kot narekovaj v nizu besedila.
Podobno uporabite 3 narekovaje (“” ”), da obdate niz z narekovajem (“)
| 12 | MsgBox "" "Uporabite 3, da obdate niz z narekovaji" ""'To bo natisnilo takojšnje okno |
Dodelite celično formulo spremenljivki niza
Formulo lahko preberemo v dani celici ali obsegu in jo dodelimo spremenljivki niza:
| 123 | 'Dodelite celično formulo spremenljivkiZatemni strFormula kot nizstrFormula = Obseg ("B1"). Formula |
Različni načini dodajanja formul v celico
Tu je še nekaj primerov, kako celici dodeliti formulo:
- Neposredno dodeli formulo
- Določite spremenljivko niza, ki vsebuje formulo
- Za ustvarjanje formule uporabite spremenljivke
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()"Alternativni načini za dodajanje formule SUM'v celico B1'Zatemni strFormula kot nizZatemni celico kot obsegdim fromRow kot obseg, doRow kot obsegNastavi celico = obseg ("B1")'Neposredno dodelitev nizacell.Formula = "= SUM (A1: A10)"'Shranjevanje niza v spremenljivko'in dodelitev lastnosti "Formula"strFormula = "= SUM (A1: A10)"celica.Formula = strFormula'Uporaba spremenljivk za izdelavo niza'in jo dodeli lastnosti "Formula"fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")celica.Formula = strFormulaEnd Sub |
Osveži formule
Kot opomnik lahko osvežite formule z ukazom Izračunaj:
| 1 | Izračunaj |
Če želite osvežiti eno formulo, obseg ali celoten delovni list, uporabite. Namesto tega izračunajte:
| 1 | Listi ("List1"). Razpon ("a1: a10"). Izračunajte |
