Práca s bunkami a rozsahmi v programe Excel VBA (výber, kopírovanie, presúvanie, úpravy)

Pri práci s Excelom trávite väčšinu času v oblasti pracovného hárka - práci s bunkami a rozsahmi.

A ak chcete automatizovať svoju prácu v Exceli pomocou VBA, musíte vedieť, ako pracovať s bunkami a rozsahmi pomocou VBA.

S rozsahmi vo VBA môžete vykonávať množstvo rôznych vecí (napríklad výber, kopírovanie, presúvanie, úpravy atď.).

Aby som teda pokryl túto tému, rozdelím tento návod na sekcie a pomocou príkladov vám ukážem, ako pracovať s bunkami a rozsahmi v programe Excel VBA.

Začnime.

Všetky kódy, ktoré spomínam v tomto návode, je potrebné vložiť do editora VB. Prejdite do sekcie „Kam umiestniť kód VBA“ a zistite, ako funguje.

Ak máte záujem naučiť sa VBA jednoduchým spôsobom, pozrite sa na moje Online školenie Excel VBA.

Výber bunky / rozsahu v programe Excel pomocou VBA

Ak chcete pracovať s bunkami a rozsahmi v programe Excel pomocou jazyka VBA, nemusíte ho vyberať.

Vo väčšine prípadov je lepšie nevyberať bunky alebo rozsahy (ako uvidíme).

Napriek tomu je dôležité, aby ste si prešli túto časť a pochopili, ako funguje. Toto bude mať zásadný význam pre vaše učenie sa VBA a v tomto návode sa použije veľa tu uvedených konceptov.

Začnime teda veľmi jednoduchým príkladom.

Výber jednej bunky pomocou VBA

Ak chcete vybrať jednu bunku v aktívnom hárku (povedzme A1), môžete použiť nasledujúci kód:

Sub SelectCell () Range ("A1"). Select End Sub

Vyššie uvedený kód má povinnú časť „Sub“ a „End Sub“ a riadok kódu, ktorý vyberá bunku A1.

Rozsah („A1“) hovorí VBA adresu bunky, na ktorú sa chceme odkazovať.

Vyberte je metóda objektu Range a vyberá bunky/rozsah špecifikovaný v objekte Range. Odkazy na bunky je potrebné uzavrieť do dvojitých úvodzoviek.

Tento kód by ukázal chybu v prípade, že je hárok grafu aktívny. Hárok s grafom obsahuje grafy a nie je široko používaný. Pretože v ňom nie sú bunky/rozsahy, vyššie uvedený kód ho nemôže vybrať a v konečnom dôsledku sa zobrazí chyba.

Upozorňujeme, že keďže chcete vybrať bunku v aktívnom hárku, stačí zadať adresu bunky.

Ak však chcete vybrať bunku v inom hárku (povedzme List2), musíte najskôr aktivovať List2 a potom v ňom vybrať bunku.

Vedľajšie pracovné hárky SelectCell () („List2“). Aktivujte rozsah („A1“). Vyberte Koniec

Podobne môžete tiež aktivovať zošit, potom v ňom aktivovať konkrétny pracovný hárok a potom vybrať bunku.

Sub SelectCell () zošity ("Book2.xlsx"). Pracovné listy ("Sheet2"). Aktivujte rozsah ("A1"). Vyberte End Sub 

Všimnite si toho, že keď sa odvolávate na zošity, musíte použiť celé meno spolu s príponou súboru (.xlsx vo vyššie uvedenom kóde). V prípade, že zošit nebol nikdy uložený, nemusíte používať príponu súboru.

Teraz tieto príklady nie sú veľmi užitočné, ale neskôr v tomto návode uvidíte, ako môžeme použiť rovnaké koncepty na kopírovanie a vkladanie buniek v programe Excel (pomocou VBA).

Rovnako ako vyberáme bunku, môžeme vybrať aj rozsah.

V prípade rozsahu to môže byť rozsah pevnej veľkosti alebo rozsah variabilnej veľkosti.

V rozsahu pevných veľkostí by ste vedeli, aký veľký rozsah je, a presnú veľkosť môžete použiť vo svojom kóde VBA. Ale s rozsahom s premenlivou veľkosťou nemáte ani potuchy, aký veľký rozsah je, a musíte použiť trochu mágie VBA.

Pozrime sa, ako to urobiť.

Výber rozsahu s pevnou veľkosťou

Tu je kód, ktorý vyberie rozsah A1: D20.

Rozsah subvýberu () („A1: D20“). Vyberte položku End Sub 

Ďalším spôsobom, ako to dosiahnuť, je použiť nasledujúci kód:

Rozsah čiastkového výberu () („A1“, „D20“). Vyberte položku End Sub

Vyššie uvedený kód vezme adresu bunky vľavo hore (A1) a adresu bunky vpravo dole (D20) a vyberie celý rozsah. Táto technika sa stáva užitočnou, keď pracujete s rozsahmi rôznej veľkosti (ako uvidíme, keď sa vlastnosť End bude zaoberať neskôr v tomto návode).

Ak chcete, aby sa výber uskutočnil v inom zošite alebo inom pracovnom hárku, musíte VBA povedať presné názvy týchto objektov.

Nasledujúci kód napríklad vyberie rozsah A1: D20 v pracovnom hárku listu 2 v zošite Book2.

Sub SelectRange () zošity ("Book2.xlsx"). Pracovné listy ("Sheet1"). Aktivujte rozsah ("A1: D20"). Vyberte End Sub

Teraz, čo keď neviete, koľko riadkov je. Čo keď chcete vybrať všetky bunky, ktoré majú v sebe hodnotu.

V týchto prípadoch musíte použiť metódy uvedené v nasledujúcej časti (pri výbere rozsahu premenlivej veľkosti).

Výber rozsahu s premenlivou veľkosťou

Rozsah buniek môžete vybrať rôznymi spôsobmi. Metóda, ktorú vyberiete, bude závisieť od štruktúry údajov.

V tejto časti sa budem zaoberať niekoľkými užitočnými technikami, ktoré sú skutočne užitočné pri práci s rozsahmi vo VBA.

Vyberte položku Používanie vlastnosti CurrentRange

V prípadoch, keď neviete, koľko riadkov/stĺpcov obsahuje údaje, môžete použiť vlastnosť CurrentRange objektu Range.

Vlastnosť CurrentRange pokrýva všetky susediace vyplnené bunky v dátovom rozsahu.

Nasleduje kód, ktorý vyberie aktuálnu oblasť, ktorá obsahuje bunku A1.

Sub SelectCurrentRegion () Range ("A1"). CurrentRegion.Select End Sub

Vyššie uvedená metóda je dobrá, keď máte všetky údaje ako tabuľku bez prázdnych riadkov/stĺpcov.

Ale v prípade, že máte vo svojich údajoch prázdne riadky/stĺpce, nevyberie tie za prázdnymi riadkami/stĺpcami. Na obrázku nižšie kód CurrentRegion vyberá údaje do riadka 10, pretože riadok 11 je prázdny.

V takýchto prípadoch môžete použiť vlastnosť UsedRange objektu hárka.

Vyberte položku Používanie vlastnosti UsedRange

UsedRange vám umožňuje odkazovať na všetky bunky, ktoré boli zmenené.

Nasledujúci kód by teda vybral všetky použité bunky v aktívnom hárku.

Sub SelectUsedRegion () ActiveSheet.UsedRange. Vyberte End Sub

Všimnite si toho, že v prípade, že máte vzdialenú bunku, ktorá bola použitá, bude to zohľadnené vyššie uvedeným kódom a budú vybrané všetky bunky, kým nebude použitá bunka.

Vyberte možnosť Použiť koncovú vlastnosť

Teraz je táto časť skutočne užitočná.

Vlastnosť Koniec vám umožňuje vybrať poslednú vyplnenú bunku. To vám umožní napodobniť účinok klávesov so šípkou nadol/hore alebo ovládania doprava/doľava.

Skúsme to pochopiť na príklade.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete rýchlo vybrať posledné vyplnené bunky v stĺpci A.

Problém je v tom, že údaje sa môžu meniť a vy neviete, koľko buniek je vyplnených. Ak to musíte urobiť pomocou klávesnice, môžete vybrať bunku A1 a potom použiť kombináciu klávesov Control + šípka nadol a vyberie poslednú vyplnenú bunku v stĺpci.

Teraz sa pozrime, ako to urobiť pomocou VBA. Táto technika je užitočná, keď chcete rýchlo skočiť na poslednú vyplnenú bunku v stĺpci s premennou veľkosťou

Sub GoToLastFilledCell () Range ("A1"). End (xlDown). Vyberte End Sub

Vyššie uvedený kód by preskočil na poslednú vyplnenú bunku v stĺpci A.

Podobne môžete End (xlToRight) použiť na skok na poslednú vyplnenú bunku v rade.

Sub GoToLastFilledCell () Range ("A1"). End (xlToRight). Vyberte End Sub

Teraz, čo keď chcete namiesto preskočenia na poslednú vyplnenú bunku vybrať celý stĺpec.

Môžete to urobiť pomocou nižšie uvedeného kódu:

Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Select End Sub

Vo vyššie uvedenom kóde sme použili prvý a posledný odkaz na bunku, ktorú musíme vybrať. Bez ohľadu na to, koľko vyplnených buniek je, vyššie uvedený kód vyberie všetky.

Nezabudnite na vyššie uvedený príklad, kde sme vybrali rozsah A1: D20 pomocou nasledujúceho riadku kódu:

Rozsah („A1“, „D20“)

Tu bola A1 bunka vľavo hore a D20 bola vpravo dole bunka v rozsahu. Rovnakú logiku môžeme použiť aj pri výbere rozsahov premennej veľkosti. Pretože však nepoznáme presnú adresu bunky v pravom dolnom rohu, použili sme ju na získanie vlastnosti End.

V rozsahu („A1“, rozsah („A1“). Koniec (xlDown)), „A1“ označuje prvú bunku a rozsah („A1“). Koniec (xlDown) označuje poslednú bunku. Pretože sme poskytli obe referencie, metóda Select vyberie všetky bunky medzi týmito dvoma referenciami.

Podobne môžete vybrať aj celú množinu údajov, ktorá má viac riadkov a stĺpcov.

Nasledujúci kód by vybral všetky vyplnené riadky/stĺpce od bunky A1.

Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Select End Sub

Vo vyššie uvedenom kóde sme použili Range („A1“). End (xlDown) .End (xlToRight) na získanie odkazu na bunku súboru údajov vyplnenú vpravo dole.

Rozdiel medzi použitím CurrentRegion a End

Ak sa zaujímate, prečo používať vlastnosť Koniec na výber vyplneného rozsahu, keď máme vlastnosť CurrentRegion, dovoľte mi povedať vám rozdiel.

S vlastnosťou End môžete určiť počiatočnú bunku. Ak máte napríklad údaje vo formáte A1: D20, ale prvý riadok sú hlavičky, môžete pomocou vlastnosti End vybrať údaje bez hlavičiek (pomocou nižšie uvedeného kódu).

Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown) .End (xlToRight)). Select End Sub

Ale CurrentRegion by automaticky vybral celý súbor údajov vrátane hlavičiek.

V tomto návode sme zatiaľ videli, ako sa na množstvo buniek odvolávať rôznymi spôsobmi.

Teraz sa pozrime na niekoľko spôsobov, ako môžeme tieto techniky skutočne použiť na vykonanie práce.

Kopírujte bunky / rozsahy pomocou VBA

Ako som už spomenul na začiatku tohto tutoriálu, výber bunky nie je potrebný na vykonanie akcií s ňou. V tejto sekcii uvidíte, ako kopírovať bunky a rozsahy bez toho, aby ste ich vybrali.

Začnime jednoduchým príkladom.

Kopírovanie jednej bunky

Ak chcete skopírovať bunku A1 a prilepiť ju do bunky D1, urobí to nasledujúci kód.

Rozsah čiastkových kópií () („A1“). Rozsah kopírovania („D1“) Koniec

Všimnite si toho, že metóda kopírovania objektu range skopíruje bunku (rovnako ako Ctrl +C) a vloží ju do určeného cieľa.

Vo vyššie uvedenom vzorovom kóde je cieľ určený v rovnakom riadku, kde používate metódu Kopírovať. Ak chcete, aby bol váš kód ešte čitateľnejší, môžete použiť nasledujúci kód:

Rozsah čiastkových kópiíCell () („A1“). Cieľ kopírovania: = Rozsah („D1“) Koncový podrad

Vyššie uvedené kódy skopírujú a prilepia hodnotu, ako aj formátovanie/vzorce do nej.

Ako ste si už mohli všimnúť, vyššie uvedený kód skopíruje bunku bez jej výberu. Bez ohľadu na to, kde sa v hárku nachádzate, kód skopíruje bunku A1 a prilepí ju na D1.

Upozorňujeme tiež, že vyššie uvedený kód prepíše akýkoľvek existujúci kód v bunke D2. Ak chcete, aby vám Excel oznámil, že v bunke D1 už niečo je, bez toho, aby ste to prepísali, môžete použiť kód nižšie.

Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Do you want to overwrite the existing data", vbYesNo) End If If Response = vbYes Then Range ("A1"). Copy Range ("D1 ") End If End Sub

Kopírovanie rozsahu s pevnou veľkosťou

Ak chcete skopírovať formát A1: D20 do formátu J1: M20, môžete použiť nasledujúci kód:

Rozsah čiastkového rozsahu kopírovania () („A1: D20“). Rozsah kopírovania („J1“) Koncový podrad

V cieľovej bunke stačí zadať adresu bunky vľavo hore. Kód by automaticky skopíroval presný skopírovaný rozsah do cieľa.

Na kopírovanie údajov z jedného listu do druhého môžete použiť rovnakú konštrukciu.

Nasledujúci kód by skopíroval A1: D20 z aktívneho hárku do Listu2.

Podradený rozsah CopyRange () ("A1: D20"). Kopírovanie pracovných listov ("List2"). Rozsah ("A1") Koncový podrad

Vyššie uvedené skopíruje údaje z aktívneho hárka. Pred spustením kódu sa preto uistite, že hárok s údajmi je aktívny. Aby ste boli v bezpečí, pri kopírovaní údajov môžete tiež zadať názov pracovného hárka.

Vedľajšie pracovné hárky CopyRange () („List1“). Rozsah („A1: D20“). Kopírujte pracovné listy („List2“). Rozsah („A1“) Koncový podrad

Dobrá vec na vyššie uvedenom kóde je, že bez ohľadu na to, ktorý hárok je aktívny, vždy skopíruje údaje z hárka1 a prilepí ich do hárka2.

Pomenovaný rozsah môžete skopírovať aj pomocou jeho názvu namiesto odkazu.

Ak máte napríklad pomenovaný rozsah s názvom „Údaje o predaji“, pomocou nižšie uvedeného kódu môžete skopírovať tieto údaje do hárku 2.

Podradený rozsah CopyRange () („Údaje o predaji“). Kopírovanie pracovných listov („List2“). Rozsah („A1“) Koncový podrad

Ak je rozsahom pomenovaného rozsahu celý zošit, na spustenie tohto kódu nemusíte byť v hárku, ktorý má pomenovaný rozsah. Pretože menovaný rozsah je rozsahom zošita, môžete k nemu získať prístup z akéhokoľvek listu pomocou tohto kódu.

Ak máte tabuľku s názvom Table1, môžete ju pomocou nižšie uvedeného kódu skopírovať do hárka2.

Rozsah čiastkovej kópie tabuľky () („Tabuľka1 [#All]“). Kopírovanie pracovných listov („List2“). Rozsah („A1“) Koniec

Rozsah môžete tiež skopírovať do iného zošita.

V nasledujúcom príklade skopírujem tabuľku programu Excel (tabuľka1) do zošita Book2.

Rozsah Sub CopyCurrentRegion () („Tabuľka1 [#All]“). Kopírovanie zošitov („Book2.xlsx“). Pracovné listy („List1“). Rozsah („A1“) Koncový podrad

Tento kód by fungoval, iba ak je zošit už otvorený.

Kopírovanie rozsahu s premenlivou veľkosťou

Jeden zo spôsobov, ako kopírovať rozsahy s premenlivou veľkosťou, je previesť ich na pomenované rozsahy alebo tabuľku programu Excel a použiť kódy uvedené v predchádzajúcej časti.

Ak to však nemôžete urobiť, môžete použiť vlastnosť CurrentRegion alebo End objektu rozsahu.

Nasledujúci kód by skopíroval aktuálnu oblasť do aktívneho hárka a vložil by ju do hárka2.

Sub CopyCurrentRegion () Range ("A1"). CurrentRegion.Copy worksheets ("Sheet2"). Range ("A1") End Sub

Ak chcete skopírovať prvý stĺpec súboru údajov do poslednej vyplnenej bunky a prilepiť ho do hárka 2, môžete použiť nasledujúci kód:

Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Copy worksheets ("Sheet2"). Range ("A1") End Sub

Ak chcete skopírovať riadky aj stĺpce, môžete použiť nasledujúci kód:

Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Copy worksheets ("Sheet2"). Range ("A1") End Sub

Upozorňujeme, že všetky tieto kódy nevyberajú bunky pri spustení. Vo všeobecnosti nájdete iba niekoľko prípadov, keď skutočne potrebujete vybrať bunku/rozsah, než na nej budete pracovať.

Priradenie rozsahov objektovým premenným

Doteraz používame úplnú adresu buniek (napríklad zošity („Book2.xlsx“). Pracovné listy („list1“). Rozsah („A1“)).

Aby bol váš kód lepšie spravovateľný, môžete tieto rozsahy priradiť k objektovým premenným a potom ich použiť.

V nižšie uvedenom kóde som napríklad priradil zdrojový a cieľový rozsah k objektovým premenným a potom som pomocou týchto premenných skopíroval údaje z jedného rozsahu do druhého.

Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets ("Sheet1") Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2") Range ("A1") SourceRange.Copy DestinationRange Koniec pod

Začneme vyhlásením premenných ako objektov Range. Potom týmto premenným priradíme rozsah pomocou príkazu Set. Keď je premennej priradený rozsah, môžete premennú jednoducho použiť.

Zadajte údaje do ďalšej prázdnej bunky (pomocou vstupného poľa)

Pomocou vstupných políčok môžete používateľovi zadať údaje.

Predpokladajme napríklad, že máte nižšie uvedený súbor údajov a chcete zadať záznam o tržbách, môžete použiť vstupné pole vo VBA. Pomocou kódu sa môžeme uistiť, že vyplní údaje v nasledujúcom prázdnom riadku.

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ť množstvo = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Hodnota + 1 ProductCategory.Value = InputBox ("Kategória produktu") Quantity.Value = InputBox ("Množstvo") Amount.Value = InputBox ("Suma") End Sub

Vyššie uvedený kód používa vstupné pole VBA na získanie vstupov od používateľa a potom vstupy zadáva do určených buniek.

Všimnite si toho, že sme nepoužili presné odkazy na bunky. Namiesto toho sme použili vlastnosť Koniec a Ofset na nájdenie poslednej prázdnej bunky a vyplnenie údajov v nej.

Tento kód nie je ani zďaleka použiteľný. Ak napríklad zadáte textový reťazec, keď sa vo vstupnom poli pýta množstvo alebo množstvo, všimnete si, že to Excel umožňuje. Podmienku If môžete použiť na kontrolu, či je hodnota číselná alebo nie, a potom ju podľa toho povoliť.

Smyčka cez bunky / rozsahy

Doteraz sme videli, ako vyberať, kopírovať a zadávať údaje do buniek a rozsahov.

V tejto sekcii uvidíme, ako prechádzať sadou buniek/riadkov/stĺpcov v rozsahu. To môže byť užitočné, keď chcete analyzovať každú bunku a na základe nej vykonať nejakú akciu.

Ak napríklad chcete vo výbere zvýrazniť každý tretí riadok, musíte sa v ňom opakovať a vyhľadať číslo riadka. Podobne, ak chcete zvýrazniť všetky negatívne bunky zmenou farby písma na červenú, musíte prejsť a analyzovať hodnotu každej bunky.

Tu je kód, ktorý bude prechádzať riadkami vo vybraných bunkách a zvýrazní alternatívne riadky.

Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow as Range Set Myrange = Selection for each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

Vyššie uvedený kód používa funkciu MOD na kontrolu čísla riadka vo výbere. Ak je číslo riadka párne, zvýrazní sa azúrovou farbou.

Tu je ďalší príklad, kde kód prechádza každou bunkou a zvýrazňuje bunky, ktoré majú zápornú hodnotu.

Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Selection for each Mycell In Myrange If Mycell <0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub

Všimnite si toho, že to isté môžete urobiť pomocou podmieneného formátovania (ktoré je dynamické a je to lepší spôsob). Tento príklad slúži iba na to, aby vám ukázal, ako funguje slučka s bunkami a rozsahmi vo VBA.

Kam vložiť kód VBA

Zaujíma vás, kam smeruje kód VBA vo vašom zošite programu Excel?

Excel má backend VBA nazývaný editor VBA. Kód musíte skopírovať a vložiť do okna kódu modulu VB Editor.

Tu sú kroky, ako to urobiť:

1. Prejdite na kartu Vývojár.
2. Kliknite na možnosť Visual Basic. Tým sa otvorí editor VB v backende.
3. Na table Project Explorer v editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, do ktorého chcete vložiť kód.Ak sa vám Project Explorer nezobrazuje, prejdite na kartu Zobraziť a kliknite na Project Explorer.
4. Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do vášho zošita.
5. Skopírujte a prilepte kód do okna modulu.