Amikor Excel -sel dolgozik, a legtöbb időt a munkalap területén tölti - cellákkal és tartományokkal foglalkozik.
Ha pedig VBA segítségével szeretné automatizálni az Excelben végzett munkáját, tudnia kell, hogyan kell dolgozni a cellákkal és tartományokkal a VBA használatával.
A VBA tartományokkal sokféle dolgot végezhet (például kiválasztás, másolás, áthelyezés, szerkesztés stb.).
Tehát ennek a témának a lefedése érdekében ezt az oktatóanyagot szakaszokra bontom, és példák segítségével megmutatom, hogyan kell dolgozni a cellákkal és tartományokkal az Excel VBA -ban.
Kezdjük el.
Az ebben az oktatóanyagban említett összes kódot el kell helyezni a VB szerkesztőbe. Lépjen a „Hová tegye a VBA -kódot” szakaszhoz, hogy megtudja, hogyan működik.Ha érdekli a VBA egyszerű tanulása, nézze meg az enyémet Online Excel VBA képzés.
Cella / tartomány kiválasztása Excelben VBA használatával
Ha az Excel celláival és tartományaival szeretne dolgozni a VBA használatával, nem kell kiválasztania.
A legtöbb esetben jobb, ha nem választ ki cellákat vagy tartományokat (amint látni fogjuk).
Ennek ellenére fontos, hogy nézze át ezt a részt, és értse meg, hogyan működik. Ez döntő fontosságú lesz a VBA -tanulásban, és az itt leírt sok fogalmat fogják használni ebben az oktatóanyagban.
Kezdjük tehát egy nagyon egyszerű példával.
Egyetlen cella kiválasztása a VBA segítségével
Ha egyetlen cellát szeretne kijelölni az aktív lapon (mondjuk A1), akkor használhatja az alábbi kódot:
Sub SelectCell () Tartomány ("A1")
A fenti kódnak kötelező a „Sub” és „End Sub” része, valamint egy kódsora, amely az A1 cellát választja ki.
A tartomány („A1”) megmondja a VBA -nak a cella címét, amelyre hivatkozni akarunk.
Válassza a lehetőséget a Range objektum metódusa, és kiválasztja a Range objektumban megadott cellákat/tartományokat. A cellahivatkozásokat idézőjelbe kell tenni.
Ez a kód hibát mutat, ha a diagramlap aktív lap. A diagramlap diagramokat tartalmaz, és nem széles körben használják. Mivel nem tartalmaz cellákat/tartományokat, a fenti kód nem tudja kiválasztani, és végül hibát jelenít meg.
Ne feledje, hogy mivel ki szeretné választani a cellát az aktív lapon, csak meg kell adnia a cella címét.
De ha ki szeretné választani a cellát egy másik lapon (mondjuk a 2. lapon), akkor először aktiválnia kell a 2. lapot, majd ki kell választania a cellát.
Sub SelectCell () Munkalapok ("Sheet2"). Aktiválja a tartományt ("A1"). Válassza ki az End Sub lehetőséget
Hasonló módon aktiválhat egy munkafüzetet, majd aktiválhat benne egy adott munkalapot, majd kiválaszthat egy cellát.
Sub SelectCell () Munkafüzetek ("Book2.xlsx"). Munkalapok ("Sheet2"). Aktiválja a tartományt ("A1"). Válassza ki az End Sub lehetőséget
Ne feledje, hogy a munkafüzetekre való hivatkozáskor a teljes nevet kell használni a fájlkiterjesztéssel együtt (.xlsx a fenti kódban). Ha a munkafüzetet soha nem mentette el, akkor nem kell a fájlkiterjesztést használni.
Ezek a példák most nem túl hasznosak, de az oktatóanyag későbbi részében látni fogjuk, hogyan használhatjuk ugyanazokat a fogalmakat a cellák Excelben történő másolására és beillesztésére (VBA használatával).
Ahogy kiválasztunk egy cellát, kiválaszthatunk egy tartományt is.
Tartomány esetén ez lehet fix mérettartomány vagy változó mérettartomány.
Rögzített mérettartományban tudja, hogy mekkora a tartomány, és használhatja a pontos méretet a VBA -kódban. A változó méretű tartományoknál azonban fogalma sincs arról, hogy mekkora a tartomány, és egy kis VBA varázslatot kell használnia.
Lássuk, hogyan kell ezt megtenni.
Fix mérettartomány kiválasztása
Itt található az A1: D20 tartományt kiválasztó kód.
Sub SelectRange () tartomány ("A1: D20"). Válassza ki a Sub Sub befejezését
Ennek másik módja az alábbi kód használata:
Sub SelectRange () tartomány ("A1", "D20")
A fenti kód felveszi a bal felső cellacímet (A1) és a jobb alsó cellacímet (D20), és kiválasztja a teljes tartományt. Ez a technika akkor válik hasznossá, ha változó méretű tartományokkal dolgozik (amint ezt látni fogjuk, amikor a Befejezés tulajdonsággal foglalkozunk ebben az oktatóanyagban).
Ha azt szeretné, hogy a kijelölés egy másik munkafüzetben vagy egy másik munkalapon történjen, akkor meg kell adnia a VBA -nak ezen objektumok pontos nevét.
Például az alábbi kód az A1: D20 tartományt választja ki a Book2 munkafüzet Sheet2 munkalapján.
Sub SelectRange () Munkafüzetek ("Book2.xlsx"). Munkalapok ("Sheet1"). Aktiválja a tartományt ("A1: D20"). Válassza ki az End Sub lehetőséget
Mi van akkor, ha nem tudja, hány sor van. Mi van, ha ki akarja választani az összes cellát, amelyek értékkel rendelkeznek.
Ezekben az esetekben a következő részben (a változó méretű tartomány kiválasztásáról) ismertetett módszereket kell használnia.
Változó méretű tartomány kiválasztása
Különböző módokon választhat ki cellatartományt. A választott módszer az adatok felépítésétől függ.
Ebben a részben néhány hasznos technikát ismertetek, amelyek igazán hasznosak, ha tartományokkal dolgozik a VBA -ban.
Válassza a CurrentRange tulajdonság használata lehetőséget
Azokban az esetekben, amikor nem tudja, hány sorban/oszlopban vannak adatok, használhatja a Range objektum CurrentRange tulajdonságát.
A CurrentRange tulajdonság lefedi az adattartomány összes szomszédos kitöltött celláját.
Az alábbiakban az a kód található, amely kiválasztja az A1 régiót tartalmazó aktuális régiót.
Sub SelectCurrentRegion () tartomány ("A1"). CurrentRegion.Select End Sub
A fenti módszer akkor jó, ha minden adat táblázatként van, üres sorok/oszlopok nélkül.
De ha üres sorok/oszlopok vannak az adataiban, akkor nem választja ki az üres sorok/oszlopok utáni sorokat. Az alábbi képen a CurrentRegion kód a 10. sorig választja ki az adatokat, mivel a 11. sor üres.
Ilyen esetekben érdemes használni a Munkalap Objektum UsedRange tulajdonságát.
Válassza a UsedRange tulajdonság használata lehetőséget
A UsedRange lehetővé teszi, hogy hivatkozzon a módosított cellákra.
Tehát az alábbi kód kiválasztja az összes használt cellát az aktív lapon.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Válassza ki az End Sub lehetőséget
Ne feledje, hogy ha van egy távoli cellája, amelyet használt, akkor a fenti kód figyelembe veszi, és az összes cellát kiválasztja, amíg a használt cellát ki nem választja.
Válassza a Végtulajdonság használata lehetőséget
Most ez a rész nagyon hasznos.
A Vége tulajdonság segítségével kiválaszthatja az utoljára kitöltött cellát. Ez lehetővé teszi, hogy utánozza a Control le/fel nyílgomb vagy a Control jobb/bal gombok hatását.
Próbáljuk meg megérteni ezt egy példa segítségével.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és gyorsan ki akarja választani az A oszlopban utoljára kitöltött cellákat.
A probléma itt az, hogy az adatok változhatnak, és nem tudja, hány cellát tölt ki. Ha ezt billentyűzet használatával kell elvégeznie, akkor válassza ki az A1 cellát, majd nyomja meg a Control + lefelé mutató nyílbillentyűket, és ez kiválasztja az oszlopban utoljára kitöltött cellát.
Most nézzük meg, hogyan kell ezt megtenni a VBA segítségével. Ez a technika jól jön, ha gyorsan szeretne ugrani a változó méretű oszlop utolsó töltött cellájába
Sub GoToLastFilledCell () tartomány ("A1"). End (xlDown). Válassza ki a End Sub lehetőséget
A fenti kód az A oszlop utolsó kitöltött cellájára ugrik.
Hasonlóképpen a Vége (xlToRight) segítségével ugorhat a sor utolsó töltött cellájára.
Sub GoToLastFilledCell () tartomány ("A1"). End (xlToRight).
Mi van akkor, ha a teljes oszlopot szeretné kiválasztani ahelyett, hogy az utoljára kitöltött cellára ugrik.
Ezt az alábbi kód használatával teheti meg:
Sub SelectFilledCells () tartomány ("A1", tartomány ("A1"). End (xlDown)).
A fenti kódban a kiválasztandó cella első és utolsó hivatkozását használtuk. Függetlenül attól, hogy hány kitöltött cella van, a fenti kód az összeset kijelöli.
Emlékezzen a fenti példára, ahol az A1: D20 tartományt választottuk a következő kódsor használatával:
Tartomány („A1”, „D20”)
Itt A1 volt a bal felső cella, a D20 pedig a jobb alsó cella a tartományban. Ugyanezt a logikát alkalmazhatjuk a változó méretű tartományok kiválasztásakor. De mivel nem tudjuk a jobb alsó cella pontos címét, ezért az End tulajdonságot használtuk annak megszerzésére.
A tartomány („A1”, tartomány („A1”). Vége (xlDown)), „A1” az első cellára és a tartomány („A1”) kifejezésre vonatkozik. Mivel mindkét hivatkozást megadtuk, a Kiválasztás módszer kiválasztja az összes cellát a két hivatkozás között.
Hasonlóképpen kiválaszthat egy teljes adathalmazt is, amely több sort és oszlopot tartalmaz.
Az alábbi kód az összes kitöltött sort/oszlopot az A1 cellából kiindulva választaná ki.
Sub SelectFilledCells () tartomány
A fenti kódban a Range („A1”) értéket használtuk. End (xlDown) .End (xlToRight), hogy lekérjük az adathalmaz jobb alsó sarkában lévő cella hivatkozását.
Különbség a CurrentRegion és a End használata között
Ha kíváncsi rá, miért használja a Vége tulajdonságot a kitöltött tartomány kiválasztásához, ha rendelkezünk a CurrentRegion tulajdonsággal, hadd mondjam el a különbséget.
A Vége tulajdonság segítségével megadhatja a kezdő cellát. Például, ha az adatai A1: D20 formátumban vannak, de az első sor fejléc, akkor a Vége tulajdonság segítségével kiválaszthatja az adatokat a fejlécek nélkül (az alábbi kód használatával).
Sub SelectFilledCells () tartomány ("A2", tartomány ("A2"). End (xlDown) .End (xlToRight)).
De a CurrentRegion automatikusan kiválasztja a teljes adatkészletet, beleértve a fejléceket is.
Ebben az oktatóanyagban eddig láthattuk, hogyan lehet különböző módszerekkel hivatkozni egy cellatartományra.
Most nézzünk néhány módszert, ahol ténylegesen felhasználhatjuk ezeket a technikákat bizonyos munkák elvégzésére.
Cella / tartomány másolása a VBA segítségével
Amint az oktatóanyag elején említettem, a cellák kiválasztása nem szükséges a műveletek végrehajtásához. Ebben a szakaszban látni fogja, hogyan másolhat cellákat és tartományokat anélkül, hogy ezeket is kijelölné.
Kezdjük egy egyszerű példával.
Egycellás másolás
Ha át szeretné másolni az A1 cellát és beilleszteni a D1 cellába, akkor az alábbi kód teszi ezt.
Sub CopyCell () tartomány ("A1"). Másolási tartomány ("D1") Vége al
Ne feledje, hogy a tartományobjektum másolási módszere másolja a cellát (csakúgy, mint a Control +C), és beilleszti a megadott helyre.
A fenti példakódban a cél ugyanazon a sorban van megadva, ahol a Másolás módszert használja. Ha még olvashatóbbá szeretné tenni a kódot, akkor használja az alábbi kódot:
Sub CopyCell () tartomány ("A1"). Másolási cél: = Tartomány ("D1") Végrész
A fenti kódok másolják és illesszék be az értéket, valamint a formázást/képleteket.
Amint azt már észrevehette, a fenti kód másolja a cellát anélkül, hogy kiválasztaná. Nem számít, hogy hol van a munkalapon, a kód másolja az A1 cellát, és illessze be a D1 -be.
Azt is vegye figyelembe, hogy a fenti kód felülírja a D2 cellában lévő meglévő kódokat. Ha azt szeretné, hogy az Excel értesítse, ha van valami a D1 cellában anélkül, hogy felülírná, akkor használja az alábbi kódot.
Sub CopyCell () If Range ("D1") "" then Response = MsgBox ("felül akarja írni a meglévő adatokat", vbYesNo) End If If Response = vbYes then Range ("A1"). Copy Range ("D1 ") End If End Sub
Fix méretű tartomány másolása
Ha az A1: D20 formátumot szeretné átmásolni J1: M20 formátumban, akkor használja az alábbi kódot:
Sub CopyRange () tartomány ("A1: D20"). Másolási tartomány ("J1") Végrész
A célcellában csak meg kell adnia a bal felső cella címét. A kód automatikusan másolja a pontos másolt tartományt a rendeltetési helyre.
Ugyanezzel a konstrukcióval másolhat adatokat egyik lapról a másikra.
Az alábbi kód másolja az A1: D20 -at az aktív lapról a Sheet2 -re.
Sub CopyRange () tartomány ("A1: D20"). Munkalapok másolása ("Sheet2"). Tartomány ("A1") Végrész
A fentiek másolják az adatokat az aktív lapról. Ezért a kód futtatása előtt győződjön meg arról, hogy az adatlap az aktív lap. A biztonság érdekében megadhatja a munkalap nevét is, miközben másolja az adatokat.
Sub CopyRange () Munkalapok ("Sheet1"). Tartomány ("A1: D20"). Copy Worksheets ("Sheet2"). Range ("A1") End Sub
A fenti kódban az a jó, hogy függetlenül attól, hogy melyik lap aktív, mindig másolja az adatokat a Sheet1 -ből, és illessze be a Sheet2 -be.
Másolhat egy elnevezett tartományt is, ha annak nevét használja a hivatkozás helyett.
Például, ha van egy „SalesData” nevű tartomány, akkor az alábbi kóddal másolhatja át ezeket az adatokat a Sheet2 -re.
Sub CopyRange () tartomány ("SalesData"). Munkalapok másolása ("Sheet2"). Tartomány ("A1") End Sub
Ha a megnevezett tartomány hatóköre a teljes munkafüzet, akkor nem kell azon a lapon lennie, amely a megnevezett tartományt tartalmazza a kód futtatásához. Mivel a megnevezett tartomány a munkafüzethez tartozik, ezt a kódot használva bármelyik lapról hozzáférhet.
Ha rendelkezik Table1 nevű táblával, akkor az alábbi kóddal másolhatja át a Sheet2 -re.
Sub CopyTable () tartomány ("Table1 [#All]"). Munkalapok másolása ("Sheet2"). Tartomány ("A1") End Sub
Egy tartományt másolhat egy másik munkafüzetbe is.
A következő példában az Excel táblázatot (1. táblázat) másolom a Book2 munkafüzetbe.
Sub CopyCurrentRegion () tartomány ("Table1 [#All]"). Munkafüzetek másolása ("Book2.xlsx"). Munkalapok ("Sheet1"). Tartomány ("A1") End Sub
Ez a kód csak akkor működik, ha a munkafüzet már nyitva van.
Változó méretű tartomány másolása
A változó méretű tartományok másolásának egyik módja, ha ezeket elnevezett tartományokká vagy Excel -táblává alakítja, és az előző részben látható kódokat használja.
De ha ezt nem tudja megtenni, használhatja a tartományobjektum CurrentRegion vagy End tulajdonságát.
Az alábbi kód másolja az aktuális régiót az aktív lapon, és illessze be a Sheet2 -be.
Sub CopyCurrentRegion () Tartomány ("A1"). CurrentRegion.Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Ha át szeretné másolni az adatkészlet első oszlopát az utolsó kitöltött cellába, és be szeretné illeszteni a Sheet2 -be, akkor használja az alábbi kódot:
Sub CopyCurrentRegion () Tartomány ("A1", Tartomány ("A1"). Vége (xlDown)). Munkalapok másolása ("Sheet2"). Tartomány ("A1") Vége
Ha a sorokat és az oszlopokat is másolni szeretné, akkor használja az alábbi kódot:
Sub CopyCurrentRegion () Tartomány ("A1", Tartomány ("A1"). Vége (xlDown). Vége (xlToRight)). Munkalapok másolása ("Sheet2"). Tartomány ("A1") Végrész
Ne feledje, hogy ezek a kódok nem választják ki a cellákat végrehajtás közben. Általában csak néhány olyan esetet talál, ahol ténylegesen ki kell választania egy cellát/tartományt, mielőtt dolgozni kezdene rajta.
Tartományok hozzárendelése az objektumváltozókhoz
Eddig a cellák teljes címét használtuk (például Munkafüzetek („Book2.xlsx”). Munkalapok („Sheet1”). Tartomány („A1”)).
A kód könnyebb kezelhetősége érdekében ezeket a tartományokat hozzárendelheti az objektumváltozókhoz, majd használhatja ezeket a változókat.
Például az alábbi kódban a forrás- és a céltartományt objektumváltozókhoz rendeltem, majd ezekkel a változókkal adatokat másoltam egyik tartományból a másikba.
Sub CopyRange () Dim SourceRange Mint tartomány Dim DestinationRange Mint range Set SourceRange = Worksheets ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange End Sub
Kezdjük azzal, hogy a változókat Range objektumként deklaráljuk. Ezután hozzárendeljük a tartományt ezekhez a változókhoz a Set utasítás segítségével. Miután a tartomány hozzá lett rendelve a változóhoz, egyszerűen használhatja a változót.
Adja meg a következő üres cellát (a beviteli mező használatával)
A Beviteli mezők segítségével engedélyezheti a felhasználónak az adatok bevitelét.
Tegyük fel például, hogy rendelkezik az alábbi adatkészlettel, és be szeretné írni az értékesítési rekordot, a VBA beviteli mezőjét használhatja. Egy kód használatával megbizonyosodhatunk arról, hogy az kitölti a következő üres sor adatait.
Sub EnterData () Dim RefRange mint tartomány Set RefRange = Range ("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 ("Termékkategória") Quantity.Value = InputBox ("Mennyiség") Amount.Value = InputBox ("Amount") End Sub
A fenti kód a VBA bemeneti mezőt használja a bemenetek beszerzésére a felhasználótól, majd a bemeneteket a megadott cellákba írja be.
Vegye figyelembe, hogy nem használtunk pontos cellahivatkozásokat. Ehelyett az End and Offset tulajdonságot használtuk az utolsó üres cella megkeresésére és a benne lévő adatok kitöltésére.
Ez a kód messze nem használható. Például, ha szöveges karakterláncot ír be, amikor a beviteli mezőben mennyiséget vagy összeget kér, észre fogja venni, hogy az Excel lehetővé teszi. Az If feltétellel ellenőrizheti, hogy az érték numerikus -e vagy sem, majd ennek megfelelően engedélyezi.
Looping keresztül cellák / tartományok
Eddig láthattuk, hogyan válasszuk ki, másoljuk és adjuk meg az adatokat cellákba és tartományokba.
Ebben a szakaszban látni fogjuk, hogyan lehet ciklusban végigmenni egy cellában/sorban/oszlopban. Ez akkor lehet hasznos, ha elemezni szeretné az egyes cellákat, és ezek alapján valamilyen műveletet szeretne végrehajtani.
Például, ha a kijelölés minden harmadik sorát ki szeretné emelni, akkor végig kell tekintenie és ellenőriznie kell a sorszámot. Hasonlóképpen, ha ki szeretné emelni az összes negatív cellát a betűtípus színének vörösre változtatásával, akkor át kell tekintenie és elemeznie kell az egyes cellák értékét.
Itt található az a kód, amely a kijelölt cellák soraiban végigmegy és kiemeli az alternatív sorokat.
Sub HighlightAlternateRows () Dim Myrange mint tartomány Dim Myrow mint tartomány Set Myrange = Selection For All Myrow in Myrange.Rows If Myrow.Row Mod 2 = 0 Majd Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
A fenti kód a MOD funkciót használja a kiválasztott sor számának ellenőrzésére. Ha a sorszám páros, ciánkék színű lesz.
Íme egy másik példa, ahol a kód minden cellán keresztül megy, és kiemeli azokat a cellákat, amelyek negatív értékkel rendelkeznek.
Sub HighlightAlternateRows () Dim Myrange mint tartomány Dim Mycell as Range Set Myrange = Kiválasztás minden Mycell -hez Myrange -ben Ha Mycell <0 Akkor Mycell.Interior.Color = vbVörös Vége, ha a következő Mycell End Sub
Ne feledje, hogy ugyanezt megteheti a Feltételes formázás használatával (ami dinamikus és jobb módja ennek). Ez a példa csak arra szolgál, hogy bemutassa, hogyan működik a hurok a cellákkal és tartományokkal a VBA -ban.
Hová tegye a VBA kódot
Kíváncsi vagy, hova kerül a VBA -kód az Excel -munkafüzetben?
Az Excel rendelkezik VBA háttérprogrammal, amelyet VBA -szerkesztőnek hívnak. Másolja és illessze be a kódot a VB Editor modul ablakába.
Ehhez tegye a következő lépéseket:
- Lépjen a Fejlesztő fülre.
- Kattintson a Visual Basic lehetőségre. Ez megnyitja a VB szerkesztőt a háttérben.
- A VB szerkesztő Project Explorer ablaktáblájában kattintson jobb gombbal a munkafüzet bármely olyan objektumára, amelybe be szeretné illeszteni a kódot.Ha nem látja a Project Explorer programot, lépjen a Nézet fülre, és kattintson a Project Explorer elemre.
- Lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ezzel beszúr egy modulobjektumot a munkafüzetébe.
- Másolja ki és illessze be a kódot a modul ablakába.
