Práce s objekty

CreateObject

Definice

Vytvoří novou instanci objektu daného typu, například Excel, Word, slovníky nebo práci se soubory.

Praktické vysvětlení

Pomocí objektu lze používat další pokročilé metody pomocí VBA. Pokud je takový objekt tvořen, je nutné ho zadat do proměnné před kterou je možné použít příkaz Set pro jeho tvorbu.

Vzorec

Set objekt = CreateObject(Object_Name)

Argumenty

Object_name – Název objektu ve formátu „aplikace.třída“ (např. „Excel.Application“).

Použití

Set xlApp = CreateObject(„Excel.Application“) – Tvorba objektu nového Excel sešitu.

Set objWord = CreateObject(„Word.Application“) – Tvorba objektu nového Wordu.

Set fso = CreateObject(„Scripting.FileSystemObject“) – Tvorba objektu pro práci se soubory a složkami.

set dictObj = CreateObject(„Scripting.Dictionary“) – Tvorba slovníku.

Použití s ostatními funkcemi

Tvorba nového Excelu pomocí objektu a příkazu Set:

Dim xlApp As Object

Set xlApp = CreateObject(„Excel.Application“)

xlApp.Visible = True ‚ Spustí Excel

xlApp.Workbooks.Add ‚ Přidá nový sešit

Set xlApp = Nothing ‚ Uvolní objekt

Tvorba objektu pro slovník s využitím příkazu Set a přidání hodnot do slovníku. Následně procházení každého klíče ve slovníku cyklem For Each, kde hodnota propojená s klíčem má být vypsána do okna Inmediate pomocí Debug.Print.

set columnDict = CreateObject(„Scripting.Dictionary“)

columnDict.Add „1“, „Příjmení“

columnDict.Add „2“, „Jméno“

For Each dict In columnDict.keys

Debug.Print columnDict(dict)

Next dict

Tvorba objektu pro práci se složkami a soubory do proměnné pomocí příkazu Set. Cesta k souborům je specifikována v proměnné „folderPath“. Následně je tato složka získání pomocí argumentu „getfolder“ a všechny soubory pomocí argumentu „Files“ budou procházeny cyklem For each. Do okna Inmediate pomocí Debug.Print budou následně vypsány názvy všech souborů ve složce.

Set fso = CreateObject(„Scripting.FileSystemObject“)

folderPath = „C:\Cesta\k\souborum\“

For Each file In folder.getfolder(folderPath).Files

Debug.Print file.Name

Next file

GetObject

Definice

Získá odkaz na existující objekt.

Praktické vysvětlení

Pomocí funkce je možné např. získat objekt otevřeného Excelu, daného slovníku, objektu souborů a složek a dalších typů objektů, které např. jsou vytvořeny.

Vzorec

Set objekt = GetObject([pathName], [Class])

Argumenty

pathName (Nepovinné) – Cesta k souboru nebo objektu.

Class (Nepovinné) – Název aplikace a třídy.

Použití

Set xlApp = GetObject(, „Excel.Application“) – Získání objektu aplikace Excel.

Použití s ostatními funkcemi

Pokud neexistuje daný objekt otevřeného Excelu vyskytne se error, který ale nezastaví průběh kódu z důvodu příkazu On Error Resume Next. Objekt bude uložen do proměnné nastavenou pomocí příkazu Set. Pokud zde error bude z důvodu neotevřeného Excelu, zjistí se error pomocí funkce Err.Number. Pokud Excel není otevřen, tak se otevře, zaevidovaný error se vymaže pomocí Err.Clear.

On Error Resume Next

Set xlApp = GetObject(, „Excel.Application“)

If Err.Number <> 0 Then Set xlApp = CreateObject(„Excel.Application“)

Err.Clear

Debug.Print

Definice

Slouží k výpisu hodnot, zpráv a diagnostických informací do Inmediate okna během běhu kódu.

Praktické vysvětlení

Funkce se používá zejména při debuggování, kdy není jisté, proč je v daném kódu chyba nebo je nutné zobrazit, co se ukládá do jednotlivých proměnných aby bylo zjištěno, proč kód nefunguje dle potřeb.

Vzorec

Debug.Print output

Argumenty

Output – Výraz nebo hodnota, která se má vypsat. Může to být číslo, text, proměnná nebo výsledek výrazu.

Použití

x = 10

Debug.Print x – Vypsání hodnoty „x“ do Inmediate okna.

With/End With

Definice

Umožňuje pracovat s objektem efektivněji a bez opakování názvu objektu.

Praktické vysvětlení

Do argumentu With se zadá pouze název objektu, pro který je nutné udělat více příkazů. Tím se daný objekt nebude opakovat na více řádcích a budou použité pouze příslušné příkazy. With následně musí být vždy ukončen pomocí „End With“, podobně jako podmínky nebo cykly. Příkazy v bloku pod „With“ musí začínat tečkou, stejně jakoby se psaly bez objektu „With“.

Vzorec

With objekt .vlastnost1 = hodnota .vlastnost2 = hodnota End With

Argumenty

Output – Výraz nebo hodnota, která se má vypsat. Může to být číslo, text, proměnná nebo výsledek výrazu.

Použití

Do buňky „A1“ má být zapsána hodnota „Hello“ a zároveň má být písmo naformátováno tučně a výplň specifickou barvou. Aby nebylo nutné stále používat příkaz „Range(„A1“)“, je použit tento příkaz pomocí „With“.

With Range(„A1“)

.Value = „Hello“

.Font.Bold = True

.Interior.Color = RGB(255, 255, 0)

End With

TypeName

Definice

Vrací název datového typu proměnné jako řetězec.

Vzorec

TypeName(variable)

Argumenty

Variable – Proměnná, pro kterou má být zjištěn datový typ.

Použití

Vrátí typ „Integer“ pro proměnnou „x“ pomocí Debug.Print:

Dim x As Integer

Debug.Print TypeName(x)

Vrátí typ „String“ pro proměnnou „y“ a také typ funkcí VarType pomocí Debug.Print pro vypsání do okna Inmediate:

Dim x As Variant

y = „Text“

Debug.Print TypeName(y), VarType(y)

VarType

Definice

Vrací číselnou hodnotu odpovídající datovému typu.

Vzorec

VarType(variable)

Argumenty

Variable – Proměnná, pro kterou má být zjištěno číslo datového typu.

Použití

Vrátí typ číslo 8 pro typ „String“ pro proměnnou „y“ a také typ název datového typu pomocí TypeName. Vše je vypsáno do okna Inmediate pomocí Debug.Print:

Dim x As Variant

y = „Text“

Debug.Print TypeName(y), VarType(y)

Set

Definice

Používá se k přiřazení objektu do proměnné.

Praktické vysvětlení

Pokud má být např. sešit nebo list sešitu používán v kódu víckrát, je vhodné ho zadat do proměnné. Pro danou proměnnou vyskočí error, pokud nebude nastavena příkazem „Set“. Tento příkaz se používá i u dalších metod objektů, často ale bývá spojováno právě se sešity nebo listy. Je vhodné daný objekt i deklarovat pomocí metody „Dim“.

Vzorec

Set Objekt_Name = instance

Argumenty

Object_Name – název proměnné

Instance – Příkaz, který se má do proměnné dostat.

Použití

Set rng = Range(„A1:B5“) – Nastavení oblasti.

Set objExcel = CreateObject(„Excel.Application“) – Nastavení aplikace.

Set objExcel = Nothing – Nastavení proměnné na nic pomocí příkazu Nothing.

Set objExcel = ThisWorkbook – Nastavení sešitu Excelu.

set objSheet = ThisWorkbook.Sheets(„List1“) – Nastavení listu v sešitu.

Nothing

Definice

Používá se k uvolnění paměti po použití objektu.

Praktické vysvětlení

Pokud v proměnné již nemá být daná hodnota, je možné ji příkaz „Nothing“ vymazat. Je vhodné např. při používání cyklů a hledání určité hodnoty, aby v případě, kdy se hodnota nenalezne, nebyla uložená stará hodnota v proměnné. Nutné používat s příkazem Set.

Vzorec

Set object = Nothing

Argumenty

Příkaz nemá žádné argumenty, je nutné zadat název objektu za „Set“.

Použití

Uvolnění paměti v případě, že už není vyžadováno s daným Excelem pracovat:

Set obj = CreateObject(„Excel.Application“)

Set obj = Nothing ‚ Uvolnění paměti

Uvažujme data, kdy v prvním sloupci je ID na listu „Ws“ v buňkách 1 až 5 a chceme nalézt pomocí Find v jakém řádku v sloupci „A“ na listu „Output“ je dané ID, přičemž v sloupci „B“ jsou hodnoty, které mají být zkopírovány na list „Ws“ hned vedle ID. Nejprve jsou listy dostány do proměnných pomocí názvu proměnné a příkazu Set. Při procházení hodnot cyklem For má být ID nalezeno. Pokud nebude nalezeno pomocí ošetření podmínky If, kdy je použita obrácena logika s příkazem „Not“, tedy pokud není hodnota Nothing, má se hodnota zkopírovát z listu „output“ ze sloupce „B“ k danému ID na listu „ws“ do sloupce „B“. Následně se proměnná uvolní a pokračuje se v dalším hledání.

Dim ws_output As Worksheet

Dim ws As Worksheet

set ws_output = ThisWorkbook.Sheets(„Output“)

set ws = ThisWorkbook.Sheets(„Source““)

For i = 1 To 5

CelltoFind = ws_output.Cells(i, 1).value¨

Set foundCell = ws.Columns(1).Find(What:=CelltoFind, LookIn:=xlValues)

If Not foundCell Is Nothing Then

ws.Cells(i, „B“).Value = ws_output.Cells(foundCell.Row, „B“).Value

End If

Set foundCell = Nothing

Next i

Application.Run

Definice

Umožňuje spustit makro nebo funkci v Excelu.

Praktické vysvětlení

Pokud je např. v modulu více podprogramů pomocí Sub. Je možné spustit jiný Sub, do kterého jsou buďto vkládány vstupní argumenty nebo má být z určitého důvodu kód v dalším podprogramu.

Vzorec

Application.Run „NázevMakra“

Použití

Mějme makro „Zkouska“, díky kterému má být spuštěno makro „Obsah“, kde je pouze použit příkaz Debug.Print.

Sub zkouska()

Application.Run „Obsah“

End Sub

Sub Obsah()

Debug.Print „Hello“

End Sub

Application.DisplayAlerts

Definice

Řídí zobrazování výstražných zpráv v Excelu. Používá se hlavně pro potlačení potvrzovacích dialogů (např. při mazání listů nebo přepisování souborů).

Praktické vysvětlení

Pokud má být např. list vymazán, v Excelu se objeví potvrzovací hláška:

V tom případě je nutné kliknout na danou možnost, že má být list skutečně odstraněn. Pokud tato možnost má být provedena automaticky pomocí makra, je nutné potlačit tato dialogová okna, což tento příkaz provádí.

Vzorec

Application.DisplayAlerts = Boolean

Použití

Application.DisplayAlerts = True – Zobrazuje upozornění (výchozí)

Application.DisplayAlerts = False – Potlačí upozornění a nebudou již zobrazena

Použití s ostatními funkcemi

Chtějme odstranit všechny list až na první list, aby nebyly zobrazeny dialogové okna pro potvrzení smazání:

Application.DisplayAlerts = False

For i = 2 To ThisWorkbook.Sheets.Count

Sheets(i).Delete

Next i

Application.ScreenUpdating

Definice

Řídí aktualizaci obrazovky v Excelu. Vypnutím (False) lze výrazně zrychlit výkon makra, protože Excel nebude obnovovat zobrazení při změnách buněk, listů nebo souborů.

Praktické vysvětlení

Pokud je např. nutné pracovat s více listy jako kopírování dat z jednoho listu na druhý, při spuštění makra by byl tento úkon vidět a zpomalovat by se daný proces. Při vypnutí nebude tento přechod viděn na pozadí.

Vzorec

Application.ScreenUpdating = Boolean

Použití

Application.DisplayAlerts = True – Zapne aktualizaci obrazovky (výchozí)

Application.DisplayAlerts = False – Vypne aktualizaci obrazovky

Budou přidávány data hodnot do buněk v Excelu. Při vypnutí aktualizace tento proces bude proveden rychleji. Následně se aktualizace opět zapne pro dokončení procesu, což ale není nutné.

Application.ScreenUpdating = False ‚ Zrychlí kód

Dim i As Integer

For i = 1 To 10000

Cells(i, 1).Value = i

Next i

Application.ScreenUpdating = True ‚ Obnoví zobrazení

Dictionary

Definice

Slovníky jsou speciální datové typy, do kterých je možné přidávat data ve tvaru klíč:hodnota, která lze dále referencovat.

Přidání dat do slovníku

Dim k As Variant

Dim dict As Object

Set dict = CreateObject(„Scripting.Dictionary“)

dict.Add Key:=“1″, Item:=“IT“ ‚ přidání první hodnoty do slovníku, Klíčem je „1“, hodnotou „IT“

dict.Add Key:=“2″, Item:=“Název kurzu“ ‚ přidání druhé hodnoty do slovníku

dict.Add Key:=“3″, Item:=“Cena“ ‚ přidání třetí hodnoty do slovníku

Procházení klíče i hodnoty slovníku

Procházení hodnot klíčů a hodnot cyklem, kdy budou vráceny obě hodnoty, klíče i hodnota do okna Inmediate pomocí příkazu Debug.Print.

For Each k In dict.Keys

Debug.Print k & “ – “ & dict.Item(k)

Next k

Procházení klíče slovníku

For Each k In dict.Keys

Debug.Print k

Next k

Procházení hodnot ze slovníku

For Each k In dict.Keys

Debug.Print dict(k)

Next k

Smazání dat ze slovníku

dict.Remove „1“ ‚ Odstranění specifického klíče

dict.RemoveAll ‚ Odstranění všech klíčů i hodnot ze slovníku

dict.Remove dict.Keys(0) ‚ Odstranění prvního klíče/hodnoty ze slovníku

dict.Remove dict.Keys(UBound(dict.Keys)) ‚ Odstranění posledního klíče/hodnoty ze slovníku