Definovaný název využitím VBA v Excel

Úvodem do definovaných názvy využitím VBA

Jak na definované názvy využitím VBA. Od teorie přes další použití. Pro přehlednost rozděleno na jednotlivé kapitoly:

• Trocha teorie jak na definované názvy
• Data
• Odkázat se na definovaný název - vlastnosti
• Zobrazit definovaný název - hodnotu (hodnoty)
• Vytvořit definovaný název
• Pokročilé ukázky vytvoření - oblasti, pro list
• Funkce v definovaných názvech - oblasti, pro list
• Komentáře k definovaným názvům - oblasti, pro list
• Smazat definovaný název
• Smazat všechny
• Smazat chybné
• Přejmenovat definovaný název
• Vypsat definované názvy využitím Debug i do buněk
• Další související články
• Ke stažení

Trocha teorie jak na definované názvy

Definované názvy jsou úžasná pomůcka, kterou lze využít i ve VBA. Odkazujete se na definovaný název buňky a nemusíte řešit její polohu (zda někdo nepřidal neubral řádky/sloupce). Mnohdy je využití definovaných názvu přehlednější a rychlejší.

Předpokládám, že znáte, ale pro jistotu. Na pasu karet Vzorce v sekci Definované názvy klik na ikonu Správce názvu. V zobrazeném dialogovém okně uvidíte všechny definované názvy, které máte k dispozici. vyberete název a klikem na ikonu Upravit můžete definovaný název upravovat (klik na Nový nadefinujete další nový název). Případně na pasu karet Vzorce v sekci Definované názvy klik na ikonu Definovat název můžete rovnou nadefinovat nový.

Více v samostatném článku.

• Definované názvy v Excel

Data

Mějme k dispozici minitabulku, které obsahuje několik pojmenovaných rozsahů.

Plus pár pojmenovaných názvů:

Odkázat se na definovaný název

Odkázat se na buňku. Vybrat, zjistit její hodnotu. Pokud znám její definovaný název.

MsgBox ("Leden: " & Range("Leden")) ' přes Select jí vybrat Range("Leden").Select

Využitím definované oblasti a "nasetování" (vložení) do proměnné rng ( Range - oblast).

Dim rng As Range Set rng = ThisWorkbook.Worksheets("List1").Range("Leden") MsgBox ("Leden: " & rng.Value)

Případně v článku jak se odkazovat na buŇky pomocí VBA.

Zobrazit definovaný název

Potřebujete-li následně pracovat s hodnotou v definovaném názvu, bud vás zajímat zda jde o

• Pojmenovanou buňku
• Pojemnovanou oblast - v té je potřeba vědět kterou položku potřebujete zobrazit

Pojmenovanou buňku

Asi nejčastěji použivaný způsob s využitím Range, a chci vlastnost Value (viz samostatný článek co Value, Value2, Text, Formula, FormulaR1C1 znamená):

Pocet = Range("Pokus").Value ' Tip nebo další vlastnosti Value2… MsgBox (Pocet)

Případně zkraceně, pogobně jako čtete z buněk [A1], vlastnost Value zadávat nemusíte, pokud ji nezaáte Excel ji použije jako základní

Pocet = [DPH_21] '' Pocet = [DPH_21].Value MsgBox (Pocet)

Nebo můžete využití Names a nesmíte zapomenout na RefersToRange.

Pocet = ThisWorkbook.Names("Pokus").RefersToRange.Value MsgBox (Pocet) ' zobrazí hodnotu

Pokud při využití Names zapomenete na RefersToRange neobdržíte hodnotu Value, ale ozkaz na buňku:

Pocet = ThisWorkbook.Names("Pokus").Value MsgBox (Pocet) ' zobrazí odkaz na bunku

Pokud neuvedete ThisWorkbook, enboli pro který list hodmnotu potřebujete, Excel vrátí chybovou hodnotu:

Pocet = Names("Pokus").Value MsgBox (Pocet) ' chyba

Pojemnovanou oblast

U pojmenované oblasti potřebujete vědět, kterou hodnotu z oblasti chcete.

Pro první:

Pocet = ThisWorkbook.Names("Kategorie").RefersToRange(1).Value MsgBox (Pocet) ' zobrazí hodnotu

Pro druhou:

Pocet = ThisWorkbook.Names("Kategorie").RefersToRange(2).Value MsgBox (Pocet)

Neuvedete-li kterou VBA Excel vratí cyhbovou hodnotu:

Pocet = ThisWorkbook.Names("Kategorie").RefersToRange.Value MsgBox (Pocet) '' Problém jde o oblast ne o jednu hodnotu

Vytvořit definovaný název

Vytvořit definovaný název ve VBA

Jak vytvořit definovné názvy

• Využitím .Names.Add
• Využitím Name

Využitím .Names.Add

Asi nejednodušeji nad celým sešitem vytvořit definovaný název Kategorie, který se bude odkazovat na List1 do oblasti A1:A6, kdy k definování oblasti využijete R1C1 odkazování:

ActiveWorkbook.Names.Add Name:="Kategorie", RefersToR1C1:="=List1!R1C1:R6C1" ' Funguje nad celým sešitem

Pro list "Vložit název" vytvořite definovaný název Kategorie2, který se bude odkazovat na List1 do oblasti A1:A6, kdy k definování oblasti využijete R1C1 odkazování:

Names.Add Name:="Kategorie2", RefersToR1C1:="=List1!R1C1:R6C1" ' Funguje pro list "Vložit název"

Pro list "List1" vytvořite definovaný název Kategorie3, který se bude odkazovat na List1 do oblasti A1:A6, kdy k definování oblasti využijete R1C1 odkazování:

Worksheets("List1").Names.Add Name:="Kategorie3", RefersToR1C1:="=List1!R1C1:R6C1" ' Funguje pro list "List1"

Využitím Name

Podobně můžete nad celým listem vytvořit definovaný název Oddeleni

Worksheets("Vložit název").Range("I2:I6").Name = "Oddeleni" ' Funguje nad celým sešitem

Podobně můžete nad celým listem vytvořit definovaný název Oddeleni2 pokud oblast je na stejném listu na kterém se makro prování.

Range("I2:I6").Name = "Oddeleni2" ' Funguje nad celým sešitem

Připadně pro list "Vložit název" vytvořit definovaný název Oddeleni3

Range("I2:I6").Name = "'Vložit název'!Oddeleni3" ' Funguje pro list "Vložit název"

Připadně pro list "List1" vytvořit definovaný název Oddeleni4

Range("I2:I6").Name = "List1!Oddeleni4" ' Funguje pro list "List1

Pokročilé ukázky vytvoření

Vytvoření definovaného názvu pro oblast

Range("$C$1:$C$7").Name = "TestOblast"

Definovaný název pro daný list. Osobně nedoporučuji, ale kdyby náhodou někdo potřeboval:

ActiveWorkbook.Worksheets("List1").Names.Add Name:="aaa", RefersToR1C1:="=List1!R16C10"

Definované názvy s využitím funkcí

Někdy je potřeba mít dynamicky definovaný název využitím funkce. Například funkce posun =POSUN(Dynamicke!$C$5;1;0;3) kterou lze využít pro definovaní dynamicky se zvětšujících oblastí. V prvním kroku pro ověření funkce posun počet řádku zvolíme napevno:

ActiveWorkbook.Names.Add Name:="Dynamicka1", RefersToR1C1:= _ "=OFFSET(Dynamicke!R5C3,1,0,3)"

Samozřejmě se pak odkážete ve vzorci například SUMA na onen definovaný název:

=SUMA(Dynamicka1)

Pokud zafunguje můžeme doplnit funkci POSUN o POČET2. Doplnil jsem i o komentář o kterém se zmíním i v samostatné kapitole:

ActiveWorkbook.Names.Add Name:="Dynamicka2", RefersToR1C1:= _ "=OFFSET(Dynamicke!R5C3,1,0,COUNTA(Dynamicke!R6C3:R55C3),1)" ActiveWorkbook.Names("Dynamicka2").Comment = "Komentář k definovanému názvu"

Samozdřejmě se ve výpočtech odkáži:

=SUMA(Dynamicka2)

Ukázka nastavení přes menu:

Komentáře k definovaným názvům

K definovaným názvům lze doplnit i komentáře, ať víte co jak proč.

ThisWorkbook.Names("Leden").Comment = "Komentář pro leden"

Odstranění komentáře, neboli jen vymažete text:

ThisWorkbook.Names("Leden").Comment = ""

Ukázka

K řešení

Problém (bug) je při komentování definovaných názvu obsahujících některé vzorce, například POSUN, atd. Tuto anomálii jsme zatím blíže nezkoumal.

Smazat definovaný název

Pokud chcete smazat konkrétní definovaná název:

ThisWorkbook.Names("NazevMesic").Delete

Lze smazati i všechny definované názvy, což je popsáno v další kapitole.

Smazat všechny

POZOR! Smaže všechny definované názvy v sešitu!

For Each DefinovanBunka In ActiveWorkbook.Names 'Vypisu do debug print názvu Debug.Print "Definovaný název: " & DefinovanBunka.Name & " - smazán" ' možno vypsat i obsah ;) a případně do buněk DefinovanBunka.Delete Next DefinovanBunka

Poznámka: vypíše ;)

Smazat chybné

Potřebuji-li smazat chybné názvy. Většinou jde o problémy se smazánými odkazy (smaže se sloupec/řádek kdy se na tento definovaný název odkazuje), kdy definovaný název obsahuje #REF!. Bohužel nové české verze Excel mají místo #REF! chybovou hodnotu #ODKAZ!, proto je potřeba ošetřit obě varianty:

' smazat chybné For Each DefinovanBunka In ActiveWorkbook.Names If InStr(1, DefinovanBunka.RefersTo, "#REF!") > 0 Or InStr(1, DefinovanBunka.RefersTo, "#ODKAZ!") > 0 Then '#ODKAZ! 'Vypisu do debug print Debug.Print "Definovaný název: " & DefinovanBunka.Name & " - smazán" ' Lze i do bunky ' ........ DefinovanBunka.Delete End If Next DefinovanBunka

V přípravě další ukázky:

....

Přejmenovat definovaný název

Pokud chcete přejmenovat exitující definovaný název na jiný

' Definovaný název Oddeleni musí existovat Names("Oddeleni").Name = "Test"

Vypsat definované názvy

Jak vypsat definované názvy a pro které rozsahy platí. Lze využít:

• Využití Debug.Print
• Vypsat do buněk

Využitím Debug.Print

Pokud potřebujete jen vypsat pomocí Debug.Print

For Each DefinovanBunka In ActiveWorkbook.Names Debug.Print DefinovanBunka.Name, DefinovanBunka.RefersTo Next bunka

Zapsaní informací do buněk

Pokud si chci vypsat do buněk, využitím For Each:

Radek = 1 For Each DefinovanBunka In ActiveWorkbook.Names Cells(Radek, 5) = DefinovanBunka.Name Cells(Radek, 6) = " " & DefinovanBunka.RefersTo Radek = Radek + 1 Next DefinovanBunka

Nebo využitím počtu v cyklu:

Set DefinovanaBunka = ActiveWorkbook.Names Set wks = Worksheets(2) 'který list For r = 1 To DefinovanaBunka.Count wks.Cells(r, 9).Value = DefinovanaBunka(r).Name wks.Cells(r, 10).Value = DefinovanaBunka(r).RefersTo ' hodnota wks.Cells(r, 11).Value = "'" & DefinovanaBunka(r).RefersTo Next r

Další související články

• Jak na definované názvy bez VBA
• ...

Ke stažení

Soubor ke stažení zdarma v přípravě. Soubor využívá makra.

Závěrem

Chybí vám v článku něco, co se týká definovaných názvu? Můžete zmínit v komentářích. Pokusím se článek doplnit/vylepšit.

Článek byl aktualizován: 26.08.2021 19:19

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.