načítání...
menu
nákupní košík
Košík

je prázdný
a
b

Kniha + CD: Excel VBA -- Výukový kurz - Martin Král

Excel VBA -- Výukový kurz
-15%
sleva

Kniha + CD: Excel VBA
Autor: Martin Král
Podnázev: Výukový kurz

Chcete vědět, jak lze pomocí jazyka VBA ušetřit čas? Potřebujete ke své práci více, než nabízejí funkce a vzorce v Excelu? Toužíte poznat tipy a triky odborníka, který se efektivním ... (celý popis)
Titul doručujeme za 5 pracovních dní
Médium: Kniha + CD
Vaše cena s DPH:  690 Kč 587
+
-
rozbalKdy zboží dostanu
19,6
bo za nákup
rozbalVýhodné poštovné: 29Kč
rozbalOsobní odběr zdarma

ukázka z knihy ukázka

Titul je dostupný ve formě:
tištěná forma elektronická forma

hodnoceni - 83.2%hodnoceni - 83.2%hodnoceni - 83.2%hodnoceni - 83.2%hodnoceni - 83.2% 100%   celkové hodnocení
3 hodnocení + 0 recenzí

Specifikace
Nakladatelství: » Computer press
Médium: Kniha + CD
Rok vydání: 2017
Počet stran: 504
Rozměr: 167 x 225 mm
Úprava: ilustrace
Vydání: Vyd. 1.
Vazba: brožovaná lepená
Datum vydání: 11. 1. 2017
Nakladatelské údaje: Brno, Computer Press, 2010
ISBN: 9788025123584
EAN: 9788025123584
Ukázka: » zobrazit ukázku
Popis

Chcete vědět, jak lze pomocí jazyka VBA ušetřit čas? Potřebujete ke své práci více, než nabízejí funkce a vzorce v Excelu? Toužíte poznat tipy a triky odborníka, který se efektivním využitím jazyka VBA zabývá již několik let? V této unikátní příručce naleznete obrovské množství konkrétních ukázek a řešení běžných problémů, s nimiž se autor setkal ve vlastní praxi a které rovněž vedou k častým dotazům na internetových fórech. Proč tedy prohledávat internetové zdroje, když můžete rady zkušeného autora najít v jediné knize? Publikace je mistrně strukturovaným výukovým kurzem: „učivo“ je probíráno „po kapkách“ tak, aby na sebe vše pečlivě navazovalo, za každou částí lekce navíc najdete otázky k tématu. V knize naleznete mimo jiné tato témata: - Nejdůležitější typy proměnných a kdy je použít - Text, čísla a datum v jazyce VBA - Popis objektového modelu Microsoft Excel - Uživatelské funkce - Vytváření ovládacích prvků - Programování událostních procedur a vlastních tříd - Formuláře pro zadávání dat - Práce s editorem VBA - Příklady automatizace jiných aplikací Microsoft Office Na CD naleznete všechny kódy příkladů z knihy a sbírku užitečných souborů z dílny autora knihy a předních vývojářů pro Excel. O autorovi: Martin Král pracuje jako vrcholový manažer v oblasti čerpacích stanic s praxí v ČR, Chorvatsku a na Ukrajině. Znalost aplikací Microsoftu využívá k automatizaci prakticky všech běžných kancelářských úloh. Je editorem a moderátorem fóra www.utteraccess.com a hlavním přispěvatelem v sekci o aplikaci Excel. Jeho online aktivity byly v letech 2007–2009 oceněny titulem MVP (Microsoft Most Valuable Professional). (výukový kurz)

Předmětná hesla
Zařazeno v kategoriích
Martin Král - další tituly autora:
 (e-book)
Excel VBA Excel VBA
 
Ke zboží "Excel VBA -- Výukový kurz" doporučujeme také:
VBA Excel v příkladech VBA Excel v příkladech
Excel 2000-2007 -- záznam, úprava a programování maker, 2., akt. vyd. Excel 2000-2007
Programování v Excelu 2010 a 2013 -- záznam, úprava a programování maker Programování v Excelu 2010 a 2013
 
Recenze a komentáře k titulu
Zatím žádné recenze.


Ukázka / obsah
Přepis ukázky

LEKCE 3

Uživatelské funkce (UDF)

V této lekci najdete:

Procedury a funkce .............................................................................................................. 230

Použití vlastních funkcí jako vzorců v listu ....................................................................... 236

Příklady uživatelských funkcí ............................................................................................. 239

‹

‹ ‹

!Kniha_K1673.indb 229!Kniha_K1673.indb 229 26.4.2010 14:09:1826.4.2010 14:09:18


230

Lekce 3: Uživatelské funkce (UDF)

Procedury a funkce

Většina příkladů kódu, který jste zatím v knize viděli, měla podobu tzv. procedur. Procedura je pro

gram ve VBA, který nevrací žádnou hodnou. Například následující procedura skryje všechny listy

aktivního sešitu kromě listu s názvem Report (alespoň jeden viditelný list musí v každém případě

v sešitu zůstat). Všimněte si deklarace obecné objektové proměnné sh As Object. Pokud bychom

použili striktní typ Worksheet, kód by nepracoval pro listy s grafem, které jsou členy kolekce Sheets,

avšak nejsou členy kolekce Worksheets. Protože typ objektu Sheet neexistuje, musíte použít pro

měnnou obecnou.

03-01 Funkce UDF.xlsm

modul MSubs

Sub HideSheets()

Const SHEET_NAME As String = „Report“

Dim x As Worksheet

Dim sh As Object

‘ pokud list neexistuje, kód ukončit

On Error Resume Next

Set x = Sheets(SHEET_NAME)

If x Is Nothing Then

Exit Sub

Else

x.Visible = xlVisible

End If

On Error GoTo 0

‘ skrýt všechny listy kromě daného

For Each sh In ActiveWorkbook.Sheets

If StrComp(sh.Name, SHEET_NAME, vbTextCompare) <> 0 Then

sh.Visible = xlSheetHidden

End If

Next sh

End Sub

Tato procedura, která může být deklarována jako Public (je spustitelná i z jiných modulů nebo přes

uživatelské rozhraní) nebo Private (je spustitelná pouze z jiných procedur a funkcí v tomtéž modu

lu), nepřijímá žádné parametry. Procedury obecně nepředávají svým jménem žádnou hodnotu vněj

šímu kódu. Samozřejmě že je v nich možno měnit hodnoty modulových i globálních proměnných,

které uchovávají svou hodnotu i po skončení této procedury, pokud je sešit otevřen. Tato procedu

ra obecně příliš užitečná není, protože pracuje pouze s aktivním sešitem a pro zadané jméno listu,

vepsané v kódu.

Můžete napsat proceduru obecnější, která bude přijímat dva parametry. Prvním bude povinné jméno

listu a druhým bude nepovinný parametr, přijímající objekt typu Workbook. Pokud tento parametr

chybí, kód použije aktivní sešit.

!Kniha_K1673.indb 230!Kniha_K1673.indb 230 26.4.2010 14:09:1826.4.2010 14:09:18


231

03-01 Funkce UDF.xlsm

modul MSubs

Sub HideSheets2(SheetName As String, Optional ObjectWB As Workbook)

Dim wb As Workbook

Dim x As Worksheet

Dim sh As Object

‘ pokud list neexistuje, kód ukončit

On Error Resume Next

Set x = Sheets(SheetName)

If x Is Nothing Then

Exit Sub

Else

x.Visible = xlVisible

End If

On Error GoTo 0

‘ pokud parametr chybí, použij aktivní sešit

If ObjectWB Is Nothing Then

Set wb = Activewrokbook

Else

Set wb = ObjectWB

End If

‘ skrýt všechny listy kromě daného

For Each sh In wb.Sheets

If StrComp(sh.Name, SheetName, vbTextCompare) <> 0 Then

sh.Visible = xlSheetHidden

End If

Next sh

End Sub

Tuto proceduru již není možné spustit přes uživatelské prostředí. Musíte ji spustit z jiné procedury

nebo z okna Immediate jedním z následujících způsobů:

Sub Test_HideSheets()

HideSheets2 „Report“, „02-08 UDFS.xlsm“

Call HideSheets2(„Report“, „02-08 UDFS.xlsm“)

End Sub

Pokud použijete výraz Call, seznam argumentů musí být uzavřen do závorek. Pokud výraz Call

nepoužijete, seznam argumentů do závorek uzavřen být nesmí (ačkoli můžete samozřejmě do závo

rek uzavřít jednotlivé argumenty!).

Uživatelské funkce se od procedur liší tím, že vrací hodnotu, která v kódu dále může a nemusí být

využita. Proceduru můžete změnit na funkci prostou změnou slova Sub na Function. Bude praco

Procedury a funkce

!Kniha_K1673.indb 231!Kniha_K1673.indb 231 26.4.2010 14:09:1926.4.2010 14:09:19


232

Lekce 3: Uživatelské funkce (UDF)

vat stejně, ale už ji nebude možno spustit na kartě Vývojář, skupina Kód. Můžete ji využít pouze

v další proceduře nebo funkci, případně spustit v okně Immediate. Hlavní výhodou je ale možnost

vracet hodnotu. V této knize jste se již setkali s funkcí, která vrací True, pokud je list v daném sešitu

přítomen, a False, pokud chybí:

Public Function bWorksheetExists(SheetName As String) As Boolean

On Error Resume Next

bWorksheetExists = Len(Worksheets(SheetName).Name) > 0

Err.clear

On Error Goto 0

End Function

Tento kód ovšem pracuje pouze pro aktivní sešit, neboť neumožňuje specifikovat sešit konkrétní.

Obecnější funkce, která bude pracovat s aktivním sešitem, pokud druhý argument chybí, může vypa

dat například takto:

03-01 Funkce UDF.xlsm

modul MFunctions

Public Function bWorksheetExists(SheetName As String, _

Optional ObjectWB As Workbook) _

As Boolean

Dim wb As Workbook

If ObjectWB Is Nothing Then

Set wb = ActiveWorkbook

Else

Set wb = ObjectWB

End If

On Error Resume Next

bWorksheetExists = Len(wb.Worksheets(SheetName).Name) > 0

Err.Clear

On Error GoTo 0

End Function

Pokud použijete tento kód v původní proceduře, můžete ji přepsat následujícím způsobem:

03-01 Funkce UDF.xlsm

modul MSubs

Sub HideSheets3(SheetName As String, Optional ObjectWB As Workbook)

Dim wb As Workbook

Dim sh As Object

‘ pokud parametr chybí, použij aktivní sešit

If ObjectWB Is Nothing Then

Set wb = ActiveWorkbook

Else

!Kniha_K1673.indb 232!Kniha_K1673.indb 232 26.4.2010 14:09:1926.4.2010 14:09:19


233

Set wb = ObjectWB

End If

‘ pokud list neexistuje, kód ukončit

If bWorksheetExists(SheetName, wb) Then

Set sh = Sheets(SheetName)

Else

Exit Sub

End If

‘ skrýt všechny listy kromě daného

For Each sh In wb.Sheets

If StrComp(sh.Name, SheetName, vbTextCompare) <> 0 Then

sh.Visible = xlSheetHidden

End If

Next sh

End Sub

Pokud jsou předávanými argumenty proměnné, můžete je funkci nebo proceduře předat pomocí klí

čových slov ByVal (předání hodnotou) a ByRef (předání odkazem). Výchozí metodou předání při

vynechání těchto slov je ByRef. Pokud je argument předán odkazem na původní proměnnou, vnořená

funkce tuto původní proměnnou změní. Při předání hodnotou je vytvořena kopie původní proměnné

a ta zůstává beze změny. Klíčové slovo ByVal se používá zejména při předávání argumentů tzv. API

funkcím Windows, kdy by změna původní systémové hodnoty mohla způsobit i pád systému.

Vše ozřejmí následující příklad. Mějme jednoduchou proceduru, která zvyšuje hodnoty tří proměn

ných o jedničku:

Sub Increment(X As Long, _

ByRef Y As Long, _

ByVal Z As Long)

X = X + 1

Y = Y + 1

Z = Z + 1

End Sub

Proceduru vnoříme do testovací procedury, která jí předá tři proměnné:

Sub Test_Increment()

Dim lX As Long, lY As Long, lZ As Long

lX = 1: lY = 1: lZ = 1

Call Increment(lX, lY, lZ)

Debug.Print lX, lY, lZ

End Sub

Procedury a funkce

!Kniha_K1673.indb 233!Kniha_K1673.indb 233 26.4.2010 14:09:1926.4.2010 14:09:19


234

Lekce 3: Uživatelské funkce (UDF)

Výsledkem je výpis, který ukáže, že první dvě původní proměnné, předané odkazem, byly zvýšeny

o jedničku. Třetí proměnná zůstala beze změny, neboť subprocedura pracovala s její kopií:

2 2 1

Funkci můžete jako argument předat i pole. Rovněž tak může funkce pole i vrátit. Následující funkce

přijímá pole hodnot, z nichž vybere pouze jedinečné hodnoty a ty setřídí.

03-01 Funkce UDF.xlsm

modul MFunctions

Public Function SortUniques(Values As Variant)

Dim Uniques(), y, i As Long

Dim col As New Collection

‘ pro případ, že je funkce volána z listu

If TypeName(Values) = „Range“ Then

y = WorksheetFunction.Transpose(Values)

Else

y = Values

End If

‘pokud argument tvoří jen jedna hodnota

If Not IsArray(y) Then

SortUniques = y

Exit Function

End If

‘pokud je pole vícerozměrné, vra chybu

On Error Resume Next

Err.Clear

If UBound(y, 2) > 0 Then

If Not CBool(Err.Number) Then

SortUniques = CVErr(xlErrNum)

Err.Clear

Exit Function

End If

End If

On Error GoTo 0

‘kolekce jedinečných hodnot

On Error Resume Next

For i = LBound(y) To UBound(y)

col.Add y(i), CStr(y(i))

Next i

On Error GoTo 0

‘kopírování kolekce do pole

ReDim Uniques(1 To col.Count)

For i = 1 To col.Count

Uniques(i) = col.Item(i)

Next i

!Kniha_K1673.indb 234!Kniha_K1673.indb 234 26.4.2010 14:09:1926.4.2010 14:09:19


235

‘volání třídicího algoritmu

BubbleSort Uniques

‘přiřazení setříděného pole do proměnné

SortUniques = Uniques

End Function

Funkce je mírně složitější, než by mohla být, protože je její začátek přizpůsoben tak, aby ji bylo možno

volat i z listu. Pokud je do argumentu Values typu Variant předán objekt Range, znamená to, že

funkce byla zavolána z listu. Do pomocného pole je v takovém případě přiřazená transformace této

oblasti tak, aby bylo výsledkem vodorovné jednorozměrné pole. Při přímém přiřazení by vzniklo

dvojrozměrné pole svislé.

Dalším krokem je kontrola na jednu hodnotu. V takovém případě samozřejmě není co třídit, hod

nota je předána do funkce a funkce končí.

Ačkoli funkci můžete modifikovat tak, aby pracovala i pro vícerozměrná pole, v tomto případě kon

trolujeme, jestli je horní mez druhého rozměru větší než nula. Pokud ano, pole je vícerozměrné

a funkci je přiřazena chybová hodnota. Pokud ne, výraz UBound(y,2) vyvolá výjimku a výraz Not

Cbool(Err.Number) bude vyhodnocen jako False.

Další část kódu přidá jednotlivé prvky pole do kolekce, a protože je používán jednoznačný klíč shod

ný s hodnotou, žádná hodnota nebude do kolekce přidána dvakrát.

Obsah kolekce poté nahrajeme

do pole a pole předáme pro

ceduře BubbleSort ke třídění.

Výsledek je nakonec zpětně

předán funkci SortUniques.

Pro vývojáře v Excelu má

význam, že funkci je možno

použít v listu několika způsoby,

jak je vidět na obrázku.

Do oblastí E1:E10 a A14:J14

jsme zadali jediný matico

vý vzorec tak, že jsme vybra

li celou oblast, zapsali vzo

rec a stiskli Ctrl+Shift+Enter.

V oblasti G1:G10 jsou obyčej

né vzorce.

Obrázek 3.1: Použití vlastní funkce v listu

Procedury a funkce

!Kniha_K1673.indb 235!Kniha_K1673.indb 235 26.4.2010 14:09:1926.4.2010 14:09:19


236

Lekce 3: Uživatelské funkce (UDF)

Použití vlastních funkcí jako vzorců v listu Pokud chcete použít funkci jako vzorec v listu, musí být zapsána v normálním modulu. Pokud bude v modulu některé třídy, ať již se jedná o list, sešit, formulář nebo vlastní třídu, bude pro uživatelské rozhraní neviditelná. Rovněž nesmí být deklarována klíčovým slovem Private. Další důležité rozhodnutí, které musíte učinit, je, ve kterém sešitu bude funkce uložena. Máte na výběr z několika možností. Pokud je funkce pouze pro potřebu na vašem počítači a chcete, aby byla dostupná pro jakýkoli otevřený sešit, můžete ji uložit do normálního modulu ve skrytém sešitu Personal.xlsb. Tento sešit je vytvořen automaticky při nahrání prvního makra s volbou Uložit makro do osobního sešitu a ve Windows Vista je uložen v osobní složce %AppData%RoamingMicrosoftExcelXLSTART. Pokud funkce souvisí se sešitem, který je distribuován mezi více uživatelů, můžete funkci umístit do modulu tohoto sešitu. Má-li být funkce k dispozici více uživatelům pro různé sešity, můžete ji distribuovat pomocí doplňku (Add-in). Kód v nainstalovaném doplňku poběží i při vysokém stupni zabezpečení před makry. Jestliže jsou soubory vytvářeny na základě šablony, uložte funkci do modulu této šablony. Když znáte nazpaměť jméno funkce a její parametry, můžete vzorec zapsat do buňky přímo. Pokud si nejste jisti, můžete použít standardní postup vložení funkce přes průvodce. Jako příklad jsme vzali funkci, která z libovolného textu extrahuje číslice, přičemž převodem na typ Long nevýznamné vedoucí nuly zleva „zahodí“. Toto je samozřejmě možno změnit vypuštěním závěrečné konverze: 03-01 Funkce UDF.xlsm modul MFunctions Function GetNumbers(InputString As String) Dim tmp As String, char As String Dim i As Long For i = 1 To Len(InputString) char = Mid$(InputString, i, 1) If char Like „[0-9]“ Then tmp = tmp & char End If Next i If Len(tmp) > 0 Then GetNumbers = CLng(tmp) Else GetNumbers = vbNullString End If End Function Vyberte buňku, do které chcete vzorec vložit, a na kartě Vzorce, sada Knihovna funkcí, klepněte na položku Vložit funkci. Vyberte kategorii Vlastní a vyberte funkci GetNumbers.

Obrázek 3.2: Vložení funkce ze seznamu

dostupných vlastních funkcí

!Kniha_K1673.indb 236!Kniha_K1673.indb 236 26.4.2010 14:09:2026.4.2010 14:09:20


237

Po klepnutí na tlačítko OK se

objeví standardní dialog, ve

kterém můžete vybrat vstupní

buňku.

Pro soukromé potřeby to větši

nou postačí, avšak pokud budete

funkci distribuovat jiným uživa

telům, pravděpodobně budete

chtít přidat alespoň stručnou

nápovědu a také funkci zařadit

do příslušné kategorie. K tomu

slouží metoda MacroOptions

objektu Application. Pokud se podíváte do nápovědy VBA, zjistíte, že textové funkce jsou v kate

gorii číslo 7.

Procedura, která funkci zaregistruje, je jednoduchá a přijímá tři parametry:

03-01 Funkce UDF.xlsm

modul MSubs

Sub RegisterFunction(fName As String, fDescription, fCategory)

Application.MacroOptions Macro:=fName, _

Description:=fDescription, _

Category:=fCategory

End Sub

Toto proceduru můžete volat pro zaregistrování funkce například takto:

Sub Register_GetNumbers()

Dim fnName As String

Dim fnDesc As String

Dim fnCat As Long

fnName = „GetNumbers“

fnDesc = „Extrahuje čísla z libovolného řetězce.“ & vbLf

fnDesc = fnDesc & „Nevýznamné nuly nejsou brány v úvahu.“

fnCat = 7

Call RegisterFunction(fnName, fnDesc, fnCat)

End Sub

Pokud nyní znovu spustíte průvodce vložením vzorce, funkce již nebude v kategorii vlastní, nýbrž

v kategorii textové funkce.

Pokud chcete vzorec odregistrovat, zavolejte proceduru tímto způsobem:

Sub UnRegister_GetNumbers()

Dim fnName As String

fnName = „GetNumbers“

Obrázek 3.3: Dialogové okno vložení vlastní funkce

Použití vlastních funkcí jako vzorců v listu

!Kniha_K1673.indb 237!Kniha_K1673.indb 237 26.4.2010 14:09:2026.4.2010 14:09:20


238

Lekce 3: Uživatelské funkce (UDF)

Call RegisterFunction(fnName, Empty, Empty) End Sub Pokud toto provedete, funkce již nebude viditelná v průvodci, a to v žádné kategorii. Na listu ji však můžete dále použít. Jestliže ji chcete vrátit do kategorie vlastní, zaregistrujte ji pod číslem 14. Pokud místo čísla předáte jméno vlastní kategorie, bude tato kategorie vytvořena. Předešlé verze Excelu po odregistrování funkci ponechaly v kategorii Vlastní. Zbavit se jí nadobro nebyl snadný úkol a jediná možnost byla využití jazyka XLM. Tato metoda přesahuje rámec této knihy. V kategorii Vlastní se objevují všechny funkce ve VBA, které jsou umístěny v normálních modulech a nejsou deklarovány klíčovým slovem Private. Pokud tyto funkce nechcete v dialogu Vložit funkci vidět, použijte v záhlaví modulu výraz Option Private Module. Funkce bude teoreticky stále možné v listu jako vzorce použít, ale nebudou se objevovat ani v rozbalovacím seznamu funkcí, ani v dialogu. Musíte jejich jméno a argumenty znát nazpaměť. Uživatelská funkce, použitá v listu (UDF), nemůže měnit žádné vlastnosti buněk v listu ani hodnoty jiných buněk, ale pouze hodnoty buňky, ve které je zapsána. To se samozřejmě týká i formátu. Pro dynamické přizpůsobení formátu hodnotě používejte podmíněný formát. Funkce nevrátí chybu, ale všechny příkazy pokoušející se měnit cokoli jiného, než hodnotu buňky se vzorcem, budou ignorovány. Funkce nemůže odstraňovat ani přidávat listy. UDF nemůže měnit ani většinu nastavení aplikace Excel. Například tyto příkazy budou ignorovány: ActiveWindow.DisplayGridlines = False Application.DisplayFormulaBar = False Application.StatusBar = „Kalkuluji...“ Application.Wait Now + TimeValue(„0:00:01“) V Excelu 2007 je však na rozdíl od předešlých verzí možno měnit typ kurzoru: Application.Cursor = xlWait Application.Cursor = xlDefault Výše uvedené se týká pouze funkce, použité ve vzorci v listu. Tatáž funkce, volaná z jiné procedury nebo funkce, vykoná všechny příkazy. UDF musí být umístěna v normálním modulu. Nesmí být umístěna v modulu jakékoli třídy, ať již se jedná o třídu ThisWorkbook, Worksheet, Chart, formulář, nebo třídu vlastní. Nesnažte se měnit argumenty, předané pomocí klíčového slova ByRef. Jakmile se funkce pokusí změnit hodnotu v předané oblasti, je porušeno pravidlo uvedené výše a funkce vrátí chybu #HODNOTA!:

Obrázek 3.4: Vlastní funkce zaregistrovaná

v kategorii textových funkcí s vlastním

komentářem

!Kniha_K1673.indb 238!Kniha_K1673.indb 238 26.4.2010 14:09:2026.4.2010 14:09:20


239

03-01 Funkce UDF.xlsm

modul MFunctions

Function FindRow(ByRef rg As Range, str As String)

Dim clFound As Range

Set clFound = rg.Find(str, LookIn:=xlValues, LookAt:=xlPart)

If clFound Is Nothing Then

FindRow = 0

Else

FindRow = clFound.Row

End If

‘tento řádek zaviní chybu

rg.Cells(1, 1) = „dummy“

End Function

Do UDF vždy předávejte jako argumenty všechny buňky nebo oblasti, na kterých je výsledek závislý.

Pokud tak neučiníte, funkce bude přepočítána pouze tehdy, když se změní obsah oblastí, které jsou

jako argumenty předány. Pomocí příkazu Application.Volatile můžete funkci přinutit přepočítá

vat se při změně jakékoli buňky, avšak toto vede ke znatelnému zpomalení vaší aplikace. Proto toto

řešení používejte pouze v nouzi, kdy všechny argumenty jako hodnoty předat nelze.

Pokud se UDF může odkazovat na prázdné buňky, doporučujeme kontrolu pomocí funkce

IsEmpty().

Příklady uživatelských funkcí

Na závěr této lekce uvedeme několik funkcí, které mají skutečný praktický význam. Pokud je peč

livě prostudujete, jistě dokážete napsat funkce podobné, které budou sloužit vašemu konkrétnímu

účelu.

Funkce vracející výši progresivní daně

Poměrně běžnou úlohou je výpočet částky, kdy je v určitých pásmech pevná procentní sazba, jako je

tomu například u v politice často diskutované progresivní daně. Následující funkce přijímá tři para

metry: vstupní částku, spodní hranice pásem a jejich sazby. Samozřejmě předpokládáme, že násle

dující spodní hranice je zároveň horní hranicí pásma předcházejícího.

03-01 Funkce UDF.xlsm

modul MFunctions

Public Function IncomeTax(Income As Double, _

rgHurdles As Range, _

rgRates As Range)

Dim cl As Range, i As Long

Dim tmp As Double

If rgHurdles.Cells.Count <> rgRates.Cells.Count Then

IncomeTax = CVErr(xlErrNum)

Exit Function

Příklady uživatelských funkcí

!Kniha_K1673.indb 239!Kniha_K1673.indb 239 26.4.2010 14:09:2026.4.2010 14:09:20


240

Lekce 3: Uživatelské funkce (UDF)

End If For Each cl In rgHurdles i = i + 1 If Income > cl.Value Then If Income > cl(2).Value And i <> rgHurdles.Cells.Count Then tmp = tmp + (cl(2).Value - cl.Value) * rgRates(i) Else tmp = tmp + (Income - cl.Value) * rgRates(i) End If Else GoTo ExitHere End If Next cl ExitHere: IncomeTax = tmp End Function Funkci můžete použít například podle obrázku. Například pro základ 120000 Kč bude daň vypočítána takto: (120000–109200)*20%+109200*15% = 18540 Funkce samozřejmě funguje i pro snižující se sazby, jak tomu bývá například u degresivní provize z prodeje. Funkce sčítající barvy Ačkoli pro obarvení buněk podle hodnot je správné využít podmíněný formát, někdy máte obarvené buňky přímo přes uživatelské menu a chcete spočítat jejich počet pro danou barvu, nebo součet buněk dané barvy. Pro různé typy kalkulací můžete samozřejmě napsat různé funkce, avšak následující funkce byla navržena tak, aby volbou třetího parametru bylo možno rozlišit mezi počtem („Count“) a součtem („Sum“): 03-01 Funkce UDF.xlsm modul MFunctions Public Function CalcColor(rg As Range, _ Optional rgColor As Range, _ Optional Operation As String) Dim cl As Range Dim lColor As Long Dim tmp If rgColor Is Nothing Then Set rgColor = Application.Caller

Obrázek 3.5: Použití vlastní funkce, vypočítávající progresivní

daň, v listu

!Kniha_K1673.indb 240!Kniha_K1673.indb 240 26.4.2010 14:09:2126.4.2010 14:09:21


241

If Len(Operation) = 0 Then Operation = „Sum“ If IsError(Application.Match(Operation, Array(„Sum“, „Count“, 0), 0)) Then CalcColor = CVErr(xlErrNum) Exit Function End If lColor = rgColor.Interior.ColorIndex For Each cl In rg If cl.Interior.ColorIndex = lColor Then Select Case Operation Case „Sum“ tmp = tmp + Val(cl.Value) Case „Count“ tmp = tmp + 1 End Select End If Next cl CalcColor = tmp End Function Funkce kontroluje, zda byly nepovinné parametry zadány. Pokud tomu tak není, použije jako referenční buňku barvy buňku, ve které je vzorec zapsán. Výchozím typem kalkulace je sčítání. Obrázek ukazuje možné použití. Výsledek 30 dostanete i zapsáním následujícího vzorce do buňky E6: =CalcColor(B2:B11) Funkce vracející údaje o souboru a uživateli Funkci zkoumající, zda list v daném sešitu existuje, jsme již uvedli. Rovněž následující funkci, která prověří, zda je sešit otevřen, jste již v knize viděli: Function bIsWorkbookOpen(FileName As String) As Boolean On Error Resume Next bIsWorkbookOpen = CBool(Len(Workbooks(FileName).Name)) End Function Jméno souboru a jeho plné cesty můžete v listu zobrazit pomocí následujících funkcí: 03-01 Funkce UDF.xlsm modul MFunctions Function WBName() WBName = ThisWorkbook.Name

Obrázek 3.6: Použití funkce, počítající s buňkami určité barvy,

v listu

Příklady uživatelských funkcí

!Kniha_K1673.indb 241!Kniha_K1673.indb 241 26.4.2010 14:09:2126.4.2010 14:09:21


242

Lekce 3: Uživatelské funkce (UDF)

End Function

Function WBFullName()

WBFullName = ThisWorkbook.FullName

End Function

Function WBPath()

WBPath = ThisWorkbook.Path

End Function

Objekt Application má samozřejmě řadu dalších vlastností, které můžete tímto způsobem využít

ve své funkci.

Dalším objektem, který můžete použít ve funkci, je objekt FileSystemObject z knihovny Micro

soft Scripting Runtime (soubor scrrun.dll). Například následující funkce vrátí velikost souboru

v bytech:

03-01 Funkce UDF.xlsm

modul MFunctions

Function FileSize(FullName As String)

Dim fso As Object

Dim f As Object

Dim fl As File

‘ inicializace objektů

Set fso = CreateObject(„Scripting.FileSystemObject“)

On Error Resume Next

Set f = fso.GetFile(FullName)

On Error GoTo 0

‘ pokud soubor neexistuje, vra prázdný řetězec

If f Is Nothing Then Then

FileSize = vbNullString

Else

FileSize = f.Size

End If

‘ uvolnění proměnných

Set f = Nothing

Set fso = Nothing

End Function

Funkci můžete použít v kombinaci s předešlými vzorci a můžete v listu zobrazit aktuální velikost

sešitu. Do kterékoli buňky zapište tento vzorec.

=FILESIZE(WBFULLNAME())

Pomocí objektu FileSystemObject můžete získat mnoho dalších informací, jako například infor

mace o souboru, jak je popsáno v tabulce 3.1.

!Kniha_K1673.indb 242!Kniha_K1673.indb 242 26.4.2010 14:09:2126.4.2010 14:09:21


243

Tabulka 3.1: Vlastnosti objektu File (soubor)

Vlastnost Význam

f.DateCreated Datum a čas vytvoření souboru

f.DateLastAccessed Datum a čas posledního přístupu k souboru

f.DateLastModified Datum a čas posledního uložení souboru

f.Drive Písmeno diskové jednotky, na níž je soubor uložen

f.ParentFolder Složka, ve které se soubor nachází

f.Path Úplná cesta k souboru včetně jeho jména (!)

f.Type Popis typu souboru tak, jak je vidět v průzkumníku Po mírné modifikaci bude funkce pracovat pro složku: 03-01 Funkce UDF.xlsm modul MFunctions Function FolderSize(FolderPath As String) Dim fso As Object Dim fld As Object ‘ inicializace objektů Set fso = CreateObject(„Scripting.FileSystemObject“) On Error Resume Next Set fld = fso.GetFolder(FolderPath) On Error GoTo 0 ‘ pokud složka neexistuje, vra prázdný řetězec If fld Is Nothing Then FolderSize = vbNullString Else FolderSize = fld.Size End If ‘ uvolnění proměnných Set fld = Nothing Set fso = Nothing End Function Objekt Folder má podobné vlastnosti jako objekt File. Má samozřejmě i dodatečné členy, jako například kolekci Subfolders. Využití této kolekce umožňuje procházet složku včetně všech jejích vnořených složek. Následující funkce prochází složku a hledá soubory, jejichž název obsahuje danou příponu. Pokud narazí na podsložky, zavolá sama sebe a znovu prochází všechny soubory dané podsložky. Tento algoritmus se nazývá rekurzivní. Jiné jeho použití je například u metody třídění Quicksort, která využívá binární třídění a je obecně rychlejší, než metoda Bubble-sort.

Příklady uživatelských funkcí

!Kniha_K1673.indb 243!Kniha_K1673.indb 243 26.4.2010 14:09:2226.4.2010 14:09:22


244

Lekce 3: Uživatelské funkce (UDF)

Následující příklad spočítá počet souborů určitého typu v dané složce.

03-01 Funkce UDF.xlsm

modul MFunctions

Function CountFiles(sFolder As String, _

sExtension As String, _

Optional bSubDirs As Boolean)

Dim fso As Object

Dim fld As Object, sfld As Object

Dim f As Object

Dim lCounter As Long

Set fso = CreateObject(„Scripting.FileSystemObject“)

Set fld = fso.GetFolder(sFolder)

For Each f In fld.Files

If StrComp(Right$(f.Name, 4), sExtension, vbTextCompare) = 0 Then

lCounter = lCounter + 1

End If

Next f

If bSubDirs Then

For Each sfld In fld.SubFolders

lCounter = lCounter + CountFiles(sfld.Name, sExtension, True)

Next

End If

CountFiles = lCounter

Set fso = Nothing

End Function

Funkci můžete použít například takto:

CountFiles(ThisWorkbook.Path, „xlsm“, True)

V tomto případě vrátí počet všech sešitů s příponou „xlsm“ (sešit Excelu s povolenými makry) ve

složce, ve které se nachází sešit, z něhož kód spouštíte, včetně všech jejích podsložek.

Jméno uživatele nainstalované sady MS Office vrátí tato jednoduchá funkce:

Function UserNameApp() As String

UserNameApp = Application.UserName

End Function

Většinou však potřebujete zjistit jméno, pod kterým je uživatel přihlášen do systému. K tomuto účelu

je třeba zavolat funkci API Windows. Její deklarace musí být na začátku modulu, před první funk

cí nebo procedurou.

!Kniha_K1673.indb 244!Kniha_K1673.indb 244 26.4.2010 14:09:2226.4.2010 14:09:22


245

03-01 Funkce UDF.xlsm modul MUser Funkce vracející některá nastavení Windows Některé základní informace můžete získat pomocí jednoduché funkce Environ(). Ta přijímá jediný argument, a to buď název informace, kterou chcete získat, nebo celočíselný index. Pokud použijete index, bude návratovou hodnotou řetězec včetně názvu informace, odděleného od informace rovnítkem. Pokud budete testovat v okně Immediate, vše bude jasnější: ? Environ(5) COMPUTERNAME=A100 ? Environ(„ComputerName“) A100 Pokud chcete vypsat všechny možnosti, dostupné na vašem počítači, do okna Immediate, použijte například tento kód: 03-01 Funkce UDF.xlsm modul MSubs Sub ListEnviron() Dim i As Long, Output As String i = 1 Output = Environ(i) Debug.Print „Index“, „EnvString“ Debug.Print String(80, „-“) Do While Len(Output) > 0 Debug.Print i, Output i = i + 1 Output = Environ(i) Loop End Sub

Obrázek 3.7: Funkce API, která vrací název účtu přihlášeného

uživatele

Příklady uživatelských funkcí

!Kniha_K1673.indb 245!Kniha_K1673.indb 245 26.4.2010 14:09:2226.4.2010 14:09:22


246

Lekce 3: Uživatelské funkce (UDF)

Výsledkem bude výpis podobný výpisu na obrázku 3.8. Je samozřejmé, že některé prvky nebudou na všech počítačích existovat. Například údaj, který má na našem systému index 32, existuje pouze tehdy, pokud má uživatel nainstalováno vývojové prostředí Visual Studio 2008. Proto doporučujeme při využití funkce Environ() opatrnost, zejména pokud vaše aplikace poběží na jiných počítačích. Na počítačích Macintosh funkce nefunguje vůbec. Přesto však můžete napsat jednoduché funkce, vracející například jméno uživatele Windows: 03-01 Funkce UDF.xlsm modul MUser Function UserName() As String Dim OutPut As String OutPut = Environ(„Username“) If Len(OutPut) = 0 Then OutPut = „Nedefinováno“ UserName = OutPut End Function nebo jméno počítače: Function ComputerName() As String Dim OutPut As String OutPut = Environ(„ComputerName“) If Len(OutPut) = 0 Then OutPut = „Nedefinováno“ ComputerName = OutPut End Function Bezpečnější je samozřejmě volat přímo systémové funkce API Windows. Následující funkce vrátí údaje o aktuálním rozlišení: 03-01 Funkce UDF.xlsm modul MResolution Private Const SM_CXSCREEN = 0 Private Const SM_CYSCREEN = 1 Private Declare Function GetSystemMetrics _ Lib „user32“ (ByVal nIndex As Long) As Long

Obrázek 3.8: Příklad výpisu všech informací dostupných přes funkci

Environ

!Kniha_K1673.indb 246!Kniha_K1673.indb 246 26.4.2010 14:09:2326.4.2010 14:09:23


247

Public Function ScreenWidth() As Long

ScreenWidth = GetSystemMetrics(SM_CXSCREEN)

End Function

Public Function ScreenHeight() As Long

ScreenHeight = GetSystemMetrics(SM_CYSCREEN)

End Function

Pokud je vaše aplikace navržena pro minimální dovolené rozlišení, můžete napsat testovací funkci

a tu následně použít ve vašem kódu:

Public Function bTestResolution(ScrWidth As Long, ScrHeight As Long)

bTestResolution = ScreenHeight >= ScrHeight And _

ScreenWidth >= ScrWidth

End Function

Sub TestResolution()

If Not bTestResolution(1200, 800) Then

MsgBox „Zvyšte rozlišení na 1200 x 800“

Exit Sub

Else

‘ kód pokračuje

End If

End Sub

Použití vlastností Application.Volatile a Application.Caller

O těchto vlastnostech jsme se již zmínili v lekci o objektu Application. Funkce, kterou používáte v lis

tu, se bude standardně chovat jako nevolatilní funkce, neboli se bude přepočítávat pouze v případě,

kdy se změní hodnota v některé z buněk, na které se odkazuje. Pokud chcete, aby se automaticky

přepočítávala při změně kterékoli z buněk v listu, doplňte do jejího kódu řádek

Application.Volatile

Následující funkce vrátí číslo posledního zaplněného řádku sloupce, určeného písmenem nebo jejich

kombinací.

03-01 Funkce UDF.xlsm

modul MFunctions

Function LastRow(strCol As String)

Dim ws As Worksheet

Dim clLast As Range

Application.Volatile

Set ws = Application.Caller.Parent

With ws

Set clLast = .Cells(.Rows.Count, strCol)

Příklady uživatelských funkcí

!Kniha_K1673.indb 247!Kniha_K1673.indb 247 26.4.2010 14:09:2326.4.2010 14:09:23


248

Lekce 3: Uživatelské funkce (UDF)

If IsEmpty(clLast) Then With clLast.End(xlUp) If IsEmpty(.Cells(1, 1)) Then LastRow = 0 Else LastRow = .Row End If End With Else LastRow = .Rows.Count End If End With End Function Funkce je do rozumné míry robustní, a pokud je poslední buňka sloupce neprázdná, nebo je celý sloupec prázdný, vrací jako výsledek správně plný počet řádků listu, respektive nulu. Řádek Application.Caller.Parent vrací odkaz na objekt třídy Worksheet, v němž je funkce jako vzorec použita. Vlastnost Caller objektu Worksheet vrací v tomto případě odkaz na buňku (objekt Range), ve které je vzorec zapsán. Vlastnost Parent obecně vrací jméno objektu, formuláře nebo kolekce, která vlastní daný objekt, kolekci nebo ovládací prvek. Vlastnost Caller může vracet i jiné hodnoty. Podrobnosti najdete v nápovědě.

Otázky

Otázka 3.1:

Jak spustíte proceduru Sub, která přijímá parametr?

Otázka 3.2:

Jaký je rozdíl mezi předáním proměnné jako parametru s klíčovými slovy ByRef a ByVal?

Která je výchozí? A kdy musíte použít klíčové slovo ByVal?

Otázka 3.3:

Kam umístíte kód funkce, pokud chcete, aby byla dostupná v kterémkoli sešitu na vašem

počítači?

Otázka 3.4:

Napište funkci GetText, která z předaného řetězce vrátí pouze alfabetické znaky (písmena).

Pro jednoduchost použijte operátor Like.

Otázka 3.5:

Funkci z předešlé otázky zaregistrujte mezi textové vzorce listu, včetně krátkého popisu.

Uveďte i kód pro její odregistrování.

!Kniha_K1673.indb 248!Kniha_K1673.indb 248 26.4.2010 14:09:2326.4.2010 14:09:23


249

Otázka 3.6:

Uveďte některá základní omezení funkce, pokud je použita jako vzorec v listu. Které příka

zy budou ignorovány?

Otázka 3.7:

Jak přinutíte funkci, která je použita jako vzorec v listu, k přepočítání, pokud je změněna

jakákoli buňka v sešitu, byť se na ni daná funkce neodkazuje? Jaké je nebezpečí tohoto pří

stupu?

Otázka 3.8:

Co znamená pojem rekurzivní funkce? Napište příklad rekurzivní funkce vracející faktori

ál čísla.

Otázka 3.9:

Proč je zapotřebí opatrnosti při použití funkce Environ?

Otázka 3.10:

Jak se v kódu funkce, použité v listu, odkážete na buňku, ve které je vzorec umístěn?

Příklady uživatelských funkcí

!Kniha_K1673.indb 249!Kniha_K1673.indb 249 26.4.2010 14:09:2426.4.2010 14:09:24




       
Knihkupectví Knihy.ABZ.cz - online prodej | ABZ Knihy, a.s.
ABZ knihy, a.s.
 
 
 

Knihy.ABZ.cz - knihkupectví online -  © 2004-2019 - ABZ ABZ knihy, a.s. TOPlist