Vsi primeri makrov, ki smo jih zapisali v prejšnji številki Mojega mikra, so se izvajali od prve do zadnje vrstice. Vsak stavek se je izvedel natančno enkrat. Pogosto pa želimo, da se določeni stavki izvršijo večkrat, ali pa da se ob (ne)izpolnjenjem pogoju sploh ne izvršijo. V te namene uporabljamo ukaze za nadzor izvajanja. Z njimi nadzorujemo tok izvajanja programske kode. Delimo jih na odločitvene in zančne. Prvi na podlagi ovrednotenega izraza cepijo tok izvajanja, drugi pa skupino stavkov ponavljajo.

ODLOČITVE

Tipičen odločitveni ukaz je If, drugi, precej redkeje uporabljan, pa Select Case, ki ga bomo v tem članku kar izpustili. Predpostavimo, da je večini bralcev ukaz If poznam, zato njegovo sintakso poglejmo na hitro in preidimo k primeru.

If Pogoj Then
[stavki, če je pogoj izpolnjen]
[ElseIf pogoj-n Then
[alternativni_stavki]]
[Else
[privzeti_stavki]]
End If

Sub UcenjeIfa()
' Primerjamo vsebino celice A1 s trenutnim sistemskim datumom
If Range("A1").Value = Date Then
MsgBox ("Podatki se nanašajo na današnji dan.")
Else
MsgBox ("Podatki so zastareli.")
End If
End Sub

V makru UcenjeIfa smo primerjali vsebino celice s trenutnim sistemskim datumom. Uporabili smo funkcijo Date. Funkcija pri poznavalcih Excela, a začetnikih pri programiranju makrov, povzroči nekaj zmede, saj so v Excelu v te namene navajeni uporabljati funkcijo delovnega zvezka Today(). Če vtipkamo v poljubno celico =Today(), bomo ravno tako dobili trenutni sistemski datum v tej celici. A kljub temu funkcije Today pa pri pisanju makrov ne moremo uporabiti. Velja torej poudariti, da funkcije delovnega zvezka in funkcije v Visual Basic for Applications (v nadaljevanju VBA) niso vedno enake. Tudi njihov nabor je različen, nekatere so na voljo le v delovnih zvezkih, druge le pri makrih. Kadar funkcija ni na voljo v VBA, obstaja pa med funkcijami delovnega zvezka, jo lahko uporabimo tudi pri pisanju makrov prek posebnega objekta WorksheetFunction. V naslednjem primeru smo uporabili funkcijo delovnega zvezka CountA, ki prešteje neprazne celice v določenem območju. CountA ni samostojna funkcija VBA.

Sub PrestejNeprazne()
'Makro sporoči število nepraznih celic v stolpcu A
MsgBox (Application.WorksheetFunction.CountA(Range("A:A")))
End Sub

ZANKE

Vrnimo se k ukazom za nadzor izvajanja. Za ponavljanje bloka programskih stavkov uporabljamo zanke. Delimo jih na dve vrsti:
• Zanke z vnaprej določenim številom ponovitev (zanka For-Next ).
• Zanke, ki se izvajajo dokler velja pogoj (zanka Do-Loop ).

Zanke z vnaprej določenim številom ponovitev

Sintaksa zanke For-Next je naslednja.

For Stevec = PrvaVrednost To KoncnaVrednost [Step Korak]
[Stavki]
[Exit For]
[Stavki]
Next [Stevec]

Števec stevec je spremenljivka, ki teče od prve do končne vrednosti. Zanko zaključuje ukaz Next, ki po vsaki ponovitvi poveča števec praviloma za eno enoto. Če tega ne želimo, korak povečanja oziroma zmanjšanja določimo z ukazom Step. Znotraj zanke For-Next so programski stavki, ki se izvajajo, dokler spremenljivka stevec ne doseže končne vrednosti. Iz zanke pa lahko tudi predčasno izstopimo. Za predčasen izstop uporabimo ukaz Exit For.

Vzemimo za primer dejstvo, da so v območju celic "C1:C12" podatki o cenah zapisani še v slovenskih tolarjih. Naša naloga je, da jih z uporabo makra pretvorimo v evre. Ker je območje celic določeno, je uporaba zanke For - Next na mestu.

Sub UcenjeFor()
'V območju celic C1:C12 vrednosti celic delimo
Dim i As Integer
For i = 1 To 12
Cells(i, 3) = Cells(i, 3) / 239.64
Next i
End Sub

Pozornemu bralcu je morda uspelo v makru UcenjeFor zaznati dve podrobnosti. Vrednosti celicam smo dodelili »po domače««. Izpustili smo lastnost Value. Stavek, ukleščen v For-Next zanko, bi se pravilneje glasil: Cells(i,3).Value = Cells(i,3).Value / 239.64. VBA je v opisanem primeru do programerja »prijazen« in zna ustrezno interpretirati njegov namen. Drugo podrobnost (govor je o številu 239,64) smo omenili že v prejšnji številki. Zapisali smo, da krajevne nastavitve osebnega računalnika pri pisanju makrov nimajo vpliva na ločilo pri zapisu decimalnih mest. Vedno uporabljamo piko.

Namen članka je osvetliti posebnosti programiranja za Excel, zato bomo malce več časa namenili zanki For Each– Next, ki je posebnost, zaradi Excelovih zbirk objektov, ki nastopajo v zanki. Sintaksa zanke se glasi:

For Each Element In ZbirkaObjetkov
[stavki]
[Exit For]
[stavki]
Next [Element]

Spomnimo na definicijo, da zbirke objektov združujejo objekte istega tipa. Tako na primer zbirka objektov WorkSheets (delovni listi) združuje vse delovne liste – objekte WorkSheet. V zanki For Each-Next se spremenljivki Element prirejajo člani zbirke objektov, od prvega do zadnjega člana. V makru UcenjeForEach1 se spremenljivki List priredijo vsi delovni listi – člani zbirke Worksheets. Omenjeni makro na priročen način natisne vse delovne liste (znotraj delovnega zvezka), ob tem pa nam ni treba vedeti, koliko delovnih listov je in kako se imenujejo. Slednje je pomembno tudi pri uporabi makra, saj makro deluje tudi, če delovne liste dodajamo, odvzemamo ali preimenujemo.

Sub UcenjeForEach1()
'Makro natisne vse delovne liste
Dim List As Worksheet
For Each List In Worksheets
List.PrintOut
Next
End Sub

Vrnimo se k prejšnjemu primeru, kjer smo imeli v območju celic "C1:C12" zneske zapisane še v slovenskih tolarjih in smo jih z makrom pretvorili v evre. Makro UcenjeFor, v katerem smo uporabili klasično zanko For-Next, pretvorbo valut vedno opravi na istem območju celic. Če želimo območje spremeniti, moramo poseči v kodo makra. Slednje je za nevešče uporabnike zelo neprikladno. V nadaljevanju predstavljena rešitev je pri uporabnikih zelo dobrodošla, saj predstavlja način, kjer neko spremembo opravimo na poljubnem območju celic, ki ga označimo pred zagonom makra.

Sub UcenjeForEach2()
Dim celica As Cell
For Each celica In Selection
celica.Value = celica.Value / 239.64
Next
End Sub

Selection (izbrano) je posebna lastnost objekta Application. V kombinaciji z zanko For Each–Next je izredno uporabna, saj omogoča, da nad poljubnimi objekti, ki so izbrani ob zagonu makra, izvedemo neko operacijo.

Zanka For Each–Next omogoča napisati makro, kjer pretvorbo opravimo na poljubnem (označenem) območju.

Zanke, ki se izvaja dokler velja pogoj
Predstavimo še zanke iz druge skupine zank. Pri njih velja takoj poudariti, da mora imeti jedro zanke vpliv na pogoj, sicer se zanka zavrti v »neskončnost« in Excel nam sporoči napako.

Zanka Do - Loop ima dve različici. Prva različica je naslednja:

Do [While Pogoj]
[stavki]
[Exit Do]
[stavki]
Loop

In druga:

Do
[stavki]
[Exit Do]
[stavki]
Loop [While Pogoj]

Razlika med različicama je v tem, da v prvem primeru obstaja možnost, da se stavki znotraj zanke sploh ne izvršijo, v drugem pa se zagotovo izvršijo vsaj enkrat. Zanka v prvem primeru najprej preveri pogoj, ki je zapisan za ukazom While, nato vse do ukaza Loop sledijo programski stavki (jedro zanke), ki se ponavljajo znotraj zanke. V drugem primeru pa se pogoj preveri šele po prvi ponovitvi programskih stavkov, ukleščenih v zanko Do-Loop. Obe zanki lahko takoj zapustimo z ukazom Exit Do. V praksi je pogosteje uporabljena prva različica.

Poglejmo si še drugi primer zanke iz skupine zank, ki se izvajajo dokler velja pogoj. Zanka While-Wend je enostavnejša:

While Pogoj
[stavki]
Wend

Programski stavki se izvajajo dokler velja pogoj, zapisan za ukazom While. Zanka While-Wend nima možnosti predčasnega zaključka. V njej tako ne moremo uporabiti ukaza Exit, ki bi nam omogočal takojšnji izhod iz zanke. Naj na tem mestu še omenimo, da lahko z ukazom Exit predčasno prekinemo tudi izvajanje makra. Uporabimo Exit Sub, če je makro procedura, oziroma Exit Function, če imamo opravka s funkcijo.

Ostajamo pri primeru pretvorbe zneskov iz slovenskih tolarjev v evre. Uporabili bomo zanko While-Wend. Posebnost te rešitve, v primerjavi z rešitvijo z uporabo zanko For-Next, je v tem, da nam ni treba v celoti poznati območja celic, v katerih so tolarski zneski, saj zanka pretvori vse zneske v celicah, dokler ne naleti na prazno celico. V makru UcenjeWhileWend se najprej postavimo v celico C1 (Cells(1,3)) in se nato z zanko While-Wend prestavljamo v celice C2, C3, C4 ..., dokler je izpolnjen pogoj, da je vrednost celice različna od prazne (Cells(i, 3).Value <> "").

Sub UcenjeWhileWend()
'Zanka se izvaja, dokler je vrednost celice različna od prazne
Dim i As Integer
i = 1
While Cells(i, 3).Value <> ""
Cells(i, 3).Value = Cells(i, 3).Value / 239.64
i = i + 1
Wend
End Sub

Stavek i = i + 1 je stavek, na katerega smo opozorili pred nekaj odstavki. Gre za stavek, ki ima vpliv na pogoj. S tem, ko se spremenljivka i spremeni, se spremeni tudi celica, ki jo pogoj vrednoti. Makro ima pomanjkljivost v tem, da se ustavi na prvi prazni celici. Kadar želimo opraviti pretvorbo na območju, ki vsebuje tudi prazne celice, je makro nezadosten. Rešitev bomo spoznali ob koncu članka.

Oglejmo si naslednji primer, ki je v praksi pogost in ga prikazuje spodnja slika. Želimo napisati makro, ki nam bo seštel podatke. Zaplet je v dejstvu, da število podatkov ni stalno, temveč se spreminja. Denimo, da imamo v stolpcu B podatke o stanju neke postavke za vsak dan v aktualnem mesecu. Prvi dan v mesecu imamo zgolj en podatek, ki je zapisan v celici B3, desetega v mesecu je podatkov deset in so zapisani v celicah B3:B12, 15. v mesecu je podatkov 15 (v celicah B3:B17) in tako naprej. Makro nam mora zapisati funkcijo Sum, ki sešteje podatke aktualnega meseca. Prvega v mesecu mora biti funkcija Sum v celici B4 in se glasiti Sum(B3), desetega v mesecu mora biti celici B13 in se glasiti Sum(B3:B12), 15. pa v celici B18 in se glasiti Sum(B3:B17) ter tako naprej. Ob nestalnem število podatkov moramo najprej rešiti problem, kam formulo zapisati, nato pa še pravilno določiti območje njenega delovanja.

V zadnjem makru se je zanka izvajala, dokler so bile celice polne. Uporabimo torej zanko While-Wend. Z njo se premikamo po stolpcu B (označuje ga število 2), dokler ne naletimo na prvo prazno celico. Podatek o vrstici prve prazne celice vsebuje spremenljivka i. Zapišimo Cells(i, 2).Value = "Tukaj bo formula". Makro preizkusimo.

Sub KamSFormulo()
Dim i As Integer
i = 3
While Cells(i, 2) <> "" 'sprehajmo se po stolpcu B, dokler ne naletimo
i = i + 1 'na prazno celico
Wend
Cells(i, 2).Value = "Tukaj bo formula"
End Sub

Besedilo "Tukaj bo formula" se zapiše v celico B13 – delamo na primeru desetega v mesecu. Prvi del naloge (kam funkcijo Sum zapisati) smo rešili. V nadaljevanju bo prikazana še veliko prikladnejša rešitev. Posvetimo se drugemu delu: določitvi območja delovanja celice. Za začetek predzadnjo vrstico makra spremenimo v Cells(i, 2).Value = "=Sum(B3:B12)". Iz primera je razvidno, da funkcijo zapišemo v celico na enak način kot besedilo. Uporabimo lastnost Value. Pravzaprav zapišemo besedilo, in ko je to zapisano v celico, Excel, zaradi enačaja na začetku, ugotovi, da gre za funkcijo. Ob samodejnem snemanju makrov (o tem bomo govorili v naslednji številki Mojega mikra) Excel za zapisovanje formul ne uporablja lastnosti Value, temveč lastnost FormulaR1C1. Ker je rezultat enak, se raje držimo že znane lastnosti Value.

V prejšnji številki smo spoznali funkcijo &, s katero združujemo nize znakov. Niz znakov "=Sum(B3:B12)", z uporabo funkcije & zapišemo kot "=Sum(B3:B" & "12" & ")". Rezultat je enak, a pripravili smo si teren za končno rešitev. Funkcija Sum mora sešteti celice do zadnje neprazne celice, to je do vrstice i-1 (v stolpcu B). Niz znakov "12" torej zamenjamo z i-1. Rešitev primera je tako makro KamSFormulo(), kjer predzadnjo vrstico zamenjamo z Cells(i, 2).Value = "=Sum(B1:B" & i-1 & ")". Poudarimo še, da smo združili besedilo "=Sum(B1:B", število i-1 in besedilo ")", Excel pa je samodejno izvršil konverzijo števila i-1 v besedilo. O konverzijah smo pisali v prejšnji številki.

V makru KamSFormulo() ima opisano iskanje ustreznega mesta, kjer se končajo podatki in kamor naj se zapiše vsota podatkov, zoprno pomanjkljivost v tem, da poišče prvo prazno celico. Če se tako med podatki pojavi prazna celica, se funkcija Sum zapiše vanjo, ne pa na konec stolpca s podatki. Najprikladnejši način, kako določiti zadnjo vrstico s podatki, ki ni občutljiv na morebitne prazne celice se glasi: zadnja_ vrstica = Cells(Rows.Count, 2).End(xlUp).Row. Sledi razlaga.

Zadnjo neprazno celico določimo tako, da se najprej postavimo v zadnjo celico stolpca B, kar dosežemo s Cells(Rows.Count, 2). Naj ne bo odveč opozorilo, da bo Excel 2007 vseboval več kot milijon (1.048.576) vrstic in ne več zgolj 65.536, zato je smelejše zapisati Rows.Count (ki prešteje vrstice), kot pa Cells(65536, 2).

End je metoda, ki deluje kot kombinacija tip Ctrl+Shift+smerna tipka pri običajnem delu z Excelom. Če smo v polni celici, kombinacija tipk označi področje polnih celic v smeri, ki jo določimo s smerno tipko. Kadar pa smo v prazni celici, nam kombinacija označi področje vključno s prvo polno celico. Slednje smo uporabili v našem primeru. Postavili smo se na konec (v prazno celico) in z metodo End(xlUp) določili območje od zadnje celice do prve polne celice v stolpcu B. V našem konkretnem primeru (ker delamo z Excelom XP) smo določili območje "B12:B65536". Metoda End se od doslej spoznanih metod razlikuje v tem, da vsebuje argument (ki ga določa konstanta), s katerim določimo smer (xlDown, xlLeft, xlRight). Konstante, ki nastopajo kot argumenti v metodah, prepoznamo po predponi xl.

Zdaj uporabimo lastnost Row. Lastnost Row, vrne število, ki označuje vrstico izbrane celice, v primeru območja celic, pa prvo vrstico območja. Ker smo v prejšnjem odstavku označili območje "B12:B65536", nam bo lastnost Row vrnila število 12.

Prikladna rešitev našega primera je tako:

Sub KamSFormulo2()
zadnja_vrstica = Cells(Rows.Count, 2).End(xlUp).Row
Cells(zadnja_vrstica + 1, 2).Value = "=Sum(B3:B" & zadnja_vrstica & ")"
End Sub

Naš primer je razmeroma enostaven, saj je bila pri določitvi območja neznanka vrstica in ne stolpec. Stolpec je namreč, če je označen s črko, težje določljiv. Čas je, da spoznamo R1C1 notacijo.

NOTACIJA R1C1

Excel praviloma uporablja t.i. notacijo A1 , kjer so stolpci označeni s črkami, vrstice pa s številkami. Poznamo pa tudi notacijo R1C1 , kjer je npr. celica A1 označena kot R1C1, celica B3 pa R3C2. Notacija R1C1 je včasih težje razumljiva in težje berljiva, a se ji pri pisanju makrov ne moremo izogniti. Naj bralca opozorimo še na dejstvo, da Excel pri samodejnem snemanju makrov vedno uporabi notacijo R1C1 , torej tudi v primerih, ko bi zadoščala notacija A1 .

Bistvo uporabe R1C1 notacije je, da izhajamo iz celice, kjer jo uporabimo. Dodatno zmedo pa vnaša pravilo, da najprej označimo vrstico in nato stolpec, kar je ravno obratno kot pri notaciji A1 . Sintaksa notacije je R[x]C[y], kjer število x pomeni odmik x vrstic od aktivne celice in število y odmik y stolpcev od aktivne celice. Za ponazoritev: v celicah B1 in C1 želimo zapisati formulo =A1+200. Pri notaciji A1 zapišemo v obe celici isto formulo (=A1+200), pri notaciji R1C1 pa ne. Kot rečeno, pri tej notaciji izhajamo iz celice, kjer formulo uporabimo. Tako v celico B1 zapišemo =RC[-1]+200, v celico C1 pa =RC[-2]+200. Naj ne bo odveč še opozorilo, da lahko v prejšnjem stavku zapisano uporabite v delovnem zvezku le, če imate omogočeno notacijo R1C1 (Orodja\Možnosti, zavihek Splošno, kljukica v polje Slog sklicevanja R1C1). V makrih ta nastavitev ni potrebna.

2/3

Samo Rubin