2. diel - Objekt Range vo VBA

V minulej lekcii sme prebrali hierarchiu objektov Excel smerujúce k adresovanie bunky

Objekty Worksheet a Range sme v predchádzajúcej lekcii prebrali z hľadiska jednoduchého adresovania. Než budeme vo vedomostiach adresovanie buniek pokračovať, zoznámime sa s vybranými vlastnosti a metódami objektu Range, aby sme tieto znalosti použili v celom ďalšom kurze.

Vybrané vlastnosti objektu Range

• Address - vracia adresu bunky alebo adresy oblasti v tvare známom z Excelu, napr. $A$7. Parametrom ReferenceStyle:=xlR1C1 možno zmeniť na notáciu R1C1.
• Cells - kolekcia buniek aktuálneho alebo odkazovaného Range. Ak Range nie je špecifikované, ide o všetky bunky ActiveSheet
• Column - označuje stĺpec v Range, pracujeme s ním cez kolekciu Columns
• CurrentRegion - označuje obdĺžnik s aktívnou bunkou, oddelený aspoň jedným prázdnym stĺpcom a riadkom
• End - určuje posledný / prvý neprázdnu bunku súvislej oblasti podľa riadku / stĺpca prvej bunky uvedenej oblasti, ovláda sa konštantami xlUp, xlDown, xlToLeft, xlToRight, ktoré určujú smer hľadanie
• EntireColumn - označuje celý stĺpec
• EntireRow - označuje celý riadok
• Formula - nastavuje alebo vracia vzorec uložený v bunke, bude prebrané podrobnejšie ďalej
• FormulaLocal - vzorec v jazyku používanom, bude prebrané podrobnejšie
• Hidden - riadi alebo udáva, či je stĺpec alebo riadok skrytý, vzťahuje sa k celému riadku / stĺpci
• Item - relatívna adresa bunky v oblasti, alebo poradie položky v oblasti, ak sa použije len prvý parameter
• Locked - označuje / nastavuje stav uzamknuté / prístupné oblasti. Možno nastaviť iba na neuzamknutej liste. Ak sa na zamknutie pýtame a ide o bunky zamknuté i odomknuté, vracia Null
• Name - názov oblasti, ktorý sme definovali, možno ho používať ako parameter napr. Range("rngNazev")
• Offset - relatívna adresa vzhľadom k objektu Range
• Range - vlastnosť vracajúci objekt range. Adresy v tejto vlastnosti uvedené sa týkajú oblasti relatívne posunuté k predchádzajúcemu objektu Range, ide teda neumelecky povedané o subregión
• Resize - mení zvolenú oblasť na nový rozsah buniek, parametre udávajú, o koľko riadkov a stĺpcov oblasť meníme
• Row - označuje riadok, pracuje sa s ním cez kolekciu Rows
• Value - hodnota uložená v bunke, možno ju čítať aj zapisovať, všeobecne môže byť ľubovoľného typu dát
• Value2 - hodnota v bunke, typy mena a dátum vracia ako čísla

Vybrané metódy objektu Range

• Activate - zvolí vo vybranej oblasti Range, pokiaľ sa nejedná o jednu bunku, ľavú hornú bunku ako aktívny
• AutoFill - automaticky vyplní oblasť na základe vzoru, podobne ako v tabuľke
• AutoFilter - nastavenie a použitie filtra
• Autofit - šírka stĺpce alebo výška riadku sa prispôsobí, tj. Optimálne sa nastaví pre zobrazenie zvolených hodnôt v oblasti
• BorderAround - nastaví parametre zobrazenia ohraničenie oblasti alebo bunky
• Calculate - prepočíta podľa objektu, ku ktorému sa viaže, všetky listy / vybraný list / vybranú oblasť
• Clear - odstráni vzorce a formátovanie v zadanom objekte. Ďalšie podobné vlastnosti, napr. ClearFormats, odstráni len formátovanie oblasti
• Consolidate - do zvoleného cieľa vloží výsledok doménové funkcie viacerých zvolených oblastí, napr. Súčet, max, počet, priemer atď. Pomocou parametra napr. Function:=xlMax
• Copy - bude v ďalších lekciách predmetom širšieho výkladu
• CopyPicture - zvolenú oblasť kopíruje do clipboard ako obrázok
• Delete - zruší oblasť. Parameter je konštanta pre posunutie buniek: xlShiftToLeft alebo xlShiftUp
• FillDown - vyplní stĺpec obsahom prvej bunky. Pri uvedení viacerých stĺpcov vyplní len prvý. Podobnú funkciu majú FillLeft, FillRight, FILLUP
• Find - umožňuje hľadanie nielen v hodnotách, ale napr. Aj vo vlastnostiach buniek a tým pripraviť napr. Automatickú zámenu fontov. Podrobne preberieme neskôr
• FunctionWizard - otvorí okno voľby funkcie
• Insert - vloží bunky, riadky alebo stĺpce do oblasti alebo na list. Parametre udávajú, aký bude posun buniek a či a ako sa bude kopírovať formátovanie
• Merge - zlúčenie buniek
• PasteSpecial - vloží kopírovanou oblasť, možno pripísať k pôvodným hodnotám alebo aj inak modifikovať cieľ pomocou parametrov
• PrintPreview - otvorí náhľad tlačovej oblasti. Priama tlač je PrintOut
• Replace - zamení znaky / slová v hodnotách objekte Range, podľa nastavených parametrov
• Run - spúšťa makro. Môže spúšťať aj funkcie definované v moduloch VBA
• Select - označí ako vybranú oblasť v rozsahu príslušného objektu Range
• Show - vybranú bunku alebo oblasť nastaví do okna zobrazenia
• Sort - triedi oblasť podľa uvedených kľúčov a podmienok
• SpecialCells - vracia oblasť vybranú na základe špeciálnych vlastností
• UnMerge - zlúčené bunky rozdelí na jednotlivé

Rôzne spôsoby adresovanie oblastí

Objekty v kombinácii s vlastnosťami odkazujúcimi na objekt Range prinášajú oveľa bohatšiu škálu "kúziel" v adresovanie buniek a ich skupín.

'Zvolíme oblast = všechny buňky od B1 do C6, různé zápisy

    Sheets(2).Range("B1:C6").Select

    Sheets(2).Range(Range("B1"), Range("C1:C6")).Select

    Sheets(2).Range(Cells(1, 2), Cells(6, 3)).Select

    Sheets(2).Range("B1", Range("B1").Offset(5, 1)).Select

    Cells(1, 2).Activate
    Sheets(2).Range(ActiveCell, ActiveCell.Offset(5, 1)).Select

Možno použiť klasického zápisu odkazu z Excelu, rovnako ako objektového z VBA:

'v adrese Range lze použít i běžného zápisu Excelu
    ActiveSheet.Cells(15, 1).Value = Range("List1!B2").Value
'shodná adresace pomocí objektů VBA
    ActiveSheet.Cells(15, 1).Value = Sheets(1).Range("B2").Value

Rozdiel medzi Cells a Offset objektu Range

• Cells je kolekcia buniek definovaného objektu, pomocou parametrov udávame, o ktorú bunku sa jedná.
• Offset je relatívna adresovanie, posúva adresovanie celého definovaného objektu o určitý počet riadkov a stĺpcov, vyjadrených parametre.

Pri oboch vlastnosťou je prvý parameter index riadku, druhý parameter index stĺpca. Rozdiel možno názorne vyjadriť na oblasti jednej bunky:

ActiveSheet.Range("B2").Offset(1, 1).Activate    ' nastaví buňku C3 jako aktivní

ActiveSheet.Range("B2").Cells(1, 1).Activate    ' nastaví buňku B2 jako aktivní

Zvláštne prípady Range

• CurrentRegion
• UsedRange
• SpecialCells

Z hľadiska použitia a adresovanie ide o dôležité objekty.

CurrentRegion

CurrentRegion je vlastnosťou objektu Range typu ActiveCell. Je viazaný na aktívnu bunku, vracia obdĺžnik, v ktorom sa aktívna bunka vyskytuje, ohraničený je najmenej jedným prázdnym stĺpcom a riadkom.

Worksheets(1).Activate
ActiveCell.CurrentRegion.Select

Tu možno názorne ukázať zložitosť objektov v Exceli. ActiveCell je vlastnosťou objektov Application alebo ActiveWindow a tieto ešte len vracajú objekt Range, ku ktorému sa viaže vlastnosť CurrentRegion. ActiveCell bez udania objektu možno písať preto, že objekt Application je natívnom objektom v hierarchii.

UsedRange

UsedRange je vlastnosťou objektu Worksheet, vracia obdĺžnik, zahŕňajúci všetky neprázdne bunky na liste.

Application.Worksheets(1).UsedRange.Select

Na obrázku je CurrentRegion oblasť podfarbená, UsedRange je vymedzená červeným obdĺžnikom.

SpecialCells

SpecialCells je metódou objektu Range, vracia rozsah buniek so špeciálnymi vlastnosťami, napr. Prázdne bunky. Riadi sa konštantami, ktorých anglický názov je výstižný.

• xlCellTypeBlanks
• xlCellTypeComments
• xlCellTypeConstants
• xlCellTypeFormulas
• xlCellTypeLastCell
• xlCellTypeVisible

Praktické použitie adries

Podľa mňa je dôležité, ak nepracujete vo VBA denne, nájsť si v adresovanie svoju "parketu" a ostatné mať na pamäti, keď čítate cudzí kód. Nakoniec podobné je to vo VBA vo všetkých smeroch a platí to nielen v ich jazyku.

Zaujímavým pre programátora sa adresovanie buniek stáva tým, že ako špecifikáciu adresy možno použiť premenných, to znamená pracovať s matematickými a textovými operáciami a v neposlednom rade je použitie v cykloch.

Dim vTxt As String
Dim vInt As Integer
Dim i As Integer
'naplnění proměnných
    vTxt = "B1:B6"
    vInt = 4
'proužití proměnných v parametrech
    Range(vTxt).Select
    For i = 0 to 10
         Debug.Print Cells(vInt + i, vInt - 3).Value
    Next i

To je v dnešnej lekcii všetko.

V nasledujúcom cvičení, Riešené úlohy k 1. - 2. lekciu VBA pre Excel, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.