Tento článok ukáže, ako vytvoriť dynamický rozsah v programe Excel VBA.
Deklarovanie konkrétneho rozsahu buniek ako premennej v programe Excel VBA nás obmedzuje na prácu iba s týmito konkrétnymi bunkami. Vyhlásením dynamických rozsahov v programe Excel získavame oveľa väčšiu flexibilitu nad našim kódom a funkciami, ktoré môže vykonávať.
Referenčné rozsahy a bunky
Keď odkazujeme na objekt Rozsah alebo Bunka v programe Excel, zvyčajne na ne odkazujeme pomocou hardcodingu v požadovanom riadku a stĺpci.
Rozsah majetku
Pomocou vlastnosti Range v príkladoch nižšie uvedených riadkov kódu môžeme vykonávať akcie v tomto rozsahu, napríklad zmenu farby buniek alebo zvýraznenie buniek tučným písmom.
| 12 | Rozsah ("A1: A5"). Font.Color = vbRedRozsah ("A1: A5"). Písmo.Bold = True |
Vlastnosť buniek
Podobne môžeme vlastnosť buniek použiť na odkazovanie na rozsah buniek priamym odkazom na riadok a stĺpec vo vlastnosti buniek. Riadok musí byť vždy číslo, ale stĺpec môže byť číslo alebo písmeno uzavreté v úvodzovkách.
Na adresu bunky A1 je možné napríklad odkazovať takto:
| 1 | Bunky (1,1) |
Alebo
| 1 | Bunky (1, „A“) |
Ak chcete použiť vlastnosť buniek na odkaz na rozsah buniek, musíme označiť začiatok rozsahu a koniec rozsahu.
Napríklad na referenčný rozsah A1: A6 by sme mohli použiť túto syntax nižšie:
| 1 | Rozsah (bunky (1,1), bunky (1,6) |
Potom môžeme vlastnosť Bunky použiť na vykonávanie akcií v rozsahu podľa nižšie uvedených príkladov riadkov kódu:
| 12 | Rozsah (bunky (2, 2), bunky (6, 2)). Font.Color = vbRedRozsah (bunky (2, 2), bunky (6, 2)). Písmo. Tučné = pravda |
Dynamické rozsahy s premennými
Keďže sa veľkosť našich údajov v Exceli mení (t.j. používame viac riadkov a stĺpcov ako rozsahy, ktoré sme zakódovali), bolo by užitočné, keby sa zmenili aj rozsahy, na ktoré odkazujeme v našom kóde. Pomocou objektu Range uvedeného vyššie môžeme vytvoriť premenné na uloženie maximálneho počtu riadkov a stĺpcov oblasti pracovného hárka programu Excel, ktorú používame, a pomocou týchto premenných dynamicky upravovať objekt Range, keď je kód spustený.
Napríklad
| 1234 | Dim lRow as integerDim lCol ako celé číslolRow = Rozsah ("A1048576"). Koniec (xlUp). RiadoklCol = Rozsah ("XFD1"). Koniec (xlToLeft). Stĺpec |
Posledný riadok v stĺpci
Keďže v pracovnom hárku je 1048576 riadkov, premenná lRow prejde do spodnej časti listu a potom pomocou špeciálnej kombinácie klávesa Koniec a klávesu so šípkou hore prejde na posledný riadok použitý v pracovnom hárku - tým získame číslo riadku, ktorý potrebujeme v našom rozsahu.
Posledný stĺpček v rade
Podobne sa lCol presunie do stĺpca XFD, ktorý je posledným stĺpcom v hárku, a potom pomocou špeciálnej kombinácie klávesov klávesu Koniec a klávesu so šípkou doľava prejde na posledný stĺpec použitý v pracovnom hárku - tým získame číslo stĺpca, ktorý v našom rozsahu potrebujeme.
Preto, aby sme získali celý rozsah, ktorý sa používa v pracovnom hárku, môžeme spustiť nasledujúci kód:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol ako celé čísloDim rng As RangelRow = Rozsah ("A1048576"). Koniec (xlUp). Riadok'pomocou lRow pomôžte nájsť posledný stĺpec v rozsahulCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnNastaviť rng = rozsah (bunky (1, 1), bunky (lRow, lCol))'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressKoniec pod |
SpecialCells - LastCell
Na získanie posledného riadka a stĺpca použitého v pracovnom hárku môžeme použiť aj metódu SpecialCells objektu Range.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol ako celé čísloDim rng As RangeDim rngBegin As RangeNastaviť rngBegin = rozsah ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnNastaviť rng = rozsah (bunky (1, 1), bunky (lRow, lCol))'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressKoniec pod |
UsedRange
Metóda Použitý rozsah zahŕňa všetky bunky, ktoré obsahujú hodnoty v aktuálnom pracovnom hárku.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeNastaviť rng = ActiveSheet.UsedRange'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressKoniec pod |
CurrentRegion
Aktuálna oblasť sa líši od UsedRange v tom, že sa pozerá na bunky obklopujúce bunku, ktorú sme deklarovali ako počiatočný rozsah (tj. Premenná rngBegin v nižšie uvedenom príklade), a potom sa pozerá na všetky bunky, ktoré sú 'pripojené' alebo priradené do tej vyhlásenej cely. Ak sa v riadku alebo stĺpci zobrazí prázdna bunka, CurrentRegion prestane hľadať ďalšie bunky.
| 12345678 | Podprúdový región ()Dim rng As RangeDim rngBegin As RangeNastaviť rngBegin = rozsah ("A1")Nastaviť rng = rngBegin.CurrentRegion'msgbox, aby nám ukázal rozsahMsgBox „Rozsah je“ & rng.AddressKoniec pod |
Ak použijeme túto metódu, musíme sa uistiť, že všetky bunky v požadovanom rozsahu sú prepojené bez prázdnych riadkov alebo stĺpcov.
Pomenovaný rozsah
V našom kóde môžeme tiež odkazovať na pomenované rozsahy. Pomenované rozsahy môžu byť dynamické, pretože keď sa údaje aktualizujú alebo vkladajú, názov rozsahu sa môže zmeniť tak, aby zahŕňal nové údaje.
V tomto prípade sa názov písma „január“ zmení na tučné písmo.
| 12345 | Sub rangeNameExample ()Dim rng as RangeNastaviť rng = rozsah („január“)rng.Font.Bold = = PravdaKoniec pod |
Ako vidíte na obrázku nižšie, ak je do názvu rozsahu pridaný riadok, názov rozsahu sa automaticky aktualizuje, aby zahrnoval daný riadok.
Ak by sme potom znova spustili ukážkový kód, rozsah ovplyvnený kódom by bol C5: C9, zatiaľ čo v prvom prípade by to bol C5: C8.
Tabuľky
V našom kóde môžeme odkazovať na tabuľky (kliknutím získate ďalšie informácie o vytváraní a manipulácii s tabuľkami vo VBA). Keď sa údaje tabuľky v programe Excel aktualizujú alebo menia, kód, ktorý odkazuje na tabuľku, bude potom odkazovať na aktualizované údaje tabuľky. Toto je obzvlášť užitočné pri odkazovaní na kontingenčné tabuľky, ktoré sú pripojené k externému zdroju údajov.
Pomocou tejto tabuľky v našom kóde môžeme na stĺpce tabuľky odkazovať podľa nadpisov v každom stĺpci a v stĺpci vykonávať akcie podľa ich názvu. Keďže riadky v tabuľke sa podľa údajov zväčšujú alebo zmenšujú, rozsah tabuľky sa podľa toho upraví a náš kód bude stále fungovať pre celý stĺpec v tabuľke.
Napríklad:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). OdstrániťKoniec pod |
