VisualBasic - ohjelmointi - kurssiII

[S.Mäenala]

3. Ohjelmoinnin perusteet

Mallisovellus
- Avaa Excel, siirry VBA:han (Alt-F11) ja lisää moduli (Insert --> Module)
- Siirrä Module1:n koodi-ikkuna tyhjän alueen vasempaan yläkulmaan ja säädä ikkunan koko itsellesi sopivaksi.
- Kopioi Module1:n koodiksi seuraava teksti:

<TABLE BORDER="0" cellspacing="0"><TR><TD>

Koodi: Valitse kaikki

Option Explicit

Sub Esimerkki()

'======== Muuttujamäärittelyt                              

'======== Ohjelmakomennot

'======== Virhekäsittelijä

End Sub

</TD></TR></TABLE>
- Talleta työkirja Kurssi -kansioon nimellä Template.xlsm (tai jos haluat Malli.xlsm)
- Mallityökirja on helppo avata Recent -valikosta.




3.1 If .. ElseIf .. Else -lause

If -lausetta käytetään valitsemaan suoritettavat komennot. Sen yksinkertaisin muoto on seuraava:

If ehto Then
[komennot]
End If

Koodin tulkinta:
- jos ehto = Tosi, komennot suoritetaan,
- muussa tapauksessa komentoja ei suoriteta vaan siirrytään End If -lauseen jälkeiselle riville.

Laajemmassa muodossa If -lauseen syntaksi kuuluu näin:

If ehto1 Then
[suorita nämä komennot]
ElseIf ehto2 Then
[suorita nämä komennot]
Else
[suorita nämä komennot]
End If

- kun yksi ehdoista toteutuu ja sitä vastaavat komennot on suoritettu, If -lauseen suoritus päättyy.
- ElseIf -lausetta ei aina tarvita, toisinaan niitä tarvitaan useampia,
- Else -lausetta ei aina tarvita, mutta hyvä ohjelmointikäytäntö on ottaa se mukaan yllättävien tapausten varalta.
- Yleisin koodaus -bugi on että End If -lause unohdetaan kirjoittaa...

Harjoitus 3-1-1

Avaa Template ja kirjoita seuraava koodi (rivikommentteja ei tarvitse kirjoittaa):
[myöhemmin kirjoitan koodit kopioitavaan muotoon mutta aluksi kun komentojen muotoja harjoitellaan, ne jäävät helpommin mieleen kun ne kirjoittaa itse.]



- 1. ohjelmarivillä kokonl -muuttuja saa arvokseen tämänhetkisen kuukaudenpäivän. Tätä postitettaessa sen arvoksi tuli 20.
- If -lauseella valitaan arvo, joka teksti -muuttujalle annetaan.

kokonl = 20 --> ehto1 ei ole Tosi --> siirrytään ehto2:een --> ehto2 toteutuu --> tekstille annetaan arvo "Olemme kuun keskivaiheilla." --> siirrytään End If -lauseen jälkeiselle riville.

Huom!
Muuttuja teksti sai tässä tietyn arvon mutta koska teksti on lokaali muuttuja sen arvo ei säily.
Sen elinkaari päättyy heti makron suorittamisen jälkeen. Tällöin sen arvo on määrittelemätön (N/A ~Not Accessible ~ei käytettävissä)

Laita koodiin seuraava pieni lisäys, joka hyödyntää sisäistä funktiota nimeltä MsgBox.



Näitä ohjelmoinnin perusharjoitksia ei välttämättä tarvitse tallentaa.

Tehtävä 3-1-1

Avaa Template.
Kirjoita yo. harjoituksen mukaisesti koodi, joka tulostaa
- "Elämme vasta alkuviikkoa.."
- "Olemme jo loppuviikolla!"
- "Tänään on Sunnuntai."
Tilanteen mukaan tietysti!

Tehtävä 3-1-2

Avaa Template ja kopioi seuraava taulukko exceliin solusta A2 alkaen.

<TABLE BORDER="1" cellspacing="0"><TR><TD>K. Abbott</TD><TD>Weybridge</TD><TD>22.03.1975
</TD></TR><TR><TD>E. Skinner</TD><TD>London</TD><TD>15.04.1998
</TD></TR><TR><TD>J. Doyle</TD><TD>Ashford</TD><TD>26.12.1997
</TD></TR><TR><TD>G. Naylor</TD><TD>London</TD><TD>29.05.1976
</TD></TR><TR><TD>M. Mellor</TD><TD>Brackley</TD><TD>03.01.1993
</TD></TR><TR><TD>J. Gordon</TD><TD>Liverpool</TD><TD>20.06.1980
</TD></TR><TR><TD>C. O'Brien</TD><TD>Bishop's Cleeve</TD><TD>17.09.1968
</TD></TR><TR><TD>L. Kennedy</TD><TD>Sutton</TD><TD>29.07.1986
</TD></TR><TR><TD>M. Davison</TD><TD>London</TD><TD>18.01.1979
</TD></TR><TR><TD>L. Warner</TD><TD>Charlbury</TD><TD>21.02.1973
</TD></TR><TR><TD>F. Bell</TD><TD>Portslade</TD><TD>09.08.1958
</TD></TR><TR><TD>J. Gilbert</TD><TD>London</TD><TD>07.11.1982
</TD></TR><TR><TD>E. Ellis</TD><TD>London</TD><TD>04.01.2000
</TD></TR><TR><TD>D. Barnes</TD><TD>Cholsey</TD><TD>01.05.1956
</TD></TR><TR><TD>C. Chandler</TD><TD>London</TD><TD>28.06.1982
</TD></TR><TR><TD>K. Swift</TD><TD>Warland</TD><TD>01.04.1989
</TD></TR><TR><TD>B. Harrison</TD><TD>Newton</TD><TD>27.01.1966
</TD></TR><TR><TD>B. Dobson</TD><TD>Titsey</TD><TD>15.07.1976
</TD></TR><TR><TD>G. Pugh</TD><TD>Nettlerbridge</TD><TD>17.10.1999
</TD></TR><TR><TD>E. Gibbons</TD><TD>Buxworth</TD><TD>22.06.1955
</TD></TR><TR><TD>L. Nicholson</TD><TD>Fulletby</TD><TD>29.10.1960
</TD></TR><TR><TD>K. Mill</TD><TD>Southampton</TD><TD>12.12.1963
</TD></TR><TR><TD></TD></TR></TABLE>
Kirjoita koodi, joka
- otsikoi (1. riville) taulukon sarakkeet, D-sarakkeen otsikoksi tulee Viiteryhmä.
- kirjoittaa D-sarakkeeseen jokaisen kansalaisen viiteryhmän
-- "junior", jos hänen ikänsä on alle 18 vuotta,
-- "senior", jos hänen ikänsä on yli 60 vuotta,
-- "medior", jos hänen ikänsä on yo. rajojen välissä.

-

[S.Mäenala]

Vihje:
Samalle riville voidaan kirjoittaa 2 tai useampia komentoja erottamalla ne kaksoispisteellä.



Laaja yksimielisyys vallitsee siitä että koodi on helppolukuisempaa jos kaikki komennot
ovat omilla riveillään.

Koodin optimoinnista suurissa toistorakenteissa:

Koodin suoritusnopeuteen vaikuttavista tekijöistä merkittävimpiä ovat

a) Muuttujien tiukka määrittely (Option Explicit)
- ilman tätä direktiiviä kaikki muuttujat määritellään Variant -tyyppisiksi.
Suurissa rakenteissa tämän on todettu kasvattavan koodin suoritusajan 3 - 4 -kertaisiksi.
Toinen haitta on suuri muistin hukkakulutus.

b) Objektimuuttujien käyttö
- Niitä käytettäessä viittausketjut lyhenevät

c) Viittausketjujen käsittelynopeuteen vaikuttavien tekniikoiden käyttö
- Pitkät useita pisteitä sisältävät viittausketjut kuten
Worksheets("Sheet1").Range("A1").Font.Size=14
on selvitettävä ennenkuin haluttuja objekteja tai niiden ominaisuuksia voidaan käsitellä.
- Viittauksen selvitys hidastaa koodin suoritusta ja tästä syystä tekniikat, joilla ketjuja voidaan lyhentää ja pisteoperaattorien määrää pienentää, ovat merkittäviä.
- eräs näistä tekniikoista on paljon käytetty:

3.2 With ... End With -komento

Seuraavassa on esitelty tyypillinen With ... End With -rakenteen käyttötilanne.
Koodi määrittelee alueen A1:J1 fontin:



Ylemmän blokin jokaisella rivillä toistuvat Range("A1:J1").Font -rakenteet on alemmassa vaihtoehdossa poistettu With ... End With -komennolla asettamalla toistuva lauseke kaikille riveille yhteiseksi.

Huomaa pisteoperaattori kunkin rivin ensimmäisenä merkkinä!
Koodi tulee selkeämmäksi ja suurissa toistorakenteissa saavutetaan aikasäästöjä.
Alemmassa koodissa viitataan jokaisella koodirivillä samaan font -objektiin ja viittaus selvitetään koodia suoritettaessa vain 1 kerran.
Sensijaan ylemmässä koodissa objekti-viittaus selvitetään joka rivillä uudestaan!

3.3 For Each ... Next -silmukka

For Each ... Next -silmukkaa käytetään kokoelmien (collections) läpikäyntiin.
Tyypillinen yksinkertainen tilanne löytyy esimerkiksi Worksheets -kokoelman käsittelystä:



- Excel antaa jokaiselle työkirjan välilehdelle indeksinumeron 1:stä alkaen.
For Each ... Next -silmukka ottaa indeksinumero-järjestyksessä välilehdet yksi kerrallaan käsiteltäväksi.
- If -lauseessa esiintyvät tähdet ( * ) ovat merkkijonoissa ns. wildcard -merkkejä, jotka vastaavat 0 - n :nää kappaletta välilehtien nimissä sallittuja kirjoitusmerkkejä.
Esim. jos välilehden nimi on 2012-toukokuu, niin välilehti deletoidaan.
Sensijaan jos nimi on huhti-touko2012, niin välilehteä ei deletoida.
- Excel ystävällisesti varoittaa välilehteä deletoitaessa:



- DisplayAlerts=False -lauseella Excel'in varoitukset mahdollisista data-menetyksistä kytketään deletoinnin ajaksi pois.

- For Each ... Next -lausetta käytettäessä ohjelmoijan ei tarvitse tietää kuinka monta objektia kokoelmaan kuuluu.
Niiden määrä saattaa myös vaihdella suorituskertojen välillä.

3.4 For ... Next -silmukka

- For ... Next -silmukan yleisin muoto on seuraava



- Askellusmuuttujan stp default-arvo = 1. Se voidaan tällöin jättää merkitsemättä.

Esimerkki kuvaa asian parhaiten.
For ... Next -ilmukan toiminta on tässä kuvattu yksityiskohtaisesti.



i:n arvo kasvaa jokaisella kierroksella yhdellä, summan arvo kasvaa jokaisella kierroksella i:n arvon verran.
For ... Next -silmukka on ohjelmoinnin kannalta hyvin keskeinen ja sen kirjoittaminen kannattaa opetella huolella.


Seuraavissa tehtävissä voidaan datana käyttää allaolevaa random-materiaalia. Kopioi taulukko Excel'iin: Sheet1 solusta A1 alkaen:

<TABLE BORDER="1" cellspacing="0"><TR><TD>1.09</TD><TD>5</TD><TD>0.64</TD><TD>8.7</TD><TD>2.18</TD><TD>2</TD><TD>3.09</TD><TD>1.14</TD><TD>11.85</TD><TD>2.71</TD><TD>1.18</TD><TD>2</TD><TD>1.96</TD><TD>2</TD><TD>2.4</TD><TD>1.1</TD><TD>4.06</TD><TD>2.15</TD><TD>1.046</TD><TD>2.32
</TD></TR><TR><TD>2.76</TD><TD>6.5</TD><TD>2.1</TD><TD>3.55</TD><TD>8.75</TD><TD>3</TD><TD>4.2</TD><TD>1.16</TD><TD>13.59</TD><TD>3.02</TD><TD>1.8</TD><TD>12</TD><TD>1.61</TD><TD>3</TD><TD>2</TD><TD>1.25</TD><TD>2.61</TD><TD>2.4</TD><TD>0.458</TD><TD>2.66
</TD></TR><TR><TD>3.86</TD><TD>6.5</TD><TD>1.1</TD><TD>1.34</TD><TD>6.75</TD><TD>3.6</TD><TD>3.87</TD><TD>0.26</TD><TD>10.96</TD><TD>2.78</TD><TD>2.39</TD><TD>13</TD><TD>2.45</TD><TD>1.5</TD><TD>2.4</TD><TD>1.05</TD><TD>3.81</TD><TD>2.15</TD><TD>0.422</TD><TD>2.27
</TD></TR><TR><TD>4.26</TD><TD>7.5</TD><TD>1.25</TD><TD>5.93</TD><TD>8.61</TD><TD>3.2</TD><TD>4.67</TD><TD>1.46</TD><TD>8.24</TD><TD>3.2</TD><TD>3.36</TD><TD>16</TD><TD>2.26</TD><TD>2</TD><TD>2.4</TD><TD>1.05</TD><TD>2.69</TD><TD>2.15</TD><TD>0.958</TD><TD>2.21
</TD></TR><TR><TD>2.62</TD><TD>4</TD><TD>0.8</TD><TD>4.42</TD><TD>9.02</TD><TD>2.7</TD><TD>3.9</TD><TD>1.25</TD><TD>10.24</TD><TD>3.06</TD><TD>3.31</TD><TD>11</TD><TD>2.79</TD><TD>2.5</TD><TD>2.2</TD><TD>1.1</TD><TD>1.86</TD><TD>2.25</TD><TD>1.853</TD><TD>2.43
</TD></TR><TR><TD>3.98</TD><TD>6</TD><TD>1.93</TD><TD>6.66</TD><TD>9.27</TD><TD>2.7</TD><TD>3.54</TD><TD>0.86</TD><TD>9.06</TD><TD>3.06</TD><TD>0.85</TD><TD>14</TD><TD>1.5</TD><TD>2.5</TD><TD>2</TD><TD>1.3</TD><TD>3.91</TD><TD>2.4</TD><TD>0.963</TD><TD>2.89
</TD></TR><TR><TD>4.95</TD><TD>11</TD><TD>1.38</TD><TD>5.95</TD><TD>5.41</TD><TD>4</TD><TD>4.11</TD><TD>0.1</TD><TD>12.11</TD><TD>3.16</TD><TD>1.64</TD><TD>19</TD><TD>2.24</TD><TD>2</TD><TD>2.4</TD><TD>1.1</TD><TD>1.93</TD><TD>2.1</TD><TD>0.429</TD><TD>2.4
</TD></TR><TR><TD>1.92</TD><TD>13.5</TD><TD>1.34</TD><TD>9.08</TD><TD>6.28</TD><TD>3.4</TD><TD>3.78</TD><TD>0.39</TD><TD>11.29</TD><TD>2.54</TD><TD>1.55</TD><TD>29</TD><TD>1.96</TD><TD>1.5</TD><TD>2.2</TD><TD>1.2</TD><TD>2.56</TD><TD>2.3</TD><TD>1.374</TD><TD>2.75
</TD></TR><TR><TD>4.19</TD><TD>5</TD><TD>0.9</TD><TD>7.65</TD><TD>1.94</TD><TD>2.2</TD><TD>4.15</TD><TD>1.95</TD><TD>11.17</TD><TD>2.66</TD><TD>2.35</TD><TD>12</TD><TD>2.33</TD><TD>2</TD><TD>2.2</TD><TD>1.2</TD><TD>2.7</TD><TD>2.35</TD><TD>1.732</TD><TD>2.86
</TD></TR><TR><TD>4.58</TD><TD>5</TD><TD>0.47</TD><TD>4.68</TD><TD>8.7</TD><TD>2.4</TD><TD>4.2</TD><TD>1.85</TD><TD>11.87</TD><TD>2.35</TD><TD>2.5</TD><TD>11</TD><TD>2.87</TD><TD>1.5</TD><TD>2.4</TD><TD>1</TD><TD>5.01</TD><TD>2.15</TD><TD>1.075</TD><TD>2.2
</TD></TR><TR><TD>4.49</TD><TD>4</TD><TD>0.4</TD><TD>6.11</TD><TD>2.74</TD><TD>2.4</TD><TD>2.98</TD><TD>0.55</TD><TD>8.75</TD><TD>2.47</TD><TD>0.56</TD><TD>17</TD><TD>2.75</TD><TD>2</TD><TD>2.4</TD><TD>1.05</TD><TD>2.59</TD><TD>2.15</TD><TD>1.561</TD><TD>2.44
</TD></TR><TR><TD>1.54</TD><TD>10</TD><TD>1.22</TD><TD>2.85</TD><TD>7.88</TD><TD>3.2</TD><TD>4.66</TD><TD>1.44</TD><TD>6.36</TD><TD>2.39</TD><TD>1.62</TD><TD>10</TD><TD>1.99</TD><TD>1.5</TD><TD>2.2</TD><TD>1.2</TD><TD>3.31</TD><TD>2.25</TD><TD>0.099</TD><TD>2.29
</TD></TR><TR><TD>2.28</TD><TD>8</TD><TD>1.42</TD><TD>1.48</TD><TD>3.35</TD><TD>1.7</TD><TD>3.38</TD><TD>1.66</TD><TD>9.16</TD><TD>2.57</TD><TD>1.4</TD><TD>2</TD><TD>1.98</TD><TD>2.5</TD><TD>2.8</TD><TD>0.9</TD><TD>4.63</TD><TD>1.9</TD><TD>0.535</TD><TD>1.84
</TD></TR><TR><TD>2.67</TD><TD>6.5</TD><TD>1.45</TD><TD>2.77</TD><TD>9.44</TD><TD>1.9</TD><TD>3.28</TD><TD>1.43</TD><TD>9.7</TD><TD>2.53</TD><TD>0.69</TD><TD>20</TD><TD>1.9</TD><TD>2</TD><TD>2.2</TD><TD>1.2</TD><TD>4.27</TD><TD>2.3</TD><TD>0.918</TD><TD>2.69
</TD></TR><TR><TD>1.25</TD><TD>4.5</TD><TD>1.51</TD><TD>4.23</TD><TD>6.52</TD><TD>3</TD><TD>4.49</TD><TD>1.54</TD><TD>12.66</TD><TD>2.76</TD><TD>2.84</TD><TD>28</TD><TD>2.91</TD><TD>2.5</TD><TD>2.8</TD><TD>0.9</TD><TD>3.87</TD><TD>1.85</TD><TD>0.417</TD><TD>1.76
</TD></TR><TR><TD>3.38</TD><TD>5</TD><TD>0.46</TD><TD>5.28</TD><TD>3.23</TD><TD>2.4</TD><TD>4.18</TD><TD>1.77</TD><TD>11.66</TD><TD>2.83</TD><TD>2.07</TD><TD>10</TD><TD>1.77</TD><TD>2.5</TD><TD>2.4</TD><TD>1.1</TD><TD>2.22</TD><TD>2.25</TD><TD>1.06</TD><TD>2.31
</TD></TR><TR><TD>3.36</TD><TD>6</TD><TD>1.27</TD><TD>4.44</TD><TD>7.12</TD><TD>3.7</TD><TD>3.75</TD><TD>0.09</TD><TD>12.75</TD><TD>2.68</TD><TD>2.57</TD><TD>21</TD><TD>2.79</TD><TD>1.5</TD><TD>2.6</TD><TD>1</TD><TD>4.59</TD><TD>2.1</TD><TD>0.9</TD><TD>2.27
</TD></TR><TR><TD>2.61</TD><TD>4.5</TD><TD>0.79</TD><TD>5.73</TD><TD>2.9</TD><TD>3.2</TD><TD>4.41</TD><TD>1.17</TD><TD>12.1</TD><TD>2.77</TD><TD>0.97</TD><TD>7</TD><TD>2.81</TD><TD>2.5</TD><TD>2.6</TD><TD>0.95</TD><TD>4.29</TD><TD>2</TD><TD>0.4</TD><TD>1.94
</TD></TR><TR><TD>3.39</TD><TD>10</TD><TD>1.32</TD><TD>4.64</TD><TD>2.63</TD><TD>3.2</TD><TD>4.56</TD><TD>1.38</TD><TD>11.12</TD><TD>2.54</TD><TD>0.97</TD><TD>14</TD><TD>1.94</TD><TD>2</TD><TD>2.2</TD><TD>1.1</TD><TD>3.82</TD><TD>2.15</TD><TD>1.975</TD><TD>2.53
</TD></TR><TR><TD>4.53</TD><TD>9.5</TD><TD>1.75</TD><TD>7.22</TD><TD>6.52</TD><TD>3.2</TD><TD>4.7</TD><TD>1.47</TD><TD>9.84</TD><TD>2.35</TD><TD>1.08</TD><TD>16</TD><TD>1.12</TD><TD>2.5</TD><TD>2</TD><TD>1.25</TD><TD>2.91</TD><TD>2.35</TD><TD>1.454</TD><TD>2.45
</TD></TR><TR><TD>4.83</TD><TD>10.5</TD><TD>1.73</TD><TD>4.9</TD><TD>4.27</TD><TD>4.5</TD><TD>4.53</TD><TD>0.04</TD><TD>12.01</TD><TD>2.62</TD><TD>2.95</TD><TD>7</TD><TD>2.79</TD><TD>1.5</TD><TD>2.2</TD><TD>1.1</TD><TD>3.73</TD><TD>2.15</TD><TD>1.139</TD><TD>2.49
</TD></TR><TR><TD>3.97</TD><TD>6.5</TD><TD>0.89</TD><TD>2.63</TD><TD>5.35</TD><TD>2.9</TD><TD>4.06</TD><TD>1.21</TD><TD>9.02</TD><TD>2.49</TD><TD>1.29</TD><TD>19</TD><TD>1.08</TD><TD>2</TD><TD>2</TD><TD>1.2</TD><TD>3.37</TD><TD>2.35</TD><TD>1.345</TD><TD>2.5
</TD></TR><TR><TD>3.51</TD><TD>5.5</TD><TD>0.87</TD><TD>4.16</TD><TD>5.05</TD><TD>2.2</TD><TD>3.34</TD><TD>1.12</TD><TD>7.06</TD><TD>2.76</TD><TD>1.65</TD><TD>19</TD><TD>2.74</TD><TD>2.5</TD><TD>3</TD><TD>0.85</TD><TD>4.05</TD><TD>1.95</TD><TD>1.517</TD><TD>2.24
</TD></TR><TR><TD>3.96</TD><TD>7.5</TD><TD>1.39</TD><TD>8.93</TD><TD>4.06</TD><TD>3.1</TD><TD>3.55</TD><TD>0.45</TD><TD>12.43</TD><TD>2.64</TD><TD>2.09</TD><TD>22</TD><TD>1.43</TD><TD>2</TD><TD>2.4</TD><TD>1.05</TD><TD>1.25</TD><TD>2.15</TD><TD>0.534</TD><TD>2.24
</TD></TR><TR><TD>1.91</TD><TD>5</TD><TD>0.76</TD><TD>3.5</TD><TD>3.38</TD><TD>1.7</TD><TD>3.04</TD><TD>1.36</TD><TD>9.47</TD><TD>2.79</TD><TD>0.75</TD><TD>24</TD><TD>1.81</TD><TD>2.5</TD><TD>2</TD><TD>1.2</TD><TD>1.58</TD><TD>2.35</TD><TD>1.771</TD><TD>2.54
</TD></TR></TABLE>
Tehtävä 3-3-1
Kirjoita For Each ... Next -silmukkaa käyttäen makro, joka tulostaa sarakkeen A lukujen summan soluun A26.

Tehtävä 3-3-2
Kirjoita For Each ... Next -silmukkaa käyttäen makro, joka etsii koko taulukon suurimman luvun ja ilmoittaa tuloksen MsgBox -funktiolla muodossa
"Taulukon suurin luku = (suurin luku)".

Tehtävä 3-4-1
Kirjoita For ... Next -silmukkaa käyttäen makro, joka tulostaa kunkin rivin summan sarakkeeseen U.

Tehtävä 3-4-2
Kirjoita For ... Next -silmukkaa käyttäen makro, joka tulostaa kunkin sarakkeen suurimman ja pienimmän arvon rivinumerot sarakkeen alle riveille 26 ja 27.

Tehtävä 3-4-3
Kirjoita For ... Next -silmukkaa käyttäen makro, joka tulostaa sarakkeeseen U sanan ylipelattu jos A-sarakkeen arvo < 2.00 ja B-sarakkeen arvo >= 4.

Tehtävä 3-4-4
Kirjoita For ... Next -silmukkaa käyttäen makro, joka kopioi koko taulukon välilehdelle Sheet2 alkaen sarakkeesta 3 riviltä 10.

[S.Mäenala]

Tehtävien 3-1-1 ja 3-1-2 ratkaisut

Kertaus:



Tehtävä 3-1-1

- Avaa Template, siirry VBA-editoriin (Alt-F11)
- Käytetään edeltävän harjoituksen 3-1-1 koodin merkintöjä.
- Palautetaan mieliin harjoituksesta 2-1 (Immediate Window) funktion Weekday käyttö.

Koodataan seuraava:



- Lause kokonl = Weekday( Date, vbMonday) palauttaa muuttujan kokonl arvoksi tämän päivän viikonpäivän numeron.
Postituspäivän (torstai) numero on 4.
- Date = nykyinen päiväys, vbMonday = viikon alkamispäivä
- Else -kohta ei tässä tapauksessa ole välttämätön mutta edustaa tervettä koodaustapaa yllätystilanteiden varalta.

- Suorita makro VBA:sta näppäilemällä F5 (kursori jollakin koodirivillä), talleta työkirja jos katsot sen tarpeelliseksi.

Tehtävä 3-1-2
Tämä tehtävä on tarkoitettu
- antamaan edistyneemmille kurssilaisille pientä purtavaa,
- painottamaan sitä että ongelmatilanteissa avuksi voi aina ottaa netin VBA-keskustelut.
Netissä kannattaa kuitenkin (etenkin ohjelmointi-kysymyksissä) muistaa että neuvojia on kahdenlaisia..

Ratkaisumalli 1
- Avaa Template, siirry VBA-editoriin (Alt-F11)
- Käytetään ensimmäisessä ratkaisumallissa hyväksi myös Excel'iä:



Kuvan mukaisesti:
- solun G2 kaava: =NOW()
- solun G5 kaava: =DATE(YEAR(G2)-60;MONTH(G2);DAY(G2))
- solu G6 vastaavasti
(suom. ftionimet: NOW = NYT, DATE = PÄIVÄYS, YEAR = VUOSI, MONTH = KUUKAUSI, DAY = PÄIVÄ)

- Tämän jälkeen voidaan kirjoittaa seuraava:



- ratkaisussa oletettiin että For ... Next -looppi ei vielä ole käytettävissä..

Ratkaisumalli 2:

Tässä on käytetty g-menetelmää ja löydetty DateAdd -kirjastofunktio.
Ratkaisu hyödyntää myös seuraavan kurssiosion For ... Next -rakenteita, jotta koodia ei tarvitse toistaa 22 kertaa.

Koodataan:



- DateAdd -funktion toiminta on seuraava:
- argumentti Date palauttaa nykyisen päiväyksen (päiväm. ja kellonaika)
- Merkkijonolla "yyyy" määritetään mitä päiväyksen osaa lisätään, tässä lisätään siis vuosia, lisäys voi olla myös negatiivinen
- merkkijonoa seuraava lukuarvo ilmoittaa lisäyksen määrän, tässä -18 / -60.
- DateAdd -funktio palauttaa sen päiväyksen, joka on annetun aikaintervallin etäisyydellä ftiolle ilmoitetusta päiväyksestä (Date).

- lause objCell.Range("B1").Value = "senior" saattaa myös kaivata pientä selitystä:
- - esimerkiksi vaikkapa For -loopin 1. kierroksella objCell:illä on arvo C2 (viittaus soluun C2)
- - määritys .Range("B1") on cbjCell:istä katsoen 1 solu siitä oikealle, siis 1 solu C2:sta oikealle (=D2)
- - eli C2:sta katsoen Range("B1") on solu D2
- - lause olisi voitu kirjoittaa myös muotoon objCell.Offset(0,1).Value = "senior", jolloin se varmasti olisi helpommin ymmärrettävissä.
run/save

Tähän asti tehtävät ovat olleet melko epäreiluja aloittelivia ohjelmoijia kohtaan kun useimmiten on tarvittu omia lisäpanoksia tehtävien tekemiseen, joko aikaisempaa kokemusta tai esim. webbi-hakuja.
Jatkon pyrin rakentamaan siten että tehtävät olisivat suoritettavissa pelkästään kurssilla jo läpikäytyjen asioiden pohjalta. Vaikeusasteita saadaan tarvittaessa lisää käyttämällä vaativampaa logiikkaa.

[S.Mäenala]

Operaattorit



Huom!
Useissa ohjelmointikielissä sijoitusoperaattori on yhtäsuuruusmerkki (=) ja yhtäsuuruusoperaattori kaksi yhtäsuuruusmerkkiä (==), joissakin jopa kolme.
- lause a = 5 antaa muuttujalle a arvon 5
- lause a == 5 testaa onko muuttujan a arvo 5.
VBA:ssa yhtäsuuruusmerkkiä käytetään molemmissa tapauksissa
- lause a = 5
- - normaalisti sijoittaa arvon 5 muuttujaan a, mutta
- - esim. If lauseessa, testaa onko a:n arvo 5.

Ilmaus (expression)

Ilmaus on kielen kannalta mielekäs symboleista (tokens) koostuva lauseke.
- symbolit voivat olla
- - vakioita, esim. "Pekka on matkoilla", 5.25, vbMonday
- - muuttujia
- - avainsanoja (keywords), esim. IsNumeric
- - operaattoreita
- - Funktio - tai Sub -proseduureja

Esimerkiksi ohjelmointikielen lauseet ('komennot') ovat ilmauksia, samoin niiden kielen kannalta mielekkäät osat.

3.5 Select Case -rakenne

Execution Control (~suorituksen ohjaus, vuonohjaus) -lauseita kutsutaan usein myös Branching (~haarautumis) -lauseiksi.
Näistä ovat käsittelyssä jo olleet:
- If -rakenne (3.1)
- For Each ... Next -silmukat (3.3)
- For start To end ... Next -silmukat (3.4)

Seuraavassa käsitellään muut ohjaus-rakenteet: Select Case, Do While ja Do Until

Select Case -rakenteen syntaksi on seuraava



Harjoitus 3-5-1

- Avaa Template. Siirry VBA:han (Alt-F11)
- Tyhjennä koodi-ikkuna ja kopioi siihen seuraava koodi:

<TABLE BORDER="0"><TR><TD>

Koodi: Valitse kaikki

Option Explicit

Sub Esimerkki()

'          Esimerkkimakro Select Case -rakenteen käytöstä                    

Dim a As Single
Dim b As Long
Dim testDate As Date
Dim testTime As Date

a = Time
testTime = Time
b = Date
testDate = Date
MsgBox "Kellonaika desimaalilukuna: Single a = " & a
MsgBox "Kellonaika Date-muuttujassa: testTime = " & testTime
MsgBox "Päiväys kokonaislukuna: Long b = " & b
MsgBox "Päiväys Date-muuttujassa: testDate = " & testDate

Select Case a
    Case Is < 0.25
        MsgBox "Hyvää yötä!"
    Case Is < 0.5
        MsgBox "Hyvää aamupäivää!"
    Case Is < 0.75
        MsgBox "Hyvää iltapäivää!"
    Case Else
        MsgBox "Hyvää iltaa!"
End Select

Select Case b Mod 7
    Case 1
        MsgBox "Hyvää Sunnuntaita!"
    Case 2
        MsgBox "Hyvää maanantaita!"
    Case 3
        MsgBox "Hyvää tiistaita!"
    Case 4
        MsgBox "Hyvää keskiviikkoa!"
    Case 5
        MsgBox "Hyvää torstaita!"
    Case 6
        MsgBox "Hyvää perjantaita!"
    Case Else
        MsgBox "Saturday Night Fever!"
End Select

End Sub

</TABLE>
- Siirry Excel'iin (Alt-F11)
- Excel'in Developer -valikosta klikkaa Insert (Lisää) ja valitse Button (nappula).
Piirrä nappula sopivaan kohtaan.
- Assign Macro (Aseta makro) -dialogissa klikkaa Esimerkki.
- Suorita makro



Harjoitus esittelee myös Time ja Date funktioiden käyttöä.
Samoin kuin If -rakenteessa, myös Select Case -rakenteeseen kannattaa aina lisätä Else -kohta yllätystilanteiden varalta.
Pakollinen se ei tässäkään ole.


Tehtävä 3-5-1

Käytä kohdassa 3.3 olevaa random-taulukkoa:
Kirjoita makro, joka
- tulostaa (sopivaan tyhjään sarakkeeseen) vedon odotusarvon, kun taulukon 10. sarakkeessa on bookkerin tarjoama kerroin ja viimeisessä sarakkeessa laskemasi rajakerroin,
- määrittää odotusarvon perusteella vedolle sopivan panoksen asteikolla 0 - 10. Saat itse asettaa panosrajat. Käytä Select Case -rakennetta.

[S.Mäenala]

3.6 Do While - ja Do Until -silmukat

Näiden kahden rakenteen toiminta on samankaltainen, sillä erolla että
- Do While -looppia kierretään niin kauan kuin annettu ehto on tosi (True)
- Do Until -looppia kierretään niin kauan kuin annettu ehto on epätosi (False)

Kummallakin komennolla on kaksi perussyntaksia. Do While -silmukalla ne ovat:



Syntaksimuotojen ero on siinä että muoto 2:ssa koodi suoritetaan aina vähintään yhden kerran, muoto 1:ssä ei välttämättä kertaakaan.

Harjoitus 3-6-1

- Avaa Template. Kopioi 1-suvulle alkaen solusta A1 pätkä tekstiä esim. tältä sivulta.
- Siirry VBA:han (Alt-F11)
- Tyhjennä koodi-ikkuna ja kopioi siihen seuraava koodi:

<TABLE BORDER="0"><TR><TD>

Koodi: Valitse kaikki

Option Explicit

Sub Esimerkki()

Dim i As Integer
Dim lastRow As Long

lastRow = ActiveSheet.UsedRange.Rows.Count      'ei laske alussa olevia tyhjiä rivejä
                                                ' => 1. rivi ei saa olla tyhjä
Do
    i = i + 1
    Cells(i, 13).Value = Len(Range("A" & i))
Loop While i < lastRow

End Sub

</TD></TR></TABLE>

- Suorita makro



- Koodi laskee A-sarakkeeseen kopioitujen tekstirivien pituuden käyttäen kirjastofunktiota Len.
- Tulostus ohjataan lempisarakkeeseeni M (=13.).
- Koodissa on myös esitelty eräs paljon käytetty viimeisen rivin määritys: lastRow = ActiveSheet.UsedRange.Rows.Count
- Liitäntä-operaattori (&) tekee kokonaisluvulle i automaattisen konversion merkkijonoksi.
Periaatteessa olisi tyylikästä käyttää CStr -konversiofunktiota mutta harvoin sitä näkee käytettävän &-operaattorin kanssa...
- Harjoituksen looppi suoritetaan aina vähintään kerran, vaikka välilehti olisi täysin tyhjäkin.

Rangeista

Range (alue) voi olla yksi ainoa solu tai usean solun yhdistelmä.
Esimerkkejä rangeista ja niiden merkintätavoista (käy nämä huolella läpi):



- Hankalasti ymmärrettävä asia näyttää olevan se että kun Cells on ominaisuus (property) niin kuinka voidaan kirjoittaa esim. Cells( 2, 2 ) . Value ts. puhua ominaisuuden ominaisuudesta.
- Tälle on yksinkertainen selitys:
- - vaikka Cells on ominaisuus niin se palauttaa objektin (Range -objektin).
Value -ominaisuus on tämän Range -objektin ominaisuus, ei siis Cells -ominaisuuden ominaisuus.

Kirjoitan lisää perustehtäviä jos niitä tarvitaan. Muuten on aika siirtyä eteenpäin..

[S.Mäenala]

3.7 Strukturoitu ohjelmointi. Normaalista ohjelmavuosta poikkeaminen

Strukturoidulla ohjelmarakenteella tarkoitetaan koodaustapoja, joissa
- ohjelmakoodi jakautuu selkeästi hahmottuviin osiin ('blokkeihin')
- koodin suoritus etenee normaalivuossa so. poikkeustilanteita lukuunottamatta ylhäältä alas
- toisto- ja haarautumisrakenteet on suunniteltu niin että ohjelman suoritus etenee loogisessa järjestyksessä ilman että käytetään pakotettuja siirtymisiä (=GoTo komentoja) koodin sisällä.

Strukturoidun ohjelmoinnin irvikuva on että suoritus (useimmiten juuri goto- komennoilla) poukkoilee sinne ja tänne.
Ammattilaiset kurtistelevat kulmakarvojaan heti kun löytävät koodista GoTo -komennon.
Sitä pidetään merkkinä huonosta ohjelmasuunnittelusta ja sen käyttö hyväksytään vain harvoissa poikkeustapauksissa.

Normaalivuosta poiketaan suunnitellusti tietyn ennalta-asetetun ehdon täyttyessä.
Silloin eri ohjelmarakenteista poistuminen noudattaa seuraavia sääntöjä:
- 1. If ... Else -rakenteista poistutaan automaattisesti heti kun ehdon täyttävä vaihtoehto on löydetty ja sitä vastaavat komennot on suoritettu.
- 2. For Each ... Next ja For ... Next loopista poistutaan välittömästi komennolla Exit For.
- esim.



- 3. Do While ... Loop ja Do Until ... Loop -silmukoista poistutaan välittömästi komennolla Exit Do.
- 4. Select Case -rakenteesta poistutaan automaattisesti heti kun ehdon täyttävä vaihtoehto on löydetty ja sitä vastaavat komennot on suoritettu.

Useimmiten ei-strukturoitu koodi voidaan helposti korvata strukturoidulla vaihtoehdolla:



Huomaa nimikkeen EndLoop: käyttö. Ohjelmariville voidaan antaa nimike (~Label) käyttäen merkintätapaa [Haluttu nimike]: .

Joillekin mahdollisesti muista kielistä tuttuja break- ja continue-lauseita ei VBA:ssa tunneta.
Break on korvattu exit'illä ja continue on korvattavissa strukturoiduilla rakenteilla.

Proseduurien suoritus voidaan keskeyttää lauseilla Exit Sub ja vastaavasti Exit Function.
Tällöin joko kontrolli siirtyy kutsuneelle proseduurille tai ilman sitä (isäntä-proseduuria) makron suoritus päättyy.


3.8 Taulukot (taulukko-muuttujat, ~Arrays)

Taulukko-muuttujia ei pidä sekoittaa normaaleihin Excel -taulukoihin.
Taulukko-muuttuja on fyysisesti olemassa vain tietokoneen muistissa, mutta ei minkään tiedoston osana.
Taulukko-muuttuja on yhteisesti nimetty samaa tyyppiä edustavien arvojen ryhmä.
Taulukkko-muuttujat kannattaa nimetä esim. arr - tai tl -suffiksia käyttäen jotta sekaantuminen proseduurien kanssa voidaan välttää.

Taulukko esitellään seuraavasti:



VBA:ssa taulukon alkio-indeksi alkaa defaultina 0:sta. Tämä voidaan muuttaa käyttämällä ilmausta 1 To ... .
Edellisessä alempana esittelty taulukko alustettaisiin esimerkiksi näin:



Taulukko voi olla 1-, 2- tai useampi-ulotteinen, 60 ulottuvuuteen asti:



Taulukoista käytetään joskus niiden matemaattisten vastineiden nimityksiä:
- matriisi
- 1-ulotteinen matriisi = vektori

3.8.1 Dynaamiset taulukot

Jos taulukon suoritusaikainen koko ei ole tiedossa, taulukko määritellään dynaamisena:




Huom!
Tietokannoissa tyypillisten esim. allaolevan kuvan mukaisten tietue-rakenteiden käsittelyyn taulukko-muuttujat eivät sovellu, koska niiden alkioiden on edustettava samaa tietotyyppiä.



Rakenteeltaan tietueita vastaavia muuttujia VBA:ssa edustavat class- (luokka-) objektit tai käyttäjän itse määrittelemät tietotyypit.


3.9 Käyttäjän määrittelemät tietotyypit

Omia tietorakenteita VBA:ssa voi luoda Type -määrittelyllä seuraavasti:



Custom data-tyypit on määriteltävä moduulien alussa ennen proseduureja!

Edellä määritellyn taulukon 1. alkio voidaan alustaa normaaliin tapaan:




3.10 Sisäänrakennetut funktiot (~built-in f's)

VBA:n peruskirjastossa on suuri joukko funktioita keskeisimpiä tehtäviä varten.
Niiden lisäksi käytettävissä on joitakin Excel'in työkirjafunktiota, joita voidaan käyttää kirjoittamalla
- Application.WorksheetFunction. ja tämän jälkeen halutun funktion nimi.

Huom.1
VBA:ssa voidaan käyttää vain niitä Excel-funktioita, joilla ei ole vastinetta VBA:n omissa funktiokirjastoissa.

Esimerkiksi seuraava koodi aiheuttaa virheilmoituksen:
- sngJuuri = Application.WorksheetFunction.Sqrt(24)
VBA:lla on oma neliöjuuri-funktio ja oikea muotoilu on sitä käyttäen:
- sngJuuri = Sqr(24)

Huom.2
Jos haluat kolleegoilta arvostusta ohjelmoijana, opettele koodaamaan VBA:n omia funktiokirjastoja käyttäen.

Excel -funktioista poiketen Excel -kaavojen käyttö sensijaan on joissain tapauksissa hyvin perusteltua.
Esim.:
- Cells(2,3) . Formula = "=A2 * B2"
tai sama R1C1 -notaatiolla:
- Cells(2,3) . FormulaR1C1 = "=RC[-2] * RC[-1]"