Activate a Select – zlozvyk ve VBA

Úvodní stránka | Blog | Excel VBA | Activate a Select – zlozvyk ve VBA

    • Activate a Select jsou sesterské metody, se kterými se potkáváme pod VBA. Jejich cílem je zjednodušeně řečeno vybrat objekt. My se budeme zabývat aktivací (výběrem) oblasti buněk a (ouška) listu v Excelu.

    Na metodu Select narazíme už ve výstupním kódu ze Záznamníku maker (Excel 2010). Příklad ukazuje výběr Listu1, buňky B5, a následné obarvení jejího pozadí na žluto.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Sub Makro1()
    '
    ' Makro1 Makro
    '
        Sheets("List1").Select
        Range("B5").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

    End Sub

    Nahrané makro tedy nejprve vybírá List1 (Select, klepli jsme na ouško listu), buňku B5 (Select, klepli jsme na buňku myší), a následně pracuje s výběrem (Selection, konstrukce With..End With). Jak si ukážeme, celá stavba makra je ale zbytečně komplikovaná a lehce nebezpečná. Navíc, v případě výběru listu bychom se měli bavit spíše o jeho aktivaci (metoda Activate).

    Začínající programátor, který příklad odkouká ze Záznamníku maker, pak použije něco v tomto duchu:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    Sub KlasickyPriklad()

        'aktivace třetího listu
        Worksheets(3).Activate
       
        'výběr oblasti B2:D6 aktivního listu
        Range("B2:D6").Select
       
        'práce s pozadím (vybrané oblasti)
        Selection.Interior.ColorIndex = 6
        Selection.Interior.ColorIndex = xlNone

    End Sub

    Nevýhody
    Jestliže zapomenete na aktivaci požadovaného listu a u objektu Range neuvedete rodiče, dosadí si za něj Excel aktivní list.
    Pokud nevyberete oblast buněk, může se stát, že pod objektem Selection bude třeba obrázek.
    Neustálé přepínaní se mezi listy a výběry buněk velmi výrazně zpomaluje vaši proceduru (dochází k překreslování obrazovky a musíme sáhnout po známé syntaxi Application.ScreenUpdating = False).

    Pozn. Výběr nelze provádět na neaktivním listu.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Sub SelectNeaktivniList()

        'aktivace prvního listu
        Worksheets(1).Activate

        'výběr nelze provádět na neaktivním listu
        'chyba
        Worksheets(3).Range("B2:D6").Select

    End Sub

    Rozdíl mezi Activate a Select se dobře demonstruje na oblasti buněk.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Sub RozdilActivateSelect()

        'aktivace prvního listu
        Worksheets(1).Activate
       
        'výběr oblasti B2:D6 aktivního listu
        Range("B2:D6").Select
        'lze, nepoužívá se
        'Range("B2:D6").Activate
       
        'aktivace buňky B5 (se zachováním výběru)
        Range("B5").Activate

    End Sub
    Metoda Select aplikovaná na výběru a Activate na buňce tohoto výběru
    Metoda Select aplikovaná na výběru a Activate na buňce tohoto výběru

    Ručně lze změny aktivní buňky ve výběru docílit klávesou TAB nebo například klávesovou zkratkou CTRL + . (tečka), která prochází rohové buňky výběru.

    A teď průšvih, na který mě upozornil klient v nefunkčním kódu. Představme si, že druhý list ze tří je skryt.

    Skrytý list a chování metod Select a Activate
    Skrytý list a chování metod Select a Activate
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    Sub BugPrikladA()

        'tři listy, druhý skrytý
       
        'aktivace prvního listu
        Worksheets(1).Activate
        Worksheets(1).Select
       
        'aktivace druhého viditelného, tedy třetího listu!
        Worksheets(2).Activate
        'chyba:
        Worksheets(2).Select
       
        'aktivace třetího listu
        Worksheets(3).Activate
        Worksheets(3).Select

    End Sub

    A vyložený bug:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Sub BugPrikladB()

        'tři listy, druhý skrytý
       
        'aktivace prvního listu
        Worksheets(1).Activate
       
        'aktivace druhého viditelného, tedy třetího listu!
        Worksheets(2).Activate
       
        'při krokování hodnota zapsána do třetího listu!
        'při spuštění celé procedury zapsána hodnota do druhého listu!
        Cells(1) = 10

    End Sub

    Abyste měli vše pod kontrolou, zvykněte si používat objektové proměnné (pro přiřazení nutný příkaz Set).

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    Sub KlasickyPrikladLepe()

        Dim wshList As Worksheet
        Dim rngOblast As Range

        'aktivace prvního listu
        'zde pouze pro ukázku!
        Worksheets(1).Activate
       
        'přiřazení třetího listu do objektové proměnné
        Set wshList = Worksheets(3)

        'přiřazení oblasti buněk do objektové proměnné
        Set rngOblast = wshList.Range("B2:D6")

        'žádný Activate pro třetí list...
        'žádný Select pro oblast buněk...

        'práce s pozadím (vybrané oblasti)
        'konstrukce With..End With
        With rngOblast
            .Interior.ColorIndex = 6
            .Interior.ColorIndex = xlNone
        End With

    End Sub

    Uvedenou techniku používejte i v rámci přenosu dat mezi dvěma listy a oblastmi (zpravidla zdroj a cíl). Na aktivní list se sice můžete odvolat jako na ActiveSheet, nicméně tento postup doporučuji jen v případě jistoty volání procedury z daného listu (makro přiřazené tlačítku) nebo u obecných maker. V případě obsluhy dvou různých sešitů definujte objektovou proměnnou i pro ně a nespoléhejte se pokud možno na odkaz ActiveWorkbook (aktivní sešit). Naopak vhodný je odkaz na ThisWorkbook (sešit nebo doplněk, z něhož je makro voláno). K aktivní buňce (aktivního listu) můžete přistoupit přes ActiveCell. Objektovou proměnnou na úrovni aplikace se zde nebudeme zabývat.

    Pozn. Pro objekt ActiveSheet nefunguje pod VBA automatický seznam členů (nabídka po zápisu tečky).

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    Sub AktivniObjekty()

        Dim wkbSesit As Workbook
        Dim wshList As Worksheet
        Dim rngBunka As Range

        'přiřazení aktivního sešitu do objektové proměnné
        Set wkbSesit = ActiveWorkbook
        'přiřazení sešitu, v němž leží tato procedura
        'do objektové proměnné
        'Set wkbSesit = ThisWorkbook

        'přiřazení aktivního listu do objektové proměnné
        Set wshList = ActiveSheet

        'přiřazení aktivního buňky do objektové proměnné
        Set rngBunka = ActiveCell

    End Sub

    Ačkoliv to v proceduře uvedeno není, nezapomínejte na konci práce korektně objektové proměnné „resetovat“ (uvolňovat pro ně vyhrazenou paměť) příkazem Set objPromenna = Nothing.

    Tipy pro odkazy na sešit a oblast buněk:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    Sub TipyNaOdkazy()

        Dim wshList As Worksheet
        Dim rngOblast As Range

        'není vhodné spoléhat se na pořadí
        'nepřehledné
        Set wshList = Worksheets(3)

        'lépe je použít název
        Set wshList = Worksheets("List3")

        'nejlepší je odkazovat se na kódové jméno listu
        'kdy není potřeba ani objektová proměnná
        'viz VBA, okno Properies
        'vlastnost Name pro vybraný modul listu
        wshMujList.Range("B10") = "mrkev"

        'odkaz na pojmenovanou buňku
        'viz Definované názvy na listu
        wshMujList.Range("MojeBunka") = "celer"

    End Sub
    Kódové jméno listu
    Kódové jméno listu

    V případě kódového jména listu se tak nemusíte bát, že vám někdo listu přejmenuje. Odkaz na pojmenovanou buňku zajistí platnost odkazu i v případě posunu buňky.

    Zlozvyk číslo jedna máte tedy za sebou a nyní k dalšímu – kopírování hodnot.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    Sub PrikladKopirovaniHodnoty()

        'aktivace prvního listu
        Worksheets(1).Activate
       
        'zkopírování buňky B5
        Range("B5").Copy
       
        'aktivace třetího listu
        Worksheets(3).Activate
       
        'vložení hodnoty do buňky B10
        Range("B10").PasteSpecial (xlPasteValues)
       
        'zrušení režimu kopírování
        Application.CutCopyMode = False

    End Sub

    Pokud kopírujeme něco jiného, než čistě hodnotu, pak se práci s Copy/Paste nevyhneme. Nicméně pro přenos hodnot/vzorců (Value, Formula) se nabízí mnohem efektivnější způsob.

    1
    2
    3
    4
    5
    6
    Sub PrikladKopirovaniHodnotyLepe()
     
        'přímé převzetí obsahu buňky
        Worksheets(3).Range("B10").Value = Worksheets(1).Range("B5").Value

    End Sub

    Jak prosté. I zde bychom měli lépe řídit zdroj a cíl přes objektovou proměnnou. Podotýkám, že takto je možné pracovat i se skrytým listem.

    Pozn. Metoda Copy umožňuje přímé zadání cíle.

    1
    2
    3
    4
    5
    6
    Sub PrikladKopirovaniBunky()

        'překopírování buňky
        Worksheets(1).Range("B5").Copy Worksheets(3).Range("B10")

    End Sub

    Nyní příklady na vytváření nového sešitu.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    Sub NovySesit()

        'vytvoření nového sešitu
        'stane se aktivním
        Workbooks.Add

        'práce s novým sešitem...
        ActiveWorkbook.Worksheets(1).Cells(1) = 10

    End Sub

    Sub NovySesitLepe()

        Dim wkbNovySesit As Workbook

        'vytvoření nového sešitu
        'a jeho přiřazení do objektové proměnné
        Set wkbNovySesit = Workbooks.Add

        'práce s novým sešitem...
        wkbNovySesit.Worksheets(1).Cells(1) = 10

    End Sub
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    Sub NovySesitZKopieListu()

        'vytvoření nového sešitu z kopie stávajícího listu
        Worksheets(1).Copy

        'práce s listem...
        ActiveSheet.Cells(1) = 10

    End Sub

    Sub NovySesitZKopieListuLepe()

        Dim wshSesit As Workbook
        Dim wshList As Worksheet
       
        'vytvoření nového sešitu z kopie stávajícího listu
        'a jeho přiřazení do objektové proměnné
        'nelze uskutečnit, metoda Copy nevrací objekt
        'Set wshList = Worksheets(1).Copy

        'vytvoření nového sešitu z kopie stávajícího listu
        Worksheets(1).Copy
       
        'přiřazení aktivního listu do objektové proměnné
        Set wshList = ActiveSheet
       
        'přiřazení sešitu do objektové proměnné (odkaz na rodiče)
        Set wshSesit = wshList.Parent
        'alternativně
        'Set wshSesit = ActiveWorkbook
       
        'práce s listem...
        wshList.Cells(1) = 10
       
    End Sub

    Doufám tedy, že tímto článkem už jste si „vybrali“ tu správnou cestu a že daný režim zůstane „aktivován“. A nezapomínejte, že Záznamník maker je dobrý sluha i pro pokročilé programátory, ale špatný pán.

    Uložit článek jako PDF