Exceliga töötades veedetakse suurem osa ajast töölehe alal - lahtrite ja vahemikega tegelemisel.
Ja kui soovite oma tööd Excelis VBA abil automatiseerida, peate teadma, kuidas VBA abil rakkude ja vahemikega töötada.
Vahemikega saab VBA -s teha palju erinevaid asju (näiteks valida, kopeerida, teisaldada, redigeerida jne).
Selle teema katmiseks jagan selle õpetuse osadeks ja näitan teile, kuidas Excel VBA lahtrite ja vahemikega töötada.
Alustame.
Kõik selles juhendis mainitud koodid tuleb paigutada VB redaktorisse. Avage jaotis „Kuhu VBA -kood panna”, et teada saada, kuidas see toimib.Kui olete huvitatud VBA lihtsast õppimisest, vaadake minu lehte Online VBA koolitus Excelis.
Lahtri / vahemiku valimine Excelis VBA abil
Rakenduste ja vahemikega töötamiseks Excelis VBA abil ei pea te seda valima.
Enamikul juhtudel on parem mitte valida lahtreid või vahemikke (nagu me näeme).
Sellest hoolimata on oluline see jaotis läbi vaadata ja mõista, kuidas see toimib. See on teie VBA õppimisel ülioluline ja selles õpetuses kasutatakse paljusid siin käsitletud mõisteid.
Nii et alustame väga lihtsast näitest.
Ühe lahtri valimine VBA abil
Kui soovite aktiivsel lehel valida ühe lahtri (näiteks A1), saate kasutada järgmist koodi:
Alamvalik Lahtri () vahemik ("A1"). Valige Lõpeta alam
Ülaltoodud koodil on kohustuslik osa „Sub” ja „End Sub” ning koodirida, mis valib lahtri A1.
Vahemik („A1”) ütleb VBA -le selle lahtri aadressi, millele soovime viidata.
Valige on objekti Range meetod ja valib objektil Range määratud lahtrid/vahemiku. Lahtrite viited tuleb lisada jutumärkidesse.
See kood näitaks viga, kui diagrammileht on aktiivne leht. Diagrammileht sisaldab diagramme ja seda ei kasutata laialdaselt. Kuna sellel pole lahtreid/vahemikke, ei saa ülaltoodud kood seda valida ja kuvab lõpuks vea.
Pange tähele, et kuna soovite aktiivsel lehel lahtri valida, peate lihtsalt määrama lahtri aadressi.
Kuid kui soovite lahtri valida mõnel teisel lehel (oletame, et leht 2), peate esmalt aktiveerima lehe 2 ja seejärel valima lahtri.
Sub SelectCell () töölehed ("Sheet2"). Aktiveerige vahemik ("A1"). Valige End Sub
Samamoodi saate aktiveerida ka töövihiku, seejärel aktiveerida selles kindla töölehe ja seejärel valida lahtri.
Sub SelectCell () Töövihikud ("Book2.xlsx"). Töölehed ("Sheet2"). Aktiveerige vahemik ("A1"). Valige End Sub
Pange tähele, et töövihikutele viidates peate kasutama täisnime koos faililaiendiga (.xlsx ülaltoodud koodis). Kui töövihikut pole kunagi salvestatud, ei pea te faililaiendit kasutama.
Nüüd ei ole need näited eriti kasulikud, kuid näete selles õpetuses hiljem, kuidas saame kasutada samu mõisteid Exceli lahtrite kopeerimiseks ja kleepimiseks (kasutades VBA -d).
Nii nagu valime lahtri, saame valida ka vahemiku.
Vahemiku korral võib see olla fikseeritud suurusvahemik või muutuv suurusvahemik.
Fikseeritud suurusvahemikus teate, kui suur see vahemik on, ja saate oma VBA -koodis täpset suurust kasutada. Kuid muutuva suurusega vahemiku korral pole teil aimugi, kui suur see vahemik on, ja peate kasutama natuke VBA maagiat.
Vaatame, kuidas seda teha.
Fikseeritud suurusega vahemiku valimine
Siin on kood, mis valib vahemiku A1: D20.
Sub SelectRange () vahemik ("A1: D20"). Valige End Sub
Teine võimalus seda teha on kasutada järgmist koodi:
Sub SelectRange () vahemik ("A1", "D20"). Valige End Sub
Ülaltoodud kood võtab ülemise vasakpoolse lahtri aadressi (A1) ja parema alumise lahtri aadressi (D20) ning valib kogu vahemiku. See tehnika on kasulik, kui töötate erineva suurusega vahemikega (nagu näeme, kui lõpu atribuut on selles õpetuses hiljem kaetud).
Kui soovite, et valik toimuks teises töövihikus või teisel töölehel, peate VBA -le ütlema nende objektide täpsed nimed.
Näiteks valiks allolev kood Book2 töövihiku lehe 2 töölehel vahemiku A1: D20.
Sub SelectRange () Töövihikud ("Book2.xlsx"). Töölehed ("Sheet1"). Aktiveerige vahemik ("A1: D20"). Valige End Sub
Mis siis, kui te ei tea, kui palju ridu seal on. Mis siis, kui soovite valida kõik lahtrid, millel on väärtus.
Sellistel juhtudel peate kasutama järgmises jaotises (muutuva suurusega vahemiku valimisel) näidatud meetodeid.
Muutuva suurusega vahemiku valimine
Lahtrite vahemiku valimiseks on erinevaid viise. Teie valitud meetod sõltub andmete ülesehitusest.
Selles jaotises käsitlen mõnda kasulikku tehnikat, mis on VBA vahemikega töötamisel tõesti kasulikud.
Valige suvand CurrentRange atribuudi kasutamine
Kui te ei tea, mitu rida/veergu andmeid on, saate kasutada objekti Range atribuuti CurrentRange.
Atribuut CurrentRange hõlmab kõiki andmevahemiku külgnevaid täidetud lahtreid.
Allpool on kood, mis valib lahtri A1 hoidva praeguse piirkonna.
AlamvalikCurrentRegion () Vahemik ("A1"). CurrentRegion.Valige Lõpeta alam
Ülaltoodud meetod on hea, kui teil on kõik andmed tabelina ilma tühjade ridade/veergudeta.
Kui aga teie andmetes on tühje ridu/veerge, ei vali see tühjade ridade/veergude järel olevaid. Alloleval pildil valib CurrentRegioni kood andmed kuni rea 10, kuna rida 11 on tühi.
Sellistel juhtudel võiksite kasutada töölehe objekti atribuuti UsedRange.
Valige UseRange atribuudi kasutamine
UsedRange võimaldab viidata muudetud lahtritele.
Seega valiks allolev kood kõik aktiivsel lehel kasutatud lahtrid.
Sub SelectUsedRegion () ActiveSheet.UsedRange. Valige End Sub
Pange tähele, et kui teil on kasutatud kauget lahtrit, arvestatakse seda ülaltoodud koodiga ja valitakse kõik lahtrid, kuni see kasutatud lahter valitakse.
Valige Lõpp -atribuudi kasutamine
Nüüd on see osa tõesti kasulik.
Atribuut Lõpp võimaldab valida viimati täidetud lahtri. See võimaldab jäljendada juhtnuppu alla/üles või nooleklahvi paremale/vasakule.
Proovime seda näite abil mõista.
Oletame, et teil on allpool näidatud andmekogum ja soovite kiiresti valida veerus A viimati täidetud lahtrid.
Probleem on selles, et andmed võivad muutuda ja te ei tea, kui palju lahtreid on täidetud. Kui peate seda tegema klaviatuuri abil, saate valida lahtri A1 ja seejärel kasutada klahvikombinatsiooni Control + allanooleklahvi ning see valib veeru viimati täidetud lahtri.
Nüüd vaatame, kuidas seda VBA abil teha. See tehnika tuleb kasuks, kui soovite kiiresti muutuva suurusega veeru viimasele täidetud lahtrile hüpata
Alam GoToLastFilledCell () vahemik ("A1"). Lõpp (xlDown). Valige End Sub
Ülaltoodud kood hüppab veeru A viimati täidetud lahtrisse.
Samamoodi saate kasutada lõppu (xlToRight), et hüpata järjest viimati täidetud lahtrisse.
Alam GoToLastFilledCell () vahemik ("A1"). Lõpp (xlToRight). Valige Lõpualam
Mis siis saab, kui soovite valida viimase veeru täitmise asemel kogu veeru.
Seda saate teha alloleva koodi abil:
Sub SelectFilledCells () Vahemik ("A1", vahemik ("A1"). Lõpp (xlDown)). Valige End Sub
Ülaltoodud koodis oleme kasutanud lahtri esimest ja viimast viidet, mille peame valima. Ükskõik kui palju täidetud lahtreid on, valib ülaltoodud kood kõik.
Pidage meeles ülaltoodud näidet, kus valisime vahemiku A1: D20, kasutades järgmist koodirida:
Vahemik ("A1", "D20")
Siin oli A1 vasakpoolne ülemine lahter ja D20 oli vahemiku parem alumine lahter. Sama loogikat saame kasutada muutuva suurusega vahemike valimisel. Aga kuna me ei tea paremas alanurgas täpset aadressi, kasutasime selle saamiseks omadust Lõpp.
Vahemikus (“A1”, vahemik (“A1”). Lõpp (xlDown)) tähistab “A1” esimest lahtrit ja vahemik (“A1”). Lõpp (xlDown) viitab viimasele lahtrile. Kuna oleme esitanud mõlemad viited, valib meetod Select kõik nende kahe viite vahel olevad lahtrid.
Samamoodi saate valida ka terve andmekogumi, millel on mitu rida ja veergu.
Allpool olev kood valiks lahtrist A1 kõik täidetud read/veerud.
Sub SelectFilledCells () Vahemik ("A1", vahemik ("A1"). End (xlDown) .End (xlToRight)). Valige End Sub
Ülaltoodud koodis oleme kasutanud vahemikku (“A1”). Lõpp (xlDown) .Lõpp (xlToRight), et saada andmestiku paremas alanurgas täidetud lahtri viide.
Erinevus CurrentRegion ja End kasutamise vahel
Kui te ei tea, miks kasutada atribuuti Lõpp, et valida täidetud vahemik, kui meil on atribuut CurrentRegion, lubage mul teile öelda erinevus.
Atribuudi Lõpp abil saate määrata alguslahtri. Näiteks kui teie andmed on vormingus A1: D20, kuid esimene rida on päised, saate atribuudi Lõpp abil valida andmed ilma päisteta (kasutades allolevat koodi).
Sub SelectFilledCells () Vahemik ("A2", vahemik ("A2"). End (xlDown) .End (xlToRight)). Valige End Sub
Kuid praegune piirkond valiks automaatselt kogu andmekogumi, sealhulgas päised.
Siiani oleme selles õpetuses näinud, kuidas viidata lahtrivahemikule erinevatel viisidel.
Vaatame nüüd mõningaid viise, kuidas saaksime neid tehnikaid mõne töö tegemiseks tegelikult kasutada.
Lahtrite / vahemike kopeerimine VBA abil
Nagu ma selle õpetuse alguses mainisin, pole lahtri valimine toimingute tegemiseks vajalik. Selles jaotises näete, kuidas kopeerida lahtreid ja vahemikke neid isegi valimata.
Alustame lihtsa näitega.
Ühe lahtri kopeerimine
Kui soovite kopeerida lahtri A1 ja kleepida selle lahtrisse D1, teeb seda allolev kood.
Alamkoopia () vahemik ("A1"). Kopeerimisvahemik ("D1") Lõpu alamosa
Pange tähele, et vahemiku objekti kopeerimismeetod kopeerib lahtri (täpselt nagu Control +C) ja kleepib selle määratud sihtkohta.
Ülaltoodud näidiskoodis on sihtkoht määratud samale reale, kus kasutate kopeerimismeetodit. Kui soovite oma koodi veelgi loetavamaks muuta, võite kasutada järgmist koodi:
Alam CopyCell () vahemik ("A1"). Kopeerimise sihtkoht: = Vahemik ("D1") Lõpu alam
Ülaltoodud koodid kopeerivad ja kleepivad sellesse nii väärtuse kui ka vormingu/valemid.
Nagu olete juba märganud, kopeerib ülaltoodud kood lahtri seda valimata. Pole tähtis, kus te töölehel asute, kopeerib kood lahtri A1 ja kleepib selle D1 -le.
Samuti pange tähele, et ülaltoodud kood kirjutaks lahtris D2 olemasoleva koodi üle. Kui soovite, et Excel teavitaks teid, kui lahtris D1 on juba midagi üle kirjutamata, võite kasutada allolevat koodi.
Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Kas soovite olemasolevad andmed üle kirjutada", vbYesNo) End If If Response = vbYes then Range ("A1"). Copy Range ("D1 ") Lõpp kui lõpp Alam
Fikseeritud suurusega vahemiku kopeerimine
Kui soovite A1: D20 kopeerida J1: M20, saate kasutada järgmist koodi:
Sub CopyRange () vahemik ("A1: D20"). Copy Range ("J1") Lõpu alam
Sihtlahtris peate lihtsalt määrama ülemise vasaku lahtri aadressi. Kood kopeerib täpse kopeeritud vahemiku automaatselt sihtkohta.
Sama konstruktsiooni abil saate andmeid ühelt lehelt teisele kopeerida.
Allpool olev kood kopeeriks A1: D20 aktiivselt lehelt lehele2.
Sub CopyRange () vahemik ("A1: D20"). Töölehtede kopeerimine ("Sheet2"). Vahemik ("A1") Lõpu alam
Ülaltoodu kopeerib andmed aktiivselt lehelt. Seega veenduge enne koodi käivitamist, et andmetega leht on aktiivne leht. Turvalisuse huvides saate andmete kopeerimisel määrata ka töölehe nime.
Sub CopyRange () töölehed ("Sheet1"). Vahemik ("A1: D20"). Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Ülaltoodud koodi puhul on hea see, et olenemata sellest, milline leht on aktiivne, kopeerib see andmed alati lehelt 1 ja kleebib lehele2.
Nimega vahemikku saate kopeerida ka viite asemel selle nime kasutades.
Näiteks kui teil on nimega vahemik nimega „SalesData”, saate nende andmete lehele2 kopeerimiseks kasutada allolevat koodi.
Alam CopyRange () vahemik ("SalesData"). Töölehtede kopeerimine ("Sheet2"). Vahemik ("A1") Lõpu alam
Kui nimetatud vahemiku ulatus on kogu töövihik, ei pea te selle koodi käitamiseks olema lehel, millel on nimetatud vahemik. Kuna nimetatud vahemik on töövihiku ulatuses, saate seda koodi kasutades pääseda juurde mis tahes lehelt.
Kui teil on tabel nimega Table1, saate alloleva koodi abil selle lehele2 kopeerida.
Alamkopeerimistabeli () vahemik ("Tabel1 [#Kõik]"). Töölehtede kopeerimine ("Leht2"). Vahemik ("A1") Lõpualam
Vahemiku saate kopeerida ka teise töövihikusse.
Järgmises näites kopeerin Exceli tabeli (Tabel1) töövihikusse Book2.
Alamkoopia CopyCurrentRegion () vahemik ("Tabel1 [#Kõik]"). Töövihikute kopeerimine ("Raamat2.xlsx"). Töölehed ("Leht1"). Vahemik ("A1") Lõpu alam
See kood töötab ainult siis, kui töövihik on juba avatud.
Muutuva suurusega vahemiku kopeerimine
Üks võimalus muutuva suurusega vahemike kopeerimiseks on teisendada need nimega vahemikeks või Exceli tabeliks ning kasutada eelmises jaotises näidatud koode.
Aga kui te ei saa seda teha, võite kasutada vahemiku objekti atribuuti CurrentRegion või End.
Allpool olev kood kopeeriks aktiivse lehe praeguse piirkonna ja kleepiks selle lehele2.
AlamkoopiaCurrentRegion () vahemik ("A1"). CurrentRegion.Copy töölehed ("Sheet2"). Vahemik ("A1") Lõpu alam
Kui soovite oma andmekogumi esimese veeru kopeerida kuni viimase täidetud lahtrini ja kleepida selle lehele2, saate kasutada järgmist koodi:
Sub CopyCurrentRegion () Vahemik ("A1", vahemik ("A1"). Lõpp (xlDown)). Töölehtede kopeerimine ("Sheet2"). Vahemik ("A1") Lõpu alam
Kui soovite ridu ja veerge kopeerida, võite kasutada järgmist koodi:
Alamkoopia CopyCurrentRegion () vahemik ("A1", vahemik ("A1"). Lõpp (xlDown). Lõpp (xlToRight)). Töölehtede kopeerimine ("leht 2"). Vahemik ("A1") Lõpualam
Pange tähele, et kõik need koodid ei vali täitmise ajal lahtreid. Üldiselt leiate vaid käputäis juhtumeid, kus peate tegelikult lahtri/vahemiku enne selle kallal töötamist valima.
Vahemike määramine objektimuutujatele
Siiani oleme kasutanud lahtrite täielikku aadressi (näiteks töövihikud (“Book2.xlsx”). Töölehed (“Sheet1”). Vahemik (“A1”)).
Koodi paremini hallatavaks muutmiseks saate need vahemikud määrata objekti muutujatele ja seejärel neid muutujaid kasutada.
Näiteks olen allolevas koodis määranud objekti muutujatele lähte- ja sihtvahemiku ning kasutanud neid muutujaid andmete kopeerimiseks ühest vahemikust teise.
Sub CopyRange () Dim SourceRange kui vahemik Dim DestinationRange As Range Määra SourceRange = Töölehed ("Sheet1"). Vahemik ("A1: D20") Set DestinationRange = Töölehed ("Sheet2"). Vahemik ("A1") SourceRange.Copy DestinationRange End Sub
Alustuseks kuulutame muutujad vahemiku objektideks. Seejärel määrame neile muutujatele vahemiku, kasutades lauset Set. Kui vahemik on muutujale määratud, saate seda lihtsalt kasutada.
Sisestage andmed järgmisesse tühja lahtrisse (sisestuskasti abil)
Saate kasutada sisestusvälju, et lubada kasutajal andmeid sisestada.
Oletame näiteks, et teil on allpool olev andmekogum ja soovite müügikirje sisestada, saate kasutada VBA sisendkasti. Koodi kasutades saame veenduda, et see täidab järgmise tühja rea andmed.
Sub EnterData () Dim RefRange vahemikuna Määra RefRange = Vahemik ("A1"). End (xlDown). Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2) ) Set Amount = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Tootekategooria") Quantity.Value = InputBox ("Kogus") Amount.Value = InputBox ("Amount") End Sub
Ülaltoodud kood kasutab kasutajatelt sisendite saamiseks VBA sisendkasti ja seejärel sisestab sisendid määratud lahtritesse.
Pange tähele, et me ei kasutanud täpseid lahtriviiteid. Selle asemel oleme viimase tühja lahtri leidmiseks ja selles olevate andmete täitmiseks kasutanud omadust Lõpp ja nihe.
See kood pole kaugeltki kasutatav. Näiteks kui sisestate tekstistringi, kui sisestuskast küsib kogust või summat, märkate, et Excel seda võimaldab. Kui kasutate tingimust If, saate kontrollida, kas väärtus on numbriline või mitte, ja lubada see vastavalt.
Looping läbi rakkude / vahemike
Siiani oleme näinud, kuidas andmeid valida, kopeerida ja sisestada lahtritesse ja vahemikku.
Selles jaotises näeme, kuidas loopida vahemikus olevate lahtrite/ridade/veergude komplekti. See võib olla kasulik, kui soovite igat lahtrit analüüsida ja selle põhjal mõnda toimingut teha.
Näiteks kui soovite valiku iga kolmanda rea esile tõsta, peate silmuse läbi vaatama ja rea numbrit kontrollima. Samamoodi, kui soovite esile tuua kõik negatiivsed lahtrid, muutes fondi värvi punaseks, peate iga lahtri väärtuse läbi vaatama ja analüüsima.
Siin on kood, mis liigub valitud lahtrite ridadest läbi ja tõstab esile alternatiivsed read.
Sub HighlightAlternateRows () Dim Myrange kui vahemik Dim Myrow kui vahemik Määra Myrange = Valik iga Myrow jaoks Myrange'is. Ridad kui Myrow.Rida Mod 2 = 0 Siis Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Ülaltoodud kood kasutab funktsiooni MOD, et kontrollida valiku reanumbrit. Kui rea number on paaris, tõstetakse see esile tsüaanvärviga.
Siin on veel üks näide, kus kood läbib iga lahtri ja tõstab esile lahtrid, millel on negatiivne väärtus.
Sub HighlightAlternateRows () Dim Myrange kui vahemik Dim Mycell As Range Set Myrange = Valik iga Mycell'i jaoks Myrange'is Kui Mycell <0 Siis Mycell.Interior.Color = vbPunane lõpp, kui järgmine Mycell End Sub
Pange tähele, et saate sama teha tingimusvormingu abil (mis on dünaamiline ja parem viis seda teha). Selle näite eesmärk on näidata teile, kuidas silmuste loopimine VBA lahtrite ja vahemikega toimib.
Kuhu panna VBA kood
Huvitav, kuhu läheb teie Exceli töövihikus VBA -kood?
Excelil on VBA taustaprogramm, mida nimetatakse VBA redaktoriks. Peate koodi kopeerima ja kleepima VB Editori mooduli koodi aknasse.
Selleks toimige järgmiselt.
- Minge vahekaardile Arendaja.
- Klõpsake suvandil Visual Basic. See avab taustaprogrammis VB redaktori.
- Paremklõpsake VB redaktori Project Exploreri paanil töövihiku mis tahes objekti, kuhu soovite koodi sisestada.Kui te ei näe Project Explorerit, minge vahekaardile Vaade ja klõpsake nuppu Project Explorer.
- Minge sisesta ja klõpsake moodulit. See lisab teie töövihikusse mooduliobjekti.
- Kopeerige ja kleepige kood mooduli aknasse.
