Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » vba-listy-bunky » definovany-nazev-excel-vba
Microsoft Excel logo

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na definovaný název (Defined Name v angličtině) využitím VBA. Od zjištění vlastností již pojmenované buňky, přes její pojmenování, případně odstranění definovaného názvu.

Ú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

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ý.

Excel definovaný název ukázka - teorie

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

Data

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

Excel definovaný název - data 1

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

Excel definovaný název - data 1

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.

Vytvořit definovaný název

Vytvoření definovaného názvu (1)

Range("$B$1").Name = "NazevPocty"

Vytvoření definovaného názvu (2)

ThisWorkbook.Names.Add Name:="NazevMesic", RefersTo:=Range("A1")

Nadefinovat vybranou (vyselektovanou) oblast

ThisWorkbook.Names.Add Name:="MojeOblast", RefersTo:=Selection

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:

Excel definovaný název - dynamicky ukázka

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

Excel definovaný název - dynamicky ukázka - komentář

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:

....

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

Microsoft Excel VBA - stahuj logo

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: 06.01.2019 18:05

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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ů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 1442

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.