Ovaj vodič će vas naučiti kako stvoriti ćelijske formule pomoću VBA.
Formule u VBA
Pomoću VBA možete pisati formule izravno u raspone ili ćelije u Excelu. Izgleda ovako:
| 123456789 | Pod Formula_Primjer ()'Dodijelite tvrdo kodiranu formulu jednoj ćelijiRaspon ("b3"). Formula = "= b1+b2"'Dodijelite fleksibilnu formulu nizu ćelijaRaspon ("d1: d100"). Formula R1C1 = "= RC2+RC3"Kraj podm |
Postoje dva svojstva raspona koja morate znati:
- .Formula - Stvara točnu formulu (reference kodiranih ćelija). Dobro za dodavanje formule u jednu ćeliju.
- .FormulaR1C1 - Stvara fleksibilnu formulu. Dobro za dodavanje formula u niz ćelija u kojima bi se reference ćelija trebale promijeniti.
Za jednostavne formule dobro je koristiti svojstvo .Formula. Međutim, za sve ostalo preporučujemo korištenje Makro snimač…
Makro snimač i ćelijske formule
Makro rekorder je naš alat za pisanje ćelijskih formula s VBA. Možete jednostavno:
- Započni snimanje
- Upišite formulu (s relativnim / apsolutnim referencama prema potrebi) u ćeliju i pritisnite enter
- Zaustavi snimanje
- Otvorite VBA i pregledajte formulu, prilagodite se prema potrebi i kopirajte+zalijepite kôd gdje je potrebno.
Smatram da je puno lakše za unos formule u ćeliju nego upisivanje odgovarajuće formule u VBA.
Obratite pažnju na nekoliko stvari:
- Makro snimač uvijek će koristiti svojstvo .FormulaR1C1
- Makro snimač prepoznaje apsolutne i relativne ćelijske reference
VBA FormulaR1C1 Svojstvo
Svojstvo FormulaR1C1 koristi referenciranje ćelija u stilu R1C1 (za razliku od standardnog stila A1 koji ste navikli vidjeti u Excelu).
Evo nekoliko primjera:
| 12345678910111213141516171819 | Pod formula R1C1_Primjeri ()'Referenca D5 (apsolutno)'= $ D $ 5Raspon ("a1"). Formula R1C1 = "= R5C4"'Referenca D5 (Relativno) iz ćelije A1'= D5Raspon ("a1"). FormulaR1C1 = "= R [4] C [3]"'Referenca D5 (apsolutni red, relativni stupac) iz ćelije A1'= 5 USDRaspon ("a1"). Formula R1C1 = "= R5C [3]"'Referenca D5 (relativni red, apsolutni stupac) iz ćelije A1'= D5 USDRaspon ("a1"). Formula R1C1 = "= R [4] C4"Kraj podm |
Uočite da referenciranje ćelija u stilu R1C1 omogućuje postavljanje apsolutnih ili relativnih referenci.
Apsolutne reference
U standardnom zapisu A1 apsolutna referenca izgleda ovako: “= $ C $ 2”. U zapisu R1C1 to izgleda ovako: “= R2C3”.
Da biste stvorili apsolutnu referencu ćelije koristeći tip u stilu R1C1:
- R + Broj retka
- C + Broj stupca
Primjer: R2C3 predstavlja ćeliju $ C $ 2 (C je 3. stupac).
| 123 | 'Referenca D5 (apsolutno)'= $ D $ 5Raspon ("a1"). Formula R1C1 = "= R5C4" |
Relativne reference
Relativne reference ćelija su reference ćelija koje se "pomiču" kada se formula premjesti.
U standardnom zapisu A1 izgledaju ovako: “= C2”. U zapisu R1C1, koristite zagrade [] da biste pomaknuli referencu ćelije od trenutne ćelije.
Primjer: Unošenje formule „= R [1] C [1]” u ćeliju B3 upućivalo bi na ćeliju D4 (ćelija 1 reda ispod i 1 stupac desno od ćelije formule).
Upotrijebite negativne brojeve za pozivanje ćelija iznad ili lijevo od trenutne ćelije.
| 123 | 'Referenca D5 (Relativno) iz ćelije A1'= D5Raspon ("a1"). Formula R1C1 = "= R [4] C [3]" |
Mješovite reference
Referencije ćelija mogu biti djelomično relativne, a djelomično apsolutne. Primjer:
| 123 | 'Referenca D5 (relativni red, apsolutni stupac) iz ćelije A1'= D5 USDRaspon ("a1"). Formula R1C1 = "= R [4] C4" |
VBA svojstvo formule
Prilikom postavljanja formula s.Svojstvo formule uvijek ćete koristiti zapis u stilu A1. Formulu unosite isto kao što biste unijeli u Excel ćeliju, osim okruženi navodnicima:
| 12 | 'Dodijelite tvrdo kodiranu formulu jednoj ćelijiRaspon ("b3"). Formula = "= b1+b2" |
Savjeti za formulu VBA
Formula s varijablom
Kada radite s formulama u VBA -i, vrlo je uobičajeno da želite koristiti varijable unutar formula ćelija. Za korištenje varijabli koristite & za kombiniranje varijabli s ostatkom niza formule. Primjer:
| 1234567 | Pod Formula_Variable ()Dim colNum As LongcolNum = 4Raspon ("a1"). Formula R1C1 = "= R1C" & colNum & "+R2C" & colNumKraj podm |
Citati formule
Ako trebate dodati citat (“) unutar formule, unesite citat dva puta (“ ”):
| 123 | Podmakro2 ()Raspon ("B3"). FormulaR1C1 = "= TEKST (RC [-1]," "mm/dd/gggg" ")"Kraj podm |
Jedan navod (“) označava VBA kraj niza teksta. Dok se dvostruki navod (“”) tretira kao citat unutar niza teksta.
Slično, upotrijebite 3 navodnika (“” ”) da okružite niz navodnikom (“)
| 12 | MsgBox "" "Upotrijebite 3 da okružite niz navodnicima" ""'Ovo će ispisati trenutni prozor |
Dodijelite formulu ćelije varijabli niza
Formulu možemo pročitati u određenoj ćeliji ili rasponu i dodijeliti je varijabli niza:
| 123 | 'Dodijelite ćelijsku formulu varijabliDim strFormula kao StringstrFormula = Raspon ("B1"). Formula |
Različiti načini dodavanja formula u ćeliju
Evo još nekoliko primjera kako dodijeliti formulu ćeliji:
- Izravno dodijelite formulu
- Definirajte varijablu niza koja sadrži formulu
- Upotrijebite varijable za stvaranje formule
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()'Alternativni načini dodavanja formule SUM'u ćeliju B1'Dim strFormula kao StringZatamnjena ćelija kao raspondim fromRow kao raspon, doRow kao rasponPostavi ćeliju = raspon ("B1")'Izravno dodjeljivanje nizaćelija.Formula = "= ZBIR (A1: A10)"'Spremanje niza u varijablu'i dodjeljivanje svojstvu "Formula"strFormula = "= ZBIR (A1: A10)"ćelija.Formula = strFormula'Korištenje varijabli za izgradnju niza'i dodjeljivanje svojstvu "Formula"fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")ćelija.Formula = strFormulaKraj podm |
Osvježi formule
Podsjećamo, za osvježavanje formula možete koristiti naredbu Izračunaj:
| 1 | Izračunati |
Za osvježavanje jedne formule, raspona ili cijelog radnog lista upotrijebite .Izračunajte umjesto toga:
| 1 | Listovi ("List1"). Raspon ("a1: a10"). Izračunajte |
