načítání...
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
-14%
sleva

Kniha + CD: Excel VBA -- Výukový kurz
Autor:

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 4 pracovní dny
Médium: Kniha + CD
Vaše cena s DPH:  690 Kč 593
+
-
rozbalKdy zboží dostanu
19,8
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-01-11
Počet stran: 504
Rozměr: 167 x 225 mm
Úprava: 504 stran : ilustrace
Vydání: Vyd. 1.
Vazba: brožovaná lepená
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:
Zákazníci kupující zboží "Excel VBA -- Výukový kurz" mají také často zájem o tyto tituly:
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 jepro

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 hodnotuvně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. Tatoprocedu

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ě dozá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. Budepraco

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ůževypa

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 jsouobyč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ýkoliotevř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ímzá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 tovětši

nou postačí, avšak pokud budete

funkci distribuovat jinýmuž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 vkate

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 dokategorie vlastní, zaregistrujte ji pod číslem 14. Pokud místo

čísla předáte jméno vlastní kategorie, bude tatokategorie vytvořena.

Předešlé verze Excelu po odregistrování funkciponechaly 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 vrozbalovací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, budouignorová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 budouignorová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řinutitpř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 jepeč

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řipara

metry: vstupní částku, spodní hranice pásem a jejich sazby. Samozřejmě předpokládáme, žená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 podlehodnot 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 bylynepovinné 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 knihovnyMicro

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říkladinfor

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, budená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ýpispodobný výpisu na obrázku 3.8.

Je samozřejmé, že některé

prvky nebudou na všechpočítačích existovat. Například

údaj, který má na našemsystému index 32, existuje pouze

tehdy, pokud má uživatelnainstalováno vývojové prostředí

Visual Studio 2008.

Proto doporučujeme přivyužití funkce Environ() opatrnost, zejména pokud vaše

aplikace poběží na jinýchpočí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 vlis

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čí tohotopří

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-2018 - ABZ ABZ knihy, a.s. TOPlist