Tento článek předvede, jak vytvořit dynamický rozsah v aplikaci Excel VBA.
Deklarace konkrétního rozsahu buněk jako proměnné v aplikaci Excel VBA nás omezuje na práci pouze s těmito konkrétními buňkami. Deklarací dynamických rozsahů v Excelu získáváme mnohem větší flexibilitu nad naším kódem a funkcemi, které může provádět.
Referenční rozsahy a buňky
Když v aplikaci Excel odkazujeme na objekt Rozsah nebo buňka, obvykle je odkazujeme na hardcoding v požadovaném řádku a sloupcích.
Rozsah Vlastnost
Pomocí vlastnosti Range v příkladech řádků kódu níže můžeme v tomto rozsahu provádět akce, jako je změna barvy buněk nebo zvýraznění tučných buněk.
| 12 | Rozsah ("A1: A5"). Font.Color = vbRedRozsah ("A1: A5"). Font.Bold = True |
Vlastnost buněk
Podobně můžeme vlastnost buňky použít k odkazu na rozsah buněk přímým odkazováním na řádek a sloupec ve vlastnosti buněk. Řádek musí být vždy číslo, ale sloupec může být číslo nebo písmeno uzavřené v uvozovkách.
Na adresu buňky A1 lze například odkazovat jako:
| 1 | Buňky (1,1) |
Nebo
| 1 | Buňky (1, „A“) |
Abychom mohli použít vlastnost buňky k odkazu na rozsah buněk, musíme označit začátek rozsahu a konec rozsahu.
Například pro referenční rozsah A1: A6 bychom mohli použít tuto syntaxi níže:
| 1 | Rozsah (buňky (1,1), buňky (1,6) |
Vlastnost Cells pak můžeme použít k provádění akcí v rozsahu podle níže uvedených příkladů řádků kódu:
| 12 | Rozsah (buňky (2, 2), buňky (6, 2)). Font.Color = vbRedRozsah (buňky (2, 2), buňky (6, 2)). Font.Bold = True |
Dynamické rozsahy s proměnnými
Protože se velikost našich dat v Excelu mění (tj. Používáme více řádků a sloupců než rozsahy, které jsme zakódovali), bylo by užitečné, kdyby se změnily také rozsahy, na které odkazujeme v našem kódu. Pomocí objektu Range výše můžeme vytvořit proměnné pro uložení maximálního počtu řádků a sloupců oblasti listu aplikace Excel, který používáme, a pomocí těchto proměnných dynamicky upravovat objekt Range, když je kód spuštěný.
Například
| 1234 | Dim lRow as integerDim lCol jako celé číslolRow = Rozsah ("A1048576"). Konec (xlUp). ŘádeklCol = Rozsah ("XFD1"). Konec (xlToLeft). Sloupec |
Poslední řádek ve sloupci
Protože v listu je 1048576 řádků, proměnná lRow přejde do spodní části listu a poté pomocí speciální kombinace klávesy Konec a klávesy Šipka nahoru přejde na poslední řádek použitý v listu - tím získáme číslo řádku, který v našem rozsahu potřebujeme.
Poslední sloupec v řadě
Podobně se lCol přesune do sloupce XFD, což je poslední sloupec v listu, a poté pomocí speciální kombinace kláves Konec plus Šipka doleva přejdete na poslední sloupec použitý v listu - tím získáme číslo sloupce, který v našem rozsahu potřebujeme.
Proto, abychom získali celý rozsah, který se používá v listu, můžeme spustit následující kód:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol jako celé čísloDim rng As RangelRow = Rozsah ("A1048576"). Konec (xlUp). Řádek'použijte lRow k nalezení posledního sloupce v rozsahulCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnNastavit rng = rozsah (buňky (1, 1), buňky (lRow, lCol))'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressEnd Sub |
SpecialCells - LastCell
Můžeme také použít metodu SpecialCells objektu Range, abychom získali poslední řádek a sloupec použitý v listu.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol jako celé čísloDim rng As RangeDim rngBegin As RangeNastavit rngBegin = rozsah ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnNastavit rng = rozsah (buňky (1, 1), buňky (lRow, lCol))'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressEnd Sub |
Použitý rozsah
Metoda Použitý rozsah zahrnuje všechny buňky, které obsahují hodnoty v aktuálním listu.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeNastavit rng = ActiveSheet.UsedRange'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressEnd Sub |
Aktuální region
Aktuální oblast se liší od UsedRange v tom, že se dívá na buňky obklopující buňku, kterou jsme deklarovali jako počáteční rozsah (tj. Proměnnou rngBegin v níže uvedeném příkladu), a poté se dívá na všechny buňky, které jsou 'připojené' nebo přidružené do té deklarované cely. Pokud se objeví prázdná buňka v řádku nebo sloupci, CurrentRegion přestane hledat další buňky.
| 12345678 | Sub Current Region ()Dim rng As RangeDim rngBegin As RangeNastavit rngBegin = rozsah ("A1")Nastavit rng = rngBegin.CurrentRegion'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressEnd Sub |
Pokud použijeme tuto metodu, musíme zajistit, aby všechny buňky v požadovaném rozsahu byly propojeny bez prázdných řádků nebo sloupců.
Pojmenovaný rozsah
V našem kódu můžeme také odkazovat na pojmenované rozsahy. Pojmenované rozsahy mohou být dynamické, pokud se při aktualizaci nebo vložení dat změní název rozsahu tak, aby zahrnoval nová data.
Tento příklad změní písmo na tučné pro název rozsahu „leden“
| 12345 | Název dílčího rozsahu Příklad ()Dim rng as RangeNastavit rng = rozsah ("leden")rng.Font.Bold = = PravdaEnd Sub |
Jak uvidíte na obrázku níže, pokud je do názvu rozsahu přidán řádek, název rozsahu se automaticky aktualizuje, aby zahrnoval tento řádek.
Pokud bychom potom spustili ukázkový kód znovu, rozsah ovlivněný kódem by byl C5: C9, zatímco v první instanci by to bylo C5: C8.
Tabulky
V našem kódu můžeme odkazovat na tabulky (kliknutím získáte další informace o vytváření a manipulaci s tabulkami ve VBA). Při aktualizaci nebo změně dat tabulky v aplikaci Excel bude kód odkazující na tabulku odkazovat na data aktualizované tabulky. To je užitečné zejména při odkazování na kontingenční tabulky, které jsou připojeny k externímu zdroji dat.
Pomocí této tabulky v našem kódu můžeme odkazovat na sloupce tabulky podle nadpisů v každém sloupci a provádět akce se sloupcem podle jejich názvu. Jak se řádky v tabulce zvětšují nebo zmenšují podle dat, rozsah tabulky se podle toho upraví a náš kód bude stále fungovat pro celý sloupec v tabulce.
Například:
| 123 | Dílčí sloupec DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). OdstranitEnd Sub |
