Transcript VBAEXCEL

VBA – Excel
Lenka Forstová
Literatura
• MS Excel 2000 – Programování ve VBA, John
Walkenbach, ComputerPress
– pro vyšší verze Excelu mladší, mírně upravená vydání
– pěkné a podrobné
2
Příklady vlastností objektů
• Objekt WorkBook
–
–
–
–
–
Author – jméno autora sešitu
Creator – jméno aplikace, která sešit vytvořila
Name – jméno sešitu
Path – cesta, kde je sešit uložen
Saved – True je–li sešit uložen, False uložen není
• Vlastnosti je možné buď nastavovat nebo naopak zjišťovat
jejich hodnoty
• Existují vlastnosti, které jsou určeny, buď jen pro čtení
nebo je pro zápis
3
Metody objektů
• Objekt Workbook
–
–
–
–
–
–
Activate – aktivuje první okno, které souvisí se sešitem
Close – zavře sešit
Protect – zamkne sešit
Save – uloží sešit
SaveAs – parametry pro uložení
UnProtect – Odemkne sešit
• Volání metody
– jméno objektu.název metody
– metody mohou mít různý počet povinných, či nepovinných
parametrů
4
Události objektů
• Provede se událost a poté se zavolá uživatelem
naprogramovaná procedura
• hlavičku nutno navolit, nelze jen napsat
– Editor VBA~v okně Project příslušný modul~MS Excel
Objects~ViewCode
• Objekt Workbook
–
–
–
–
Př.
Activate
Open
BeforeClose
BeforePrint
5
Objekty a jejich kolekce
• Kolekce je soubor objektů stejného typu
• Název kolekce je většinou shodný s názvem objektu + s
• Prvky z dané kolekce musím vybrat
• Možných je několik způsobů výběru objektů
– indexem
Worksheets(1).Name = "První list"
– jménem
Worksheets("List1").Name = "První list"
– pomocí metody Item – většinou se nepoužívá
Worksheets.Item(1).Name = "První list"
• Metoda Count
• Metoda AddItem
• cyklus For Each
Př.
6
Reference k objektům
Application.WorkBooks("Makra.xls"). _
WorkSheets("List1").Range("A1").Value = 10
• Objekt aplikace pokud pracujete v Excelu nemusíte používat
• pokud vynecháte objekt WorkBook, je chápáno, že se
odkazujete na aktivní sešit
WorkSheets("List1").Range("A1").Value = 10
nebo
ActiveWorkBook.WorkSheets("List1").Range("A1")._
Value = 10
• vlastnost ActiveWorkBook je vlastnost aplikace
7
Reference k objektům
pokud vynecháte objekt Worksheet, je chápáno, že se
odkazujete na aktivní list
Range("A1").Value = 10
nebo
ActiveSheet.Range("A1").Value = 10
• některé objekty mají určené defaultní vlastnosti
Range("A1") = 10
nebo
Range("A1").Value = 10
• odkaz na aktivní buňku nebo výběr
ActiveCell.Value = 10
8
Reference k objektům
• Pozor!
Bunka = Range("A1")
vs.
Set Bunka = Range("A1")
9
Prvky jazyka spojené s objekty
• Struktura With – End With
– zjednodušení odkazů na objekty
With <object>
<příkazy>
End With
• Proměnná typu Object
Dim Oblast As Object
Set Oblast = WorkSheets(1).Range("A1:B3")
10
Prvky jazyka spojené s objekty
• vynulování proměnné typu object – znamená přiřazení
hodnoty Nothing
• porovnání odkazů na objekty Is
If Oblast Is Nothing then ...
• místo identifikátoru Object můžete uvést konkrétní typ
Dim Oblast As Range
Set Oblast = WorkSheets(1).Range("A1:B3")
11
Objekt Application
• reprezentuje celou aplikaci Excelu
• Vlastnosti objektu
–
–
–
–
–
–
–
Caption – nadpis
Cursor – ukazatel myši
DisplayAlerts – potlačení zobrazení některých hlášení
DisplayFormulaBar – skrytí řádku vzorců
DisplayStatusBar – skrytí stavového řádku
OperatingSystem – vrací název a verzi operačního systému
StatusBar – vrací nebo nastavuje text na stavové řádce
StatusBar = "Počkej"
StatusBar = False
– WindowState – stav okna
– ScreenUpdating
– Workbooks
workbooks.xls
12
Objekt Application
• další vlastnosti objektu
–
–
–
–
–
ActiveWorkbook
ThisWorkbook
ActiveSheet
ActiveCell
Selection
13
Metody objektu Application
– Calculate – vyvolá přepočítání vzorců ve všech sešitech
– Dialogs – vrací objekt dialog vestavěných Dialogových oken
Excelu
Application.Dialogs(xlDialogOpen).Show
'zobrazí dialogové okno
Dialogy.xls
– GetOpenFileName
–
–
–
–
–
GetSaveAsFileName
InputBox
Quit – ukončení aplikace Excelu
Run – umožňuje spustit makro zapsané v libovolném jazyce
Wait – přeruší makro do času uvedeného jako parametr
14
Objekt WorkBook
• vlastnosti, které lze nastavit v SummaryInfo a Předvolbách
– Name – jméno sešitu
– Path – určuje cestu, kde je soubor uložen
– Saved – True – je–li soubor uložen
• Metody objektu WorkBook
–
–
–
–
–
–
–
Activate – aktivuje první okno sešitu
Close – uzavírá sešit – parametry
Protect – uzamčení sešitu – parametry
Sheets – kolekce listů sešitu
Worksheets – kolekce tabulkových listů sešitu
Save – Uloží sešit na disk – nepovinný parametr filename
UnProtect – odemkne daný sešit
15
Objekt WorkSheet
• objekt, který reprezentuje tabulky Excelu
• vlastnosti objektu
– Name – vrací nebo nastavuje jméno listu
– UsedRange – vrací objekt typu Range reprezentující vyplněnou
oblast listu
– Visible – skrytí listu, lze ho také nastavit tak, aby jeho zobrazení
bylo možné pouze makrem
Skryte.xls
16
Metody objektu WorkSheet
– Cells – vrací buňky jako objekt typu Range
.Cells(2,1)
– Range – oblast, která reprezentuje buňku nebo oblast buněk
.Range("A1:B3")
.Range("A1","B3")
.Range(.Cells(1,1),.Cells(3,2))
o jiné, násobná oblast
.Range("A1, B3")
– Columns, Rows – kolekce objektů typu Range
17
Další metody WorkSheet
–
–
–
–
–
–
Activate – aktivuje daný list
Calculate – přepočítává všechny buňky v listě
Copy – kopíruje list před nebo za uvedený list v sešitě
Delete – odstraní zadaný list ze sešitu
Protect –uzamkne, ochrání list
Unprotect – odemkne daný list
18
Objekt Range
• Tento objekt reprezentuje buňku nebo výběr buňek tabulky
v Excelu
• Vlastnosti objektu Range – práce s oblastmi
– CurrentRegion – vrací objekt Range aktuální oblasti, to je oblast
ohraničená prázdným řádkem a sloupcem
– Column – vrací číslo prvního sloupce v první oblasti
Range("C2:E4,A2:B5")' = 3
– EntireColumn, EntireRow – reprezentuje celý řádek, či sloupec ve
vybrané oblasti
– Row – vrací číslo prvního řádku v první oblasti
19
Vlastnosti Range
• Vlastnosti pracující s hodnotami a vzorci
– Formula – vrací nebo nastavuje vzorec – anglické názvy funkcí
– FormulaLocal – vrací nebo nastavuje vzorec – názvy funkcí dle Excelu a
oddělovače nastavené v ovládacím panelu
– Value – vrací nebo nastavuje hodnotu v buňce nebo v oblasti buněk
• Vlastnosti objektu Range
– ColumnWidth, Height, Hidden, RowHeight, Width
• Formát buňky
– Font, HorizontalAligment, Interior, NumberFormat, Orientation, Style,
VerticalAligment,
20
Metody objektu Range
–
–
–
–
–
–
–
–
Activate – aktivuje jednu buňku ve vybrané oblasti
Address – vrací adresu oblasti
AutoFill – vytváří řadu hodnot v oblasti buňek
AutoFit – přizpůsobí šířku sloupce
BorderAround – přidá rámeček
Borders – vrací kolekci Borders
Calculate – přepočítá buňky ve vybrané oblasti
Characters – vrací objekt typu characters, který umožňuje formátovat znaky
uvnitř řetězce
– Clear, ClearContents, ClearFormat
– Offset – posune oblast o daný počet řádků a sloupců
21
Další metody objektu Range
–
–
–
–
–
–
Copy – kopíruje oblast buněk
PasteSpecial – vloží obsah schránky na místo dané objektem
Columns – kolekce sloupců ve vybrané oblasti
Rows – kolekce řádků vybrané oblasti
Cells – kolekce buněk
Range
ActiveSheet.Columns(2).Range("A1")
'B1
– Select – označí oblast buněk tabulky
• používat s mírou
– Resize – změna rozsahu oblasti
Set r1 = Range("A1:B2").Resize(4,1)
Př.
'A1:A4
22
Metody Application pro Range
– Union – sjednocení oblastí
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
– Intersect – průnik oblastí
Set r1 = Intersect(ActiveCell.EntireColumn, _
Př.
ActiveSheet.UsedRange)
23
Objekt Font
• Objekt složí k nastavení písma, jeho velikosti a stylu
• Vlastnosti
–
–
–
–
–
–
–
Bold – tučné
Color – barva písma – fukce RGB
ColorIndex – vrací nebo nastavuje barvu písma z palety barev
FontStyle – vlastnosti jako Italic a Bold
Name – jméno fontu
Size – vrací nebo nastavuje velikost písma v bodech
Strikethrough, Subscript, SuperScript, UnderLine
24
Objekt Interior
• Objekt popisující vnitřek buněk
• Vlastnosti objektu
–
–
–
–
–
Color – barva výplně jako RGB
ColorIndex – brava z palety
Pattern – vrací nebo nastavuje vzorek výplně buňky
PatternColor – vrací nebo nastavuje barvu vzorku
PatternColorIndex – vrací nebo nastavuje číslo barvy z palety
barev
25
Objekt Border
• Objekt popisující okraje buněk
• Vlastnosti pobjektu
–
–
–
–
Color
ColorIndex
LineStyle – vrací nebo nastavuje styl čáry
Weight – nastavuje tloušťku čáry
26
Objekt Window
• objekt reprezentuje okno v Excelu
• Windows – kolekce, obsahuje všechna okna v aplikaci,
otevřené sešity, informační okna
• Workbooks(n) – obsahuje pouze okna daného sešitu
• Vlastnosti
–
–
–
–
Caption – nastavuje nebo vrací jméno okna
DisplayFormulas – zobrazuje vzorce v buňkách
DisplayGridlines – nastavuje zobrazení mřížky
DisplayHeadings – nastavuje, zda bude zobrazeno záhlaví řádků
27
Vlastnosti objektu Window
– DisplayWorkbookTabs – v listě se zobrazí záložky listů
– ScrollColumn – nastaví nebo zjistí sloupce, který je zobrazen u
levého okraje okna dokumentu
– ScrollRow – stejné jako u sloupce
– Height – vrací nebo nastavuje výšku okna v bodech
– Left – nastavuje nebo vrací polohu okna os levého okraje
využitelné oblasti
– Width, Top
– WindowState– mění stav okna
– Zoom – nastavuje nebo vrací zvětšení okna v procentech
28
Metody objektu Window
– Activate – sktivuje okno
– Close – uzavře okno
– SelectedSheets – vrací kolekci Sheets, která reprezentuje všechny
listy v sešitě
– LargeScroll – posunuje okno po strankách
– SmallScroll – posunuje obsah okna po jednotlivývh řádcích nebo
sloupcích
29