Tänään on 28.03.2024, 17:44.

VisualBasic - ohjelmointi - kurssi

Strategiat, kertoimenlaskenta ja muut ohjeet/vinkit tänne.
Vastaa Viestiin
S.Mäenala
Avatar
Jäsen
Viestit: 1190
Liittynyt: 18.01.2011, 19:17
Pisteitä: 3733
Paikkakunta: Kamppi

VisualBasic - ohjelmointi - kurssi

Viesti Kirjoittaja S.Mäenala »

Visual Basic for Applications (VBA) on Excel-makrojen ohjelmointikieli. Makrot taas ovat erilaisten Excel-suoritteiden automatisointiin tarkoitettuja lyhyitä ohjelmia.

Nimityskäytäntö:
- seuraavassa työkirjan välilehtiä (worksheets) kutsutaan välilehdiksi
- Excel-ikkunan yläosassa olevien valikoiden välilehtiä (tabs) kutsutaan valikoiksi.

Huomautus:
- en ryhdy arvailemaan mitä jotkin englanninkieliset Excel -nimikkeet ovat suomeksi. Käytän suomalaisia nimiä vain jos olen kutakuinkin varma niistä.
Tämä menettely tietysti virheiden ja väärinkäsitysten välttämiseksi..

1. Työskentely-ympäristö

Perusasetukset:

Excel'in Developer valikko (Developer Tab)
- Valitse Excel-Asetuksista Customize Ribbon (Mukauta Valikot?)

Kuva

Varmista että Developer -valikko on ruksattuna ja klikkaa Ok.

Kuva

Developer -valikko ilmestyy valikko-ketjuun.

Makrojen turva-asetukset (Macro Security)
- Valitse Developer -valikosta Macro Security

Kuva

- Kohdan Macro Settings oma asetukseni on Disable all macros with notification.
Suosittelen jompaa kumpaa kahdesta ylimmästä vaihtoehdosta.

Kuva

Kansio turvallisille makrotiedostoille (Trusted Locations)
- Luo itsellesi sopivaan paikkaan Kurssi -kansio
- Valitse edellisessä ikkunassa Trusted Locations
- Klikkaa sieltä Add new location ja aseta luomasi Kurssi -kansio turvalliseksi.
- Ruksaa myös kohta Subfolders

Kuva

Näillä asetuksilla Excel hyväksyy mukisematta kaikki Kurssi -kansiossa olevat makro-sovellukset.
Ei-turvallisten kansioiden makro-tiedostoista joko tulee turvallisuusriski-varoitus tai niiden suorittaminen estetään kokonaan, valinnasta riippuen.

===

Harjoitus 1: Makron nauhoitus (Record Macro)

Makron koodi voidaan tuottaa kirjoittamalla se käsin. Näin toimitaan kehittyneempiä, monimutkaisempia makroja luotaessa.
Koodi voidaan aikaansaada myös nauhoittamalla joitain Excel'issä manuaalisesti suoritettuja toimenpiteitä.

Ensimmäisissä harjoituksissa joudutaan paljolti operoimaan asioilla, joita ei vielä ole käsitelty.
Harjoitusten tarkoituksena on lähinnä tutustua ohjelmointi-ympäristöön ja eri työkaluihin.
Varsinaisia ohjelmointitavoitteita ne eivät juurikaan sisällä.

1.
- Avaa Excel
- Tallenna (tyhjä) työkirja omaan Kurssi-kansioosi nimellä 01-Record1.xlsm
Huomaa tallentaessasi valita tiedostoformaatiksi Excel Macro-Enabled Workbook (*.xlsm)
- Anna työkirjan ensimmäisen välilehden nimeksi "EkaSivu"

Kuva

2.
- Valitse Insert -valikosta Shapes ja sieltä edelleen TextBox

Kuva

- Piirrä hiirellä sopivaan kohtaan noin reilun kahden solun levyinen teksti-laatikko.

Kuva

- tee laatikosta kopio (raahaa laatikko Ctrl-nappi pohjassa toiseen paikkaan)

Kuva

- Kirjoita ylemmän laatikon tekstiksi MUOTOILE SOLUT ja alempaan laatikkoon vastaavasti POISTA MUOTOILUT

3.
Klikkaa ylempää laatikkoa hiiren 2-napilla ja valitse Assign Macro (aseta makro)

Kuva

- Anna makron nimeksi MuotoileSolut ja klikkaa Record (nauhoita)

Kuva

Kuva

- Klikkaa myös Record Macro -ikkunassa Ok. Makron nauhoitus alkaa tällä Ok-klikkauksella.
Tee nauhoituksen aikana vain kaksi alla mainittua asiaa!

- Klikkaa solua A1
- Klikkaa Excel-ikkunan vasemmasta alakulmasta Ready (Valmis)

Makro on nauhoitettu ja voimme siirtyä tarkastelemaan aikaansaannostamme.

4.
- Siirry VB-editoriin klikkaamalla Developer -valikon vasemmasta laidasta Visual Basic tai näppäilemällä Alt - F11.
- Näet editorissa jotain tämän näköistä:

Kuva

- Jos Projekti-ikkuna ei ole näkyvissä, saat sen auki klikkaamalla kuvaketta 1.
- Properties-ikkunan saa vastaavasti auki 2:lla merkitystä kuvakkeesta.

5.
- Klikkaa vasemmalla olevan Modules -kansion edessä olevaa + -merkkiä avataksesi kansion.
- Löydät kansiosta moduulin Module1, jonka edellä tehty makro-nauhoitus loi.
- Kaksoisklikkaa Module1:stä avataksesi sen koodi-ikkunan.

Kuva

- Makron nauhoitus loi Sub -proseduurin, jonka nimeksi annettiin MuotoileSolut.
- Sub -proseduuri (~ subroutine ~aliohjelma) alkaa sanalla Sub ja päättyy sanoihin End Sub.
- Varsinainen suoritettava koodi on näiden välissä.
- Hipsulla ( ' ) alkavat rivit ovat kommentteja (älä sekoita näitä solujen kommentteihin Excel'issä). Ne on kuvassa merkitty vihreällä.
- Kommentit eivät vaikuta koodin suoritukseen millään tavalla.
- Sisennys Range -rivillä on tehty helpottamaan lukemista mutta sisennyksilläkään ei itse suoritukseen ole mitään vaikutusta.
- Range -rivin koodi ei tee muuta kuin valitsee solun A1, siitä tulee aktiivi solu.

6.
Poistetaan nämä turhanpäiväisyydet ja korvataan ne koodilla:

Range("A1:Z35").Interior.Color=RGB(176,208,232)

Kuva

Kun VB-editorissa kirjoituskursori on jollakin koodirivillä, kyseinen makro voidaan suorittaa näppäimellä F5.
Suoritetaan makro tällä kertaa kuitenkin Excel'issä, jotta pääsemme kokeilemaan hienoa käynnistysnappulaamme.
Siirrytään Excel'iin näppäilemällä taas Alt - F11.
===

Muista tallettaa työkirja!

Tehtävä 1-1
Edelläkuvattua mallia noudattaen, nauhoita alemmalle nappulalle koodi, joka poistaa ylemmän nappulan tekemän muotoilun.
Ohje: poista nauhoituksen aikana solualueen muotoilu Excel'issä manuaalisesti.
Tehtävä 1-2 (vaativampi)
Tee edellä kuvattu tehtävä kirjoittamalla koodi itsenäisesti VB-editorissa. Tehtävä voidaan suorittaa usealla vaihtoehtoisella tavalla.

(taisin noudattaa kuva-lupaustani...)

Pisteitä

Pisteitä yhteensä: 47. Antamasi peukut: 0.

Minikommentit

29.09.2014 22:35 <mikko> Jahas, heti kotitehtäviä.

29.09.2014 22:43 <S.Mäenala> Näin, eikä sitten luntata googelista..!

29.09.2014 22:47 <mikko> Yritän suoriutua. Tosin mietin myös toista lähestymislinjaa,

29.09.2014 22:47 <mikko> jota tyttäreni käytti elämänsä ensimmäiset koulupäivän

29.09.2014 22:48 <mikko> jälkeen huutamalla minulle, että hänen päivänsä menee

29.09.2014 22:48 <mikko> pilalle koska joutuu tekemään läksyjä. Ei alkanut sekään

29.09.2014 22:48 <mikko> tarina ihan nappiin :)

29.09.2014 23:03 <S.Mäenala> Moni on huomannut että aikuisiällä opiskelu on paljon pal-

29.09.2014 23:03 <S.Mäenala> kitsevampaa kun nuoruuden hörhöilyt on jätetty taakse..

30.09.2014 08:33 <lammpa-1> Kiitos S.Mäenalalle jo etukäteen tästä varmasti erittäin

30.09.2014 08:34 <lammpa-1> suurta vaivaa aiheuttavasta kurssista! Täytynee itsekin

30.09.2014 08:34 <lammpa-1> hankkia uudempi Excel. Tähän asti menty 2003:lla.

30.09.2014 12:26 <S.Mäenala> Kannattaa tehdä tuo P! 2007:an tehtiin iso peruremontti ja..

30.09.2014 12:28 <S.Mäenala> 2010:ssä vielä valikot muuttuivat Ribbon-muotoon.


S.Mäenala
Avatar
Jäsen
Viestit: 1190
Liittynyt: 18.01.2011, 19:17
Pisteitä: 3733
Paikkakunta: Kamppi

Re: VisualBasic - ohjelmointi - kurssi

Viesti Kirjoittaja S.Mäenala »

Kuva

2.1 Peruskäsitteitä: Objektit

Visual Basic on olio-ohjelmointikieli (object-oriented programming language) ja siksi on perustavalla tavalla tärkeää ymmärtää mitä objektit ovat.

Perusjaottelu on seuraava:

Kuva

Objectit ovat säiliöitä (containers), jotka pitävät sisällään lukuisan joukon erilaisia jäseniä: ominaisuudet, metodit, tapahtumat.
Jäsenillä on edelleen omat määreensä: parametrit.

Esimerkki
Olkoon Valioliiga objekti (tämäntyyppisiä ohjelmoijan määrittelemiä objekteja ei ole määritelty Excel'in omissa kirjastoissa).
Valioliigalla saattaisi olla seuraavanlaisia jäseniä:
- Joukkueet (ominaisuus)
- MäärääSakko (metodi)
- KausiAlkaa (tapahtuma)

Joukkueet on toisaalta myös objekti, jolla on vastaavasti omat jäsenensä, jne.

Jäsenyys merkitään piste-operaattorilla ( . )

Lausekkeessa
Valioliiga.Joukkueet.Maalivahdit.Nimi.Teksti
kaikki neljä ensimmäistä ovat objekteja, Teksti tyypillisesti ei ole.

Samalla kuitenkin
- Joukkueet on Valioliigan ominaisuus (Valioliiga.Joukkueet)
- Maalivahdit on Joukkueiden ominaisuus
- Nimi on Maalivahtien ominaisuus
- Teksti on Nimen ominaisuus

Jos haluan antaa Liverpoolin 2-maalivahdille välittömästi potkut, kirjoitan

Kuva

Jos haluan asettaa vaihtuneen ykkösmaalivahdin nimen, kirjoitan VaihdaNimi -metodia käyttävän komennon

Kuva

tai vaihtoehtoisesti kirjoitan vain lyhyesti
Maalivahdit(1).Nimi.Teksti="U. Jones"

Kun maalivahti vaihtuu saadaan ehkä tapahtuma UusiPelaajaPalkattu.

Edelläoleva on tarkoitettu vain havainnollistamaan objekti-rakennetta.
Käytännössä vastaavien lausekkeiden muodon sanelee objektien määrittelytapa.

Objekti-rakenteet tuntuvat aluksi varmasti mutkikkailta. Onneksi VBA:n laajaa objekti-kirjastoa ei tarvitse osata ulkoa.
Tyypilliset käytännöt oppii nopeasti. Harvemmin käytettyjen objektien määrittelyn voi aina tarkistaa Referenssistä:

http://msdn.microsoft.com/en-us/library ... 14%29.aspx

Esimerkiksi foorumi-viestiä kirjoittaessamme me emme mieti että seuraavaksi naputtelen tähän subjektin ja sitten verbin..., olemme oppineet kielen ilmaisutavat.
Ohjelmointikielen ilmaisutavat (syntaksi) ovat paljon kielioppia yksinkertaisempia.

Harjoitus 2-1: Immediate Window

Immediate Window on suunniteltu debuggerointi -käyttöön (bug ~bugi, debugger ~virheenkorjaaja).
Sitä voidaan kuitenkin hyödyntää laajemminkin.
1.
- Avaa Excel, tallenna työkirja nimellä 02-Immediate.xlsm
- Siirry VBA -editoriin (Alt - F11)
- Klikkaa Projekti -ikkunan tyhjää aluetta kakkos-napilla ja valitse Lisää Moduli

Kuva

- Jos Immediate -ikkuna ei ole auki, näppäile Ctrl - G
2.
- A. Kirjoita Immediate -ikkunaan teksti
print "Tänään on " & now
- paina Enter
- Print -komennon voi korvata kysymysmerkillä (?) (Tyhjennä Immediate -ikkunan teksti tarvittaessa.)
- B. Kirjoita ikkunaan
? format(now, "dddd")
- paina enter
- C. Kirjoita ikkunaan
? 24*1.95
- paina Enter
- D. Kirjoita ikkunaan
? application.username
- paina Enter
3.
- Immediate -ikkuna on myös näppärä tapa testata koodejaan ilman että välillä tarvitsee mennä Excel'iin katsomaan toimiko vaiko ei.
- Kirjoita Module1:n koodi-ikkunaan seuraava koodi:

Kuva

- Varmista että kirjoituskursori on jollakin koodirivillä ja aja makro näppäimellä F5.
- Totuttele käyttämään copy/paste -menetelmää, se on ohjelmoinnissa korvaamaton apu!
- Tutki tuloksia ja tee kokeiluja.
4.
- kokeile myös seuraavia

Kuva

Talleta työkirja!
===

Tehtävä01-1: Ratkaisu
Nauhoituksella saadaan seuraava koodi:

Kuva

Tehtävä01-2: Ratkaisu
Oikea ohjelmoija kirjoittaa jotakin tämäntyppistä (2 ratkaisumallia)

Kuva

Tehtävissä esiintyviä käsitteitä tarkastellaan myöhemmin yksityiskohtaisemmin.

Pisteitä

Pisteitä yhteensä: 21. Antamasi peukut: 0.

Minikommentit

01.10.2014 21:10 <lammpa-1> SM sai ekat tehtävät oikein :D

01.10.2014 21:19 <S.Mäenala> Pientä valoa näkyy siis täälläkin!

03.10.2014 17:37 <mikko> Viikonloppu tehdäänkin SM:n kotiläksyjä!

03.10.2014 17:38 <mikko> Ja turha vääntää "vitsiä" tuosta ;)


S.Mäenala
Avatar
Jäsen
Viestit: 1190
Liittynyt: 18.01.2011, 19:17
Pisteitä: 3733
Paikkakunta: Kamppi

Re: VisualBasic - ohjelmointi - kurssi

Viesti Kirjoittaja S.Mäenala »

2.2 Peruskäsitteitä: Hierarkia, Kokoelmat, Aktiiviset objektit

Objektit muodostavat hierarkisen rakennelman:
- huipulla on Application -objekti, joka edustaa Excel'iä itseään
- Workbooks -ominaisuus on Application -objektin jäsen ja edustaa kaikkia avoinnaolevia työkirjoja.
- Worksheets -ominaisuus on Workbooks -objektin jäsen ja edustaa kohteena olevan työkirjan kaikkia välilehtiä.
- Range -ominaisuus on Worksheets -objektin jäsen
- Cells -ominaisuus on Range -objektin jäsen, jne.

On ehkä vähän yllättävää että mitään Cell - tai Cells -objektia ei ole olemassa.

Kokoelmat (Collections)

Soveltuvilta osin yksittäisten objektien muodostamat kokoelmat ovat myös objekteja:
- Workbook - Workbooks
- Worksheet - Worksheets
Esimerkiksi Workbook -objekti määrittää työkirja-objektin jäsenet. Yksittäiseen työkirjaan viitataan kuitenkin poikkeuksetta seuraavantyylisillä merkinnöillä:

- Workbooks("Pelaajat.xlsx")
- Workbooks(1)
- ActiveWorkbook
- ThisWorkbook


Aktiiviset objektit

Esimerkiksi vaikkapa Application.Workbooks.Worksheets.Range.Value -tyylinen viittausketju saattaa ominaisuuksineen kasvaa hyvin pitkäksi.
Onneksi tälläistä ketjua ei aina tarvitse kirjoittaa kokonaan näkyviin.
Jos siitä osa (alusta) jätetään kirjoittamatta, Excel käyttää poisjätettyjen objektien paikalla kulloinkin aktiivisina olevia objekteja.
Joissain tapauksissa isäntä-objekti on kuitenkin pakollinen (required)

Työkirjoista voi kerrallaan vain yksi olla aktiivinen (ActiveWorkbook).
Aktiivisessa työkirjassa vain yksi välilehti voi olla aktiivinen (ActiveSheet).
Aktiivisen välilehden soluista vain yksi voi olla aktiivinen (ActiveCell).

Huom! Työkirjaan voidaan kirjoittaa vaikka se ei ole aktiivinen.
Itse asiassa työkirjaan voidaan kirjoittaa vaikka se ei ole edes avoinna Excel'issä.

Koodin tallentaminen
VBA-koodi tallennetaan moduuleihin. Koodi koostuu proseduureista, joita on kahta tyyppiä:
- Sub -proseduurit ja
- Function -proseduurit
Sub -proseduureista olemme jo nähneet lyhyitä esimerkkejä.
Sub'in ja Function'in merkittävin ero on siinä että Function -proseduuri palauttaa arvon, Sub ei palauta.
Moduulit tallentuvat työkirjan mukana kun työkirja Excelissä tallennetaan.
Moduuleita voi kuitenkin tarkastella tai muuttaa vain VB-editorissa.

Harjoitus 2-2: Jäseniin viittaaminen, Virhetilanteet

Otetaan esimerkiksi Protect -metodi.
Se on Worksheet -objektin jäsen. Se ei ole Worksheets -objektin jäsen.
Väärinkäsityksiä syntyy helposti kun Protect metodiin ei kuitenkaan voida viitata Worksheet -objektin välityksellä.
Kaikki seuraavat ovat virhemerkintöjä:
- Worksheet.Protect
- Worksheet("Sheet1").Protect
- Worksheet(1).Protect


Oikeita merkintätapoja ovat:
- Worksheets("Sheet1").Protect
- Worksheets(1).Protect
- ActiveSheet.Protect


Protect -metodilla on 16 parametria, jotka kaikki ovat optionaalisia (~ei-pakollisia).
Voitaisiin siis kirjoittaa viittaus:
- Worksheets("Sheet1").Protect "asd123", False, False, True, False, True, True, True, False, True, False, False, True, False, False, False

Ei kovin havainnollista!
Tyylikkäämpi tapa on kirjoittaa tarvittavien parametrien nimet näkyviin ja jättää oletus- (default-) -arvoiset pois:
- Worksheets("Sheet1").Protect Password:="asd123", AllowInsertingRows:=True

Huom! Jos ominaisuus tai metodi palauttaa arvon, parametrit ilmoitetaan suluissa (vastaavaan tapaan kuin funktioiden argumentit):
- Worksheets("Sheets1").Address ( RowAbsolute:=False, ColumnAbsolute:=False )

1.
- Avaa uusi työkirja ja tallenna se nimellä 03-Referencing_and_Code_errors.xlsm
- Siirry editoriin (Alt - F11)
- Klikkaa Projekti -ikkunan tyhjää aluetta kakkos-napilla valitse Lisää Moduli
- Kirjoita Module1:n koodiksi:

Sub Test()

Worksheet("Sheet1").Protect Password:="asd123", AllowInsertingRows:=True

End Sub


2.
- Suorita makro (F5)
- Saat seuraavan virheilmoituksen (klikkaa Ok):

Kuva

- VB-editori siirtyy Break -tilaan

Kuva

- Virhetilanteen aiheuttanut komento tai proseduuri on korostettu ja otsikkoon ilmestyy teksti [break]
- Siirry Design -tilaan klikkaamalla kuvaketta Design Mode.
3.
- Lisää Worksheet -sanan perään s (Worksheets("Shee...) ja suorita makro uudelleen.
- Nyt suoritus onnistuu ja välilehti suojataan!
- Excel'in Review -valikon Protect Worksheet -teksti vaihtuu:

Kuva

- Poista suojaus manuaalisesti!

4.
- Editoi Module1:n koodi seuraavasti:

Sub Test()

Debug.Print Worksheets("Sheet1").Protect
Debug.Print ActiveCell.Address(rowAbsolute:=False, columnAbsolute:=False)

End Sub


- Huomaa sulut alemmassa komennossa.
- Varmista että Immediate -ikkuna on auki (Ctrl-G) ja suorita makro (F5).

Kuva

- ensimmäinen koodirivi ei tuottanut Immediate'en mitään (tyhjän rivin), koska Protect -metodi ei palauta mitään arvoa.
- toinen koodirivi tuotti ikkunaan (nykyisen ActiveCell'in) osoitteen, ts. Address -metodi palautti (String -tyyppisen) arvon.

Tehtävä 2-1 : Kaksi tärkeää komentoa

Sijoita jokaiseen (suurempaan) makro-sovellukseen aina seuraavat komennot:
- koodin alkuun muuttuja-määrittelyjen jälkeen (Excel'in oletusarvot muutetaan):

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


- koodin loppuun juuri ennen End Sub -komentoa (oletusarvot palautetaan):

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


- ScreenUpdating -ominaisuus määrittää suoritetaanko ruudunpäivitys jokaisen komennon jälkeen vai ei.
- Calculation -ominaisuus määrittää kaavojen laskentamoodin.
Oletusarvo on Automatic, jolloin jokaisen solumuutoksen jälkeen lasketaan kaikki ne työkirjan kaavat, joihin kyseisen solun arvo vaikuttaa.

Nämä kaksi ominaisuutta vaikuttavat makron suoritusnopeuteen usein hyvin radikaalisti.
Tästä saa hyvän käsityksen seuraavasta muutaman vuoden takaisesta sovelluksesta.

Lataa ja tallenna Kurssi -kansioon:
https://drive.google.com/file/d/0B5MepY ... sp=sharing

Suorita rar-pakkauksessa oleva sovellus tennis-servicegame.xlsm.
Sovellus on tenniksen syöttöpeli-simulaattori.
Simulaattori laskee mikä on pelaajan todennäköisyys voittaa oma syöttövuoronsa kun tiedetään millä todennäköisyydellä hän voittaa syöttämänsä pallon (Serve win prop., kuvassa 60%).

Kuva

Alkutilanteessa koodin ScreenUpdating ja Calculation -komennot on merkitty kommenteiksi, jolloin ne jäävät makroa ajettaessa suorittamatta.

Kuva

Omalla koneellani makron suoritus tällöin kesti 148 sekuntia yo. kuvan asetuksilla.
Kun kommentti-merkit poistetaan ja makro ajetaan uudelleen samoilla asetuksilla lyhenee suoritusaika merkittävästi.

Kuva

Tällä muutoksella makron suoritusaika omalla masiinalla oli n. 1 sekunti. Melkoinen nopeusero!
Makro pelasi 25000 kokonaista tenniksen syöttövuoroa 1 sekunnissa, tietokoneet ovat huiman nopeita..

Pisteitä

Pisteitä yhteensä: 18. Antamasi peukut: 0.

Minikommentit

28.10.2014 03:36 <J00nas> Minulla tulee tuota suojausta tehdessä, kun lisää tuon s:n,

28.10.2014 03:37 <J00nas> että run-time error '9': subscript out of range

28.10.2014 03:38 <J00nas> Mikäs tuossa nyt sitten menee väärin, kun ohjeiden mukaan

28.10.2014 03:38 <J00nas> tuon harjoituksen tein?

28.10.2014 04:13 <S.Mäenala> Joonas, Sinulla ilmeisesti ei ole työkirjassa välilehteä, ..

28.10.2014 04:15 <S.Mäenala> jonka nimi on Sheet1. Muita subscripteja komento ei sisällä.

28.10.2014 04:21 <J00nas> Aivan nimen piti tosiaan olla samalla kielellä, kun se

28.10.2014 04:21 <J00nas> ite excelissäkin on


S.Mäenala
Avatar
Jäsen
Viestit: 1190
Liittynyt: 18.01.2011, 19:17
Pisteitä: 3733
Paikkakunta: Kamppi

Re: VisualBasic - ohjelmointi - kurssi

Viesti Kirjoittaja S.Mäenala »

2.3 Option Explicit

Option Explicit on kääntäjä-direktiivi (kääntäjä on ohjelma, joka muokkaa meidän kirjoittamamme komennot yksi kerrallaan tietokoneen ymmärtämään muotoon).
- Option Explicit -direktiiviä käytettäessä kääntäjä olettaa että ohjelmoija esittelee (declares) kaikki muuttujat ennenkuin niitä käytetään.

Miksi?
A.
- oletetaan että olemme antaneet muuttujalle lvv arvon 0.22 (lvv = 0.22)
Laskemme tuotteen vähimmäismyyntihinnan kaavalla vmh = (1 + llv) * veroton hinta
Kirjoitimme vahingossa kaavaan muuttujan lvv tilalle llv.
- ilman Option Explicit -ohjetta kääntäjä otaksuu että llv on uusi muuttuja ja antaa sille normaaliin tapaan lähtöarvoksi nollan (0).
Seurauksena on paha bugi, jonka etsiminen 1000 rivin koodista saattaa olla hermoja raastavaa puuhaa.
- Option Explicit -direktiiviä käytettäessä kääntäjä ilmoittaa virheestä ja korostaa kooditekstistä esittelemättömän muuttujan! Virhe on helppo korjata.
B.
- kääntäjä määrittelee ilman esittelyä olevat muuttujat aina Variant -tyyppisiksi. Ne kuluttavat paljon muistia.

2.4 Muuttujat

Muuttujat voivat määrittelyltään edustaa useita eri data-tyyppejä. Ne on esitelty kuvassa taulukkona:

Kuva


Muuttujan vaikutusalue (Scope)

Kuva

Paikalliset muuttujat esitellään Dim -lauseella (~dimension ~ulottuvuus):

Kuva

- Kuvan strEtunimi -muuttuja on määritelty tekstiksi (merkkijono-muuttujaksi).
- esimerkissä on käytetty ns. unkarilaista merkintäkäytäntöä (Hungarian notation). Siinä muuttajan nimeen lisätään sen datatyyppiä kuvaava etuliite.
String -muuttujille liite on tavallisesti str tai s.
- Muutujanimet ovat kirjainkoosta riippumattomia (non-case-sensitive).
Nimen täytyy alkaa kirjaimella (vältä ääkkösiä!!). VBA:n varattuja sanoja ei luonnollisestikaan saa käyttää muuttujaniminä (esim. Function).

Vihje:
Vaikka eri proseduurien ja moduulien nimiavaruudet ovat erillisiä, kannattaa välttää
saman nimen käyttämistä eri yhteyksissä.

- Muuttujan strEtunimi arvo asetetaan sijoitusoperaattoria ( = ) käyttäen näin:
strEtunimi = "Tuomo"

- Jos tämän muuttujan arvoksi asetetaan vaikkapa 124 (strEtunimi = 124), Excel tekee automaattisen tyyppi -muunnoksen (type conversion).
Luvusta 124 tulee merkkijono "124".
- Tämä ei ole tyylikästä ohjelmointia. Kokenut ohjelmoija käyttäisi tässä VBA:n kirjastofunktiota CStr() (~convert to string):
strEtunimi = CStr(124)

Huom!
Objekti -muuttujien arvo asetetaan käyttäen Set -lausetta:

Kuva

Vihje:
VBA:n avainsanat (esim. Sub, Dim, As, String, ..) voidaan kirjoittaa pienin kirjaimin.
Sensijaan esimerkiksi muuttujanimeä strEtunimi esiteltäessä, kirjain E on kirjoitettava isolla kirjaimella jos sen halutaan nimessä olevan iso.
Esittelyn jälkeen nimi voidaan kirjoittaa pienin kirjaimin.

Muuttujan alustaminen
VBA:ssa muuttujat alustetaan esittelyn yhteydessä automaattisesti:
- merkkijono-muuttujat saavat arvon "" ( = tyhjä merkkijono, kaksi peräkkäistä lainausmerkkiä)
- numeeriset muuttujat saavat arvon nolla ( = 0)
- totuusarvomuuttujat saavat arvon False ( = epätosi, 0)
- variant -muuttujat saavat arvon Empty ( = tyhjä arvo)
- objektimuuttujat saavat arvon Nothing ( = niillä ei ole nimettyä instanssia l. esiintymää)

- Kun muuttuja määritellään lokaalisti (=proseduurin sisällä, kuten tässä) se on käytettävissä vain kyseisessä proseduurissa (Sub Muuttujat() ).
Se lakkaa olemasta (sen muistivaraus poistetaan) kun proseduuri on suoritettu.

- Jos muuttuja määritellään Dim -lauseella proseduurien ulkopuolella:

Kuva

, se on käytettävissä kaikissa saman moduulin proseduureissa. Se myös säilyttää arvonsa eri kutsujen välillä.

- Jos muuttuja halutaan määritellä globaalisti, käytetään Public -lausetta.
Tällöin muuttujan näkyvyysalue kattaa kaikki työkirjan moduulit. Se säilyttää arvonsa kunnes työkirja suljetaan.

Kuva

Muuttujatyyppejä ovat mainittujen lisäksi esimerkiksi enumeraatiot ja taulukko-muuttujat (array ~taulukko-muuttuja, matriisi)

2.5 Vakiot (Constants)

Vakiot noudattavat samaa datatyyppi-jaottelua kuin muuttujatkin.
Ohjelmoija voi VBA:n oman vakio-kirjaston lisäksi määritellä myös omia vakioitaan.

Kuva


Harjoitus 2-3

Varsinaisten ohjelmointiesimerkkien viritteleminen on edelleenkin hankalaa mutta jatketaan tutustumista makrojen yleisiin ominaisuuksiin.

Makro voidaan käynnistää monella tavalla. Tässä harjoituksessa makro käynnistetään kaksoisklikkauksella.

1.
- Avaa uusi työkirja, tallenna se nimellä 04-Macro_Trigger.xlsm

2.
Kopioi allaoleva taulukko soluihin B2 - G21

Koodi: Valitse kaikki

0	1	2	0	2	1
1	0	2	1	3	1
0	0	0	4	0	4
1	0	1	1	2	1
1	0	0	2	1	2
1	0	1	0	2	0
1	0	0	2	1	2
1	0	2	0	3	0
1	0	1	1	2	1
2	0	2	1	4	1
0	0	2	1	2	1
0	0	2	0	2	0
3	0	0	1	3	1
0	1	1	0	1	1
1	0	1	1	2	1
3	0	2	0	5	0
1	0	3	0	4	0
1	1	1	1	2	2
2	0	2	1	4	1
1	0	1	3	2	3

3.
- Siirry VB-editoriin (Alt-F11) ja kaksoisklikkaa Project -ikkunassa riviä Sheet1.
- Kopioi Sheet1:n koodi-ikkunaan seuraava koodi:

Koodi: Valitse kaikki

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim lRow As Long
Dim rng As Range
Dim iSum As Integer

Application.ScreenUpdating = False
Range("J:J").ClearContents
lRow = Target.Row
Cancel = True
If lRow = 1 Or lRow > 21 Then
    Cells(lRow, 10).Value = "klikkasit tyhjää riviä"
Else
    For Each rng In Range(Cells(lRow, 2), Cells(lRow, 7))
        iSum = iSum + rng.Value
    Next rng
    Cells(lRow, 10).Value = "tämän rivin summa on " & iSum
End If
Set Target = Nothing
Application.ScreenUpdating = True

End Sub
4.
- Siirry Excel'iin (Alt-F11) ja tallenna työkirja!!
- Kaksoisklikkaa jotakin solua lähellä välilehden yläkulmaa.

Tehtävä 2-2 (helpompi)

Muuta Sheet1:n koodia siten että J-sarakkeen sijaan soluun B23 tulostuu teksti "rivin summa on 10" (esimerkiksi).

Tehtävä 2-3 (vaikeampi)

Kirjoita Sheet1:lle koodi, joka kääntää harjoituksessa olevat toiminnot sarakkeille.
Ts. siten että jos saraketta C klikataan soluun C23 tulee teksti "sarakkeen summa on 3".

Tehtävät saattavat vasta-alkajalle olla liian vaikeita. Ei kannata huolestua, nyt kun Getting Started on vähitellen saatu valmiiksi varsinaiset ohjelmointiharjoitukset aloitetaan aivan alusta.

Pisteitä

Pisteitä yhteensä: 18. Antamasi peukut: 0.

Minikommentit


S.Mäenala
Avatar
Jäsen
Viestit: 1190
Liittynyt: 18.01.2011, 19:17
Pisteitä: 3733
Paikkakunta: Kamppi

Re: VisualBasic - ohjelmointi - kurssi

Viesti Kirjoittaja S.Mäenala »

Tehtävien 2-2 ja 2-3 ratkaisut

Tehtävien tarkoituksena oli ainoastaan esitellä makrojen ominaisuuksia. Koodien yksityiskohtia ei ole vielä tarpeen ymmärtää.
Nämä kaksi oli suunnattu vähän edistyneemmille, jotta eivät pitkästyisi.

Käydään tehtävät tällä kertaa yksityiskohtaisemmin läpi.

Edeltävän harjoituksen koodi oli seuraava:

Kuva

Rivi 1: Kts. kohta 2.3 yllä
Rivi 2: Tyhjä rivi (ei merkitystä)
Rivi 3: Harjoitus hyödyntää VBA:n tapahtumakäsittelijä - (EventHandler -) proseduuria nimeltään Worksheet_BeforeDoubleClick
- normaalisti Excel'issä kaksois-klikkauksella siirrytään solun editointi -tilaan (solun sisältö voidaan kirjoittaa).
- BeforeDoubleClick -proseduuri määrittää tehdäänkö tätä ennen joitain asioita.
- proseduuri ottaa kaksi muuttujaa:
- - Target: Range -objekti, joka edustaa kaksois-klikattua solua (tämän käyttöjärjestelmä ilmoittaa Excel'ille)
- - Cancel: totuusarvo-muutuja (Boolean), joka määrittää suoritetaanko normaali kaksois-klikkaus -toiminto tämän proseduurin jälkeen vai ei. Default = False (normaali kaksois-klikkaus -toiminto suoritetaan proseduurin jälkeen).
Rivi 4: Tyhjä rivi
Rivi 5: Määritellään Long -tyypin muuttuja nimeltään lRow. Rivi- ja sarakenumeroihin viittaavat muuttujat määritellään Long -tyyppisinä.
Rivi 6: Määritellään objekti -muuttuja nimeltään rng tyypiltään Range -objekti.
Rivi 7: Määritellään Integer -tyypin muuttuja iSum. Integer ~ [ -32000 --> +32000]
Rivi 8: Tyhjä rivi
Rivi 9: Kts: kohta Tehtävä 2-1
Rivi 10: Tyhjennetään sarake J, poistetaan edellisen kaksoisklikkauksen tulostus.
Rivi 11: Annetaan muuttujan lRow arvoksi kaksois-klikatun solun rivinumero.
- Target muuttujan arvo saatiin käyttöjärjestelmältä kun se ilmoitti Excel'ille kaksois-klikkauksesta.
- Excel katsoi onko BeforeDoubleClick-nimistä proseduuria määritelty työkirjalle ja kun sellainen löytyi Excel käynnisti proceduurin ilmoittaen muuttujien Target ja Cancel arvot (viittaus klikattuun soluun Range -objektina ja False).
Rivi 12: Estetään normaali kaksois-klikkaus -toiminto (kirjoitustilaan siirtyminen) proseduurin jälkeen asettamalla Cancel -muuttujan arvoksi True (Tosi).
Rivit 13 - 20: If -lauseella tutkitaan oliko kaksois-klikkaus tyhjällä rivillä, eli onko muuttujan lRow arvo =1 tai suurempi kuin 21.
- jos oli klikattu tyhjää riviä asetetaan kohdesolun arvoksi "klikkasit tyhjää riviä" -merkkijono (solun osoite: rivi = lRow, sarake = 10 (J-sarake))
- muussa tapauksessa solun arvoksi asetetaan teksti "tämän rivin summa on " ja rivillä olevien lukujen summa. Summa lasketaan käymällä läpi kaikki Target -rivin solut sarakkeista 2 --> 7.
Rivi 21: objekti muuttujan varaama muistialue vapautetaan (itse muuttujan arvoa ei tarkasti ottaen voida muuttaa koska sen määreenä on ByVal).
- tässä esimerkissä riviä ei välttämättä tarvitse kirjoittaa.
Rivi 22: Rivillä 9 poistettu näytönpäivitys palautetaan.
Rivi 23: Tyhjä rivi
Rivi 24: Proseduurin suoritus päättyy.

Tehtävässä 2-2 yo. koodi piti muuttaa niin että tulostus saadaan soluun B23.
Tämä saadaan aikaan niin että riveillä 14 ja 19 määrittely Cells(lRow, 10) korvataan määrittelyllä Cells(23,2) tai vaihtoehtoisesti määrittelyllä Range("B23")
Harjoituksen koodin rivi 10 jää turhaksi ja voidaan poistaa.

Tehtävän 2-3 ratkaisu:

Avaa uusi tyhjä työkirja. Siirry VBA:han (Alt-F11).
Kaksoisklikkaa Project -ikkunassa riviä Sheet1.
Kopioi Sheet1:n koodi -ikkunaan seuraava koodi:

Koodi: Valitse kaikki

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim lColumn As Long
Dim rng As Range
Dim iSum As Integer

Application.ScreenUpdating = False
ActiveSheet.Rows(23).ClearContents
lColumn = Target.Column
Cancel = True
If lColumn = 1 Or lColumn > 7 Then
    Cells(23, lColumn).Value = "klikkasit tyhjää saraketta"
Else
    For Each rng In Range(Cells(2, lColumn), Cells(21, lColumn))
        iSum = iSum + rng.Value
    Next rng
    Cells(23, lColumn).Value = "tämän sarakkeen summa on " & iSum
End If
Set Target = Nothing
Application.ScreenUpdating = True

End Sub

Pisteitä

Pisteitä yhteensä: 9. Antamasi peukut: 0.

Minikommentit


100% 200€ bonus librabet.com.

Katso kaikki vedonlyöntibonukset.

Vastaa Viestiin