Lekce 2 - Objekt Range ve VBA

V minulé lekci, Úvod do VBA pro Excel, jsme probrali hierarchii objektů Excel směřující k adresování buňky.

Objekty Worksheet a Range jsme v předchozí lekci probrali z hlediska jednoduchého adresování. Než budeme ve znalostech adresování buněk pokračovat, seznámíme se s vybranými vlastnosti a metodami objektu Range, abychom tyto znalosti použili v celém dalším kurzu.

Vybrané vlastnosti objektu Range

• Address - vrací adresu buňky nebo adresy oblasti ve tvaru známém z Excelu, např. $A$7. Parametrem ReferenceStyle:=xlR1C1 lze změnit na notaci R1C1.
• Cells - kolekce buněk aktuálního nebo odkazovaného Range. Pokud Range není specifikováno, jde o všechny buňky ActiveSheet
• Column - označuje sloupec v Range, pracujeme s ním přes kolekci Columns
• CurrentRegion - označuje obdélník s aktivní buňkou, oddělený alespoň jedním prázdným sloupcem a řádkem
• End - určuje poslední/první neprázdnou buňku souvislé oblasti podle řádku/sloupce první buňky uvedené oblasti, ovládá se konstantami xlUp, xlDown, xlToLeft, xlToRight, které určují směr hledání
• EntireColumn - označuje celý sloupec
• EntireRow - označuje celý řádek
• Formula - nastavuje nebo vrací vzorec uložený v buňce, bude probráno podrobněji dále
• FormulaLocal - vzorec v jazyce používaném, bude probráno podrobněji
• Hidden - řídí nebo udává, zda je sloupec nebo řádek skrytý, vztahuje se k celému řádku/sloupci
• Item - relativní adresa buňky v oblasti, nebo pořadí položky v oblasti, pokud se použije jenom první parametr
• Locked - označuje/nastavuje stav uzamčené/přístupné oblasti. Lze nastavit pouze na nezamčeném listu. Pokud se na zamknutí dotazujeme a jde o buňky zamknuté i odemknuté, vrací Null
• Name - název oblasti, který jsme definovali, lze jej používat jako parametr např. Range("rngNazev")
• Offset - relativní adresa vzhledem k objektu Range
• Range - vlastnost vracející objekt Range. Adresy v této vlastnosti uvedené se týkají oblasti relativně posunuté k předchozímu objektu Range, jde tedy neuměle řečeno o podoblast
• Resize - mění zvolenou oblast na nový rozsah buněk, parametry udávají, o kolik řádků a sloupců oblast měníme
• Row - označuje řádek, pracuje se s ním přes kolekci Rows
• Value - hodnota uložená v buňce, lze ji číst i zapisovat, obecně může být libovolného datového typu
• Value2 - hodnota v buňce, typy měna a datum vrací jako čísla

Vybrané metody objektu Range

• Activate - zvolí ve vybrané oblasti Range, pokud se nejedná o jednu buňku, levou horní buňku jako aktivní
• AutoFill - automaticky vyplní oblast na základě vzoru, podobně jako v tabulce
• AutoFilter - nastavení a použití filtru
• AutoFit - šířka sloupce nebo výška řádku se přizpůsobí, tj. optimálně se nastaví pro zobrazení zvolených hodnot v oblasti
• BorderAround - nastaví parametry zobrazení ohraničení oblasti nebo buňky
• Calculate - přepočítá podle objektu, ke kterému se váže, všechny listy/vybraný list/vybranou oblast
• Clear - odstraní vzorce a formátování v zadaném objektu. Další podobné vlastnosti, např. ClearFormats, odstraní jenom formátování oblasti
• Consolidate - do zvoleného cíle vloží výsledek doménové funkce více zvolených oblastí, např. součet, max, počet, průměr atd. pomocí parametru např. Function:=xlMax
• Copy - bude v dalších lekcích předmětem širšího výkladu
• CopyPicture - zvolenou oblast kopíruje do ClipBoardu jako obrázek
• Delete - zruší oblast. Parametr je konstanta pro posunutí buněk: xlShiftToLeft nebo xlShiftUp
• FillDown - vyplní sloupec obsahem první buňky. Při uvedení více sloupců vyplní pouze první. Podobnou funkci mají FillLeft, FillRight, FillUp
• Find - umožňuje hledání nejen v hodnotách, ale např. také ve vlastnostech buněk a tím připravit např. automatickou záměnu fontů. Podrobně probereme později
• FunctionWizard - otevře okno volby funkce
• Insert - vloží buňky, řádky nebo sloupce do oblasti nebo na list. Parametry udávají, jaký bude posun buněk a zda a jak se bude kopírovat formátování
• Merge - sloučení buněk
• PasteSpecial - vloží kopírovanou oblast, lze přičíst k původním hodnotám nebo i jinak modifikovat cíl pomocí parametrů
• PrintPreview - otevře náhled tisknuté oblasti. Přímý tisk je PrintOut
• Replace - zamění znaky/slova v hodnotách objektu Range, podle nastavených parametrů
• Run - spouští makro. Může spouštět i funkce definované v modulech VBA
• Select - označí jako vybranou oblast v rozsahu příslušného objektu Range
• Show - vybranou buňku nebo oblast nastaví do okna zobrazení
• Sort - třídí oblast podle uvedených klíčů a podmínek
• SpecialCells - vrací oblast vybranou na základě speciálních vlastností
• UnMerge - sloučené buňky rozdělí na jednotlivé

Různé způsoby adresování oblastí

Objekty v kombinaci s vlastnostmi odkazujícími na objekt Range přinášejí mnohem bohatší škálu "kouzel" v adresování buněk a jejich skupin.

'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

Lze použít klasického zápisu odkazu z Excelu, stejně jako 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

Rozdíl mezi Cells a Offset objektu Range

• Cells je kolekce buněk definovaného objektu, pomocí parametrů udáváme, o kterou buňku se jedná.
• Offset je relativní adresování, posouvá adresování celého definovaného objektu o určitý počet řádků a sloupců, vyjádřených parametry.

U obou vlastností je první parametr index řádku, druhý parametr index sloupce. Rozdíl lze názorně vyjádřit na oblasti jedné buňky:

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áštní případy Range

• CurrentRegion
• UsedRange
• SpecialCells

Z hlediska použití a adresování jde o důležité objekty.

CurrentRegion

CurrentRegion je vlastností objektu Range typu ActiveCell. Je vázán na aktivní buňku, vrací obdélník, ve kterém se aktivní buňka vyskytuje, ohraničen je nejméně jedním prázdným sloupcem a řádkem.

Worksheets(1).Activate
ActiveCell.CurrentRegion.Select

Zde lze názorně ukázat složitost objektů v Excelu. ActiveCell je vlastností objektů Application nebo ActiveWindow a tyto teprve vracejí objekt Range, ke kterému se váže vlastnost CurrentRegion. ActiveCell bez udání objektu lze psát proto, že objekt Application je nativním objektem v hierarchii.

UsedRange

UsedRange je vlastností objektu Worksheet, vrací obdélník, zahrnující všechny neprázdné buňky na listu.

Application.Worksheets(1).UsedRange.Select

Na obrázku je CurrentRegion oblast podbarvená, UsedRange je vymezena červeným obdélníkem.

SpecialCells

SpecialCells je metodou objektu Range, vrací oblast buněk se speciálními vlastnostmi, např. prázdné buňky. Řídí se konstantami, jejichž anglický název je výstižný.

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

Praktické použití adres

Podle mne je důležité, pokud nepracujete ve VBA denně, najít si v adresování svoji "parketu" a ostatní mít na paměti, když čtete cizí kód. Nakonec podobné je to ve VBA ve všech směrech a platí to nejen v tomto jazyku.

Zajímavým pro programátora se adresování buněk stává tím, že jako specifikaci adresy lze použít proměnných, to znamená pracovat s matematickými a textovými operacemi a v neposlední řadě je použití v cyklech.

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šní lekci vše.

V následujícím cvičení, Řešené úlohy k 1. - 2. lekci VBA pro Excel, si procvičíme nabyté zkušenosti z předchozích lekcí.