Pri delu z Excelom večino svojega časa preživite na področju delovnega lista - ukvarjate se s celicami in obsegi.
In če želite avtomatizirati svoje delo v Excelu z uporabo VBA, morate vedeti, kako delati s celicami in obsegi z uporabo VBA.
Z obsegi v VBA lahko počnete veliko različnih stvari (na primer izbirate, kopirate, premikate, urejate itd.).
Zato bom za obravnavo te teme razdelil to vadnico na oddelke in vam pokazal, kako delati s celicami in obsegi v Excelu VBA z uporabo primerov.
Začnimo.
Vse kode, ki jih omenjam v tej vadnici, je treba postaviti v urejevalnik VB. Pojdite na razdelek »Kam postaviti kodo VBA«, če želite izvedeti, kako deluje.Če vas zanima učenje VBA na enostaven način, si oglejte moj Spletno usposabljanje Excel VBA.
Izbira celice / obsega v Excelu z uporabo VBA
Za delo s celicami in obsegi v Excelu z uporabo VBA vam ga ni treba izbrati.
V večini primerov je bolje, da ne izberete celic ali obsegov (kot bomo videli).
Kljub temu je pomembno, da greste skozi ta razdelek in razumete, kako deluje. To bo ključnega pomena pri vašem učenju VBA in v tej vadnici bo uporabljenih veliko konceptov, zajetih tukaj.
Zato začnimo z zelo preprostim primerom.
Izbira ene celice z uporabo VBA
Če želite v aktivnem listu izbrati eno celico (recimo A1), lahko uporabite spodnjo kodo:
Sub SelectCell () Range ("A1"). Izberite End Sub
Zgornja koda ima obvezni del "Sub" in "End Sub" ter vrstico kode, ki izbere celico A1.
Obseg (“A1”) pove VBA naslov celice, na katero se želimo sklicevati.
Izberite je metoda predmeta Range in izbere celice/obseg, določene v objektu Range. Sklice na celice morajo biti v dvojnih narekovajih.
Ta koda bi pokazala napako, če je list z grafikonom aktiven. List z grafikoni vsebuje grafikone in se ne uporablja široko. Ker v njem ni celic/obsegov, ga zgornja koda ne more izbrati in na koncu prikaže napako.
Upoštevajte, da morate izbrati celico na aktivnem listu, zato morate samo navesti naslov celice.
Če pa želite celico izbrati na drugem listu (recimo Sheet2), morate najprej aktivirati Sheet2 in nato izbrati celico v njem.
Sub SelectCell () Delovni listi ("Sheet2"). Aktivirajte obseg ("A1"). Izberite End Sub
Podobno lahko aktivirate tudi delovni zvezek, nato v njem aktivirate določen delovni list in nato izberete celico.
Sub SelectCell () Delovni zvezki ("Book2.xlsx"). Delovni listi ("Sheet2"). Aktivirajte obseg ("A1"). Izberite End Sub
Upoštevajte, da morate pri sklicevanju na delovne zvezke uporabiti celotno ime skupaj s pripono datoteke (.xlsx v zgornji kodi). Če delovni zvezek nikoli ni bil shranjen, vam ni treba uporabiti razširitve datoteke.
Zdaj ti primeri niso zelo uporabni, vendar boste kasneje v tej vadnici videli, kako lahko uporabimo iste koncepte za kopiranje in lepljenje celic v Excelu (z uporabo VBA).
Tako kot izberemo celico, lahko izberemo tudi obseg.
V primeru razpona je to lahko območje s fiksno velikostjo ali s spremenljivo velikostjo.
V fiksnem obsegu velikosti bi vedeli, kako velik je obseg, in lahko uporabite natančno velikost v kodi VBA. Toda s spremenljivo velikostjo nimate pojma, kako velik je obseg, zato morate uporabiti malo čarovnije VBA.
Poglejmo, kako to storiti.
Izbira obsega fiksne velikosti
Tu je koda, ki bo izbrala obseg A1: D20.
Sub SelectRange () Range ("A1: D20"). Izberite End Sub
Drug način za to je uporaba spodnje kode:
Sub SelectRange () Range ("A1", "D20"). Izberite End Sub
Zgornja koda vzame zgornji levi naslov celice (A1) in spodnji desni naslov celice (D20) in izbere celoten obseg. Ta tehnika postane uporabna, ko delate z obsegi spremenljive velikosti (kot bomo videli, ko bo lastnost End zajeta kasneje v tej vadnici).
Če želite, da se izbira izvede v drugem delovnem zvezku ali na drugem delovnem listu, morate VBA povedati natančna imena teh predmetov.
Spodnja koda bi na primer izbrala obseg A1: D20 na delovnem listu Sheet2 v delovnem zvezku Book2.
Sub SelectRange () Delovni zvezki ("Book2.xlsx"). Delovni listi ("Sheet1"). Aktivirajte obseg ("A1: D20"). Izberite End Sub
Kaj pa, če ne veste, koliko vrstic je tam. Kaj pa, če želite izbrati vse celice, ki imajo vrednost.
V teh primerih morate uporabiti metode, prikazane v naslednjem razdelku (o izbiri obsega spremenljive velikosti).
Izbira obsega različnih velikosti
Različne celice lahko izberete na različne načine. Izbira metode je odvisna od strukture podatkov.
V tem razdelku bom zajel nekaj uporabnih tehnik, ki so resnično uporabne pri delu z obsegi v VBA.
Izberite Uporaba lastnosti CurrentRange
V primerih, ko ne veste, koliko vrstic/stolpcev vsebuje podatke, lahko uporabite lastnost CurrentRange predmeta Range.
Lastnost CurrentRange zajema vse sosednje napolnjene celice v podatkovnem območju.
Spodaj je koda, ki bo izbrala trenutno območje, ki vsebuje celico A1.
Pod SelectCurrentRegion () Obseg ("A1"). CurrentRegion. Izberite End Sub
Zgornja metoda je dobra, če imate vse podatke kot tabelo brez praznih vrstic/stolpcev.
Če pa imate v svojih podatkih prazne vrstice/stolpce, ne bo izbral tistih, ki so za praznimi vrsticami/stolpci. Na spodnji sliki koda CurrentRegion izbere podatke do 10. vrstice, saj je 11. vrstica prazna.
V takih primerih boste morda želeli uporabiti lastnost UsedRange predmeta delovnega lista.
Izberite Uporabi lastnost UsedRange
UsedRange vam omogoča sklicevanje na vse spremenjene celice.
Torej bi spodnja koda izbrala vse uporabljene celice na aktivnem listu.
Sub SelectUsedRegion () ActiveSheet.UsedRange. Izberite End Sub
Upoštevajte, da če bi uporabili oddaljeno celico, bi to upoštevali zgornja koda in vse celice, dokler ne bi bila izbrana uporabljena celica.
Izberite Uporaba končne lastnosti
Zdaj je ta del zelo koristen.
Lastnost End vam omogoča, da izberete zadnjo zapolnjeno celico. To vam omogoča, da posnemate učinek tipk puščice navzdol/navzgor ali tipk za upravljanje desno/levo.
Poskusimo to razumeti na primeru.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite hitro izbrati zadnje zapolnjene celice v stolpcu A.
Težava je v tem, da se lahko podatki spremenijo in ne veste, koliko celic je napolnjenih. Če morate to narediti s tipkovnico, lahko izberete celico A1 in nato uporabite Control + puščična tipka navzdol in izbrala bo zadnjo zapolnjeno celico v stolpcu.
Zdaj pa poglejmo, kako to storiti z uporabo VBA. Ta tehnika je priročna, če želite hitro skočiti na zadnjo zapolnjeno celico v stolpcu spremenljive velikosti
Sub GoToLastFilledCell () Range ("A1"). End (xlDown). Izberite End Sub
Zgornja koda bi skočila na zadnjo zapolnjeno celico v stolpcu A.
Podobno lahko uporabite End (xlToRight) za skok na zadnjo zapolnjeno celico v vrsti.
Sub Obseg GoToLastFilledCell () ("A1"). Konec (xlToRight). Izberite Končaj Sub
Kaj pa, če želite izbrati celoten stolpec, namesto da skočite na zadnjo zapolnjeno celico.
To lahko storite s spodnjo kodo:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Izberite End Sub
V zgornji kodi smo uporabili prvo in zadnjo referenco celice, ki jo moramo izbrati. Ne glede na to, koliko polnih celic je, bo zgornja koda izbrala vse.
Zapomnite si zgornji primer, kjer smo izbrali obseg A1: D20 z naslednjo vrstico kode:
Razpon ("A1 ″," D20 ")
Tu je bila A1 zgornja leva celica, D20 pa spodnja desna celica v območju. Enako logiko lahko uporabimo pri izbiri razponov različnih velikosti. Ker pa ne vemo točnega naslova spodnje desne celice, smo za njegovo pridobitev uporabili lastnost End.
V območju (“A1”, obseg (“A1”). Konec (xlDown)) se “A1” nanaša na prvo celico in obseg (“A1”). Konec (xlDown) se nanaša na zadnjo celico. Ker smo podali obe sklici, metoda Select izbere vse celice med tema dvema sklicema.
Podobno lahko izberete tudi celoten nabor podatkov, ki ima več vrstic in stolpcev.
Spodnja koda bi izbrala vse zapolnjene vrstice/stolpce, ki se začnejo od celice A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Izberite End Sub
V zgornji kodi smo uporabili Range (“A1”). End (xlDown) .End (xlToRight), da dobimo sklic na spodnjo desno zapolnjeno celico nabora podatkov.
Razlika med uporabo CurrentRegion in End
Če se sprašujete, zakaj uporabite lastnost End za izbiro zapolnjenega obsega, ko imamo lastnost CurrentRegion, naj vam povem razliko.
Z lastnostjo End lahko podate začetno celico. Na primer, če imate svoje podatke v A1: D20, vendar so prva vrstica glave, lahko z lastnostjo End izberete podatke brez glav (uporabite spodnjo kodo).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown) .End (xlToRight)). Izberite End Sub
Toda CurrentRegion bi samodejno izbral celoten nabor podatkov, vključno z glavami.
Doslej smo v tej vadnici videli, kako se na različne načine sklicevati na vrsto celic.
Zdaj pa poglejmo nekaj načinov, kako lahko dejansko uporabimo te tehnike za delo.
Kopirajte celice / obsege z uporabo VBA
Kot sem omenil na začetku te vadnice, izbira celice ni potrebna za izvajanje dejanj na njej. V tem razdelku boste videli, kako kopirati celice in obsege, ne da bi jih sploh izbrali.
Začnimo s preprostim primerom.
Kopiranje enocelične
Če želite kopirati celico A1 in jo prilepiti v celico D1, bi to naredila spodnja koda.
Sub CopyCell () Range ("A1"). Copy Range ("D1") End Sub
Upoštevajte, da metoda kopiranja predmeta obseg kopira celico (tako kot Control +C) in jo prilepi v podani cilj.
V zgornjem primeru kode je cilj določen v isti vrstici, kjer uporabljate metodo Kopiraj. Če želite svojo kodo narediti še bolj berljivo, lahko uporabite spodnjo kodo:
Sub CopyCell () Range ("A1"). Copy Destination: = Range ("D1") End Sub
Zgornje kode bodo vanj kopirale in prilepile vrednost ter oblikovanje/formule.
Kot ste morda že opazili, zgornja koda kopira celico, ne da bi jo izbrala. Ne glede na to, kje ste na delovnem listu, bo koda kopirala celico A1 in jo prilepila na D1.
Upoštevajte tudi, da bi zgornja koda prepisala obstoječo kodo v celici D2. Če želite, da vas Excel obvesti, če je v celici D1 že nekaj, ne da bi to prepisali, lahko uporabite spodnjo kodo.
Sub CopyCell () If Range ("D1") "" Potem Response = MsgBox ("Ali želite prepisati obstoječe podatke", vbYesNo) End If If If Response = vbYes Potem Range ("A1"). Copy Range ("D1 ") End If End Sub
Kopiranje obsega s fiksno velikostjo
Če želite kopirati A1: D20 v J1: M20, lahko uporabite spodnjo kodo:
Sub CopyRange () Range ("A1: D20"). Copy Range ("J1") End Sub
V ciljni celici morate samo določiti naslov zgornje leve celice. Koda bi samodejno kopirala natančno kopiran obseg v cilj.
Z isto konstrukcijo lahko kopirate podatke z enega lista na drugega.
Spodnja koda bi kopirala A1: D20 iz aktivnega lista v List2.
Podrazpon CopyRange () ("A1: D20"). Kopiraj delovne liste ("Sheet2"). Obseg ("A1") End Sub
Zgornje kopira podatke z aktivnega lista. Zato se pred zagonom kode prepričajte, da je list s podatki aktivni list. Zaradi varnosti lahko med kopiranjem podatkov določite tudi ime delovnega lista.
Sub CopyRange () Delovni listi ("List1"). Obseg ("A1: D20"). Kopiraj delovne liste ("List2"). Obseg ("A1") Končni pod
Dobra stvar pri zgornji kodi je, da bo ne glede na to, kateri list je aktiven, vedno kopiral podatke iz Sheet1 in jih prilepil v Sheet2.
Imenovani obseg lahko kopirate tudi tako, da namesto sklica uporabite njegovo ime.
Če imate na primer imenovani obseg, imenovan 'SalesData', lahko uporabite spodnjo kodo za kopiranje teh podatkov v Sheet2.
Podrazpon CopyRange () ("SalesData"). Kopiraj delovne liste ("Sheet2"). Obseg ("A1") End Sub
Če je obseg imenovanega obsega celoten delovni zvezek, vam za izvajanje te kode ni treba biti na listu z imenom. Ker je imenovani obseg delovnega zvezka, lahko do njega dostopate s katerega koli lista s to kodo.
Če imate tabelo z imenom Table1, jo lahko s spodnjo kodo kopirate v Sheet2.
Podrazpon CopyTable () ("Tabela1 [#All]"). Kopiraj delovne liste ("List2"). Obseg ("A1") Konec pod
Obseg lahko kopirate tudi v drug delovni zvezek.
V naslednjem primeru kopiram tabelo Excel (Tabela1) v delovni zvezek Book2.
Podkopija PodročjeKurrentRegion () Obseg ("Tabela1 [#All]"). Kopiranje delovnih zvezkov ("Knjiga2.xlsx"). Delovni listi ("List1"). Obseg ("A1") Končna pod
Ta koda bi delovala le, če je delovni zvezek že odprt.
Kopiranje obsega s spremenljivo velikostjo
Eden od načinov kopiranja obsegov spremenljive velikosti je, da jih pretvorite v imenovane obsege ali Excelovo tabelo in uporabite kode, kot je prikazano v prejšnjem razdelku.
Če pa tega ne morete storiti, lahko uporabite CurrentRegion ali lastnost End predmeta range.
Spodnja koda bi kopirala trenutno regijo v aktivni list in jo prilepila v Sheet2.
PodkopijaCurrentRegion () Obseg ("A1"). CurrentRegion.Copy delovni listi ("Sheet2"). Obseg ("A1") End Sub
Če želite kopirati prvi stolpec nabora podatkov do zadnje zapolnjene celice in ga prilepiti v Sheet2, lahko uporabite spodnjo kodo:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Če želite kopirati vrstice in stolpce, lahko uporabite spodnjo kodo:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Kopiraj delovne liste ("Sheet2"). Range ("A1") End Sub
Upoštevajte, da vse te kode med izvajanjem ne izbirajo celic. Na splošno boste našli le peščico primerov, v katerih morate dejansko izbrati celico/obseg, preden začnete z delom.
Dodeljevanje obsegov predmetnim spremenljivkam
Doslej smo uporabljali polni naslov celic (kot so delovni zvezki (»Book2.xlsx«). Delovni listi (»List1«). Razpon (»A1«)).
Če želite, da je vaša koda bolj obvladljiva, lahko te obsege dodelite objektnim spremenljivkam in nato uporabite te spremenljivke.
Na primer, v spodnji kodi sem dodelil izvorno in ciljno območje spremenljivkam predmeta in nato uporabil te spremenljivke za kopiranje podatkov iz enega območja v drugega.
Sub CopyRange () Zatemni SourceRange kot obseg Dim DestinationRange kot obseg Set SourceRange = Delovni listi ("Sheet1"). Range ("A1: D20") Set DestinationRange = Delovni listi ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange End Sub
Začnemo z razglasitvijo spremenljivk za predmete Range. Nato tem spremenljivkam dodelimo obseg z uporabo stavka Set. Ko je obseg dodeljen spremenljivki, jo lahko preprosto uporabite.
Vnesite podatke v naslednjo prazno celico (z uporabo polja za vnos)
Uporabite lahko polja za vnos, da uporabniku omogočite vnos podatkov.
Recimo, da imate spodaj nabor podatkov in želite vnesti zapis o prodaji, lahko uporabite polje za vnos v VBA. S kodo se lahko prepričamo, da zapolni podatke v naslednji prazni vrstici.
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 ) Nastavi znesek = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Vrednost + 1 ProductCategory.Value = InputBox ("Kategorija izdelka") Quantity.Value = InputBox ("Količina") Amount.Value = InputBox ("Amount") End Sub
Zgornja koda uporablja polje VBA za vnos, da pridobi vnose od uporabnika, nato pa vnese vnose v podane celice.
Upoštevajte, da nismo uporabili natančnih referenc celic. Namesto tega smo z lastnostjo End in Offset poiskali zadnjo prazno celico in v njej napolnili podatke.
Ta koda še zdaleč ni uporabna. Če na primer vnesete besedilni niz, ko polje za vnos zahteva količino ali količino, boste opazili, da Excel to dovoljuje. S pogojem If lahko preverite, ali je vrednost številska ali ne, in jo nato ustrezno dovolite.
Zanka po celicah / razponih
Doslej smo lahko videli, kako izbrati, kopirati in vnesti podatke v celice in obsege.
V tem razdelku bomo videli, kako prečkati niz celic/vrstic/stolpcev v obsegu. To je lahko koristno, če želite analizirati vsako celico in na podlagi nje izvesti nekaj dejanj.
Na primer, če želite označiti vsako tretjo vrstico v izboru, se morate prečrtati in preveriti številko vrstice. Podobno, če želite označiti vse negativne celice tako, da barvo pisave spremenite v rdečo, morate prečrtati in analizirati vrednost vsake celice.
Tu je koda, ki se bo vrtela skozi vrstice v izbranih celicah in označevala nadomestne vrstice.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Izbor za vsak Myrow v Myrange.Rows If Myrow.Row Mod 2 = 0 Potem Myrow.Interior.Color = vbCyan End Če je naslednji Myrow End Sub
Zgornja koda uporablja funkcijo MOD za preverjanje številke vrstice v izboru. Če je številka vrstice soda, je označena v cijan barvi.
Tu je še en primer, ko gre koda skozi vsako celico in označi celice, ki imajo v njej negativno vrednost.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Izbor za vsako Mycell v Myrange Če je Mycell <0 Potem Mycell.Interior.Color = vbRdeči konec Če je naslednji Mycell End Sub
Upoštevajte, da lahko isto storite s pogojnim oblikovanjem (ki je dinamično in boljši način za to). Ta primer je samo za prikaz, kako zankanje deluje s celicami in obsegi v VBA.
Kam vstaviti kodo VBA
Se sprašujete, kje je koda VBA v vašem Excelovem delovnem zvezku?
Excel ima zaledje VBA, imenovano urejevalnik VBA. Kodo morate kopirati in prilepiti v okno kode modula VB Editor.
Tu so naslednji koraki:
- Pojdite na zavihek Razvijalec.
- Kliknite možnost Visual Basic. To bo odprlo urejevalnik VB v zaledju.
- V podoknu Raziskovalec projektov v urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerega želite vstaviti kodo.Če ne vidite Raziskovalca, pojdite na zavihek Pogled in kliknite Raziskovalec projektov.
- Pojdite na Vstavi in kliknite Modul. S tem boste v svoj delovni zvezek vstavili objekt modula.
- Kodo kopirajte in prilepite v okno modula.
