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.
Minikommentit