FAKULTETA ZA MATEMATIKO IN FIZIKO Ljubljana

SNEMANJE MAKROV V EXCELU 2007 Seminarska naloga

Mentor: prof. dr. Tomaţ PISANSKI

Avtor: Matjaţ KRIŢANC Praktična matematika

Radomlje, april 2010

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

2

KAZALO 1

UVOD ............................................................................................................................ 3

2

KAJ JE MAKRO............................................................................................................ 3

3

2.1

KAJ JE FUNKCIJA ................................................................................................ 3

2.2

KAJ JE MODUL .................................................................................................... 3

KAKO POSNAMEMO MAKRO .................................................................................. 3 3.1

SNEMANJE MAKRA ............................................................................................ 4

3.2

KAKO ZAŢENEMO MAKRO .............................................................................. 6

3.2.1 4

VGRADITEV MAKRA V ORODNO VRSTICO ZA HITRI DOSTOP ....... 6

PRIMERI........................................................................................................................ 8 4.1

OBLIKA DATUMA ............................................................................................... 8

4.2

POSEBNO LEPLENJE VREDNOST .................................................................... 8

4.3

TISOČICE .............................................................................................................. 9

4.4

Z GESLOM ZAŠČITI DELOVNI LIST ................................................................ 9

4.5

ODŠČITI Z GESLOM ZAŠČITEN DELOVNI LIST ......................................... 10

5

ZAKLJUČEK ............................................................................................................... 11

6

VIRI IN LITERATURA .............................................................................................. 12

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

1

3

UVOD

Glede na to, da se matematiki na svojih delovnih mestih dostikrat ukvarjajo z raznimi analizami in statistikami in da je v večini podjetij osnovno orodje za te naloge še vedno Excel. Na fakulteti smo se naučili nekaj osnov Excela, nekaj čisto matematičnih oziroma statističnih funkcij, rešili nekaj nalog za katere Excel ni najbolj primerno orodje,… Glede na to, da sem po naravi bolj praktik in da nerad opravljam ponavljajoče (enake) korake v nedogled, si za prvo reševanje določenega problema rajši vzamem malo več časa in postopek reševanja poizkušam čim bolj avtomatizirati oziroma poenostaviti. Zato sem se odločil, da vam bom v tej seminarski nalogi poizkušal predstaviti malo bolj praktično uporabo Excela 2007. Razloţil in pokazal vam bom, kako se v Excelu 2007 posname makro. V prvem delu je razlaga pojma makra ter njegova uporaba. Sledijo napotki kako posnamemo makro. V zadnjem delu pa so prikazani praktični primeru uporabe makrov in njihova koda. 2

KAJ JE MAKRO

Pri vsakodnevni uporabi Excela se nam pogostokrat zgodi, da uporabljamo isto zaporedje ponavljajočih se ukazov oziroma da moramo določeno opravilo večkrat ponoviti. Excel nam nudi odlično moţnost, da si takšna opravila lahko shranimo in tako z avtomatizacijo teh postopkov oziroma opravil prihranimo veliko časa in energije. To storimo tako, da posnamemo makro. Makro je niz računalniških navodil (ukazov in funkcij), zapisan v programskem jeziku Visual basic za aplikacije (VBA), ki so shranjene v modulu Microsoft Visual Basica (v makro datoteki) in se lahko izvedejo kadarkoli ţelimo izvesti določeno opravilo. 2.1

KAJ JE FUNKCIJA

Funkcija je vnaprej sestavljena formula, ki sprejme vrednosti, izvede operacijo in vrne vrednosti. Funkcije se uporabljajo za poenostavljanje in skrajševanje formul na delovnem listu, še posebej tistih, ki izvajajo dolge ali zapletene izračune. 2.2

KAJ JE MODUL

Modul je zbirka deklaracij, izjav in procedur shranjenih v datotečni enoti. Obstajata dve vrsti modulov: standardni in razredni. 3

KAKO POSNAMEMO MAKRO

V Excelu enostavne makre lahko posnamemo (»naklikamo«), bolj zahtevne makre pa ustvarimo z uporabo Microsoft Visual Basica.

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

3.1

4

SNEMANJE MAKRA

V tem poglavju vam bom predstavil kako dejansko posnamemo makro (korak za korakom). V Excelu imamo dve moţnosti kako lahko dostopamo do menija za snemanje makrov:  Na kartici View (Pregled), na skrajni desni strani traku (Ribbon) v skupini Macros (Makri).



Na kartici Developer (Razvijalec), na skrajni levi strani traku (Ribbon) v skupini Code (Koda).

Če jeziček Developer (Razvijalec) ni na voljo in ga ţelimo prikazati, to naredimo tako, da kliknemo Gumb »Microsoft office« in nato Excel Options (Excelove moţnosti). V kategoriji Popular (Priljubljeno) pod Top options for working with excel (Najboljše moţnosti za delo z Excelom) potrdimo potrditveno polje Show Developer tab in the Ribbon (V traku prikaţi jeziček Razvijalec) in nato kliknimo V redu. Pred snemanjem makra se moramo zavedati, da se vsak klik z miško, premik s smerniško tipko zapisuje v datoteko makra (krmarjenje po traku ni vključeno v posnete korake). Zato moramo biti med snemanjem previdni pri uporabi miške in tipkovnice.

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

5

V nadaljevanju vam bom opisal, kako posnamemo makro, kot sem navedel v prvi alineji.  Na kartici View (Pregled) v skupini Macros (Makri) kliknemo na jeziček Record macro (Snemanje makra). Odpre se nam spodnje okence.



V polje Macro name (Ime makra) vnesemo ime za makro.

Paziti moramo, da je prvi znak v imenu makra črka. Znaki, ki sledijo so lahko črke, številke ali podčrtaji. V imenu makra ni mogoče uporabiti presledkov, vendar lahko besede ločite s podčrtaji. 



   

V polje Shortcut key (Bliţnjična tipka) vnesemo katerokoli malo ali veliko črko, ki jo ţelimo uporabiti kot bliţnjico za priklic makra (nekatere kombinacije tipk so ţe zasedene). Če izberemo malo črko, potem je bliţnjica za zagon makra Ctrl + izbrana črka, če pa izberemo veliko črko, potem pa je bliţnjica za zagon makra Ctrl + Shift + izbrana črka. Ta korak ni obvezen je pa priporočljiv, ker si s tem olajšamo uporabo makrov. Na seznamu Store macro in (Shrani makro v) izberemo delovni zvezek, v katerega ţelimo shraniti makro. Priporočljivo je, da makro shranimo v Personal Macro Workbook (Osebni delovni zvezek z makri), ker nam bo posneti makro v Excelu na voljo v vseh delovnih zvezkih. Če skriti Osebni delovni zvezek z makri še ne obstaja, ga bo Excel ustvaril sam. V polje Description (Opis) vnesemo opis makra (vnos ni obvezen). Če ţelimo začeti snemanje makra, kliknemo OK (V redu). Nato izvedemo niz ukazov, ki jih ţelimo posneti. Na kartici View (Pregled) v skupini Macros (Makri) kliknemo na jeziček Stop Recording (Ustavi snemanje). Snemanje lahko ustavimo s klikom na ikono , ki se nahaja na levi strani vrstice stanja.

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

6

KAKO ZAŢENEMO MAKRO

3.2

Makro prikličemo na tri načine:  S kombinacijo dveh oziroma treh tipk (Ctrl + izbrana črka, če pri snemanju makra v okence za bliţnjico vpišemo malo črko ali pa Ctrl + Shift + izbrana črka, če pri snemanju makra v okence za bliţnjico vpišemo veliko črko).  Lahko ga izberem na seznamu vseh makrov in ga zaţenemo (View/Macros/View macros/izberemo ţelen makro/run oziroma Developer/Macros/View macros/izberemo ţelen makro/run).  Za enostavnejšo uporabo pa si lahko za priklic makra naredimo bliţnjico v orodni vrstici za hitri dostop. 3.2.1 VGRADITEV MAKRA V ORODNO VRSTICO ZA HITRI DOSTOP 

Če ţelimo makro vgraditi v Quick Acces toolbar (orodno vrstico za hitri dostop), kliknemo na Gumb »Microsoft office« in nato Excel Options (Excelove moţnosti) izberemo kategorijo Customize.

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

7



Odpre se nam zavihek Customize the Quick Acess Toolbar. V levem meniju Choose comands from iz liste izberemo podmeni macros in nato v spodnjem seznamu poiščemo naš nov makro ter kliknemo gumb Add (Dodaj).



V desni meni se nam prenese naš makro, s klikom na gumb Modify mu lahko priredimo tudi novo ikono. S klikom na OK (V redu) potrdimo naš izbor in ikona za naš makro se nam vgradi v Quick Acces toolbar (orodno vrstico za hitri dostop).



KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

4 4.1

8

PRIMERI OBLIKA DATUMA

Z makrom Oblika_datuma lahko vsak datumski format spremenil v obliko mm.dd.yyyy. Učinek tega makra je le vizualen. VBA koda Sub Oblika_datuma() ' ' Oblika_datuma Macro ' Datumski zapis spremenimo v formar dd.mm.yyyy. ' ' Keyboard Shortcut: Ctrl+d ' Selection.NumberFormat = "dd/mm/yyyy" End Sub 4.2

POSEBNO LEPLENJE VREDNOST

Z makrom Posebno_lepljenje_vrednost »povozimo« vsako formulo in vsako povezavo z vrednostjo le te. S tem prekinemo vse povezave v druge delovne zvezke in druge delovne liste (istega delovnega zvezka). Formulam pa priredimo vrednost. S tem postopkom zmanjšamo velikost datoteke, končnemu uporabniku pa onemogočimo, da bi s svojo nespretnostjo prekinil povezave v datoteki (glede na to, da nima osnovne datoteke s podatki). VBA koda Sub Posebno_lepljenje_vrednost() ' ' Posebno_lepljenje_vrednost Macro ' Vse formule in povezave prepišemo z vrednostmi le teh (Posebno lepljenje/vrednost) ' ' Keyboard Shortcut: Ctrl+y ' Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

4.3

9

TISOČICE

Z makrom Tisočice spremenimo obliko celice, ki vsebuje številke, tako da se nam izpišejo tisočice in da se nam število prikaţe brez decimalnih mest (vrednost števila je še vedno enaka, spemeni se le prikaz). VBA koda Sub Tisočice() ' ' Tisočice Macro ' Format celice 1000000.00 se spremeni v 1.000.000 ' ' Keyboard Shortcut: Ctrl+e ' Selection.NumberFormat = "#,##0" End Sub 4.4

Z GESLOM ZAŠČITI DELOVNI LIST

Z makrom Z_geslom_zaščiti_delovni_list z geslom ki smo si ga izbrali (to geslo lahko v urejevalniku za Microsoft Visual Basic poljubno spreminjamo) zaščitimo posamezne delovne liste, Tega makra ne moremo posneti samo s klikanjem (ročno moramo popraviti kodo). Makro je uporaben takrat, ko moramo z istim geslom zaščititi večje število delovnih listov. VBA koda Sub Z_geslom_zaščiti_delovni_list() ' ' Z_geslom_zaščiti_delovni_list Macro ' Z geslom "Matematika13" zaklenemo izbran delovni list. ' ' Keyboard Shortcut: Ctrl+g ' ActiveSheet.Protect password:="Matematika13" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

4.5

10

ODŠČITI Z GESLOM ZAŠČITEN DELOVNI LIST

Z makrom Odščiti_z_geslom_zaščiten_delovni_list prekličemo zaščito posameznim delovnim listom, z geslom, katerega smo določili v prejšnjem poglavju (to geslo lahko v urejevalniku za Microsoft Visual Basic poljubno spreminjamo). Tega makra ne moremo posneti samo s klikanjem (ročno moramo popraviti kodo). Makro je uporaben takrat, ko moramo preklicati zaščito več delovnim listom, ki so zaščiteni z istim geslom. VBA koda Sub Odščiti_z_geslom_zaščiten_delovni_list() ' ' Odščiti_z_geslom_zaščiten_list Macro ' S pomočjo makra prekličemo zaščito tistim listom, ki so zaklenjeni z geslom "Matematika13" ' ' Keyboard Shortcut: Ctrl+h ' ActiveSheet.Unprotect password:="Matematika13" ActiveSheet.Unprotect End Sub Kako bi pa zaščitili oziroma preklicali zaščito vsem delovnim listom v posameznem delovnem zvezku si pa lahko poiščete na internetu (na povezavah, ki sem jih priloţil na koncu seminarske naloge ali pa s pomočjo Googla).

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

5

11

ZAKLJUČEK

V seminarski nalogi sem vam na kratko predstavil le del praktične uporabnosti Excela. Excel ima zelo veliko moţnosti uporabe in vsak posameznik si ga priredi (prilagodi) po svojem okusu. S pomočjo makrov sem si pri svojem delu precej olajšal in pospešil določene dolgočasne postopke, ki se nenehno ponavljajo. Nekateri postopki imajo le vizualni učinek, drugi pa so tudi malo bolj zapleteni in zahtevni. Če koga to področje malo bolj zanima, mu predlagam, da si za začetek ogleda spodnje internetne povezave, kasneje pa lahko v roke vzame tudi kakšno knjigo o VBA programiranju (npr. Excel 2007 VBA Programming for Dummies by John Walkenbac, Microsoft Office Excel 2007 Visual Basic for Applications Step by Step by Reed Jacobson…) Za tiste, ki jim programiranje ne leţi preveč, pa priporočam, da si na računalnik naloţijo kakšno poskusno verzijo Excelovih dodatkov (npr. ASAP Utilities, Power Utility Pak v7,…).

KRIŢANC, Matjaţ, Snemanje makrov v Excelu 2007

6

VIRI IN LITERATURA

1. WALKENBACH, John, 2007. Microsoft® Office Excel® 2007 Bible 2. Elektronski viri (povezave na spletne strani): 2.1. http://office.microsoft.com/sl-si/excel/HP100141111060.aspx 2.2. http://office.microsoft.com/en-us/excel/CH101001571033.aspx 2.3. http://spreadsheets.about.com/od/advancedexcel/ss/080703macro2007.htm 2.4. http://www.mrexcel.com/articles.shtml

12