Dometi i ćelije u VBA
Excel proračunske tablice pohranjuju podatke u ćelije. Ćelije su raspoređene u redove i stupce. Svaka ćelija može se identificirati po sjecištu njezina retka i stupca (primjerice B3 ili R3C2).
Excel raspon odnosi se na jednu ili više ćelija (npr. A3: B4)
Adresa ćelije
Oznaka A1
U zapisu A1, ćelija se naziva slovom stupca (od A do XFD), a zatim brojem retka (od 1 do 1.048.576).
U VBA se možete pozvati na bilo koju ćeliju koristeći Objekt dometa.
| 123456789 | 'Pogledajte ćeliju B4 na trenutno aktivnom listuMsgBox raspon ("B4")'Pogledajte ćeliju B4 na listu pod nazivom' Podaci 'MsgBox radni listovi ("Podaci"). Raspon ("B4")'Pogledajte ćeliju B4 na listu pod nazivom' Podaci 'u drugoj OTVORENOJ radnoj knjizipod nazivom "Moji podaci"MsgBox radne knjige ("Moji podaci"). Radni listovi ("Podaci"). Raspon ("B4") |
Oznaka R1C1
U zapisu R1C1 ćelija se označava s R nakon čega slijedi broj retka, zatim slovo ‘C’, a zatim broj stupca. npr. B4 u oznaci R1C1 označit će R4C2. U VBA koristite Objekt ćelija koristiti oznaku R1C1:
| 12 | 'Pogledajte ćeliju R [6] C [4], tj. D6Ćelije (6, 4) = "D6" |
Raspon stanica
Oznaka A1
Da biste se pozvali na više ćelija, koristite ":" između adrese početne ćelije i zadnje adrese ćelije. Sljedeće će se odnositi na sve ćelije od A1 do D10:
| 1 | Raspon ("A1: D10") |
Oznaka R1C1
Za pozivanje na više ćelija upotrijebite “,” između adrese početne ćelije i zadnje adrese ćelije. Sljedeće će se odnositi na sve ćelije od A1 do D10:
| 1 | Raspon (ćelije (1, 1), ćelije (10, 4)) |
Pisanje na ćelije
Da biste zapisali vrijednosti u ćeliju ili susjednu skupinu ćelija, jednostavno se obratite rasponu, stavite znak =, a zatim upišite vrijednost za pohranu:
| 12345678910 | 'Spremite F5 u ćeliju s adresom F6Raspon ("F6") = "F6"'Spremite E6 u ćeliju s adresom R [6] C [5], tj. E6Ćelije (6, 5) = "E6"'Pohranite A1: D10 u rasponu A1: D10Raspon ("A1: D10") = "A1: D10"' iliRaspon (ćelije (1, 1), ćelije (10, 4)) = "A1: D10" |
Čitanje iz ćelija
Da biste pročitali vrijednosti iz ćelija, jednostavno pogledajte varijablu za spremanje vrijednosti, stavite znak =, a zatim pogledajte raspon za čitanje:
| 1234567891011 | Dim val1Dim val2'Čitajte iz ćelije F6val1 = Raspon ("F6")'Čitajte iz ćelije E6val2 = Ćelije (6, 5)MsgBox val1Msgbox val2 |
Napomena: Za spremanje vrijednosti iz raspona ćelija morate koristiti Array umjesto jednostavne varijable.
Ne susjedne stanice
Da biste se pozvali na susjedne ćelije, koristite zarez između adresa ćelija:
| 123456 | 'Spremite 10 u ćelije A1, A3 i A5Raspon ("A1, A3, A5") = 10'Spremite 10 u ćelije A1: A3 i D1: D3)Raspon ("A1: A3, D1: D3") = 10 |
Presjek stanica
Za upućivanje na susjedne ćelije koristite razmak između adresa ćelija:
| 123 | 'Spremite' Col D 'u D1: D10'što je uobičajeno između A1: D10 i D1: F10Raspon ("A1: D10 D1: G10") = "Stupac D" |
Odmak od ćelije ili raspona
Pomoću funkcije Odstupanje možete premjestiti referencu iz zadanog raspona (ćelije ili grupe ćelija) za navedeni broj_strelica i broj_ stupaca.
Offset sintaksa
Raspon. Odstupanje (broj_strelica, broj_ stupaca)
Odmak od ćelije
| 12345678910111213141516 | 'OFFSET iz ćelije A1'Pogledajte samu ćeliju'Premjesti 0 redaka i 0 stupacaRaspon ("A1"). Odmak (0, 0) = "A1"'Premjesti 1 redak i 0 stupacaRaspon ("A1"). Odmak (1, 0) = "A2"'Premjesti 0 redaka i 1 stupacRaspon ("A1"). Odmak (0, 1) = "B1"'Premjesti 1 redak i 1 stupacRaspon ("A1"). Odmak (1, 1) = "B2"'Premjestite 10 redaka i 5 stupacaRaspon ("A1"). Odmak (10, 5) = "F11" |
Odmak od raspona
| 123 | 'Pomicanje reference na raspon A1: D4 za 4 retka i 4 stupca'Nova referenca je E5: H8Raspon ("A1: D4"). Odmak (4,4) = "E5: H8" |
Postavljanje reference na raspon
Za dodjeljivanje raspona varijabli raspona: deklarirajte varijablu tipa Range, a zatim pomoću naredbe Set postavite je na raspon. Imajte na umu da morate koristiti naredbu SET jer je RANGE objekt:
| 12345678 | 'Deklarirajte varijablu RangeZatamnite myRange kao raspon'Postavite varijablu na raspon A1: D4Postavi myRange = Raspon ("A1: D4")'Ispisuje $ A $ 1: $ D $ 4MsgBox myRange.Adres |
Promijenite veličinu raspona
Metoda promjene veličine objekta Range mijenja dimenziju referentnog raspona:
| 1234567 | Zatamnite myRange As Range'Raspon za promjenu veličinePostavi myRange = Raspon ("A1: F4")'Ispisuje $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Adress |
Gornja lijeva ćelija promijenjenog raspona ista je kao gornja lijeva ćelija izvornog raspona
Promijeni veličinu sintakse
Raspon.Premjeri (broj_strelica, broj_ stupaca)
OFFSET vs Resize
Odstupanje ne mijenja dimenzije raspona već ga pomiče za navedeni broj redaka i stupaca. Promjena veličine ne mijenja položaj izvornog raspona, ali mijenja dimenzije na navedeni broj redaka i stupaca.
Sve ćelije u listu
Objekt Ćelije odnosi se na sve ćelije na listu (1048576 redaka i 16384 stupaca).
| 12 | 'Očisti sve ćelije na radnim listovimaStanice.Jasno |
UsedRange
Svojstvo UsedRange daje vam pravokutni raspon od gornje lijeve ćelije do korištene ćelije do desne donje korištene ćelije aktivnog lista.
| 1234567 | Zatamni kao radni listPostavite ws = ActiveSheet'$ B $ 2: $ L $ 14 ako je L2 prva ćelija s bilo kojom vrijednošću'i L14 je posljednja ćelija s bilo kojom vrijednošću na'aktivni listDebug.Print ws.UsedRange.Address |
CurrentRegion
Svojstvo CurrentRegion daje vam susjedni pravokutni raspon od gornje lijeve ćelije do desne donje korištene ćelije koja sadrži referenciranu ćeliju/raspon.
| 1234567891011 | Zatamnite myRange As RangePostavi myRange = Raspon ("D4: F6")'Ispisuje $ B $ 2: $ L $ 14'Ako postoji ispunjena staza od D4: F16 do B2 I L14Ispravljanje ispisa myRange.CurrentRegion.Address'Također se možete pozvati na jednu početnu ćelijuSet myRange = Range ("D4") 'Ispisuje $ B $ 2: $ L $ 14 |
Svojstva raspona
Možete dobiti adresu, broj retka/stupca ćelije i broj redaka/stupaca u rasponu kako je dolje navedeno:
| 123456789101112131415161718192021 | Zatamnite myRange As RangePostavi myRange = Raspon ("A1: F10")'Ispisuje $ A $ 1: $ F $ 10Ispravljanje ispisa myRange.AddressPostavi myRange = Raspon ("F10")'Ispisuje 10 za 10. redIspravljanje ispisa myRange.Row'Ispisi 6 za stupac FIspravljanje ispisa myRange.ColumnPostavi myRange = Raspon ("E1: F5")'Ispisuje 5 za broj redova u rasponuIspravljanje ispisa myRange.Rows.Count'Ispisuje 2 za broj stupaca u rasponuIspravljanje ispisa myRange.Columns.Count |
Posljednja ćelija u listu
Možeš koristiti Redovi.Broj i Stupci.Broj nekretnine sa Stanice objekt za dobivanje posljednje ćelije na listu:
| 1234567891011 | 'Ispišite posljednji broj retka'Ispisi 1048576Debug.Print "Reci u listu:" & Rows.Count'Ispišite posljednji broj stupca'Ispisi 16384Debug.Print "Columns in the sheet:" & Columns.Count'Ispišite adresu posljednje ćelije'Ispisuje XFD $ 1048576Debug.Print "Adresa zadnje ćelije u listu:" & Cells (Rows.Count, Columns.Count) |
Zadnji korišteni broj retka u stupcu
Svojstvo END vodi vas do posljednje ćelije u rasponu, a End (xlUp) vas vodi do prve korištene ćelije iz te ćelije.
| 123 | Dim lastRow As LonglastRow = Cells (Rows.Count, "A"). End (xlUp) .Row |
Broj posljednje korištene kolone u redu
| 123 | Dim lastCol As LonglastCol = Cells (1, Columns.Count) .End (xlToLeft) .Column |
Svojstvo END vodi vas do posljednje ćelije u rasponu, a End (xlToLeft) vodi vas lijevo do prve korištene ćelije iz te ćelije.
Također možete koristiti svojstva xlDown i xlToRight za navigaciju do prvih donjih ili desnih korištenih ćelija trenutne ćelije.
Svojstva ćelija
Zajednička svojstva
Ovdje je kôd za prikaz uobičajenih svojstava ćelija
| 12345678910111213141516171819202122 | Prigušena ćelija kao rasponPostavi ćeliju = raspon ("A1")ćelija.AktivirajOtklanjanje pogrešaka. Ispišite ćeliju. Adresa'Ispišite $ A $ 1Otklanjanje pogrešaka. Ispis ćelija.Vrijednost'Ispisi 456' AdresaOtklanjanje pogrešaka. Ispis ćelija.Formula'Ispisi = SUM (C2: C3)'KomentirajOtklanjanje pogrešaka. Ispiši ćeliju.Komentar.Tekst' StilOtklanjanje pogrešaka. Ispis ćelija.Stil'Format ćelijeDebug.Print cell.DisplayFormat.NumberFormat |
Font ćelije
Objekt Cell.Font sadrži svojstva Cell Font:
| 1234567891011121314151617181920 | Prigušena ćelija kao rasponPostavi ćeliju = raspon ("A1")'Običan, kurziv, podebljan i podebljan kurzivcell.Font.FontStyle = "Podebljani kurziv"' Isto kaoćelija.Font.Bold = Tačnocell.Font.Italic = Tačno'Postavite font na Couriercell.Font.FontStyle = "Kurir"'Postavi boju fontaćelija.Font.Boja = vbPlava' ilicell.Font.Color = RGB (255, 0, 0)'Postavite veličinu fontaćelija.Font.Veličina = 20 |
Kopiranje i lijepljenje
Zalijepi sve
Rasponi/ćelije se mogu kopirati i zalijepiti s jednog mjesta na drugo. Sljedeći kod kopira sva svojstva izvornog raspona u odredišni raspon (ekvivalentno CTRL-C i CTRL-V)
| 1234567 | 'Jednostavna kopijaRaspon ("A1: D20"). KopirajRadni listovi ("List2"). Raspon ("B10"). Zalijepi'ili'Kopiraj s trenutnog lista na list pod nazivom' List 2 'Raspon ("A1: D20"). Odredište za kopiranje: = Radni listovi ("List 2"). Raspon ("B10") |
Posebno zalijepi
Odabrana svojstva izvornog raspona mogu se kopirati na odredište pomoću PASTESPECIAL opcije:
| 123 | 'Zalijepite raspon samo kao vrijednostiRaspon ("A1: D20"). KopirajRadni listovi ("Sheet2"). Raspon ("B10"). PasteSpecial Paste: = xlPasteValues |
Evo mogućih opcija za opciju Zalijepi:
| 12345678910111213 | 'Zalijepite posebne vrstexlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
Sadržaj za automatsko prilagođavanje
Veličina redaka i stupaca može se promijeniti prema sadržaju pomoću donjeg koda:
| 12345 | 'Promijenite veličinu redaka 1 do 5 tako da odgovara sadržajuRedci ("1: 5"). Automatsko prilagođavanje'Promijenite veličinu stupaca A u B tako da odgovara sadržajuStupci ("A: B"). Automatsko prilagođavanje |
Više primjera raspona
Preporučuje se da koristite Makro snimač dok izvodite potrebne radnje putem grafičkog sučelja. Pomoći će vam razumjeti različite dostupne opcije i kako ih koristiti.
Za svakoga
Lakše je proći kroz raspon pomoću Za svakoga konstruirati kao što je prikazano ispod:
| 123 | Za svaku ćeliju u rasponu ("A1: B100")»Učini nešto sa ćelijomSljedeća ćelija |
Pri svakoj iteraciji petlje jedna ćelija u rasponu dodjeljuje se varijabli c, a izrazi u petlji For izvode se za tu ćeliju. Petlja izlazi kada se sve ćelije obrade.
Vrsta
Sortiranje je metoda Range objekta. Raspon možete razvrstati tako da navedete mogućnosti sortiranja u Range.Sort. Donji kôd razvrstit će stupce A: C na temelju ključa u ćeliji C2. Poredak razvrstavanja može biti xlAcecending ili xlDescending. Zaglavlje: = xlDa se treba koristiti ako je prvi red zaglavlje.
| 12 | Stupci ("A: C"). Tipka za sortiranje1: = Raspon ("C2"), _order1: = xlAscending, Header: = xlDa |
Pronaći
Find je također metoda Range Object. Pronalazi prvu ćeliju sa sadržajem koji odgovara kriterijima pretraživanja i vraća ćeliju kao objekt raspona. To se vraća Ništa ako nema podudarnosti.
Koristiti FindNext metodom (ili FindPrevious) za pronalaženje sljedeće (prethodne) pojave.
Sljedeći kôd promijenit će font u "Arial Black" za sve ćelije u rasponu koje počinju s "John":
| 12345 | Za svaki c u rasponu ("A1: A100")Ako c Kao "Ivan*" Tadac.Font.Name = "Arial Black"Završi akoSljedeće c |
Sljedeći kôd zamijenit će sve pojave "To Test" u "Passed" u navedenom rasponu:
| 12345678910 | S rasponom ("a1: a500")Postavite c = .Find ("Za testiranje", LookIn: = xlValues)Ako nije c, tada je ništafirstaddress = c.AdresaČinic.Value = "Prošlo"Postavite c = .FindNext (c)Petlja dok nije c Nije ništa I c. Adresa prva adresaZavrši akoZavrši s |
Važno je napomenuti da morate koristiti raspon da biste koristili FindNext. Također morate navesti uvjet zaustavljanja, u protivnom će se petlja izvršavati zauvijek. Obično se adresa prve pronađene ćelije pohranjuje u varijablu i petlja se zaustavlja kada ponovno dođete do te ćelije. Također morate provjeriti postoji li slučaj kada se ne pronađe ništa što bi zaustavilo petlju.
Adresa dometa
Upotrijebite Range.Address da biste dobili adresu u stilu A1
| 123 | MsgBox raspon ("A1: D10"). Adresa' iliRaspon ispravljanja ispisa ("A1: D10"). Adresa |
Upotrijebite xlReferenceStyle (zadano je xlA1) da biste dobili adrese u stilu R1C1
| 123 | MsgBox raspon ("A1: D10"). Adresa (Referentni stil: = xlR1C1)' iliRaspon ispravljanja ispisa ("A1: D10"). Adresa (Referentni stil: = xlR1C1) |
To je korisno kada se bavite rasponima pohranjenima u varijablama i želite obraditi samo za određene adrese.
Domet do polja
Brže je i lakše prenijeti raspon u niz, a zatim obraditi vrijednosti. Trebate deklarirati niz kao Variant kako biste izbjegli izračunavanje veličine potrebne za popunjavanje raspona u nizu. Dimenzije niza postavljene su tako da odgovaraju broju vrijednosti u rasponu.
| 123456789 | Dim DirArray kao varijanta'Spremite vrijednosti u raspon u poljeDirArray = Raspon ("a1: a5"). Vrijednost'Petlja za obradu vrijednostiZa svaki c U DirArrayuOtklanjanje pogrešaka. Ispis cSljedeći |
Niz dometa
Nakon obrade, polje možete zapisati na raspon. Za pisanje niza u gornjem primjeru u raspon morate navesti raspon čija veličina odgovara broju elemenata u nizu.
Upotrijebite donji kod za upisivanje niza u raspon D1: D5:
| 123 | Raspon ("D1: D5"). Vrijednost = DirArrayRaspon ("D1: H1"). Vrijednost = Primjena.Prijenos (DirArray) |
Imajte na umu da morate transponirati niz ako ga upisujete u redak.
Raspon zbroja
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Raspon ("A1: A10"))Ispravljanje ispisa SumOfRange |
Možete koristiti mnoge funkcije dostupne u Excelu u svom VBA kodu tako da navedete Application.WorkSheetFunction. ispred naziva funkcije kao u gornjem primjeru.
Count Range
| 1234567 | 'Broji broj ćelija s brojevima u rasponuCountOfCells = Application.WorksheetFunction.Count (Raspon ("A1: A10"))Otklanjanje pogrešaka. Broj ispisa ćelija'Broji broj ćelija koje nisu prazne u rasponuCountOfNonBlankCells = Application.WorksheetFunction.CountA (Raspon ("A1: A10"))Debug.Print CountOfNonBlankCells |
Napisala: Vinamra Chandra
