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.

Klikni a stahuj!