Domov › Telefon › Úvod do programování v Excelu. Správa objektů a sbírek. Objekty, vlastnosti a metody VBA

Úvod do programování v Excelu. Správa objektů a sbírek. Objekty, vlastnosti a metody VBA

Vytvoření vlastní funkce ve VBA Excel, její syntaxe a komponenty. Popis uživatelsky definované funkce a jejích argumentů. Metoda Application.MacroOptions.

Vlastní funkce je procedura VBA, která provádí zadané výpočty a vrací výsledek. Používá se pro vkládání do buněk listu aplikace Excel nebo volání z jiných procedur.

Deklarace vlastní funkce

Syntaxe funkce

Název funkce ([Seznam argumentů]) [Operátoři] [Název = výraz] [Operátoři] [Název = výraz] End Function

Funkční komponenty

Statický je nepovinné klíčové slovo označující, že hodnoty proměnných deklarovaných ve funkci jsou zachovány mezi voláními funkce.
Jméno- požadovaná součást, název uživatelské funkce.
ArgumentList- volitelná součást, jedna nebo více proměnných představujících argumenty, které jsou předány funkci. Argumenty jsou uzavřeny v závorkách a odděleny čárkami.
Operátoři- volitelná součást, blok operátorů (instrukcí).
Jméno = výraz- volitelná komponenta*, přiřazující název funkce k hodnotě výrazu nebo proměnné. Hodnota je obvykle přiřazena funkci těsně před ukončením.
Exit Function- volitelná komponenta, vynutí opuštění funkce, pokud již byla přiřazena konečná hodnota.

*Jedna ze složek Jméno = výraz by měl být považován za povinný, protože pokud funkci nepřiřadíte hodnotu, ztratí se smysl jejího použití.

Viditelnost funkcí

Viditelnost uživatelem definované funkce je určena volitelnými klíčovými slovy Public a Private, která lze zadat před příkazem Function (nebo Static, pokud je použit).

Klíčové slovo Veřejnost označuje, že funkce bude dostupná pro volání z jiných procedur ve všech otevřených modulech sešitu aplikace Excel. Funkce deklarována jako Veřejnost, se zobrazí v dialogovém okně Průvodce funkcí.

Klíčové slovo Soukromé označuje, že funkce bude dostupná pro volání z jiných procedur pouze v rámci programového modulu, ve kterém se nachází. Funkce deklarována jako Soukromé, se nezobrazí v dialogovém okně Průvodce funkcí, ale lze jej do buňky zadat ručně.

Pokud není zadáno klíčové slovo Public nebo Private, funkce se považuje za deklarovanou jako veřejná ve výchozím nastavení.

Chcete-li zajistit, aby byla vlastní funkce vždy dostupná ve všech otevřených sešitech aplikace Excel, uložte ji do nedeklarované viditelnosti nebo jako veřejnou. Pokud však plánujete přenést sešit s uživatelsky definovanou funkcí na jiný počítač, kód funkce musí být v programovém modulu přenášeného sešitu.

Příklad uživatelské funkce

Jako příklad budeme uvažovat jednoduchou uživatelskou funkci, jejíž popis doplníme v dalším odstavci. Funkce se nazývá „Division“ a je deklarována s datovým typem Variant, protože její návratovou hodnotou může být číslo nebo text. Argumenty Dividenda a Dělitel funkce jsou také deklarovány jako varianty, protože buňky Excelu mohou obsahovat číselné hodnoty různých typů a funkce IsNumeric také kontroluje různé datové typy a vyžaduje, aby její argumenty byly deklarovány jako varianty.

Funkce Dělení(Dividenda jako varianta, dělitel jako varianta) jako varianta If IsNumeric(Dividend) = False Or IsNumeric(Divisor) = False Then Division = "Chyba: Dividenda a Dělitel musí být čísla!" Exit Function ElseIf Dělitel = 0 Then Division = "Chyba: dělení nulou!" Exit Function Else Division = Dividenda / Dělitel End If End Funkce

Tato funkce rozdělí hodnoty dvou buněk v listu aplikace Excel. Před rozdělením se zkontrolují dva bloky podmínek:

Pokud dělenec nebo dělitel není číslo, funkce vrátí hodnotu: „Chyba: Dividenda a dělitel musí být čísla!“ a funkce je nucena skončit pomocí příkazu Exit Function.
Pokud je dělitel nula, funkce vrátí hodnotu: „Chyba: dělení nulou!“ a funkce je nucena skončit pomocí příkazu Exit Function.

Pokud nejsou splněny kontrolované podmínky (hodnota je vrácena False), čísla se rozdělí a funkce vrátí podíl (výsledek dělení).

Tuto funkci můžete zkopírovat do svého standardního modulu a bude dostupná v části „Definováno uživatelem“ Průvodce funkcí. Zkuste vložit funkci Division do buňky listu pomocí průvodce a experimentujte s ní.

Funkce „Division“ nemá praktický význam, ale dobře ukazuje, jak jsou vlastní funkce deklarovány, vytvářeny a fungují ve VBA Excel. Pomůže také demonstrovat, jak přidat popisy k funkcím a argumentům. Můžete se seznámit s celou uživatelskou funkcí.

Přidání popisu funkce

V seznamu funkcí zobrazených průvodcem není možné přidávat nebo upravovat jejich popis. Seznam maker umožňuje přidávat popisy k procedurám, ale neobsahuje funkce. Problém je vyřešen následovně:

Spusťte Průvodce funkcí, podívejte se, jak se zobrazuje název požadované funkce, a zavřete jej.
Otevřete ji a do pole „Název makra“ zadejte název vlastní funkce.
Klikněte na tlačítko „Možnosti“ a v okně, které se otevře, přidejte nebo upravte popis.
Klikněte na tlačítko "OK" a poté v okně seznamu maker - "Zrušit". Popis je připraven!

Přidání popisu pomocí příkladu funkce „Rozdělení“:

Popis funkce "Rozdělit" v dialogovém okně "Argumenty funkce" Průvodce funkcí:

Pomocí okna Seznam maker můžete přidat popis samotné funkce, ale ne její argumenty. Ale to lze provést pomocí metody Application.MacroOptions.

Metoda Application.MacroOptions

Metoda Application.MacroOptions umožňuje přidat popis k vlastní funkci, přiřadit klávesovou zkratku, určit kategorii, přidat popisy argumentů a přidat nebo změnit další možnosti. Podívejme se na nejčastěji používané funkce této metody.

Příklad kódu s metodou Application.MacroOptions:

SubroutineName() Application.MacroOptions _ Macro:="FunctionName", _ Description:="Popis funkce", _ Category:="Název kategorie", _ ArgumentDescriptions:=Array("Description 1", "Description 2", " Popis 3", ...) End Sub

Název podprogramu- jakýkoli jedinečný název vhodný pro pojmenování procedur.
NameFunction- název funkce, jejíž parametry jsou přidány nebo změněny.
Popis funkce- popis funkce, která je přidána nebo změněna.
Název kategorie- název kategorie, do které bude funkce zařazena. Pokud parametr Kategorie chybí, bude uživatelská funkce zapsána do výchozí sekce Definováno uživatelem. Pokud zadaný název kategorie odpovídá jednomu z názvů ve standardním seznamu, funkce se do něj zapíše. Pokud takový Název kategorie v seznamu není, vytvoří se nová sekce s tímto názvem a funkce se do ní umístí.
"Popis 1", "Popis 2", "Popis 3", ...- popisy argumentů v pořadí, v jakém se objevují v deklaraci uživatelské funkce.

Tato rutina se spustí jednou a poté ji lze smazat nebo použít jako šablonu pro úpravu parametrů dalších uživatelských funkcí.

Nyní pomocí metody Application.MacroOptions zkusme změnit popis vlastní funkce "Division" a přidat popisy argumentů.

Sub ChangeDescription() Application.MacroOptions _ Macro:="Division", _ Description:="Popis funkce Division změněný metodou Application.MacroOptions", _ ArgumentDescriptions:=Array("- libovolná číselná hodnota", "- numeric hodnota jiná než nula ") End Sub

Po jednom spuštění tohoto podprogramu dostaneme následující výsledek:

Metoda Application.MacroOptions nefunguje v , ale i zde můžete najít řešení. Přidejte popisy k uživatelským funkcím a jejich argumenty v běžném sešitu Excelu, poté exportujte modul s funkcemi do libovolného adresáře na vašem pevném disku a importujte odtud do Osobního sešitu maker. Všechny popisy budou uloženy.

Základy programování VBA

Komentáře (0)

Proces vývoje programu ve VBA - projekt, může sestávat z několika fází v závislosti na konečném výsledku. Pokud potřebujete získat program, který bude provádět určité výpočty nebo akce rozšiřující matematické možnosti standardní aplikace Microsoft Office, pak stačí vytvořit naprogramovat modul. Chcete-li tento program používat, můžete na pracovní plochu aplikace umístit tlačítko, jehož stisknutí způsobí spuštění programu. Chcete-li to provést, musíte povolit panel nástrojů v aplikaci pomocí příkazu PohledPanely nástrojůOvládací prvky a poté vytvořte tlačítko s příslušným programovým kódem. Nebo spusťte program pomocí příkazu ServisMakroMakra.

Vývoj „úplného“ programu (který vyžaduje ke spuštění samostatné okno s různými ovládacími prvky) bude zahrnovat dvě fáze. První fází je fáze vizuálního programování, ve které se vytvoří okno ( formulář) programy, kde jsou umístěny potřebné ovládací prvky. Druhou je fáze programování, ve které se vytvářejí části programu ( postupy), prováděné v reakci na určité události. Událostí je například kliknutí levým tlačítkem myši na příkazové tlačítko (událost Click), stisk klávesy na klávesnici (událost KeyPress) atd. Takovou aplikaci můžete použít kliknutím na tlačítko „Spustit projekt“.

2.1. Objekty, vlastnosti a metody VBA

Jedním ze základních pojmů ve VBA je objekt. Objekt je něco, co ovládáte pomocí programu VBA, jako je formulář, tlačítko, list nebo rozsah buněk MS Excel. Každý objekt má nějaké vlastnosti. Například tvar může nebo nemusí být aktuálně viditelný na obrazovce. Dalším příkladem vlastnosti objektu je písmo pro zobrazení informací v buňce (objektu) listu.

Objekt také obsahuje seznam metod, které se na něj vztahují. Metody je to, co můžete dělat s předmětem. Můžete například zobrazit formulář na obrazovce nebo jej skrýt pomocí metod Zobrazit a Skrýt.

Tedy, objekt- jedná se o programový prvek, který má vlastní zobrazení na obrazovce, obsahuje nějaké proměnné, které jej určují vlastnosti a některé metody k ovládání objektu. Například MS Excel má mnoho vestavěných objektů:

Rozsah(“ Adresa”)	Rozsah buněk (může obsahovat pouze jednu buňku).
Buňky (i, j)	Buňka umístěná na průsečíku i-tého řádku a j-tého sloupce listu MS Excel (i a j jsou celá čísla).
Řádky(Linka č.)	Řádek s daným číslem.
Sloupce(Sloupec č.)	Sloupec s daným číslem
Listy(“ Jméno”)	List se zadaným názvem.
Listy(List č.)	List s uvedeným číslem.
Pracovní list	Pracovní list.

Nastavení hodnot vlastností je jedním ze způsobů, jak manipulovat s objekty. Syntaxe pro nastavení hodnoty vlastnosti objektu je následující:

Objekt. Vlastnost = výraz

Hlavní vlastnost objektů Buňky A Rozsah, je Hodnota(hodnota), která však nemusí být uvedena. Například:

Rozsah („A5:A10“). Hodnota = 0 nebo Rozsah(“ A5: A10”) = 0 - do rozsahu buněk A5:A10 se zadá hodnota 0.

Buňky(2, 4). Hodnota = n nebo Buňky(2, 4) = n- hodnota proměnné n se zadává do buňky umístěné na průsečíku 2. řádku a 4. sloupce (buňka s adresou „D2“).

Syntaxe pro čtení vlastností objektu je následující:

Proměnná = Objekt. Vlastnictví

Například:

Xn = Buňky(1, 2). Hodnota nebo Xn = Rozsah(“ B1”). Hodnota- proměnné Xn je přiřazena hodnota z buňky B1 aktuálního listu.

Syntaxe pro použití metod na objekt je:

Objekt. Metoda

Například:

Listy(2). Aktivovat - aktivujte list s č. 2.

Listy("Diagram").Vymazat - odstranit list s názvem „Graf“.

Rozsah("A5:A10").Clear - Vymazat rozsah buněk A5:A10.

Rozsah("A2:B10").Vyberte - Vyberte oblast buněk A2:B10.

MS Excel má objekty, které obsahují jiné objekty. Například sešit obsahuje listy, list obsahuje oblast buněk atd. Objekt nejvyšší úrovně je Aplikace(aplikace). Pokud změníte jeho vlastnosti nebo zavoláte jeho metody, výsledek se použije na aktuální úlohu MS Excel. Například:

Aplikace. Přestat- dokončovací práce s Excelem.

Všimněte si, že tečku za názvem objektu lze použít k přesunu z jednoho objektu na druhý. Například následující výraz vymaže druhý řádek listu květen v sešitu Zpráva:

Aplikace.Sešity("Zpráva").Pracovní listy("květen").Řádky(2).Smazat

Je třeba poznamenat následující:

Nemusíte psát název objektu Aplikace, protože toto je výchozí nastavení.
Při práci s podobjektem již aktivovaného objektu není nutné specifikovat obsahující objekt.
VBA používá některé vlastnosti a metody, které vracejí objekt, na který odkazují (to umožňuje rychle určit požadovaný objekt). Příklady takových vlastností: ActiveCell (aktivní buňka), ActiveSheet (aktivní list), ActiveWorkBook (aktivní sešit). Hodnotu aktivní buňky tedy můžete nastavit následovně:

ActiveCell.Value = "Ano!}".

2.2. Popis dat

Všechny objekty, se kterými programovací jazyk VBA pracuje, patří k určitému typu.

Datový typ definuje:

Rozsah možných hodnot proměnné;

Struktura organizace dat;

Operace definované na datech tohoto typu.

Datové typy se dělí na jednoduché (skalární) a komplexní (strukturované). U jednoduchých datových typů jsou možné datové hodnoty jednoduché a nedělitelné. Složité typy mají strukturu, která zahrnuje různé jednoduché datové typy. Skalární datové typy jsou uvedeny v tabulce 2.1.

Tabulka 2.1. Skalární typy VBA

Zadejte název	ruština název typu	Možné hodnoty
	Logický
	Byte

	Dlouhé celé číslo	2147483648…+2147483647
	Číslo s plovoucí desetinnou čárkou	3.4E38…-1.4E-45 pro záporné hodnoty. 1.4E-45...3.4E38 pro kladné hodnoty.
	Číslo s plovoucí desetinnou čárkou s dvojitou přesností	1.7E308…-4.9E-324 pro záporné hodnoty. 4.9E-324…1.7E308 pro kladné hodnoty.
	Měnový	Desetinná čísla s pevnou desetinnou pozicí. Před desetinnou čárkou je 15 možných číslic a za nimi 4.
	Řetězec	Existují dva typy řetězců: řetězce s pevnou délkou (až 2 16 znaků) a řetězce s proměnnou délkou (až 2 31 znaků). Data jsou psána v uvozovkách.
		Termíny se liší od 01.01.100. do 31. prosince 9999

		Obecný typ, jehož hodnotou může být kterýkoli z datových typů uvedených výše, objekty, hodnoty NULL a chybové hodnoty ERROR.

Proměnné v programu mohou být popsány nebo ne. V druhém případě mu bude přiřazen typ Varianta. Proměnnou můžete explicitně popsat jak na začátku bloku, tak na jakémkoli místě, kde je potřeba použít novou proměnnou. Je lepší popsat všechny proměnné explicitně a zpravidla na začátku bloku. Chcete-li zakázat použití proměnných, které nebyly explicitně deklarovány, musíte příkaz vložit na začátek programu Možnost Explicitní.

2.2.1. Popis jednoduchých proměnných

Popis jednoduchých proměnných má následující syntaxi:

ZtlumitVARIABLE_NAMEJakTYPE_NAME

Jeden operátor Ztlumit můžete popsat libovolný počet proměnných, ale design Jak musí být specifikováno pro každou z nich, jinak proměnné bez Jak bude přiřazen typ Varianta.

Například.

Dim X jako bajt, Z jako celé číslo,S, CrybolovJako String

Zde proměnná X je proměnná typu byte, proměnná Z- celočíselný typ, proměnná S- typ option (výchozí), proměnná Slovo- typ řetězce.

2.2.2. Popis konstant

Data, která se v rámci programu nemění, lze považovat za konstanty. Lze je popsat následovně:

ConstCONSTANT_NAMEJakTYPE_NAME = CONSTANT_EXPRESSION

Například.

Const Pi As Double = 3,141593

2.2.3. Popis polí

Pro ukládání vektorů, matic atd. můžete použít pole.

Pole - Jedná se o strukturovaný datový typ, což je sekvence paměťových buněk, které mají společný název a ukládají data stejného typu. Každý prvek pole je identifikován indexem (číslem). Zavolá se počet prvků v poli rozměr pole. Pole je popsáno následující konstrukcí:

ZtlumitARRAY_NAME(LIST_DIMENSIONS) JakTYPE_NAME

V seznamu rozměrů pole je každý rozměr oddělen čárkou a je určen nastavením dolní a horní hranice pro změnu indexů.

Například.

Dim X(1 až 5) jako celé číslo, Y(1 až 10, 1 až 20) jako dvojité

Zde X- jednorozměrné pole skládající se z 5 prvků celočíselného typu, Y- dvourozměrné pole s 10 řádky a 20 sloupci s prvky numerického typu s dvojitou přesností.

2.3. Výrazy

Výrazy určují pořadí, ve kterém jsou akce prováděny s datovými prvky. Výrazy se skládají z operandů a operátorových symbolů. Operandy jsou konstanty, proměnné, ukazatele funkcí a výrazy v závorkách.

2.3.1. Typy operací

Operace mohou být aritmetické, relační a logické:

- aritmetické operace:
^ umocňování,
* násobení,
/ divize,
\ celá divize,
mod zbytek divize,
+ plus,
- mínus;

- vztahové operace:
< меньше,
> více
<= меньше или равно,
>= větší nebo rovno
= rovný,
<>nerovný;

- logické operace:
Není to logická negace,
A logické "A",
Nebo logické "NEBO".

Výsledkem logické operace může být jedna ze dvou hodnot:
Pravda nebo nepravda.

2.3.2. Priorita provozu

Pokud výraz obsahuje několik operací, priorita jejich provedení je následující:

1. Nejprve se provedou aritmetické operace v pořadí uvedeném v tabulce 2.2.

Tabulka 2.2. Priorita aritmetických operací

3. Logické operace se provádějí jako poslední v pořadí, v jakém jsou uvedeny v tabulce 2.3.

Tabulka 2.3. Priorita logických operací

Popis provozu	Označení ve VBA
Logická negace
Logické "A"
logické "NEBO"

Pokud výraz obsahuje několik operací stejné priority, je pořadí jejich provádění zleva doprava. Ke změně pořadí akcí ve výrazu se používají závorky.

Výrazy mohou být aritmetické, relační nebo logické.

Aritmetické výrazy se zapisují pomocí operandů číselných typů a aritmetických operací a výsledkem je číselná hodnota. V aritmetickém výrazu můžete použít standardní matematické funkce, které jsou uvedeny v tabulce 2.4.

Tabulka 2.4. Standardní matematické funkce VBA

Matematický zápis	Název funkce ve VBA	Popis
		Vrátí hodnotu, jejíž typ odpovídá typu předávaného argumentu, rovna absolutní hodnotě zadaného čísla.
		Vrátí Double obsahující arkustangens čísla.
		Vrátí Double obsahující kosinus úhlu.
		Vrátí hodnotu typu odpovídající typu argumentu, který obsahuje celočíselnou část čísla.
		Vrátí Double obsahující přirozený logaritmus čísla.
		Vrátí Double obsahující výsledek násobení čísla E (základ přirozených logaritmů) na indikovanou mocninu.
		Vrátí hodnotu Variant (Integer) odpovídající znaménku zadaného čísla.
		Vrátí Double obsahující sinus úhlu.
		Vrátí Double obsahující druhou odmocninu zadaného čísla.
		Vrátí Double obsahující tangens úhlu.

Projevy postoje určit, zda je výsledek pravdivý nebo nepravdivý při porovnávání dvou operandů. Můžete porovnávat data jakéhokoli podobného typu. Výsledek relační operace je pouze logický: True – „true“ nebo False – „false“.

Logické výrazy. Výsledkem booleovského výrazu je logická hodnota True nebo False. Nejjednodušší typy logických výrazů jsou: logická konstanta, logická proměnná, logická funkce, relační výraz. Logické operace se provádějí pouze s operandy typu Boolean.

Příklad. Napište 1 £ X £ 5 a určete hodnotu výrazu při X = 3,1

Výraz ve VBA bude vypadat takto:

X>=1 A X<=5

Výsledkem výrazu bude Věrný.

Chcete-li získat seznam všech matematických funkcí, stačí zadat název jakékoli známé matematické funkce (např. HŘÍCH) a poté stiskněte klávesu F1 Matematické funkce . Ve výsledném seznamu můžete získat informace o účelu kterékoli z vestavěných matematických funkcí a jejich argumentu.

Chcete-li získat seznam všech derivací matematických funkcí a pravidel pro jejich tvorbu, stačí zadat název libovolné známé matematické funkce (např. HŘÍCH) a poté stiskněte klávesu F1 a pod popisem vybrané funkce vyberte odkaz na Odvozené matematické funkce .

Níže v tabulce 2.5 je seznam funkcí, které lze získat pomocí vestavěných matematických funkcí.

Tabulka 2.5. Odvozené matematické funkce

Matematický zápis	Název funkce	Kombinace vestavěných funkcí

	Kosekant
	Kotangens
	arcsinus	Atn(X/Sqr(-X*X+1))
	oblouk kosinus	Atn(-X/Sqr(-XX+1))+2Atn(1)
	Arcsecant	Atn(X/Sqr(XX-1))+Sgn((X)-1)2*Atn(1)
	Arccosecant	Atn(X/Sqr(XX-1))+(Sgn(X)-1)2*Atn(1)
	Arckotangens
	Hyperbolický sinus	(Exp(X)-Exp(-X))/2
	Hyperbolický kosinus	(Exp(X)+Exp(-X))/2
	Hyperbolická tečna	(Exp(X)-Exp(-X))/(Exp(X)+Exp(-X))
	Hyperbolický sekant	2/(Exp(X)+Exp(-X))
	Hyperbolický kosekans	2/(Exp(X)-Exp(-X))
	Hyperbolický kotangens	(Exp(X)+Exp(-X))/(Exp(X)-Exp(-X))
	Hyperbolický arcsinus	Log(X+Sqr(X*X+1))
	Hyperbolický arc cosinus	Log(X+Sqr(X*X-1))
	Hyperbolický arkustangens	Log((1+X)/(1-X))/2
	Hyperbolický arcsekant	Log((Sqr(-X*X+1)+1)/X)
	Hyperbolický arkosekant	Log((Sgn(X)Sqr(XX+1)+1)/X)
	Hyperbolický oblouk kotangens	Log((X+1)/(X-1))/2
	Logaritmus na základnu N

Další novinky

Než začnete vytvářet své vlastní funkce VBA, je užitečné vědět, že Excel VBA má velkou sbírku předpřipravených vestavěných funkcí, které můžete použít při psaní kódu.

Seznam těchto funkcí lze zobrazit v editoru VBA:

Otevřete sešit aplikace Excel a spusťte editor VBA (klikněte sem Alt+F11) a poté klikněte F2.
Vyberte knihovnu z rozevíracího seznamu v levé horní části obrazovky VBA.
Zobrazí se seznam vestavěných tříd a funkcí jazyka VBA. Kliknutím na název funkce se ve spodní části okna zobrazí stručný popis. Lisování F1 otevře stránku online nápovědy pro danou funkci.

Úplný seznam vestavěných funkcí VBA s příklady lze navíc nalézt na webu Visual Basic Developer Center.

Vlastní funkce a dílčí procedury ve VBA

V aplikaci Excel Visual Basic je sada příkazů, které provádějí konkrétní úkol, umístěna do procedury Funkce(Funkce) popř Sub(Podprogram). Hlavní rozdíl mezi postupy Funkce A Sub je to postup Funkce vrátí výsledek, postup Sub- Ne.

Pokud tedy potřebujete provést akce a získat nějaký výsledek (například sečíst několik čísel), obvykle se používá postup Funkce a abyste mohli jednoduše provést některé akce (například změnit formátování skupiny buněk), musíte vybrat postup Sub.

Argumenty

Různá data lze předat procedurám VBA pomocí argumentů. Seznam argumentů je uveden při deklaraci procedury. Například postup Sub ve VBA přidá zadané celé číslo (Integer) do každé buňky ve vybraném rozsahu. Toto číslo můžete předat proceduře pomocí argumentu, jako je tento:

Sub AddToCells(i As Integer) ... End Sub

Uvědomte si, že mít argumenty k postupům Funkce A Sub ve VBA je volitelný. Některé postupy nevyžadují argumenty.

Nepovinné argumenty

Procedury VBA mohou mít volitelné argumenty. Toto jsou argumenty, které může uživatel zadat, pokud chce, a pokud jsou vynechány, procedura pro ně použije výchozí hodnoty.

Když se vrátíme k předchozímu příkladu, chcete-li nastavit celočíselný argument funkce jako nepovinný, deklarovali byste ji takto:

Sub AddToCells (Volitelné i As Integer = 0)

V tomto případě celočíselný argument i výchozí bude 0.

V proceduře může být několik volitelných argumentů, všechny jsou uvedeny na konci seznamu argumentů.

Předávání argumentů hodnotou a odkazem

Argumenty ve VBA lze předat proceduře dvěma způsoby:

ByVal– předání argumentu hodnotou. To znamená, že do procedury je předána pouze hodnota (tj. kopie argumentu), a proto všechny změny provedené v argumentu uvnitř procedury budou při jejím ukončení ztraceny.
ByRef– předání argumentu odkazem. To znamená, že procedura předá skutečnou adresu argumentu v paměti. Jakékoli změny provedené v argumentu v rámci procedury budou uloženy při ukončení procedury.

Pomocí klíčových slov ByVal nebo ByRef V deklaraci procedury můžete přesně určit, jak je argument předán proceduře. To je znázorněno níže na příkladech:

Pamatujte, že argumenty ve VBA jsou standardně předávány odkazem. Jinými slovy, pokud se nepoužívají klíčová slova ByVal nebo ByRef, pak bude argument předán odkazem.

Než budete pokračovat v učení postupů Funkce A Sub Podrobněji bude užitečné znovu se podívat na vlastnosti a rozdíly mezi těmito dvěma typy postupů. Následuje stručný popis postupů VBA Funkce A Sub a jsou uvedeny jednoduché příklady.

VBA procedura "Funkce"

Editor VBA postup rozpozná Funkce

Funkce...Konec Funkce

Jak již bylo zmíněno dříve, postup Funkce ve VBA (na rozdíl od Sub), vrátí hodnotu. Pro návratové hodnoty platí následující pravidla:

Datový typ návratové hodnoty musí být deklarován v hlavičce procedury Funkce.
Proměnná obsahující vrácenou hodnotu musí být pojmenována stejně jako procedura Funkce. Tato proměnná nemusí být deklarována samostatně, protože vždy existuje jako nedílná součást procedury Funkce.

To je dokonale ilustrováno na následujícím příkladu.

Příklad procedury VBA „Funkce“: Provedení matematické operace se 3 čísly

Níže je uveden příklad kódu procedury VBA Funkce, který přebírá tři argumenty typu Dvojnásobek(čísla s plovoucí desetinnou čárkou s dvojitou přesností). V důsledku toho procedura vrátí jiné číslo typu Dvojnásobek, rovno součtu prvních dvou argumentů mínus třetí argument:

Funkce SumMinus(dNum1 jako dvojnásobek, dNum2 jako dvojnásobek, dNum3 jako dvojnásobek) jako dvojnásobek SumMinus = dNum1 + dNum2 - dNum3 Koncová funkce

Jedná se o velmi jednoduchý postup VBA Funkce ilustruje, jak jsou data předávána proceduře prostřednictvím argumentů. Můžete vidět, že datový typ vrácený procedurou je definován jako Dvojnásobek(toto říkají slova Jako Double po seznamu argumentů). Tento příklad také ukazuje, jak výsledek postupu Funkce je uložen v proměnné se stejným názvem jako název procedury.

Volání procedury VBA "Funkce"

Pokud je jednoduchý postup popsaný výše Funkce vložen do modulu v editoru Visual Basic, lze jej volat z jiných procedur VBA nebo použít na listu v sešitu aplikace Excel.

Volání procedury VBA "Funkce" z jiné procedury

Postup Funkce lze volat z jiné procedury VBA jednoduchým přiřazením této procedury k proměnné. Následující příklad ukazuje volání procedury SumMinus, který byl definován výše.

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Volání procedury VBA "Funkce" z listu

Postup VBA Funkce lze volat z listu aplikace Excel stejným způsobem jako jakoukoli jinou vestavěnou funkci aplikace Excel. Proto postup vytvořený v předchozím příkladu Funkce – SumMinus lze volat zadáním následujícího výrazu do buňky listu:

SumMinus(10, 5, 2)

Procedura VBA "Sub"

Redaktor VBA chápe, že je před ním postup Sub, když narazí na skupinu příkazů uzavřených mezi následujícími úvodními a závěrečnými příkazy:

Díl...Konec Díl

Procedura VBA "Sub": Příklad 1. Zarovnání na střed a změna velikosti písma ve vybraném rozsahu buněk

Podívejme se na příklad jednoduché procedury VBA Sub, jehož úkolem je změnit formátování vybraného rozsahu buněk. Buňky jsou nastaveny na zarovnání na střed (svisle i vodorovně) a velikost písma se změní na uživatelem zadanou velikost:

Sub Format_Centered_And_Sized(Volitelné iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Tento postup Sub provádí akce, ale nevrací výsledky.

Tento příklad také používá volitelný argument iFontSize. Pokud argument iFontSize nepřešlo do řízení Sub, pak se jeho výchozí hodnota považuje za 10. Pokud však argument iFontSize přešel do řízení Sub, pak se ve vybraném rozsahu buněk nastaví velikost písma určená uživatelem.

Procedura VBA "Sub": Příklad 2. Zarovnání na střed a použití tučného písma ve vybraném rozsahu buněk

Další postup je podobný právě probíranému, ale tentokrát místo změny velikosti aplikuje na vybraný rozsah buněk styl tučného písma. Toto je příklad postupu Sub, kterému se nepředávají žádné argumenty:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Volání procedury "Sub" v Excelu VBA

Volání procedury VBA "Sub" z jiné procedury

Volání procedury VBA Sub z jiného postupu VBA, musíte si klíčové slovo zapsat Volání, název procedury Sub a pak v závorkách argumenty postupu. To je znázorněno na níže uvedeném příkladu:

Sub main() Call Format_Centered_And_Sized(20) End Sub

Pokud postup Format_Centered_And_Sized má více než jeden argument, musí být odděleny čárkami. Takhle:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Volání procedury VBA "Sub" z listu

Postup Sub nelze zadat přímo do buňky listu aplikace Excel, jak to lze provést pomocí postupu Funkce, protože postup Sub nevrací hodnotu. Nicméně postupy Sub, které nemají žádné argumenty a jsou deklarovány jako Veřejnost(jak bude ukázáno později) bude k dispozici uživatelům listu. Pokud tedy jednoduché postupy diskutované výše Sub vložen do modulu v editoru jazyka Visual Basic a poté postup Format_Centered_And_Tučné bude k dispozici pro použití v pracovním listu Excelu a postupu Format_Centered_And_Sized– nebude k dispozici, protože má argumenty.

Zde je jednoduchý způsob, jak spustit (nebo provést) proceduru Sub, dostupné z pracovního listu:

Klikněte Alt+F8(stiskněte klávesu Alt a přidržte jej a stiskněte klávesu F8).
V zobrazeném seznamu maker vyberte to, které chcete spustit.
Klikněte Vykonat(Běh)

Chcete-li provést postup Sub rychle a snadno mu můžete přiřadit kombinaci kláves. Postup:

Klikněte Alt+F8.
V zobrazeném seznamu maker vyberte to, kterému chcete přiřadit klávesovou zkratku.
Klikněte Možnosti(Možnosti) a v zobrazeném dialogovém okně zadejte klávesovou zkratku.
Klikněte OK a zavřete dialogové okno Makro(Makro).

Pozor: Při přiřazování klávesové zkratky makru se ujistěte, že se nepoužívá jako standardní v Excelu (např. Ctrl+C). Pokud vyberete existující klávesovou zkratku, bude znovu přiřazena k makru a v důsledku toho může uživatel makro náhodně spustit.

Rozsah postupu VBA

Část 2 tohoto tutoriálu probírala téma rozsahu proměnných a konstant a role klíčových slov Veřejnost A Soukromé. Tato klíčová slova lze také použít ve vztahu k procedurám VBA:

Pamatujte, že pokud před deklarováním procedury VBA Funkce nebo Sub klíčové slovo není vloženo, pak je nastavena výchozí vlastnost pro proceduru Veřejnost(to znamená, že bude k dispozici všude v daném projektu VBA). To se liší od deklarování proměnných, které ve výchozím nastavení jsou Soukromé.

Předčasné ukončení procedur VBA „Function“ a „Sub“

Pokud potřebujete ukončit provádění procedury VBA Funkce nebo Sub, bez čekání na jeho přirozený konec, pak na to existují operátory Exit Function A Exit Sub. Použití těchto operátorů je ukázáno níže na jednoduchém příkladu postupu Funkce, která očekává, že obdrží kladný argument k provedení dalších operací. Pokud je proceduře předána nekladná hodnota, nelze provádět žádné další operace, takže uživateli musí být zobrazena chybová zpráva a procedura musí být okamžitě ukončena:

Funkce VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 If sVAT_Rate<= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function

Vezměte prosím na vědomí, že před dokončením postupu Funkce – Částka_DPH, je do kódu vložena vestavěná funkce VBA MsgBox, která uživateli zobrazí vyskakovací varovné okno.

Funkce napsaná ve VBA je kód, který provádí výpočet a vrací hodnotu (nebo pole hodnot). Jakmile funkci vytvoříte, můžete ji používat třemi způsoby:

Jako vzorec v listu, kde může převzít argumenty a vrátit hodnoty.
Jako součást vaší rutiny VBA. V proceduře Sub nebo uvnitř jiných funkcí.
V pravidlech podmíněného formátování.

Přestože Excel již obsahuje více než 450 vestavěných funkcí, také někdy chybí. Někdy vestavěné funkce nemohou dělat to, co chcete. Někdy, abyste dosáhli výsledku, musíte vytvořit obrovský a složitý vzorec, který není pro ostatní srozumitelný. V tomto případě můžete vytvořit vlastní funkci, která se snadno čte a používá.

Všimněte si, že uživatelsky definované funkce vytvořené pomocí VBA jsou obecně výrazně pomalejší než vestavěné funkce. Proto se nejlépe hodí pro situace, kdy nemůžete získat výsledek pomocí vestavěných funkcí nebo kde není mnoho výpočtů a penalizace za výkon není kritická.

Vestavěné funkce můžete využívat nejen vy, ale i vaši kolegové. Funkce, které napíšete, se objeví vedle ostatních v dialogovém okně Průvodce funkcí. Možná vás proces vytváření funkcí vyděsí, ale spěchám vás ujistit, že je to docela jednoduché.

Jaký je rozdíl mezi procedurou (Sub) a funkcí (Function)?

Hlavní rozdíl je v tom, že procedura (sub) se používá k provádění sady příkazů a není určena, na rozdíl od funkce (funkce), k vrácení hodnoty (nebo pole hodnot).

Pro demonstraci uveďme příklad. Například existuje řada čísel od 1 do 100 a je třeba oddělit sudá od lichých.

Pomocí (pod)postupu můžete například procházet buňky a liché zvýraznit pomocí výplně. A funkci lze použít v sousedním sloupci a vrátí hodnotu TRUE nebo FALSE v závislosti na tom, zda je hodnota sudá nebo ne. Tito. nebudete moci změnit barvu výplně pomocí funkce v listu.

Vytvoření jednoduché uživatelsky definované funkce ve VBA

Pojďme si vytvořit jednoduchou vlastní funkci ve VBA a uvidíme, jak to celé funguje.

Níže je uveden kód funkce, která v textu ponechá pouze čísla, přičemž hodnoty písmen zahodí.

Čísla funkcí (Text jako řetězec) As Long Dim i As Long Dim result As String For i = 1 To Len(Text) If IsNumeric(Mid(Text, i, 1)) Then result = result & Mid(Text, i, 1 ) Další čísla = CLng(výsledek) End Function

Aby vám vše fungovalo, musíte tento kód vložit do modulu knihy. Pokud nevíte, jak na to, začněte článkem.

Nyní se podívejme, jak funkce funguje, zkusme ji použít na listu:

Před analýzou samotné funkce si povšimněme 2 příjemných momentů, které se objevily po jejím vytvoření:

Stala se dostupnou, stejně jako jakákoli jiná vestavěná funkce (jak vytvořit skrytou funkci, řekneme vám později).
Když zadáte znak "=" a začnete psát název funkce, Excel zobrazí všechny shody a zobrazí nejen vestavěné funkce, ale i ty vlastní.

Pojďme analyzovat funkci krok za krokem

Nyní se pojďme ponořit do hloubky a podívat se, jak tato funkce vznikla. Funkce začíná řádkem

Čísla funkcí (Text As String) As Long

Slovo Funkce hovoří o začátku funkce, za nímž následuje její název, v našem případě Čísla.

Název funkce nesmí obsahovat mezery. Také nemůžete volat funkci, pokud koliduje s názvem odkazu na buňku. Nemůžete například pojmenovat funkci ABC123, protože tento název také odkazuje na buňku v listu aplikace Excel.
Neměli byste své funkci dávat stejný název jako existující funkci. Pokud to uděláte, Excel dá přednost vestavěné funkci.
Pokud chcete oddělit slova, můžete použít znak podtržítka. Například, Množství_ve slovech je platné jméno.

Za jménem jsou v závorkách popsány argumenty funkce. Podobně jako vestavěné funkce Excelu. V našem případě je použit jediný argument Text. Za názvem argumentu, který jsme uvedli Jako String, to znamená, že naším argumentem je textová hodnota nebo odkaz na buňku obsahující textovou hodnotu. Pokud nezadáte datový typ, VBA s ním bude zacházet jako Varianta(což znamená, že můžete použít jakýkoli datový typ, VBA to zjistí za vás).

Poslední část prvního řádku Jak dlouho určuje datový typ, který funkce vrací. V našem případě bude funkce vracet celočíselné hodnoty. To také není nutné.

Druhý a třetí řádek funkce deklarují další vnitřní proměnné, které budeme používat.

Dim i As Long Výsledek ztlumení jako řetězec

Variabilní i použijeme jej k výčtu znaků. Proměnná výsledek k uložení mezivýsledku funkce.

Funkce má za úkol projít všechny znaky v proměnné Text a uložit pouze ty, které jsou čísly. Smyčku tedy začneme od 1 do posledního znaku.

Pro i = 1 To Len(Text)

Len je funkce, která určuje počet znaků.

Hlavním řádkem funkce je kontrola, zda další znak textu je číslo, a pokud ano, jeho uložení do proměnné result

If IsNumeric(Mid(Text, i, 1)) Then result = result & Mid(Text, i, 1)

K tomu potřebujeme funkci IsNumeric- vrací se Věrný pokud je text číslo a Falešný jinak.

Funkce Střední přebírá z argumentu Text i postava (význam 1 , znamená, že funkce Střední zabere pouze 1 znak)/

Funkce Další- uzavírá smyčku Pro tady je vše jasné.

Čísla = CLng(výsledek)

Tímto řádkem převedeme textovou proměnnou výsledek, který obsahuje všechny číslice argumentu Text, na číselnou hodnotu. A my říkáme, jaký výsledek má naše funkce vydat Čísla.

Poslední řádek kódu je End Function. Toto je povinný řádek kódu, který VBA sděluje, že zde kód funkce končí.

Výše uvedený kód popisuje různé části typické uživatelsky definované funkce vytvořené ve VBA. V budoucích článcích se na tyto prvky podíváme podrobněji a také se podíváme na různé způsoby provádění funkce VBA v Excelu.

6.1. Objekty, metody, vlastnosti

Visual Basic umožňuje vytvářet softwarové produkty, které dokážou dostatečně automatizovat řešení konkrétních uživatelských úloh.

VBA je objektově orientované prostředí, které obsahuje velkou sadu objektů, z nichž každý má mnoho vlastností a metod. Objekty a nástroje patří do určité třídy (například do třídy TextBox).

Vlastnosti a metody jsou také členy třídy. Vlastnosti popisují, jak objekt vypadá, včetně informací o technikách formátování textu, barvě písma a velikosti písma. Metody jsou procedury, které lze provést na objektu (postupy pro vytvoření a odstranění objektu, procedury událostí, které určují, jak objekt interaguje s uživatelem atd.).

Objekty (analogické k podstatnému jménu)

Za objekt je považován jakýkoli prvek aplikace ─ buňka, list, sešit, graf. Ve skutečnosti je objektem samotná aplikace Excel. Objekty mohou zahrnovat oblasti buněk, rámečky buněk, okna, skripty, styly a tak dále. Každá třída objektů má svou vlastní sadu vlastností, funkcí a událostí.

Metody (analogické ke slovesu)

Metoda je akce, kterou lze provést na objektu. Metody jsou implementovány provedením procedury, která je členem třídy objektů.

Syntaxe volání: Object.Method – uveďte název objektu volajícího metodu a název samotné metody oddělené tečkou.

Příklad: Ball.Kick nebo Ball.Hit; Voda. Pijte

Kombinace dvojtečky a rovnítka v kódu vždy označuje parametr metody, tzn. jak se akce provádí.

Metody mohou mít mnoho parametrů, některé požadované nebo ne.

Příklad 1: ochrana listu List1 před změnami (metoda ochrany)

Listy(“List1”).Chraňte

Příklad 2: Přidání nového listu

Worksheet.Add Before:=Worksheets(1)

Vlastnost (analogické k přídavnému jménu)

Vlastnost je atribut objektu, který popisuje, jak objekt vypadá (jeho barva, velikost a umístění) a jak se chová (zda je viditelný nebo odkazuje na jiný objekt). Když je objekt vytvořen, je provedena procedura k vytvoření instance tohoto objektu.

Chcete-li vlastnosti přiřadit novou hodnotu, musíte vytvořit operátor přiřazení, ve kterém budou jméno a vlastnost objektu (oddělené tečkou) uvedeny vlevo od znaménka rovná se a nová hodnota vpravo.

Syntaxe volání: Object.Property

Vlastnost je vždy přítomna na levé nebo pravé straně výrazů zahrnujících přiřazení hodnoty. Před rovnítkem není dvojtečka.

Příklad 1: přejmenování List1 na Účty:

Tabulky(“List1”).Název =”Účty”

Událost je interakce uživatele s konkrétním objektem na listu. Každá třída objektů má svou vlastní skupinu událostí, na které objekty této třídy reagují.

Argumenty

Argumenty se používají k poskytování metod s hodnotami, které potřebují k provádění úkolů, na které jsou naprogramovány. Úloha je správně dokončena pouze v případě, že každý prvek má typ určený pro danou metodu. Jako argumenty lze předávat čísla, text a booleovské hodnoty (true a false).

Existují dva způsoby, jak předat argumenty metodě:

─ interní, ve kterém musí být argumenty specifikovány v určitém pořadí;

Příklad: ActiveCell.BorderAround LineStyle. Hmotnost. ColorIndex. Barva

Akce metody BorderAround objektu Range nastaví nové atributy ohraničení kolem zadané oblasti. Při jeho použití je třeba zadat argumenty pro určení stylu čáry, tloušťky čáry a barvy. Vlastnost ColorIndex navíc umožňuje definovat barvu pomocí čísla a vlastnost Color pomocí konstanty VisualBasic.

─ externí, ve kterém je posloupnost argumentů libovolná.

Příklad: Rozsah (“A1:C7”).

Typ argumentu:

– povinné argumenty (pro úpravu parametrů ET)

– volitelné argumenty (pro úpravu objektů (změna barvy, velikosti, rámečku))

6.2. Struktura použitá ve VBA

Na nejvyšší úrovni hierarchie je aplikace, za ní následují projekty spojené se skutečnými dokumenty dané aplikace. Třetí úroveň obsahuje moduly (moduly aplikace, uživatelské moduly, moduly tříd, moduly formulářů a moduly odkazů) a poslední úroveň obsahuje jejich procedury a funkce.

Hierarchie používaná ve VBA je znázorněna na Obr. 6.1.

Rýže. 6.1. Hierarchie používaná ve VBA

Modul- jedná se o část pořadu, koncipovanou v takové podobě, která umožňuje jeho nezávislé vysílání. Modul se skládá ze dvou částí: části Deklarace a části Postupy a funkce. První část popisuje globální proměnné, uživatelem definované typy a výčtové typy, zatímco druhá popisuje procedury a funkce.

Postup se nazývá fragment kódu (minimální sémanticky úplná programová struktura) uzavřený mezi operátory Sub a End Sub.

Syntaxe definice procedury:

Dílčí název_procedury(argument_1, argument_2,_, argument_n)

Výpis VBA

Soukromé – nastavuje rozsah procedury – modulu, ve kterém je popsána. Lze jej volat pouze procedurami stejného modulu

Veřejné – postup bude dostupný všem modulům (výchozí nastavení)

Přítel - postup je viditelný pouze v projektu, kde je popsána třída, které je členem.

VBA také používá procedury bez parametrů, které mohou fungovat jako příkazová makra a procedury událostí.

Na Obr. 6.2 představuje rozhraní VBA.

Rýže. 6.2. Rozhraní VBA

6.3. Datové typy používané ve VBA

6.3.1. Proměnné

Variabilní je pojmenovaná oblast paměti používaná k ukládání dat, když je procedura spuštěna.

Chcete-li použít proměnnou, musí být popsána (deklarována).

Syntaxe operátoru deklarace proměnné je:

Ztlumit proměnnou

Dim – klíčové slovo označující, že je deklarována proměnná (dimension – size);

Proměnná – název deklarované proměnné;

As – klíčové slovo používané při specifikaci datového typu (as – how);

Typ – datový typ pro deklarovanou proměnnou

Jeden příkaz Dim může popisovat několik proměnných a uvádět je oddělené čárkami.

Dim i As Byte, j As Integer, k As Integer

Tabulka 6.1. představuje hlavní datové typy používané k nastavení proměnných.

Tabulka 6.1. Typy dat

Typ dat	Proměnné hodnoty
Boolean	Booleovské proměnné, které nabývají jedné ze dvou hodnot: True nebo False
Bajt (krátké celé číslo bez znaménka)	celé číslo z rozsahu 0 až 255
Celé číslo	celá čísla z rozsahu -32 768 až 32 767
Dlouhý (dlouhý celek)	celá čísla z rozsahu -2,147,483,648 až 2,147,483,647
Měna	proměnné pro peněžní výpočty s pevným počtem desetinných míst; vám umožní vyhnout se hromadění chyb při zaokrouhlování
Datum	proměnné pro uložení data a času
Jednoduché (s jednou přesností s plovoucí desetinnou čárkou)	čísla se zlomkovou částí od -3,40282310 38 do -1,40129810 -45 pro záporná čísla a od 1,40129810 -45 do 3,40282310 38 pro kladná čísla
Double (dvojitá přesnost s plovoucí desetinnou čárkou)	čísla se zlomkovou částí od -1,7976931348623110 308 do -4,9406564584124710 -324 pro záporná čísla a od 4,9406564584124710 -324 do 1,7976931348623110 308 pro kladná čísla
Řetězec (řetězec s proměnnou délkou)	proměnné pro uložení řetězců znaků o délce 0 až 64 kB
Varianta (univerzální)	Automatické přizpůsobení datům
Objekt	proměnné pro ukládání odkazů na objekty

Pokud popis proměnných neuvádí jejich typ, je jim automaticky přiřazena Varianta. To znamená, že v buňce odpovídající této proměnné může být uložen jakýkoli typ informace (obdoba formátu „General“ v ET).

Příklad: Dim i, j As Integer

To je ekvivalentní následujícímu zápisu: Dim i As Variant, j As Integer

Chcete-li nahrát stejný formát, potřebujete:

Dim i As Integer, j As Integer

Pro použití určitých proměnných v různých částech programu se používá tzv. rozsah.

Rozsah proměnné je oblast programů, kde je název proměnné považován za platný (viditelný), a proto je možný přístup k její hodnotě (obr. 6.3).

Rýže. 6.3. Variabilní rozsah VBA

Existují tři úrovně proměnlivé viditelnosti a pět způsobů její reklamy.

Úroveň 1 – Procedura (rozsah je procedura, ve které je proměnná deklarována).

*** Příkaz Dim deklaruje proměnnou kdekoli v proceduře, ale vždy předchází příkazům, které ji používají. Taková proměnná může existovat pouze během provádění procedury, poté se hodnota této proměnné ztratí a paměť se uvolní.

*** Statické (podobně jako Dim) – ale! deklaruje statickou proměnnou. Po ukončení procedury se paměť neuvolní a hodnota se neztratí.

Úroveň 2 – Modul

*** soukromý operátor deklaruje proměnnou v sekci Deklarace (mimo procedury modulu)

*** operátor Dim (v tomto případě) je zcela podobný soukromému operátorovi

Úroveň 3 - Aplikace

*** Veřejný operátor deklaruje proměnnou v sekci Deklarace

6.3.2. Konstanty

Dělí se na zakázkové a vestavěné.

Uživatelské konstanty vyžadovat oznámení. Chcete-li to provést, použijte operátor formuláře:

Konstanta = hodnota

Const je klíčové slovo, které označuje, že je deklarována konstanta;

As – klíčové slovo, kterým začíná specifikace datového typu;

Konstanta – název deklarované konstanty;

Typ – datový typ pro konstantu;

Hodnota – hodnota přiřazená konstantě.

Const pi As Double = 3,141592654

Const e As Double = 2,718281828

Const Message = "Vypnutí"

Můžete deklarovat více konstant oddělených čárkami:

Const min = 0, max = 1000

Vestavěné konstanty nevyžadují oznámení. Vestavěné názvy konstant začínají předponou vb, například vbFriday.

6.4. Použití standardních oken operačního systému Windows

VB má velké množství zabudovaných procedur, které se liší od uživatelských procedur tím, že jejich popisy byly naprogramovány vývojáři VBA.

Funkce MsgBox a InputBox se používají k poskytování uživatelského vstupu do kódu programu a výstupu z něj a také k vytváření vlastních dialogových oken.

6.4.1. Funkce MsgBox

MsgBox („zpráva“, [tlačítka, nadpis]) - tato funkce zobrazí dialogové okno obsahující zprávu o délce až 1024 znaků, do které lze zahrnout hodnotu proměnných pomocí operace zřetězení a také (volitelná) tlačítka pro reakce na zobrazení okna (standardně je zde pouze tlačítko OK).

Při definování složitého dialogového okna pomocí funkce MsgBox se používají následující konstanty:

1) Nastavení vzhledu okna zprávy (obr. 6.4):

vbCritical, vbQuestion, vbExclamation, vbInformation.

Rýže. 6.4. Vzhled oken

2) Chcete-li nastavit tlačítka v okně zprávy:

vbOkCancel, vbAbortRetryIgnore, vbYesNOCancel, vbYesNO, vbRetryCancel.

3) Chcete-li nastavit další akce po kliknutí na příslušné tlačítko:

vbOk, vbCancel, vbAbort, vbRetry, vbIgnore, vbYes, vbNO.

Příklad postupu 1:

Private Sub Example_1()

y = MsgBox("Zavřít okno", vbQuestion + vbYesNoCancel, "Zpráva systému Windows")

Kód procedury 1 ve VBA a výsledek spuštění programu jsou uvedeny na Obr. 6.5.

Rýže. 6.5. Příklad postupu 1

Příklad postupu 2:

Sub Ahoj()

y = MsgBox("Zavřít okno", vbQuestion + vbYesNoCancel, "Zpráva systému Windows")

Pokud y = vbYes Then MsgBox ("VVVVVVVVVVVVVVVVVVVV") Jinak

If y = vbNo Then ActiveCell = "Ahoj"

Kód procedury 2 ve VBA a výsledek spuštění programu jsou uvedeny na Obr. 6.6.

Rýže. 6.6. Příklad postupu 2

6.4.2. Funkce InputBox

InputBox („zpráva“[, název] [, výchozí hodnota] [, souřadnice x] [, souřadnice y]) je funkce sloužící k zadávání hodnot proměnných do programu. Tato funkce zobrazí dialogové okno obsahující vstupní pole, tlačítka OK a Storno, zprávu (výzva k zadání) a (volitelně) název okna, výchozí vstupní hodnotu a horizontální a vertikální souřadnice okna.

Takže můžete zadat číslo pomocí příkazu:

a = InputBox("první číslo")

Rýže. 6.7. Typ funkce InputBox

6.4.3. Společné používání funkcí MsgBox a InputBox

V praxi se funkce MsgBox a InputBox používají společně k vytváření procedur. Kromě nich lze navíc použít podmíněný příkaz If, který umožňuje zkontrolovat podmínky zadané uživatelem a na základě jeho závěrů vytvořit výsledek.

Podmíněný operátor If je operátor, který umožňuje určit provedení určitých akcí v závislosti na zadaných podmínkách. Hlavní komponenty pro to jsou:

1) pokud (pokud)

2) pak (pak)

3) jinak (jinak)

Tedy výraz - pokud a>1 pak b= a+1 jinak bude vypadat b=a-1

Pokud a>1, pak b= a+1, jinak b=a-1.

Příklad postupu 3:

Počítač musí vynásobit dvě čísla a výsledek zobrazit v jedné z buněk tabulky. Pokud je jejich produkt vyšší než 2000, měl by počítač vydat další zprávu „Přijatá hodnota je větší než 2000“.

Dílčí příklad_2()

Dim a, b, y As Long

a = InputBox("první číslo")

b = InputBox("druhé číslo")

Pokud y< 2000 Then Range("A4") = y Else MsgBox ("Полученное значение больше 2000")

Kód procedury 3 ve VBA a výsledek spuštění programu s různými podmínkami jsou uvedeny na Obr. 6.8.

Rýže. 6.8. Příklad postupu 3

Zvažte program, který obsahuje komplexní funkci MsgBox a příkaz If.

Příklad postupu 4:

Zadejte dvě libovolná čísla. Pak je položena otázka: "Jste si jistý, že je chcete znásobit?" a možnosti odpovědí: „ano“, „ne“. Pokud je odpověď „ano“, čísla se vynásobí a zobrazí se zpráva s výsledkem, jinak se akce neprovede.

Dílčí příklad()

Dim a, b, d As Double

a = InputBox("první číslo")

b = InputBox("druhé číslo")

y = MsgBox("Opravdu je chcete znásobit?", vbCritical + vbYesNo, "Otázka")

Pokud y = vbYes Then d = a * b Jinak MsgBox ("Akce zrušena")

Pokud y = vbYes, pak MsgBox (d)

Kód procedury 3 ve VBA a výsledek spuštění programu s různými podmínkami jsou uvedeny na Obr. 6.9.

Rýže. 6.9. Příklad postupu 4

6.5. Ovládací konstrukce VBA

Řídicí konstrukce programovacího jazyka jsou instrukce a skupiny instrukcí, jejichž použití umožňuje podle potřeby měnit sekvenci provádění dalších programových instrukcí. Tyto struktury jsou rozděleny do větví a cyklů. Větvení je řídicí struktura, která umožňuje přeskakovat určité skupiny instrukcí během provádění v závislosti na hodnotě podmínky. Smyčka je řídicí struktura, která představuje schopnost opakovaně provádět skupiny instrukcí před výskytem události.

6.5.1. Větvení

Pokud… Pak Stavebnictví

Konstrukce If...Then instruuje VBA, aby učinila nejjednodušší rozhodnutí: pokud je podmínka následující po příkazu If pravdivá, musí provést následující příkaz (nebo příkazy); pokud je podmínka nepravdivá, musíte přejít na řádek umístěný bezprostředně po podmíněné konstrukci. Jednořadá konstrukce:

If podmínka Then příkaz[y]

Při použití více příkazů (blok If):

Pokud podmínka Pak

operátor

[operátoři]

Jednořádkový příklad:

A ge = InputBox("zadejte svůj věk.", "Věk")

Pokud Věk< 21 Then MsgBox "Вы не можете покупать алкогольные напитки.", "несовершеннолетний"

Rýže. 6.10 Příklad konstrukce If then

Pokud…tak…Jiná konstrukce

Pomocí této konstrukce můžete zvolit jeden směr aktivity, pokud je podmínka pravdivá, a jiný, pokud je nepravdivá. Lze jej použít například pro práci s okny obsahujícími dvě tlačítka.

Syntaxe konstrukce:

Pokud podmínka Pak

operator_1

operator_2

Je-li podmínka pravdivá, VBA provede první skupinu příkazů - příkazy_1, pokud je nepravdivá, přesune se na řádek Else a poté provede druhou skupinu příkazů - příkaz_2.

S ub vozrast()

Věk = InputBox("zadejte svůj věk.", "Věk")

Pokud Věk< 21 Then

MsgBox "Nesmíte kupovat alkoholické nápoje.", "nepodstatné"

Vkus = InputBox("co chcete koupit?", "Chuť")

Rýže. 6.11. Příklad konstrukce If Then Else

Konstrukce If…Ten…ElseIf…Else

Pomocí tohoto designu si můžete vybrat jeden z několika směrů působení. V závislosti na složitosti programu můžete použít libovolný počet řádků ElseIf.

Syntaxe konstrukce:

If condition_1 Then

operator_1

ElseIf condition_2 Then

operator_2

ElseIf condition_3 Then

operator_3

operátoři_4

Tuto konstrukci je vhodné použít s počtem příkazů ElseIf ne větším než 5. V tomto případě je lepší použít konstrukci Select Case.

Vyberte Design pouzdra

Místo více příkazů ElseIf můžete použít konstrukci Select Case pro zhuštěnější zobrazení programu.

Tuto konstrukci se doporučuje použít, pokud rozhodnutí, které je třeba v programu učinit, závisí na jedné proměnné nebo na výrazu, který má alespoň tři nebo čtyři hodnoty. Taková proměnná (nebo výraz) se nazývá testovací případ.

Opakovaný výraz je porovnán s výrazy po Case. Pokud se shodují, provede se odpovídající příkaz, jinak se zkontrolují další výrazy. Pokud se žádný z výrazů neshoduje, provedou se příkazy za Case Else.

Syntaxe konstrukce:

Vyberte Case iterable_expression

Výraz případu_1

operator_1

Výraz případu_2

operator_2

operátory

6.5.2. Cykly

Existují tři typy smyček: s kontrolní podmínkou, s čítačem a smyčky založené na datové struktuře.

V podmíněných cyklech se příkaz nebo skupina příkazů opakuje, dokud není podmínka splněna. Takové smyčky se nazývají smyčky Do.

Pokud je počet opakování těla smyčky znám předem, není potřeba kontrolovat podmínku řízení. Takové smyčky se nazývají opakující se smyčky výčtu. Smyčky tohoto typu se dělí do dvou skupin: smyčky s čítačem a smyčky založené na datové struktuře.

Ve smyčkách s čítačem se používá speciální proměnná - čelit, jehož hodnota se zvyšuje nebo snižuje o danou hodnotu s každým opakováním těla cyklu – krok cyklu. Smyčka končí poté, co hodnota čítače dosáhne (nebo překročí) koncovou hodnotu čítače smyčky.

Syntax:

Pro čítač = start_value To end_value Krok loop_step

<тело цикла>

Další počítadlo

Příklad výpočtu součtu hodnot obsažených v lichých buňkách prvního sloupce prvního listu (v rámci prvních 10 buněk):

Dim I As Integer

S = 0

Pro I = 1 až 10 Krok 2

S = S + Aplikace.Pracovní listy(1).Buňky(I, 1).Hodnota

Rýže. 6.12. Příklad smyčky 1

Ve smyčkách datové struktury se tělo smyčky postupně opakuje pro všechny homogenní objekty, které tvoří pole nebo rodinu. V tomto případě objektová proměnná funguje jako čítač.

Syntax:

Pro každý prvek ve struktuře dat

<тело цикла>

Další prvek

Příklad postupného zobrazení oken zpráv s názvy všech listů v aktuálním sešitu:

D im S jako pracovní list

Pro každý S In Application.Worksheets

Rýže. 6.13. Příklad smyčky 2

6.6. Použití ovládacích prvků ke spuštění makra a zadávání dat

Chcete-li použít ovládací prvky na listu, musíte pro každý prvek napsat kód ve VBA. Chcete-li to provést, musíte se nejprve přepnout do režimu návrhu. Tento režim také mění vlastnosti objektu v okně Vlastnosti.

Chcete-li napsat programový kód ovládacího prvku, musíte na tento prvek dvakrát kliknout (obr. 6.14 a) a ten se automaticky přepne do režimu VBA a vygeneruje „závorky operátora“ s ohledem na událost uživatelského formuláře (obr. 6.14 b).

Rýže. 6.14. Tvarování ovládacích držáků operátora

Události UserForm

Událost je signál daný, když se s objektem něco stane. Tlačítko může například generovat událost v reakci na kliknutí myší, vstupní řádek v reakci na něco zadávaného, kliknutí myší na něj atd.

Rýže. 6.15 Události UserForm

Některé typy událostí:

Myší události- jedno (dvojité) kliknutí levým tlačítkem myši na objekt; stisknutí (uvolnění) tlačítka myši; pohybem kurzoru myši nad ovládacím prvkem.

Události na klávesnici- stisknutí jednoduchého symbolu, funkčních kláves nebo nějaké kombinace symbolů na klávesnici.

Události formuláře- nakládání (Load), vykládání (Unload) formulářů atd.

K události Click dojde, když uživatel stiskne a uvolní tlačítko myši, když je ukazatel myši nad objektem. K události může dojít, když se změní hodnota ovládacího prvku.

Formát procedury obsluhy události

Private Sub Form_Click()

Private Sub object_Click()

kde objekt je název objektu, na který se tato obsluha odkazuje.

Příklad:

Private Sub Form_Click()

MsgBox „Klikněte“

Rýže. 6.17. Příklad vytvoření tlačítka

Zápis programového kódu pro EE

Programování ovládacího prvku se řídí stejnými principy jako programování makra.

Programování tlačítek:

Příklad 1: Napsání Hello po stisknutí tlačítka:

Private Sub Button_Click()

MsgBox "Ahoj :)"

Rýže. 6.18. Příklad 1 vytvoření ovládacího prvku

Příklad 2: Kontrolní podmínky: přepočet tabulky

Rýže. 6.19. Zdrojová tabulka

Vytvořit tlačítko (obr. 6.20)

Rýže. 6.20. Vytvoření tlačítka pro implementaci příkladu 2

Dvojitým kliknutím se dostanete do okna vytvoření procedury (obr. 6.21):

Rýže. 6.21. Okno vytvoření procedury

Pojďme vytvořit kód:

Private Sub sum_Click()

Dim I As Integer

Pro I = 2 až 8 Krok 1

N = buňky (I, 2) * buňky (I, 3)

S = S + Buňky (I, 4). Hodnota

Buňky (I + 2, 4) = S

Kde Cells(I, 4) je číslo buňky, kde I je řádek, 4 je sloupec (D).

Kód uvažovaného příkladu ve VBA a výsledek spuštění programu s různými podmínkami jsou uvedeny na Obr. 6.22.

Rýže. 6.22. Implementace předmětného příkladu

6.7. Vlastní formuláře vytvořené ve VBA

Vlastní formuláře jsou nezbytné k vytvoření efektivnější uživatelské zkušenosti.

Například namísto zadávání osobních údajů přímo do listu můžete vytvořit vlastní formulář, který vás vyzve k zadání údajů, nebo formulář pro program pro výpočet dat.

Formuláře jsou objekty, které mohou být volány jinými aplikačními moduly.

Do formulářů můžete přidat vlastní metody a vlastnosti. Chcete-li ve formuláři vytvořit novou metodu, musíte přidat proceduru deklarovanou slovem Public:

Public Sub UserMethod()

operátory

Styly rozhraní:

1) jeden dokument (SDI) – můžete otevřít pouze jeden dokument a pro otevření dalšího musíte aktivní dokument zavřít;

2) multi-document (MDI) – podporuje několik formulářů uvnitř formuláře hlavního kontejneru; má prvky v nabídce Okno pro přepínání mezi okny nebo dokumenty;

3) styl průzkumníka – okno, které má dva panely nebo oblasti, obvykle sestávající z hierarchické prezentace úrovní informací vlevo a oblasti zobrazení vpravo.

Kroky vytvoření formuláře:

1. Potřebné ovládací prvky jsou umístěny na formuláři a krásně uspořádány.

Výsledek: máme formulář s ovládacími prvky, ale popisky na nich jsou standardní: Command1, Label1 atd.

2. Nastavte vlastnosti formuláře a ovládacích prvků

Výsledek: formulář má požadovaný vzhled, všechny ovládací prvky mají jasné nápisy, potřebné obrázky atd. Nelze jej spustit kvůli nedostatku programového kódu.

3. Obslužné rutiny událostí pro ovládací prvky jsou napsány.

Výsledek: provedení akcí v souladu s úkolem.

D
Pro vložení uživatelského formuláře potřebujete Insert – UserForm.

Pokud nemáte okno vlastností, můžete je otevřít takto: Zobrazit okno vlastností.

Rýže. 6.23. Rozhraní pro vytváření formulářů

Příklad 1: Vytvořte formulář pro výpočet funkce
obsahující:

místo pro výstup (z programu) výsledku výpočtu;

textové pole pro zadání počátečních údajů;

tlačítko pro spuštění a zrušení programu.

Po zadání typu formuláře (obr. 6.24) je třeba zadat kód programu.

Rýže. 6.24. Vytvoření formuláře pro příklad 1

Dvojitým kliknutím na tlačítko "Vypočítat" se formulář přepne do editace kódu.

P rivate Sub Calc_Click()

1: a = TextBox_a.Value

2: b = TextBox_b.Value

3: c = Sqr(a^2 + b^2)

4: Label1.Caption = "c = " & Str(c)

Private Sub Cancel_Click()

Rýže. 6.25. Ukázka toho, jak funguje formulář v příkladu 1

Uživatelský formulář lze odvodit z libovolného modulu. K jeho zobrazení se používá metoda Show. Jméno.Ukázat

Private Sub VSch_Click()

Rýže. 6.26. Pomocí metody Zobrazit

Příklad 2. Vytvoření formuláře pro uživatele pro zadávání proměnných a ovládání přepínačů se sčítáním a odečítáním a zobrazením výsledku.

Rýže. 6.27. Vytvoření formuláře a jeho kódu například ve VBA 2

Private Sub CommandButton1_Click()

Dim nejprve As Long, podruhé As Long

první = tb1.Hodnota

sekunda = tb2.Hodnota

"Pokud je vybráno první tlačítko, přidejte proměnné

Pokud ob1.Value = True Then

lab4.Titulek = první + druhý

"Pokud je vybráno druhé tlačítko, odečtěte proměnné

Pokud ob2.Value = True Then

lab4.Caption = první - druhý

Rýže. 6.28. Ilustrace toho, jak funguje formulář v příkladu 2

Příklad 3: Vytvoření formuláře pro uživatelský vstup a výstup do řádků tabulky

Rýže. 6.29. Vytvoření formuláře pro příklad 3

Private Sub CB_Cancel_Click()

Private Sub CB_ok_Click()

Dim LastRow As Long

LastRow = Worksheets("Sheet3").Range("A65536").End(xlUp).Row + 1

Buňky (Poslední řádek, 1).Hodnota = tb1.Hodnota

Buňky (Poslední řádek, 2).Hodnota = tb2.Hodnota

Cells(LastRow, 3).Hodnota = tb3.Value

Rýže. 6.30. Ilustrace toho, jak funguje formulář v příkladu 3

Otázky pro sebeovládání

Co je VBA?

Co jsou objekty, metody a vlastnosti ve VBA?

Popište hierarchii ve VBA?

Jaký je postup?

Co je to kontrola?

Popište syntaxi proměnné?

Jaké typy konstant se používají ve VBA?

K čemu slouží funkce MsgBox a InputBox?

Jaké řídicí konstrukce se používají ve VBA?

Co je událost uživatelského formuláře?

Co je uživatelský formulář?

Jaké styly rozhraní se používají při vytváření uživatelského formuláře?

Glosář

VBA (Visual Basic for Applications) je objektově orientovaný vysokoúrovňový makroprogramovací jazyk zabudovaný do všech programů Microsoft Office.

Makro (nebo makropříkaz) je sekvence příkazů a funkcí napsaných v modulu VBA, která umožňuje automatizovat provádění základních operací.

Ovládací prvek - objekty umístěné na listech a v dialogových oknech určených k zobrazení, zadávání a výpočtu dat.

Režim návrhu přepne Excel do režimu, který zakáže všechny ovládací prvky na listu.

Za objekt je považován jakýkoli prvek aplikace ─ buňka, list, sešit, graf. Objekty mohou zahrnovat oblasti buněk, rámečky buněk, okna, skripty, styly a tak dále. Každá třída objektů má svou vlastní sadu vlastností, funkcí a událostí

Metoda je akce, kterou lze provést na objektu.

Vlastnost je atribut objektu, který popisuje, jak objekt vypadá (jeho barva, velikost a umístění) a jak se chová (zda je viditelný nebo odkazuje na jiný objekt).

Událost je interakce uživatele s konkrétním objektem na listu.

Modul je část programu navržená ve formě, která umožňuje jeho nezávislé vysílání.

Procedura je fragment kódu (minimální sémanticky úplná programová struktura) uzavřený mezi operátory Sub a End Sub.

Proměnná je pojmenovaná oblast paměti používaná k ukládání dat, když je procedura spuštěna.

Rozsah proměnné je oblast programů, kde je název proměnné považován za platný (viditelný), a proto je možný přístup k její hodnotě.

MsgBox - Tato funkce zobrazí dialogové okno obsahující zprávu o délce až 1024 znaků, do které lze zahrnout hodnotu proměnných pomocí operace zřetězení a také (volitelně) tlačítka pro reakci na zobrazení okna (ve výchozím nastavení, pouze tlačítko OK).

InputBox je funkce sloužící k zadávání hodnot proměnných do programu.

Operátor If je operátor, který umožňuje určit provádění určitých akcí v závislosti na zadaných podmínkách.

Řídicí konstrukce programovacího jazyka jsou instrukce a skupiny instrukcí, jejichž použití umožňuje podle potřeby měnit sekvenci provádění dalších programových instrukcí.

Větvení je řídicí struktura, která umožňuje přeskakovat určité skupiny instrukcí během provádění v závislosti na hodnotě podmínky.

Smyčka je řídicí struktura, která představuje schopnost opakovaně provádět skupiny instrukcí před výskytem události.

Řídicí událost je signál odeslaný, když se s objektem něco stane.

7. Výměna dat v tabulkovém procesoru

Oblíbené v kategorii: