Transcript luento4_VBA2

T-106.1061 Tietotekniikan työkurssi
Luento 4
VBA 2
T-106.1061 Tietotekniikan työkurssi
Luento 4
Luennon sisältö
• ohjelmointikielen perustoiminnot
–
–
–
–
–
–
–
tiedon siirto ohjelman ja ulkomaailman välillä
tiedon tallentaminen
matemaattiset toiminnot
vertailutoiminnot
ohjelman suorituksen ohjaaminen
ohjelman rakenteen määrittely
kielen määrittely
• hyvän koodin kirjoittaminen
• VBA sovelluksissa
• makrojen nauhottaminen
Kalvo 2
T-106.1061 Tietotekniikan työkurssi
Luento 4
Luennon tavoitteet
• kerrataan edellisellä luennolla opittuja asioita
• ohjelmoinnin perustoiminnot käydään loppuun
• ymmärrät, mitä pitää ottaa huomioon hyvää koodia
kirjoitettaessa
• opit esimerkkien avulla VBA:n käyttöä sovelluksissa:
Excel, PowerPoint ja Word
• tiedät, mihin ja miten makrojen
nauhoittamista voi käyttää
Kalvo 3
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 4
Muuttujat
• ohjelmassa tieto tallennetaan muuttujiin, jotka ovat
tietokoneen muistista varattuja paikkoja
• muuttujalla on tyyppi, joka ilmaisee siihen tallennettavan
tiedon laadun
• muuttujan määrittely
– Dim nimi As String
– Dim luku As Integer
– määrittelypakko: Option Explicit
• staattiset ja dynaamiset muuttujat
• Private / Public
• vakiot (Const)
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 5
Muuttujien perustyypit
• Luvut
– Byte
– Integer
– Long
647
– Single
– Double
•
•
•
•
kokonaisluku väliltä 0...255
kokonaisluku väliltä -32 768...32 767
kokonaisluku väliltä -2 147 483 648...2 147 483
desimaaliluku väliltä n. -3,4x1038...3,4x1038
desimaaliluku väliltä n. -1,7x10308...1,7x10308
String
merkkijono
Boolean totuusarvo, true tai false
Date
päivä
muuttuja voi olla myös määrittelemätön (Variant)
T-106.1061 Tietotekniikan työkurssi
Luento 4
Muuttujien käyttö
•
•
•
•
sijoitusoperaattorina =-merkki
Muuttujan tyyppi: ctrl-i
muuttujan arvoa voidaan muuttaa: m=m+2
muuttujan arvo voidaan välittää funktiolle ja saada
funktiolta paluuarvona
• muunnokset eri muuttujatyyppien välillä
– merkkijonon muuttaminen lukuarvoksi Val-funktiolla
– funktio palauttaa nollan, jos muunnos epäonnistuu eli
merkkijono ei sisällä lukua
Kalvo 6
T-106.1061 Tietotekniikan työkurssi
Luento 4
Muuttujien käyttö
Public Sub Swap (X As Long, Y As_
Long)
Dim Tmp As Long
Tmp = X
X = Y
Y = Tmp
End Sub
Kalvo 7
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 8
Esimerkki Val-funktion käytöstä
Dim teksti As String
Dim luku As Integer
teksti = ”12345”
luku = Val(teksti)
teksti = ”ei luku”
luku = Val(teksti)
’ Luku sisältää nyt arvon 12345
’ Luku sisältää nyt arvon 0,
’ koska muunnettava
’ merkkijono ei ollut luku
T-106.1061 Tietotekniikan työkurssi
Luento 4
Taulukot
• Taulukon luominen
’Luodaan 10-alkioinen taulukko
Dim taulukko(1 To 10) As String
’ Luodaan 64-alkioinen taulukko
Dim shakkilauta(1 To 8, 1 To 8) As Integer
• Taulukon alkioon viittaaminen
taulukko(4) = ”Maija”
shakkilauta(4,5) = 0
Kalvo 9
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 10
Ohjelman rakenne
• aliohjelma on itsenäinen kokonaisuus, joka jollain
lähtöarvoilla suorittaa tietyn tehtävän ja mahdollisesti
palauttaa tietyn arvon
• aliohjelmaa voidaan kutsua eri paikoista
– paikallinen
– yleinen
• aliohjelmilla ohjelmiin
saadaan selkeyttä
• ohjelma saadaan jaettua
pienempiin ymmärrettäviin osiin
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 11
VBA:n rakenteet
• Function-rakenne
–
–
–
–
voidaan antaa lähtöarvoja eli argumentteja
argumentit voivat olla pakollisia tai vapaaehtoisia (Optional)
palauttaa aina arvon, paluuarvon
voidaan käyttää suoraan Excelissä
• Sub-rakenne
– voidaan antaa lähtöarvoja samoin kuin edellä
– ei palauta arvoa
– kutsutaan makroksi, jos ei ole lähtöarvoja
• rakenteen näkyminen: Private ja Public
• rakenteen käynnistäminen: Visual Basicista, automaattisesti ja
sovelluksesta
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 12
Aliohjelmien käyttö
•
•
•
•
ohjelmaan saadaan rakennetta
selkeyttä
mahdollista hahmottaa helpommin
samaa ”palikkaa” voidaan käyttää useaan kertaan eri
puolilta ohjelmaa
• aliohjelmien käyttöä ei saa korvata hypyillä
T-106.1061 Tietotekniikan työkurssi
Luento 1
Argumenttien käytön periaatteet
Sub Ohjelma
lainamäärä1
korko1
lainaaika
kustannus1
Function LainaKustannus
lainamäärä
korko
lainaaika
LainaKustannus
Function AnnaAika
lainamäärä1
korko2
maxaika
lainamäärä1
korko2
lainamäärä
korko
kustannus1
lainamäärä
korko
AnnaAika
Sub Muunna
lainamäärä
korko
Kalvo 13
T-106.1061 Tietotekniikan työkurssi
Luento 4
Argumenttien välitys ja paluuarvot
• aliohjelmat toimivat itsenäisesti
• ei globaaleja muuttujia
• annetaan kaikki aliohjelman tarvitsema tieto
argumentteina
• välitetyn argumentin arvo voi muuttua tai ei
– ByVal: arvo ei muutu
– ByRef: arvo muuttuu
• Optional – pakollinen vai valinnainen
• tietotyypin ilmoittaminen As
Kalvo 14
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 15
Muisti
ByVal ja By Ref
Pääohjelma
a =
Sub
a
End
a =
Sub
a
End
12
Foobar(ByVal a)
=13
Sub
12
Foobar(ByRef a)
= 13
Sub
a
12
Foobar
a
13
Pääohjelma
a
13
Foobar
a
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 16
Kielen määrittely – syntaksi
• jotta tietokone ymmärtää ohjelmakoodia, täytyy olla olemassa
jokin sovittu “kielioppi”
• syntaksi ilmoittaa mm
– miten käskyt syötetään
– missä järjestyksessä asiat ovat
– mitkä sanat ovat varattuja
T-106.1061 Tietotekniikan työkurssi
Luento 1
VBA kielen määrittely – syntaksi
Sub Proseduuri()
…
End Sub
Function Funktio()
…
Funktio=…
…
End Function
•sovittu tietyt käskyt (varatut sanat)
•sovittu tietty järjestys
•sovittu tietyt operaatiot
•esimerkiksi Application.Workbook
Kalvo 17
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 18
Säännöt proseduurien, argumenttien, muuttujien ja
vakioiden nimeämisestä
• ensimmäisen merkin on oltava kirjain
• nimessä ei saa olla välilyöntejä, pisteitä (.),
huutomerkkejä (!) tai merkkejä @, &, $, #
• varattuja käskysanoja ei saa käyttää (esim. If, Goto ja
True)
• nimiä, joilla on sama määrittelytaso, ei voi käyttää
useita kertoja (esimerkiksi yhdessä proseduurissa kaksi
kertaa sama muuttujanimi)
T-106.1061 Tietotekniikan työkurssi
Luento 4
VBA:n ohjeet
Kalvo 19
?
• F1-näppäimellä ongelmakohdan päällä
• kursivoitu korvataan tarvittavalla objektilla tai vastaavalla
– esim. objekti.Activate
• lihavoidut ja kursivoidut sanat ovat funktion
argumentteja, hakasulkeissa ne ovat valinnaisia
– esim. MsgBox(prompt[;buttons][;title][;helpfile;context])
• aaltosulkeet ja suora viiva ilmaisevat pakollisen valinnan
kahden kohdan välillä
– esim. Option Compare {Binary | Text}
T-106.1061 Tietotekniikan työkurssi
Rivin katkaisu
_ merkki.
Esim:
Sub Esim()
MsgBox _
”Hello World”
End Sub
Luento 4
Kalvo 20
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 21
Mistä funktioita löytää / miten kirjoitan koodia?
• View - Object Browser tai F2
– Classes
• Tools – Options
– Auto Quick Info
• Helppi
• Koodia on helppo aloittaa tekemään napin tai formin
avulla
• Muutoin uusia aliohjelmia voi yksinkertaisesti kirjoittaa
koodi-ikkunaan tai Insert-valikosta
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 22
Esimerkkifunktioita
• Count – alueen solujen laskeminen
– Application.WorksheetFunction.Count(alue)
• IsEmpty – onko solu tyhjä
If IsEmpty(ActiveSheet.Range(”A1”).Value)
then
...
End If
• CStr – datan muunto merkkijonoksi
– Esim: postinumeroiden konvergointi tekstiksi
MsgBox CStr(Now)
MsgBox CStr(Chr(169))
(→© )
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 23
Tekstijonon käsittely
Mid-funktio
MsgBox Mid(”testijono”, 3, 4) ’stij
MsgBox Mid(”testijono”, 6)
’jono
MsgBox Mid(”testijono”, 10)
’tyhjä
Left-funktio – postinumeron erottaminen tekstistä
MsgBox Left(”testijono”, 1)
’t
MsgBox Left(”testijono”, 5)
’testi
Right-funktio
MsgBox Right(”testijono”, 4)
’jono
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 24
Lisää merkkijonofunktioita
Haku jonosta: InStr
MsgBox InStr(1,”Esimerkki”,”kk”)
’7
MsgBox InStr(8,”Esimerkki”,”kk”)
’0
Välilyöntien poisto merkkijonosta: LTrim, RTrim, Trim
MsgBox Trim(”
tes
ti
”)
Välilyöntien lisääminen merkkijonoon: Space
MsgBox Space(12-Len(”testi”))
’tulostaa 12 merkin pituisen testi-sanan
T-106.1061 Tietotekniikan työkurssi
Luento 4
MsgBox valintaikkunana
• MsgBoxiin saa ehdollisia valintoja (Kyllä / Ei / Cancel)
• Lyhyt esimerkki:
Private Sub ValintaNappi_Click()
Dim Vastaus As Long
Vastaus = MsgBox("Oletko aivan
varma?",_
vbYesNo, "Vahvista")
If Vastaus = vbYes Then ...
Else ...
End If
End Sub
Kalvo 25
T-106.1061 Tietotekniikan työkurssi
Luento 4
Eri komentojen syntakseja
If condition Then
[statements]
[Else
[elsestatements]]
End If
[Public | Private] [Static] Function name_
[(arglist)] [As type]
[statements]
[name = expression]
End Function
Kalvo 26
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 27
VBA:n objektit – toiminnot, metodit, menetelmät...
•
•
objekteja: Excel, työkirja, solu
ominaisuudet ja niiden muuttaminen
– Worksheets("Taul1").Range("A1").Value = 3,14159
– Application.Workbooks("esim.xls").Worksheets("T
aul1").Name = "taulukko1"
•
objekteille suoritettavat toiminnot ja niihin liittyvät metodit
– Worksheets("Taul1").Activate
– Workbooks.Open("esim.xls")
•
•
erotetaan pisteellä objektista tai toisesta ominaisuudesta
voi palauttaa arvon
– MsgBox ”Avoinna olevia työkirjoja on ” & _
Application.Worksheets.Count
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 28
VBA:n objektit – toiminnot, metodit, menetelmät... (2)
• lyhytviittaukset
–
–
–
–
objekteja, joiden kohde osoittaa aina aktiiviseen objektiin
ActiveSheet
ActiveCell
Esim. ActiveCell.Value = 5
• oikean objektin löytäminen
– Help-valikko
– makronauhuri
– Object Browser-ikkuna
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 29
Esimerkki: alue argumenttina
Public Function Karvo(alue as Range) As Double
Dim b As Range
Dim a As Double
a=0
For Each b In alue
a=a+b.value
next b
Karvo = a / alue.count
End Function
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 30
Esimerkki: solun arvon muuttaminen
Worksheets(”Taulu”).Range(”A1”).Value =
3
Range(”B1”).Formula = ”=5-10*rand”
Range(”C1:E3”).Value = 6
T-106.1061 Tietotekniikan työkurssi
Luento 4
Esimerkki: Excelin funktion käyttäminen
Cells(1, 1).Value=
WorksheetFunction.Round(2.34534, 2)
Kalvo 31
T-106.1061 Tietotekniikan työkurssi
Luento 4
(Esimerkki: Workbookin lisäys)
Sub CreateAndSave()
Set newBook = Workbooks.Add
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
newBook.SaveAs Filename =fname
End Sub
Kalvo 32
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 33
Esimerkki: objektimuuttujan käyttö
• objektimuuttujat asetetaan osoittamaan olemassa oleviin objekteihin Setkäskyllä
Public Sub Esim()
Dim a,b As Excel.Range
Set a = Worksheets(”Taul1”).Range(”A1”)
Set b = Worksheets(”Taul1”).Range(”A1”)
a.Value = 6
MsgBox b
b.Value = 3
MsgBox a
End Sub
’ Ikkunassa näytetään arvo 6
’ Muutetaan samalla a:n arvoa
’ Ikkunassa näytetään arvo 3
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 34
Makronauhuri
• makronauhuri on kätevä apuväline, kun etsit Visual Basic
-menetelmiä ja ominaisuuksia, joita haluat käyttää
• kaikkea et voi nauhoittaa
–
–
–
–
–
ehdolliset haarakohdat
silmukkarakenteet
lasketut valinnat ja viittaukset
jotkin sisäiset funktiot ja valintaikkunat
omat valintaikkunat
• (älä käytä hiirtä nauhoittaessasi)
• voit parantaa makroja muokkaamalla moduliin
nauhoitettua koodia
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 35
Makronauhurin käyttö
•
•
•
•
avaa Developer-valikosta Code - Record Macro
tee toiminnot Excelissä kuten muutenkin
pysäytä nauhoitus Stop Recording-painikkeella
makro on käytössäsi Module-ikkunassa
• Makron ajaminen: Developer – Macros - < makron nimi>
T-106.1061 Tietotekniikan työkurssi
Luento 4
Hyvän koodin kirjoittaminen
• ohjelman rakenne on hyvä
– aliohjelmien käyttö (sub ja function)
– ohjausrakenteiden käyttö (valinnat ja toistot)
• argumenttien välitys on oikein
– ei globaaleja muuttujia
– paluuarvoja käytetään hyväksi
• käytetty järkevästi kielen mahdollisuuksia
• koodi on järjestelty hyvin
– rivien käyttö
– sisennys
• kommentointi ja havainnolliset nimet
Kalvo 36
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 37
Kommentit
• selkokieliset huomautukset ohjelmakoodissa
• kommentointi on tärkeää – lisää selkeyttä
– myöhemmin helpompi ”muistaa” omaa koodia
– muiden helpompi ymmärtää koodia
– helpottaa ohjelman ylläpitoa
•
•
•
•
heittomerkin (’) tai (Rem)-sanan jälkeen
kielen tulkki ohittaa kommentit
kommentteja monimutkaisiin kohtiin – ei liikaa
Visual Basic on melko selkeää kieltä, joten se ei vaadi
kovin paljon kommentteja
T-106.1061 Tietotekniikan työkurssi
Luento 4
Ohjelmoinnin virheiden välttäminen
• ohjelmoinnissa tulee aina virheitä
• syntaksi-virheet
– kielen määrittelyssä on virhe
– ohjelma ei pysty kääntämään koodia
• suorituksen aikaiset virheet
• loogiset virheet
– kielen määrittely on oikein ja koodi kääntyy
– ohjelma ei kuitenkaan toimi halutulla tavalla
• loogiset virheet on vaikeampia löytää
• virheenkorjaus, debugging – työkalut
Kalvo 38
T-106.1061 Tietotekniikan työkurssi
Luento 4
VBA koodin optimointi
• tarpeettomien nauhoitettujen osien poisto
• variant-muuttujan käytön minimointi
• Toistorakenteiden järkevä käyttö
– OLE-viittausten välttäminen With-lauseella
– For Each…Next -silmukan käyttäminen
• muut
–
–
–
–
indeksilukujen selkeä käyttäminen
objektin aktivoinnin ja valitsemisen minimointi
määriteltyjen objektityyppien käyttö
vakioiden käyttäminen
Kalvo 39
T-106.1061 Tietotekniikan työkurssi
Luento 4
Esim. With-lauseen käyttämisestä
With
Application.Workbooks(”esim.xls”)._
Worksheets.Cells(1,1)
.Value = 0
.Font.Name = ”Arial”
.Font.ColorIndex = 3
End With
EI SIIS NÄIN
Application.Workbooks(”esim1.xls”).Worksheets._
Cells(1,1).Value = 0
Application.Workbooks(”esim1.xls”).Worksheets._
Cells(1,1).Font.Name = ”Arial”
Kalvo 40
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 41
VBA:n käyttö Office-sovelluksissa
• objektimallilla viittaaminen
• Excelin toiminnot ja viittaaminen
– taulukkosivut
– kuvaajat
– solujen arvot
• PowerPoint
• Word
• Visual Basic on tehokas tapa saada lisää ominaisuuksia
sovelluksiin
T-106.1061 Tietotekniikan työkurssi
Luento 4
Mitä muuta VBA:lla voi tehdä?
• Visual Basic for Applications muissa sovelluksissa
– Word
– PowerPoint
– Access
• muodot ja piirrokset
• tekstintäyttöobjektit
• muut hauskat automaatiot
Kalvo 42
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kalvo 43
VBA:n vinkkejä
• ESC-näppäin tai CTRL+BREAK pysäyttää loputtoman silmukan
• voit jatkaa lausetta seuraavalle riville: kirjoita rivin loppuun
välilyönti ja alaviiva (_)
• lauseiden muodostamisen työkalut
–
–
–
–
–
–
–
automaattinen syntaksin tarkistus
vaadi muuttujan määrittely (Option Explicit)
automaattinen lauseen muodostin
automaattinen pikavihje
automaattinen arvovihje
sisennä automaattisesti
sarkainleveys
T-106.1061 Tietotekniikan työkurssi
Luento 4
Mihin VBA:ta voidaan käyttää Excelissä?
• funktiot Exceliin
• usein käytettyjen toimintasarjojen toisto
– muotoilut
– ulkopuolisen datan tuominen Exceliin
• sovellusten tekeminen muille käyttäjille
– personoidut taulukot
– käyttäjän antamien tietojen syöttö
• voidaan yhdistää muihin Excelin ominaisuuksiin
Kalvo 44
T-106.1061 Tietotekniikan työkurssi
Luento 4
Mitä tästä kaikesta oli hyötyä?
• opittuasi kerran ohjelmoimaan on muiden
ohjelmointikielten oppiminen ”helppoa”
• tiedät jotain siitä, mitä ohjelmointi on
–
–
–
–
peräkkäisiä valintoja ja toistoja
tiettyjä rakenteita
sovittuja kielen määrittelyjä
virheiden korjaamista 
• ymmärrät ehkä paremmin Office-ohjelmia, jotka on
suurelta osin tehty Visual Basicilla
• ohjelmoimaan oppii vain ohjelmoimalla…
Kalvo 45
T-106.1061 Tietotekniikan työkurssi
Luento 4
Kertaus luennon tärkeimmistä asioista
• arvot aliohjelmien välillä välitetään argumentteina
• kielen syntaksi on tärkeää osata, jotta kieltä pystyy
käyttämään
• Visual Basicia voi käyttää tehostamaan sovellusten
toimintaa
• makronauhuria käytettäessä
pitää muuttaa koodia käsin
Kalvo 46
T-106.1061 Tietotekniikan työkurssi
Kysymyksiä
Luento 4
Kalvo 47