Kada radite s Excelom, većinu svog vremena provodite u području radnog lista - baveći se ćelijama i rasponima.
A ako želite automatizirati svoj rad u Excelu pomoću VBA, morate znati raditi s ćelijama i rasponima pomoću VBA.
Postoji mnogo različitih stvari koje možete učiniti s rasponima u VBA -i (poput odabira, kopiranja, premještanja, uređivanja itd.).
Stoga ću za pokrivanje ove teme podijeliti ovaj vodič na odjeljke i pokazati vam kako raditi s ćelijama i rasponima u Excelu VBA koristeći primjere.
Započnimo.
Svi kodovi koje spominjem u ovom vodiču moraju se staviti u VB Editor. Idite na odjeljak "Gdje staviti VBA kôd" da biste saznali kako to funkcionira.Ako ste zainteresirani za učenje VBA na jednostavan način, provjerite moj Mrežna Excel VBA obuka.
Odabir ćelije / raspona u Excelu pomoću VBA
Za rad s ćelijama i rasponima u Excelu pomoću VBA -e ne morate je odabrati.
U većini slučajeva bolje je da ne odaberete ćelije ili raspone (kao što ćemo vidjeti).
Unatoč tome, važno je proći kroz ovaj odjeljak i razumjeti kako to funkcionira. Ovo će biti ključno u vašem VBA učenju, a puno pojmova koji su ovdje obrađeni koristit će se u ovom vodiču.
Pa počnimo s vrlo jednostavnim primjerom.
Odabir jedne ćelije pomoću VBA
Ako želite odabrati jednu ćeliju na aktivnom listu (recimo A1), tada možete upotrijebiti donji kod:
Podsjetnik Odaberite ćelijski () raspon ("A1"). Odaberite Kraj podv
Gornji kôd ima obvezne dijelove "Sub" i "End Sub" te redak koda koji odabire ćeliju A1.
Raspon (“A1”) govori VBA adresu ćelije na koju se želimo odnositi.
Izaberi je metoda objekta Range i odabire ćelije/raspon navedene u objektu Range. Referencije ćelija moraju biti zatvorene u dvostrukim navodnicima.
Ovaj bi kôd pokazao pogrešku u slučaju da je list grafikona aktivan list. List grafikona sadrži grafikone i nije u širokoj upotrebi. Budući da u sebi nema ćelija/raspona, gornji kôd ga ne može odabrati i na kraju će pokazati pogrešku.
Imajte na umu da budući da želite odabrati ćeliju na aktivnom listu, trebate samo navesti adresu ćelije.
No, ako želite odabrati ćeliju na drugom listu (recimo Sheet2), morate prvo aktivirati Sheet2, a zatim odabrati ćeliju u njemu.
Sub SelectCell () Radni listovi ("Sheet2"). Aktivirajte raspon ("A1"). Odaberite End Sub
Slično, možete aktivirati i radnu knjigu, zatim aktivirati određeni radni list u njoj, a zatim odabrati ćeliju.
Sub SelectCell () Radne knjige ("Book2.xlsx"). Radni listovi ("Sheet2"). Aktivirajte raspon ("A1"). Odaberite End Sub
Imajte na umu da kada govorite o radnim knjigama morate koristiti puno ime zajedno s nastavkom datoteke (.xlsx u gornjem kodu). U slučaju da radna knjiga nikada nije spremljena, ne morate koristiti proširenje datoteke.
Ovi primjeri nisu jako korisni, ali kasnije ćete u ovom vodiču vidjeti kako možemo koristiti iste koncepte za kopiranje i lijepljenje ćelija u Excelu (koristeći VBA).
Baš kao što odabiremo ćeliju, možemo odabrati i raspon.
U slučaju raspona, to može biti raspon fiksne veličine ili raspon promjenjive veličine.
U fiksnom rasponu veličina znali biste koliki je raspon i mogli biste koristiti točnu veličinu u svom VBA kodu. No s rasponom promjenjive veličine nemate pojma koliki je raspon i trebate upotrijebiti malo VBA magije.
Pogledajmo kako to učiniti.
Odabir raspona fiksne veličine
Ovdje je kôd koji će odabrati raspon A1: D20.
Raspon podizbora Područje () ("A1: D20"). Odaberite Kraj podv
Drugi način za to je korištenje donjeg koda:
Raspon podizbora Područje () ("A1", "D20"). Odaberite Kraj podv
Gornji kôd uzima gornju lijevu adresu ćelije (A1) i donju desnu adresu ćelije (D20) i odabire cijeli raspon. Ova tehnika postaje korisna kada radite s rasponima promjenjive veličine (kao što ćemo vidjeti kada svojstvo End bude pokriveno kasnije u ovom vodiču).
Ako želite da se odabir dogodi u drugoj radnoj knjizi ili na drugom radnom listu, morate VBA -i reći točne nazive ovih objekata.
Na primjer, donji kôd odabrao bi raspon A1: D20 na radnom listu Sheet2 u radnoj knjizi Book2.
Sub SelectRange () Radne knjige ("Book2.xlsx"). Radni listovi ("Sheet1"). Aktivirajte raspon ("A1: D20"). Odaberite End Sub
Što ako ne znate koliko redaka ima. Što ako želite odabrati sve ćelije koje imaju vrijednost.
U tim slučajevima morate koristiti metode prikazane u sljedećem odjeljku (o odabiru raspona promjenjive veličine).
Odabir raspona promjenjive veličine
Postoje različiti načini na koje možete odabrati niz ćelija. Metoda koju odaberete ovisit će o tome kako su podaci strukturirani.
U ovom odjeljku pokriti ću neke korisne tehnike koje su zaista korisne kada radite s rasponima u VBA -i.
Odaberite Korištenje svojstva CurrentRange
U slučajevima kada ne znate koliko redaka/stupaca ima podataka, možete upotrijebiti svojstvo CurrentRange objekta Range.
Svojstvo CurrentRange pokriva sve susjedne ispunjene ćelije u rasponu podataka.
Ispod je kôd koji će odabrati trenutno područje koje sadrži ćeliju A1.
Pod SelectCurrentRegion () Raspon ("A1"). CurrentRegion. Odaberite End Sub
Gornja metoda je dobra ako imate sve podatke kao tablicu bez praznih redaka/stupaca.
No u slučaju da u podacima imate prazne retke/stupce, neće odabrati one iza praznih redaka/stupaca. Na donjoj slici kôd CurrentRegion odabire podatke do retka 10 jer je redak 11 prazan.
U takvim slučajevima možete koristiti svojstvo UsedRange objekta radnog lista.
Odaberite Korištenje svojstva UsedRange
UsedRange omogućuje vam upućivanje na sve promijenjene ćelije.
Tako bi donji kôd odabrao sve korištene ćelije u aktivnom listu.
Sub SelectUsedRegion () ActiveSheet.UsedRange. Odaberite End Sub
Imajte na umu da će se, u slučaju da imate iskorištenu udaljenu ćeliju, uzeti u obzir gornji kôd i sve ćelije dok se ta korištena ćelija ne odabere.
Odaberite Upotreba krajnjeg svojstva
Ovaj dio je zaista koristan.
Svojstvo End omogućuje vam odabir zadnje ispunjene ćelije. To vam omogućuje da oponašate učinak tipki sa strelicama dolje/gore ili tipki za upravljanje desno/lijevo.
Pokušajmo to shvatiti na primjeru.
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite brzo odabrati zadnje ispunjene ćelije u stupcu A.
Problem je u tome što se podaci mogu promijeniti i ne znate koliko je ćelija ispunjeno. Ako to morate učiniti pomoću tipkovnice, možete odabrati ćeliju A1, a zatim upotrijebiti Control + tipku sa strelicom prema dolje i ona će odabrati zadnju ispunjenu ćeliju u stupcu.
Pogledajmo sada kako to učiniti pomoću VBA. Ova je tehnika korisna kada želite brzo skočiti do zadnje ispunjene ćelije u stupcu promjenjive veličine
Sub GoToLastFilledCell () Raspon ("A1"). Kraj (xlDown). Odaberite kraj Sub
Gornji kôd skočio bi na posljednju ispunjenu ćeliju u stupcu A.
Slično, možete koristiti End (xlToRight) za skok na posljednju ispunjenu ćeliju u nizu.
Sub GoToLastFilledCell () Raspon ("A1"). Kraj (xlToRight). Odaberite kraj Sub
Što ako želite odabrati cijeli stupac umjesto da skočite na posljednju ispunjenu ćeliju.
To možete učiniti pomoću donjeg koda:
Sub SelectFilledCells () Raspon ("A1", Raspon ("A1"). Kraj (xlDown)). Odaberite End Sub
U gornjem kodu koristili smo prvu i posljednju referencu ćelije koju moramo odabrati. Bez obzira koliko ispunjenih ćelija ima, gornji kôd će odabrati sve.
Sjetite se gornjeg primjera gdje smo odabrali raspon A1: D20 pomoću sljedeće linije koda:
Domet ("A1 ″," D20 ")
Ovdje je A1 gornja lijeva ćelija, a D20 donja desna ćelija u rasponu. Istu logiku možemo koristiti pri odabiru raspona promjenjive veličine. No, budući da ne znamo točnu adresu donje desne ćelije, za dobivanje smo koristili svojstvo End.
U rasponu (“A1”, Raspon (“A1”). Kraj (xlDown)), “A1” se odnosi na prvu ćeliju i raspon (“A1”). Kraj (xlDown) se odnosi na posljednju ćeliju. Budući da smo dali obje reference, metoda Select odabire sve ćelije između ove dvije reference.
Slično, možete odabrati i cijeli skup podataka koji ima više redaka i stupaca.
Kod u nastavku odabrao bi sve ispunjene retke/stupce počevši od ćelije A1.
Sub SelectFilledCells () Raspon ("A1", Raspon ("A1"). Kraj (xlDown). Kraj (xlToRight)). Odaberite End Sub
U gornjem kodu koristili smo Range (“A1”). End (xlDown) .End (xlToRight) da bismo dobili referencu donje desne popunjene ćelije skupa podataka.
Razlika između korištenja CurrentRegion i End
Ako se pitate zašto koristiti svojstvo End za odabir ispunjenog raspona kada imamo svojstvo CurrentRegion, dopustite mi da vam kažem razliku.
S svojstvom Kraj možete odrediti početnu ćeliju. Na primjer, ako imate podatke u A1: D20, ali prvi red su zaglavlja, pomoću svojstva Kraj možete odabrati podatke bez zaglavlja (pomoću donjeg koda).
Sub SelectFilledCells () Raspon ("A2", Raspon ("A2"). Kraj (xlDown). Kraj (xlToRight)). Odaberite Kraj podvrste
No CurrentRegion bi automatski odabrao cijeli skup podataka, uključujući zaglavlja.
Do sada smo u ovom vodiču vidjeli kako se pozivati na niz ćelija na različite načine.
Pogledajmo sada neke načine na koje zapravo možemo upotrijebiti ove tehnike za obavljanje nekog posla.
Kopiranje ćelija / raspona pomoću VBA
Kao što sam spomenuo na početku ovog vodiča, odabir ćelije nije nužan za izvođenje radnji na njoj. U ovom ćete odjeljku vidjeti kako kopirati ćelije i raspone, a da ih ni ne odaberete.
Počnimo s jednostavnim primjerom.
Kopiranje jedne ćelije
Ako želite kopirati ćeliju A1 i zalijepiti je u ćeliju D1, učinio bi to donji kod.
Sub CopyCell () Raspon ("A1"). Raspon kopiranja ("D1") Kraj Sub
Imajte na umu da metoda kopiranja objekta raspona kopira ćeliju (baš kao i Control +C) i zalijepi je na navedeno odredište.
U gornjem primjeru koda odredište je navedeno u istom retku u kojem koristite metodu Kopiraj. Ako želite učiniti svoj kod još čitljivijim, upotrijebite donji kod:
Sub CopyCell () Raspon ("A1"). Odredište kopiranja: = Raspon ("D1") Kraj Sub
Gore navedeni kodovi kopirat će i zalijepiti vrijednost kao i oblikovanje/formule u nju.
Kao što ste već mogli primijetiti, gornji kôd kopira ćeliju bez odabira. Bez obzira gdje se nalazite na radnom listu, kôd će kopirati ćeliju A1 i zalijepiti je na D1.
Također imajte na umu da će gornji kôd prebrisati bilo koji postojeći kôd u ćeliji D2. Ako želite da vas Excel obavijesti postoji li već nešto u ćeliji D1, a da to ne prepišete, možete upotrijebiti donji kod.
Sub CopyCell () If Range ("D1") "" then Response = MsgBox ("Želite li prebrisati postojeće podatke", vbYesNo) End If If If Response = vbYes then Range ("A1"). Copy Range ("D1 ") End If End Sub
Kopiranje raspona fiksne veličine
Ako želite kopirati A1: D20 u J1: M20, možete upotrijebiti donji kod:
Podopseg raspona kopiranja () ("A1: D20"). Raspon kopiranja ("J1") Kraj sub
U odredišnoj ćeliji samo trebate navesti adresu gornje lijeve ćelije. Kôd bi automatski kopirao točno kopirani raspon u odredište.
Istu konstrukciju možete koristiti za kopiranje podataka s jednog lista na drugi.
Šifra u nastavku kopirala bi A1: D20 s aktivnog lista na List2.
Podopseg raspona kopiranja () ("A1: D20"). Kopiranje radnih listova ("List 2"). Raspon ("A1") Kraj pod
Gore navedeno kopira podatke s aktivnog lista. Stoga provjerite je li list s podacima aktivni list prije pokretanja koda. Kako biste bili sigurni, tijekom kopiranja podataka možete odrediti i naziv radnog lista.
Sub CopyRange () Radni listovi ("List1"). Raspon ("A1: D20"). Kopiranje radnih listova ("List2"). Raspon ("A1") Kraj
Dobra stvar kod gornjeg koda je da bez obzira na to koji je list aktivan, uvijek će kopirati podatke iz Sheet1 i zalijepiti ih u Sheet2.
Također možete kopirati imenovani raspon koristeći njegovo ime umjesto reference.
Na primjer, ako imate imenovani raspon pod nazivom 'SalesData', možete upotrijebiti donji kod za kopiranje ovih podataka u Sheet2.
Sub CopyRange () Raspon ("SalesData"). Kopiraj radne listove ("Sheet2"). Raspon ("A1") Kraj Sub
Ako je opseg imenovanog raspona cijela radna knjiga, ne morate biti na listu koji ima imenovani raspon da biste pokrenuli ovaj kôd. Budući da je imenovani raspon obuhvaćen radnom knjigom, možete mu pristupiti s bilo kojeg lista pomoću ovog koda.
Ako imate tablicu s imenom Table1, pomoću donjeg koda možete je kopirati u Sheet2.
Raspon podkopirane tablice () ("Tablica1 [#Svi]"). Kopiraj radne listove ("List2"). Raspon ("A1") Kraj pod
Također možete kopirati raspon u drugu radnu knjigu.
U sljedećem primjeru kopiram tablicu programa Excel (tablica1) u radnu knjigu Book2.
Raspon podkopiranjaCurrentRegion () ("Tablica1 [#Sve]"). Kopiranje radnih knjiga ("Knjiga2.xlsx"). Radni listovi ("List1"). Raspon ("A1") Kraj
Ovaj bi kôd funkcionirao samo ako je radna knjiga već otvorena.
Kopiranje raspona promjenjive veličine
Jedan od načina kopiranja raspona promjenjive veličine je njihovo pretvaranje u imenovane raspone ili Excel tablicu i korištenje kodova kako je prikazano u prethodnom odjeljku.
Ali ako to ne možete učiniti, možete upotrijebiti svojstvo CurrentRegion ili End svojstva range.
Kod u nastavku kopirao bi trenutno područje u aktivni list i zalijepio ga u Sheet2.
PodkopijaCurrentRegion () Raspon ("A1"). CurrentRegion.Copy Radni listovi ("Sheet2"). Raspon ("A1") Kraj Sub
Ako želite kopirati prvi stupac vašeg skupa podataka do zadnje ispunjene ćelije i zalijepiti ga u Sheet2, možete upotrijebiti donji kod:
Sub CopyCurrentRegion () Raspon ("A1", Raspon ("A1"). Kraj (xlDown)). Kopiranje radnih listova ("List2"). Raspon ("A1") Kraj
Ako želite kopirati retke i stupce, možete upotrijebiti donji kod:
Sub CopyCurrentRegion () Raspon ("A1", Raspon ("A1"). Kraj (xlDown) .End (xlToRight)). Kopiranje radnih listova ("Sheet2"). Raspon ("A1") Kraj
Imajte na umu da svi ti kodovi ne odabiru ćelije tijekom izvršavanja. Općenito, pronaći ćete samo nekoliko slučajeva u kojima morate odabrati ćeliju/raspon prije nego što radite na njoj.
Dodjeljivanje raspona objektnim varijablama
Do sada smo koristili punu adresu ćelija (kao što su radne bilježnice (“Book2.xlsx”). Radni listovi (“List1”). Raspon (“A1”)).
Da biste svojim kodom učinili lakše upravljanim, ove raspone možete dodijeliti objektnim varijablama, a zatim koristiti te varijable.
Na primjer, u donjem kodu dodijelio sam izvorni i odredišni raspon objektnim varijablama, a zatim sam ih upotrijebio za kopiranje podataka iz jednog raspona u drugi.
Sub CopyRange () Dim SourceRange kao raspon Dim DestinationRange kao raspon Set SourceRange = Radni listovi ("Sheet1"). Raspon ("A1: D20") Set DestinationRange = Radni listovi ("Sheet2"). Raspon ("A1") SourceRange.Copy DestinationRange Kraj podm
Počinjemo proglašavanjem varijabli objektima Range. Zatim dodjeljujemo raspon ovim varijablama pomoću izraza Set. Nakon što je raspon dodijeljen varijabli, možete jednostavno koristiti varijablu.
Unesite podatke u sljedeću praznu ćeliju (pomoću okvira za unos)
Pomoću okvira za unos možete omogućiti korisniku unos podataka.
Na primjer, pretpostavimo da imate niz podataka u nastavku i želite unijeti zapis o prodaji, možete koristiti okvir za unos u VBA. Pomoću koda možemo provjeriti ispunjava li podatke u sljedećem praznom retku.
Sub EnterData () Dim RefRange As Range Set RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Postavi iznos = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Vrednost + 1 ProductCategory.Value = InputBox ("Kategorija proizvoda") Quantity.Value = InputBox ("Količina") Iznos.Vrijednost = InputBox ("Iznos") Kraj pod
Gornji kôd koristi okvir za unos VBA za dobivanje unosa od korisnika, a zatim unosi ulaze u navedene ćelije.
Imajte na umu da nismo koristili točne reference ćelija. Umjesto toga, koristili smo svojstvo Kraj i Pomak da pronađemo posljednju praznu ćeliju i ispunimo podatke u nju.
Ovaj kod je daleko od toga da se može koristiti. Na primjer, ako unesete tekstualni niz kada okvir za unos traži količinu ili količinu, primijetit ćete da Excel to dopušta. Možete upotrijebiti uvjet Ako za provjeru je li vrijednost numerička ili ne, a zatim je dopustite u skladu s tim.
Petlje kroz ćelije / raspone
Do sada smo mogli vidjeti kako odabrati, kopirati i unijeti podatke u ćelije i raspone.
U ovom odjeljku ćemo vidjeti kako proći kroz skup ćelija/redaka/stupaca u rasponu. To bi moglo biti korisno kada želite analizirati svaku ćeliju i na temelju nje izvršiti neku radnju.
Na primjer, ako želite označiti svaki treći redak u odabiru, morate se petljati i provjeriti ima li broj retka. Slično, ako želite istaknuti sve negativne ćelije promjenom boje fonta u crvenu, morate proći kroz petlje i analizirati vrijednost svake ćelije.
Ovdje je kôd koji će se provlačiti kroz retke u odabranim ćelijama i označavati zamjenske retke.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Odabir za svaki Myrow U Myrange.Rows If Myrow.Row Mod 2 = 0 Zatim Myrow.Interior.Color = vbCyan Kraj Ako je sljedeći Myrow Kraj Sub
Gornji kôd koristi funkciju MOD za provjeru broja retka u odabiru. Ako je broj retka paran, bit će označen cijan bojom.
Evo još jednog primjera gdje kôd prolazi kroz svaku ćeliju i ističe ćelije koje u njoj imaju negativnu vrijednost.
Sub HighlightAlternateRows () Priguši Myrange kao raspon Dim Mycell as Range Set Myrange = Odabir za svaku Mycell u Myrange Ako je Mycell <0 Zatim Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Imajte na umu da istu stvar možete učiniti pomoću uvjetnog oblikovanja (koje je dinamično i bolji način za to). Ovaj primjer služi samo za prikaz načina na koji petlja radi s ćelijama i rasponima u VBA -i.
Gdje staviti VBA kod
Pitate se gdje se VBA kôd nalazi u vašoj radnoj knjizi programa Excel?
Excel ima VBA pozadinu koja se naziva VBA editor. Morate kopirati i zalijepiti kôd u prozor koda modula VB Editor.
Evo koraka za to:
- Idite na karticu Developer.
- Kliknite na opciju Visual Basic. Ovo će otvoriti VB editor u pozadini.
- U oknu Project Explorer u VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige u koji želite umetnuti kôd.Ako ne vidite Project Explorer, idite na karticu View i kliknite na Project Explorer.
- Idite na Umetni i kliknite na Modul. Ovo će umetnuti objekt modula u vašu radnu knjigu.
- Kopirajte i zalijepite kôd u prozor modula.
