Při práci s Excelem trávíte většinu času v oblasti listu - jednáním s buňkami a rozsahy.
A pokud chcete automatizovat svou práci v Excelu pomocí VBA, musíte vědět, jak pracovat s buňkami a rozsahy pomocí VBA.
S rozsahy ve VBA můžete dělat spoustu různých věcí (například výběr, kopírování, přesun, úpravy atd.).
Abych pokryl toto téma, rozdělím tento návod na sekce a ukážu vám, jak pracovat s buňkami a rozsahy v aplikaci Excel VBA pomocí příkladů.
Začněme.
Všechny kódy, které zmiňuji v tomto tutoriálu, je třeba umístit do editoru VB. Chcete -li zjistit, jak funguje, přejděte do části „Kam umístit kód VBA“.Pokud máte zájem naučit se VBA jednoduchým způsobem, podívejte se na můj Online školení Excel VBA.
Výběr buňky / rozsahu v aplikaci Excel pomocí VBA
Chcete -li pracovat s buňkami a rozsahy v aplikaci Excel pomocí VBA, nemusíte ji vybírat.
Ve většině případů je lepší nevybírat buňky nebo rozsahy (jak uvidíme).
Navzdory tomu je důležité projít si tuto sekci a pochopit, jak funguje. To bude klíčové ve vašem učení VBA a v tomto tutoriálu bude použito mnoho zde pojednaných konceptů.
Začněme tedy na velmi jednoduchém příkladu.
Výběr jedné buňky pomocí VBA
Pokud chcete vybrat jednu buňku v aktivním listu (řekněme A1), můžete použít následující kód:
Sub SelectCell () Range ("A1"). Select End Sub
Výše uvedený kód má povinnou část „Sub“ a „End Sub“ a řádek kódu, který vybírá buňku A1.
Rozsah („A1“) říká VBA adresu buňky, na kterou chceme odkazovat.
Vybrat je metoda objektu Range a vybírá buňky/rozsah určený v objektu Range. Odkazy na buňky je třeba uzavřít do uvozovek.
Tento kód by ukázal chybu v případě, že list grafu je aktivní list. List s grafy obsahuje grafy a není široce používán. Protože v něm nejsou buňky/rozsahy, výše uvedený kód jej nemůže vybrat a skončil by s chybou.
Všimněte si toho, protože chcete vybrat buňku v aktivním listu, stačí zadat adresu buňky.
Pokud však chcete vybrat buňku v jiném listu (řekněme List2), musíte nejprve aktivovat List2 a poté v ní vybrat buňku.
Dílčí listy SelectCell () („List2“). Aktivujte rozsah („A1“). Vyberte Konec Sub
Podobně můžete také aktivovat sešit, pak v něm aktivovat konkrétní list a poté vybrat buňku.
Sub SelectCell () Sešity ("Book2.xlsx"). Pracovní listy ("List2"). Aktivujte rozsah ("A1"). Vyberte Konec Sub
Všimněte si, že když se odkazujete na sešity, musíte použít úplné jméno spolu s příponou souboru (.xlsx ve výše uvedeném kódu). V případě, že sešit nebyl nikdy uložen, nemusíte používat příponu souboru.
Nyní tyto příklady nejsou příliš užitečné, ale později v tomto kurzu uvidíte, jak můžeme použít stejné koncepty ke kopírování a vkládání buněk v aplikaci Excel (pomocí VBA).
Stejně jako vybereme buňku, můžeme také vybrat rozsah.
V případě rozsahu to může být rozsah pevné velikosti nebo rozsah proměnné velikosti.
V rozsahu pevné velikosti byste věděli, jak velký rozsah je, a můžete použít přesnou velikost v kódu VBA. Ale s rozsahem s proměnnou velikostí nemáte tušení, jak velký rozsah je, a musíte použít trochu kouzla VBA.
Podívejme se, jak to udělat.
Výběr rozsahu pevné velikosti
Zde je kód, který vybere rozsah A1: D20.
Sub SelectRange () Range ("A1: D20"). Select End Sub
Dalším způsobem, jak toho dosáhnout, je použití níže uvedeného kódu:
Sub Select Range () Range ("A1", "D20"). Select End Sub
Výše uvedený kód převezme adresu buňky vlevo nahoře (A1) a adresu buňky vpravo dole (D20) a vybere celý rozsah. Tato technika se stává užitečnou, když pracujete s rozsahy různě velkých velikostí (jak uvidíme, když je vlastnost End popsána později v tomto kurzu).
Pokud chcete, aby k výběru došlo v jiném sešitu nebo jiném listu, pak musíte VBA sdělit přesné názvy těchto objektů.
Níže uvedený kód by například vybral rozsah A1: D20 v listu List2 v sešitu Book2.
Sub SelectRange () Sešity ("Book2.xlsx"). Pracovní listy ("Sheet1"). Aktivujte rozsah ("A1: D20"). Vyberte End Sub
Co když nevíte, kolik řádků je. Co když chcete vybrat všechny buňky, které v sobě mají hodnotu.
V těchto případech musíte použít metody uvedené v další části (při výběru rozsahu proměnné velikosti).
Výběr rozsahu s proměnnou velikostí
Rozsah buněk můžete vybrat různými způsoby. Metoda, kterou zvolíte, bude záviset na tom, jak jsou data strukturována.
V této části se budu zabývat některými užitečnými technikami, které jsou opravdu užitečné při práci s rozsahy ve VBA.
Vyberte použití vlastnosti CurrentRange
V případech, kdy nevíte, kolik řádků/sloupců má data, můžete použít vlastnost CurrentRange objektu Range.
Vlastnost CurrentRange pokrývá všechny souvislé vyplněné buňky v datovém rozsahu.
Níže je kód, který vybere aktuální oblast, která obsahuje buňku A1.
Sub SelectCurrentRegion () Range ("A1"). CurrentRegion.Select End Sub
Výše uvedená metoda je dobrá, když máte všechna data jako tabulku bez prázdných řádků/sloupců.
Ale v případě, že máte ve svých datech prázdné řádky/sloupce, nevybere ty za prázdnými řádky/sloupci. Na obrázku níže kód CurrentRegion vybírá data do řádku 10, protože řádek 11 je prázdný.
V takových případech můžete použít vlastnost UsedRange objektu listu.
Vyberte použití vlastnosti UsedRange
UsedRange umožňuje odkazovat na všechny buňky, které byly změněny.
Níže uvedený kód by tedy vybral všechny použité buňky v aktivním listu.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Všimněte si, že v případě, že máte vzdálenou buňku, která byla použita, bude považována za výše uvedený kód a budou vybrány všechny buňky, dokud nebude použita použitá buňka.
Vyberte použití vlastnosti Konec
Nyní je tato část opravdu užitečná.
Vlastnost Konec umožňuje vybrat poslední vyplněnou buňku. To vám umožní napodobit efekt ovládání pomocí kláves se šipkou dolů/nahoru nebo ovládání doprava/doleva.
Zkusme to pochopit na příkladu.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete rychle vybrat poslední vyplněné buňky ve sloupci A.
Problém je v tom, že data se mohou měnit a nevíte, kolik buněk je vyplněno. Pokud to musíte provést pomocí klávesnice, můžete vybrat buňku A1 a poté použít kombinaci kláves Control + šipka dolů a vybere poslední vyplněnou buňku ve sloupci.
Nyní se podívejme, jak to udělat pomocí VBA. Tato technika je užitečná, když chcete rychle přeskočit na poslední vyplněnou buňku ve sloupci s proměnnou velikostí
Sub GoToLastFilledCell () Range ("A1"). End (xlDown). Vyberte End Sub
Výše uvedený kód by přeskočil na poslední vyplněnou buňku ve sloupci A.
Podobně můžete použít End (xlToRight) k přeskočení na poslední vyplněnou buňku v řadě.
Sub GoToLastFilledCell () Range ("A1"). End (xlToRight). Vyberte End Sub
Co teď, když chcete místo přeskakování na poslední vyplněnou buňku vybrat celý sloupec.
Můžete to udělat pomocí níže uvedeného kódu:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Select End Sub
Ve výše uvedeném kódu jsme použili první a poslední odkaz na buňku, kterou musíme vybrat. Bez ohledu na to, kolik vyplněných buněk existuje, výše uvedený kód vybere všechny.
Pamatujte si výše uvedený příklad, kde jsme vybrali rozsah A1: D20 pomocí následujícího řádku kódu:
Rozsah („A1 ″,“ D20 ”)
Zde A1 byla buňka vlevo nahoře a D20 byla buňka vpravo dole v rozsahu. Stejnou logiku můžeme použít při výběru rozsahů různých velikostí. Protože ale neznáme přesnou adresu buňky vpravo dole, použili jsme k jejímu získání vlastnost End.
V rozsahu („A1“, rozsah („A1“). Konec (xlDown)) znamená „A1“ první buňku a rozsah („A1“). Konec (xlDown) označuje poslední buňku. Protože jsme poskytli obě reference, Select metoda vybere všechny buňky mezi těmito dvěma odkazy.
Podobně můžete také vybrat celou sadu dat, která má více řádků a sloupců.
Níže uvedený kód by vybral všechny vyplněné řádky/sloupce počínaje buňkou A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Select End Sub
Ve výše uvedeném kódu jsme použili Range („A1“). End (xlDown) .End (xlToRight) k získání odkazu na buňku dat vyplněnou vpravo dole.
Rozdíl mezi použitím CurrentRegion a End
Pokud vás zajímá, proč použít vlastnost End k výběru vyplněného rozsahu, když máme vlastnost CurrentRegion, řeknu vám rozdíl.
Pomocí vlastnosti End můžete zadat počáteční buňku. Pokud například máte data v A1: D20, ale první řádek jsou záhlaví, můžete pomocí vlastnosti End vybrat data bez záhlaví (pomocí níže uvedeného kódu).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown) .End (xlToRight)). Select End Sub
Ale CurrentRegion by automaticky vybral celou datovou sadu, včetně záhlaví.
V tomto kurzu jsme zatím viděli, jak odkazovat na řadu buněk různými způsoby.
Nyní se podívejme na některé způsoby, kde můžeme tyto techniky skutečně použít k provedení určité práce.
Kopírujte buňky / rozsahy pomocí VBA
Jak jsem zmínil na začátku tohoto tutoriálu, výběr buňky není nutný k provádění akcí na ní. V této části uvidíte, jak kopírovat buňky a rozsahy, aniž byste je vybrali.
Začněme jednoduchým příkladem.
Kopírování jedné buňky
Pokud chcete zkopírovat buňku A1 a vložit ji do buňky D1, udělá to níže uvedený kód.
Dílčí rozsah CopyCell () ("A1"). Rozsah kopírování ("D1") Koncový díl
Všimněte si toho, že metoda kopírování objektu range zkopíruje buňku (stejně jako Control +C) a vloží ji do zadaného cíle.
Ve výše uvedeném příkladu kódu je cíl určen na stejném řádku, kde používáte metodu Copy. Pokud chcete, aby byl váš kód ještě čitelnější, můžete použít níže uvedený kód:
Dílčí rozsah CopyCell () („A1“). Cílové místo kopírování: = Rozsah („D1“) End Sub
Výše uvedené kódy zkopírují a vloží hodnotu i formátování/vzorce do ní.
Jak jste si již mohli všimnout, výše uvedený kód zkopíruje buňku, aniž byste ji vybrali. Bez ohledu na to, kde jste na listu, kód zkopíruje buňku A1 a vloží ji na D1.
Všimněte si také, že výše uvedený kód přepíše jakýkoli existující kód v buňce D2. Pokud chcete, aby vám Excel oznámil, že v buňce D1 již něco je, aniž by to přepsal, můžete použít níže uvedený kód.
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írování rozsahu s pevnou velikostí
Pokud chcete kopírovat A1: D20 do J1: M20, můžete použít níže uvedený kód:
Rozsah dílčího rozsahu kopírování () („A1: D20“). Rozsah kopírování („J1“) Koncový dílčí rozsah
V cílové buňce stačí zadat adresu levé horní buňky. Kód by automaticky zkopíroval přesný zkopírovaný rozsah do cíle.
Stejnou konstrukci můžete použít ke kopírování dat z jednoho listu na druhý.
Níže uvedený kód by zkopíroval A1: D20 z aktivního listu do List2.
Dílčí rozsah CopyRange () ("A1: D20"). Kopírování pracovních listů ("List2"). Rozsah ("A1") Koncový díl
Výše uvedené zkopíruje data z aktivního listu. Před spuštěním kódu se tedy ujistěte, že list s daty je aktivním listem. Abyste byli v bezpečí, můžete při kopírování dat také zadat název listu.
Dílčí pracovní listy CopyRange () („List1“). Rozsah („A1: D20“). Kopírovat pracovní listy („List2“). Rozsah („A1“) Koncový díl
Dobrá věc na výše uvedeném kódu je, že bez ohledu na to, který list je aktivní, vždy zkopíruje data z List1 a vloží je do List2.
Pojmenovaný rozsah můžete také zkopírovat pomocí jeho názvu místo odkazu.
Pokud například máte pojmenovaný rozsah s názvem „SalesData“, můžete pomocí níže uvedeného kódu zkopírovat tato data do List2.
Dílčí rozsah CopyRange () („SalesData“). Kopírování pracovních listů („List2“). Rozsah („A1“) Koncový díl
Pokud je rozsahem pojmenovaného rozsahu celý sešit, ke spuštění tohoto kódu nemusíte být na listu, který má pojmenovaný rozsah. Vzhledem k tomu, že pojmenovaný rozsah je pro sešit vymezen, můžete k němu přistupovat z libovolného listu pomocí tohoto kódu.
Pokud máte tabulku s názvem Table1, můžete ji pomocí níže uvedeného kódu zkopírovat do Sheet2.
Dílčí rozsah CopyTable () ("Tabulka1 [#All]"). Kopírování pracovních listů ("List2"). Rozsah ("A1") Koncový díl
Rozsah můžete také zkopírovat do jiného sešitu.
V následujícím příkladu zkopíruji tabulku aplikace Excel (Tabulka1) do sešitu Book2.
Dílčí rozsah CopyCurrentRegion () ("Tabulka1 [#All]"). Kopírování sešitů ("Book2.xlsx"). Pracovní listy ("List1"). Rozsah ("A1") Koncový díl
Tento kód by fungoval, pouze pokud je sešit již otevřený.
Kopírování rozsahu proměnných velikostí
Jedním ze způsobů, jak kopírovat rozsahy s proměnnou velikostí, je převést je na pojmenované rozsahy nebo tabulku aplikace Excel a použít kódy, jak je uvedeno v předchozí části.
Pokud to ale nemůžete udělat, můžete použít vlastnost CurrentRegion nebo End objektu range.
Níže uvedený kód by zkopíroval aktuální oblast do aktivního listu a vložil ji do List2.
Dílčí rozsah CopyCurrentRegion () („A1“). Listy CurrentRegion.Copy („List2“). Rozsah („A1“) Koncový díl
Pokud chcete zkopírovat první sloupec sady dat do poslední vyplněné buňky a vložit ji do List2, můžete použít níže uvedený kód:
Sub CopyCurrentRegion () Rozsah ("A1", Rozsah ("A1"). Konec (xlDown)). Kopírování pracovních listů ("List2"). Rozsah ("A1") Konec Sub
Pokud chcete zkopírovat řádky i sloupce, můžete použít níže uvedený kód:
Sub CopyCurrentRegion () Rozsah ("A1", Rozsah ("A1"). Konec (xlDown). Konec (xlToRight)). Kopírování pracovních listů ("List2"). Rozsah ("A1") Konec Sub
Všimněte si, že všechny tyto kódy nevyberou buňky při spuštění. Obecně najdete jen hrstku případů, kdy skutečně potřebujete vybrat buňku/rozsah, než na ní budete pracovat.
Přiřazení rozsahů objektovým proměnným
Doposud jsme používali úplnou adresu buněk (například sešity („Book2.xlsx“). Pracovní listy („List1“). Rozsah („A1“)).
Aby byl váš kód lépe ovladatelný, můžete tyto rozsahy přiřadit objektovým proměnným a poté tyto proměnné použít.
Například v níže uvedeném kódu jsem přiřadil zdrojový a cílový rozsah objektovým proměnným a poté tyto proměnné použil ke kopírování dat z jednoho 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 End Sub
Začneme deklarací proměnných jako Range objekty. Potom těmto proměnným přiřadíme rozsah pomocí příkazu Set. Jakmile je proměnné přiřazen rozsah, můžete ji jednoduše použít.
Zadejte data do další prázdné buňky (pomocí vstupního pole)
Pomocí vstupních polí můžete uživateli zadat data.
Předpokládejme například, že máte níže uvedenou sadu dat a chcete zadat záznam o prodeji, můžete použít vstupní pole ve VBA. Pomocí kódu se můžeme ujistit, že vyplňuje data v dalším prázdném řádku.
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 ) Nastavit částku = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Kategorie produktu") Quantity.Value = InputBox ("Množství") Amount.Value = InputBox ("Částka") End Sub
Výše uvedený kód používá pole vstupu VBA k získání vstupů od uživatele a poté vstupy zadá do zadaných buněk.
Všimněte si, že jsme nepoužili přesné odkazy na buňky. Místo toho jsme použili vlastnost Konec a Posun k nalezení poslední prázdné buňky a vyplnění dat do ní.
Tento kód není zdaleka použitelný. Pokud například zadáte textový řetězec, když se ve vstupním poli zeptáte na množství nebo částku, všimnete si, že to aplikace Excel umožňuje. Pomocí podmínky If můžete zkontrolovat, zda je hodnota číselná nebo ne, a poté ji podle toho povolit.
Smyčka přes buňky / rozsahy
Doposud jsme viděli, jak vybrat, zkopírovat a zadat data do buněk a rozsahů.
V této části uvidíme, jak procházet sadou buněk/řádků/sloupců v rozsahu. To může být užitečné, když chcete analyzovat každou buňku a na jejím základě provést nějakou akci.
Pokud například chcete zvýraznit každý třetí řádek ve výběru, musíte provést smyčku a zkontrolovat číslo řádku. Podobně, pokud chcete zvýraznit všechny negativní buňky změnou barvy písma na červenou, musíte procházet a analyzovat hodnotu každé buňky.
Zde je kód, který bude procházet řádky ve vybraných buňkách a zvýrazňovat alternativní řádky.
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
Výše uvedený kód pomocí funkce MOD kontroluje číslo řádku ve výběru. Pokud je číslo řádku sudé, zvýrazní se azurovou barvou.
Zde je další příklad, kdy kód prochází každou buňku a zvýrazňuje buňky, které v sobě mají zápornou 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šimněte si, že to samé můžete udělat pomocí podmíněného formátování (což je dynamické a lepší způsob, jak to udělat). Tento příklad slouží pouze k tomu, abychom vám ukázali, jak funguje smyčka s buňkami a rozsahy ve VBA.
Kam vložit kód VBA
Zajímá vás, kam jde kód VBA do vašeho sešitu v Excelu?
Excel má VBA backend nazvaný VBA editor. Musíte zkopírovat a vložit kód do okna kódu modulu VB Editor.
Zde jsou kroky, jak toho dosáhnout:
- Přejděte na kartu Vývojář.
- Klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
- V podokně Průzkumník projektů v editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete vložit kód.Pokud nevidíte Průzkumníka projektů, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
- Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro váš sešit.
- Zkopírujte a vložte kód do okna modulu.
