VBA-perusteet erittäin lyhyesti

VBA (Visual Basic for Applications) on Microsoftin sovellusohjelmissa makrokielenä käytetty ohjelmointikieli. Seuraavassa käydään lyhyesti läpi erilaisia VBA:n rakenteita, joista makrojen muuttamisen tai kirjoittamisen yhteydessä voi olla suurestikin hyötyä.

Sisällysluettelo

Kommentointi

Visual Basicin kommentit erotetaan ohjelmakoodista heittomerkillä ('). Heittomerkki voidaan sijoittaa mihin tahansa ohjelmakoodin sekaan. Heittomerkin jälkeinen rivin osa on kokonaan kommenttia! Kommentteja voidaan siis sijoittaa myös suoritetavien rivien loppuun.

Sub kysy_luku() ' Kysy_luku alkaa

        ' Ohjelma kysyy käyttäjältä luvun ja
        ' sijoittaa sen aktiiviseen soluun

        ActiveCell.Value = InputBox("Anna luku") ' kysely ja sijoitus

End Sub ' Kysy_luku loppuu

Muuttujien määrittely ja tietotyypit

Muuttujien tietotyypit voivat olla seuraavat: Byte, Boolean, Integer, Decimal, Object, Long, Currency, Single, Double, Date, String, Variant.

Esimerkki muutaman tyyppisten muuttujien alustamisesta:

'Muuttujaan voidaan sijoittaa kokonaislukuja
Dim muuttuja as Integer
 
'Muuttujaan voidaan sijoittaa merkkijonoja
Dim muuttuja as String

'Muuttujaan voidaan sijoittaa mitä tahansa tietoa
Dim muuttuja as Variant

'Muuttujaan voidaan sijoittaa desimaalilukuja
Dim muuttuja as Double

'Muuttujaan voidaan sijoittaa mitä tahansa tietoa (Oletustyyppi on Variant)
Dim muuttuja

Erilaisia operaattoreita

Seuraavassa esitellään muutamia erilaisia operaattoreita.

Aritmeettiset operaattorit

Aritmeettisia operaattoreita esitellään muutaman esimerkin avulla.

Dim potenssi, osamaara, tulo, erotus, summa As Variant

' potenssi saa arvon 25
potenssi = 5^2

' osamaara saa arvon 2,5
osamaara = 5/2

' tulo saa arvon 10
tulo = 5*2

' erotus saa arvon 3
erotus = 5-2

' summa saa arvon 7
summa = 5+2

Vertailuoperaattorit

Vertailuoperaattoreita esitellään seuraavassa muutaman esimerkin avulla.

' Erisuuri kuin 
Luku1 <> Luku2

' Yhtäsuuri kuin 
Luku1 = Luku2

' Pienempi kuin 
Luku1 < Luku2

' Suurempi kuin 
Luku1 > Luku2

' Pienempi tai yhtäsuuri kuin
Luku1 <= Luku2

' Suurempi tai yhtäsuuri kuin
Luku1 >= Luku2

Liitosoperaattorit

Merkkijonojen liittäminen onnistuu muutaman operaattorin avulla, joita seuraavassa hieman havainnollistetaan.

merkkijono = merkkijono1 & merkkijono2
merkkijono = merkkijono1 + merkkijono2

Esimerkki liitosoperaattoreiden käytöstä: 

Sub liitos()

        Dim luku, solu As Variant
        
        ' Poimitaan A1 solusta luku
        luku = Range("a1").Value
        
        ' Muodostetaan poimitun luvun perusteella soluosoite.
        ' Jos solussa A1 oli luku 10, niin soluosoitteeksi tulee A10.
        solu = "A" &	 luku
        
        ' Kirjoitetaan soluun A10 tekstiä
        Range(solu).Value = "Tämän solun osoite saatiin!"

End Sub

Kommunikointi käyttäjän kanssa

Seuraavassa on esiteltynä muutamia käyttäjän kanssa kommunikointiin tarkoitettuja funktioita. Funktioilla voidaan informoida käyttäjää tapahtumista tai kysyä käyttäjältä tietoja.

Msgbox (Sanomaikkuna)

Syntaksi:
MsgBox(kehote [, painikkeet] [, ikkunaotsikko])

Seuraavassa esimerkissä on yksinkertainen sanomaikkunasta, jossa on pelkkä OK-painike. Palautettavaa arvoa voidaan haluttaessa tutkia muuttujan arvo avulla. Palautettava arvo riippuu painetusta painikkeessa sanomaikkunassa.

Esimerkki 1:

Sub viesti()
  Dim Arvo As Integer
  Arvo = MsgBox("Pelkkä OK-painike!", 0, "Koko ikkunan otsikko")
End Sub

Esimerkki 2:

Sub viesti()
  Dim Arvo As Integer
  Arvo = MsgBox("Yes, No ja Cancel -painikkeet", 3, "Koko ikkunan otsikko")
End Sub

Seuraavaan on listattu toisena parametrina annettavat painikevaihtoehdot.

Arvo Ikkunan muutos
0       Näyttää ainoastaan OK-painikkeen.
1       Näyttää OK ja Cancel -painikkeet.
2       Näyttää Abort, Retry ja Ignore -painikkeet.
3       Näyttää Yes, No ja Cancel -painikkeet.
4       Näyttää Yes ja No -painikkeet.
5       Näyttää Retry ja Cancel -painikkeet.
16      Näyttää Critical Message -ikonin. 
32      Näyttää Warning Query -ikonin.
48      Näyttää Warning Message -ikonin.
64      Näyttää Information Message -ikonin.

Seuraavassa listassa on erilaisten painikkeiden palauttamia arvoja! Painikkeiden arvojen käsittelystä enemmän myöhemmissä esimerkeissä!

Arvo    Painike
1       OK
2       Cancel
3       Abort
4       Retry
5       Ignore
6       Yes
7       No

Inputbox (Syöttöikkuna)

InputBox-ikkuna on helppo ja hyvä tapa kysellä käyttäjältä sovellukseen liittyviä tietoja. Seuraavassa lyhyt esimerkki tietojen kysymisestä.

Syntaksi:
InputBox(kehote[, ikkunaotsikko] [, oletusteksti] [, xpaikka] [, ypaikka])

Seuraavassa on lyhyt esimerkkin syöttöikkunan käytöstä.

Sub syottoruutu()
  Dim syote As String
  syote = InputBox("Ilmoitettava asia", "Otsikkotiedot")
End Sub

Muutamia tietorakenteita

IF-lause (Ehtolause)

Ehtolauseet ovat tarkoitettu erilaisten ehtojen testaamiseen. Ehtojen tai jonkin ehdon toteutuessa voidaan tehdä toimenpide, joka koostuu yhdestä tai useasta ohjelmointikielisestä lauseesta.

If ehto Then
        [lauseita]
[ElseIf ehtoja Then
        [lauseita] 
...
[Else
        [Lauseita]]
End If

Seuraavassa muutamia esimerkkejä ehtolauseen käytöstä.

Esimerkki 1:

Sub If_lause()
    
Dim Arvo As Integer
' Kysytään käyttäjältä lähteekö hän syömään
Arvo = MsgBox("Haluatko lähteä syömään?", 0, "Sanomaruudun otsikko")

' Tutkitaan käyttäjän painaman painikkeen palauttama arvo
If Arvo = 1 Then
    Arvo = MsgBox("Mennään syömään!")
End If
 
End Sub

Esimerkki 2:
Sub If_Else_lause()
    
Dim Arvo As Integer

' Kysytään käyttäjältä lähteekö hän syömään
Arvo = MsgBox("Haluatko lähteä syömään?", 1, "Sanomaruudun otsikko")

' Tutkitaan käyttäjän painaman painikkeen palauttama arvo
If Arvo = 1 Then
    Arvo = MsgBox("Mennään syömään!")
Else
    Arvo = MsgBox("Mennään sitten myöhemmin syömään!")
End If

End Sub

Esimerkki 3:
Sub If_Elseif_Else_lause()
    
Dim Arvo As Integer

' Kysytään käyttäjältä lähteekö hän syömään
Arvo = MsgBox("Haluatko lähteä syömään?", 3, "Sanomaruudun otsikko")

' Tutkitaan käyttäjän painaman painikkeen palauttama arvo
If Arvo = 6 Then
    Arvo = MsgBox("Mennään syömään!")

ElseIf Arvo = 7 Then
    Arvo = MsgBox("Mennään sitten toiste syömään!")
Else
    Arvo = MsgBox("Mennään sitten myöhemmin syömään!")
End If

End Sub

Monivalintalause

Monivalintalauseella voidaan korvata edellä esitettyjä ehtolauseita. Monivalntarakennetta kannattaa käyttää, jos testattavien ehtojen määrä kasvaa suureksi.

Select Case testi
[Case testitulos
        [lauseita]] 
...
[Case Else
        [Lauseita]]
End Select

Seuraavassa esimerkissä edellä käytetyt ehtolauseet on korvattu Select Case -rakenteella.

Sub Select_Case_lause()

Dim Arvo As Integer
Arvo = MsgBox("Haluatko lähteä syömään?", 3, "Sanomaruudun otsikko")

Select Case Arvo
Case 6
        Arvo = MsgBox("Mennään syömään!")
Case 7
    Arvo = MsgBox("Mennään sitten toiste syömään!")
Case Else
        Arvo = MsgBox("Mennään sitten myöhemmin syömään!")
End Select

End Sub

Erilaisia silmukkarakenteita

FOR-silmukka

For-silmukka on kiinteä silmukkarakenne, jonka avulla voidaan tehdä operaatioita ennalta tiedetty määrä. Operaatiot suoritetaan jostakin alkuarvosta loppuarvoon määrätyin askelarvoin.

Syntaksi:
For Laskuri = alkuarvo To loppuarvo [Step Askel]
[Lauseet]
[Exit For]
[Lauseet]
Next [Laskuri]

Esimerkki yksinkertaisen For-silmukkarakenteen käytöstä.

Sub For_silmukka()
' Esimerkki silmukkarakenteesta
' Tulostetaan luvut yhdestä kymmeneen
' aktiiviseen laskentataulukkoon
Dim Arvo As Integer
    For Arvo = 1 To 10
        Cells(Arvo, 1).Value = Arvo
    Next Arvo

End Sub

Do Loop -silmukka

Do Loop -silmukka sopii hyvin erilaisiin testeihin, joissa silmukoiden lukumäärä ei ole ennalta käsin nähtävillä. Silmukkarakenteesta on olemassa kaksi erillistä versioita, joissa toisessa silmukan sisällä olevat lauseet suoritetaan vähintään kerran. Toisessa rakenteessa silmukan sisään ei välttämättä edes mennä.

Rakenne 1:
Do [{While | Until} ehto]
[Lauseet]
[Exit Do]
[Lauseet]
Loop

Rakenne 2:
Do 
[Lauseet]
[Exit Do]
[Lauseet]
Loop [{While | Until} ehto]

Seuraavassa esimerkki kummastakin silmukkarakenteesta. Silmukkarakenteen eron huomaa antamalla solujen lukumääräksi nollan (0).

Esimerkki 1:
Sub Do_Loop_silmukka()

' Esimerkki silmukkarakenteesta
' Tulostetaan luvut yhdestä kymmeneen
' aktiiviseen laskentataulukkoon

Dim Arvo, Loppu As Integer
Arvo = 1
Loppu = InputBox("Anna solujen lukumäärä")
    Do While Arvo <= Loppu
' Laitetaan arvo soluun, jonka riviindeksiä kasvatetaan koko ajan
        Cells(Arvo, 1).Value = Arvo
        Arvo = Arvo + 1
    Loop

End Sub

Esimerkki 2:
Sub Do_Loop_silmukka2()

' Esimerkki silmukkarakenteesta
' Tulostetaan luvut yhdestä kymmeneen
' aktiiviseen laskentataulukkoon

Dim Arvo, Loppu As Integer
Arvo = 1
Loppu = InputBox("Anna solujen lukumäärä")
    Do
        Cells(Arvo, 1).Value = Arvo
        Arvo = Arvo + 1
    Loop While Arvo <= Loppu

End Sub

Esimerkkejä yksinkertaisista VBA-työkaluista

Seuraavassa muutamia käyttökelpoisia esimerkkejä VBA:lla tehdyistä työkaluista. Työkalut eivät ole toteutettu optimaalisesti eivätkä ole välttämättä virheettömiä. Jos sinulla on parannusehdotuksia työkaluihin, niin laita ihmeessä tulemaan niistä kommentteja Petri Heinoselle (peheinon@mit.jyu.fi).

Excel-taulukon muuttaminen HTML-taulukoksi

Seuraava esimerkki muuttaa aktiiviseksi valitun solualueen HTML-taulukoksi ja kopioi taulukon valmiiksi leikepöydälle. Työkalun käyttöön on olemassa myös tarkemmat ohjeet.

Sub taulukko()
' Petri Heinonen 20.02.2002
' Tämä ohjelma tekee Excel-taulukosta HTML-taulukon

' Alustetaan tarvittavat muuttujat
Dim arvo As String
Dim i As Integer
Dim j As Integer
Dim rivilkm As Integer
Dim sarakelkm As Integer
Dim uusinimi As String
Dim nimi As String

' Otetaan talteen aktiivisen lomakkeen nimi
nimi = ActiveSheet.Name
' Määritellään aktiivisen alueenb rivien lukumäärä
rivilkm = Selection.Rows.Count
' Määritellään aktiivisen alueen sarakkeiden lukumäärä
sarakelkm = Selection.Columns.Count
' Lisätään uusi lomake
Sheets.Add
' Otetaan uuden lomakkeen nimi talteen
uusinimi = ActiveSheet.Name
' Aktivoidaan vanha lomake
Sheets(nimi).Activate
' Aletaan varsinaisen taulukon muodostaminen
Worksheets(uusinimi).Cells(1, 1).Value = "<table>"
 
For i = 1 To rivilkm
  ' Aloitetaan uusi rivi
  Worksheets(uusinimi).Cells(i + 1, 1).Value = "<tr>"
  For j = 1 To sarakelkm
      ' Luetaan alkuperäisen taulukon solu
      arvo = Selection.Cells(i, j).Value
   If i = 1 Then    ' Taulukon otsikkotiedot ensimmäiselle riville
      arvo = "<th>" + arvo + "</th>"
   Else             ' Taulukon datat muille riveille
      arvo = "<td>" + arvo + "</td>"
   End If
      ' Sijoitetaan muutetut solun tiedot uuteen taulukkoon
      Worksheets(uusinimi).Cells(i + 1, j + 1).Value = arvo
  Next j
  Worksheets(uusinimi).Cells(i + 1, j + 1).Value = "</tr>"
Next i
  
Worksheets(uusinimi).Cells(i + 1, 1).Value = "</table>"
 
' Aktivoidaan lomake, jolla on muodostunut taulukko
Sheets(uusinimi).Activate
   
Range(Cells(1, 1), Cells(i + 1, j + 1)).Copy

End Sub

Omien taulukkolaskentafunktioiden toteuttaminen

VBA:n avulla voidaan tehdä omia taulukkolaskentafunktioita, joita voidaan käyttää apuna laskennassa. Omat funktiot voidaan lisätä taulukkolaskentaohjelmissa normaalisti ohjatun funktion lisäämistä valikkokomennolla Insert | Function. Oma funktio löytyy käyttäjän määrittelemien (engl. User Defined) funktioiden joukosta.

Seuraava omasumma-funktio on yksikertaisin esimerkki oman funktion toteuttamisesta. Funktio laskee kahden luvun tai kahdessa solussa olevien lukujen summan. Esimerkkifunktio ei ole käyttökelpoinen, koska taulukkolaskentaohjelmista löytyy parempia funktioita kyseiseen käyttötarkoitukseen, mutta se on riittävän yksikertainen esimerkki funktion toiminnasta.

Function omasumma(ekaluku As Integer, tokaluku As Integer) As Integer

' Petri Heinonen 20.02.2002
' Tämä on esimerkki mahdollisimman yksinkertaisesta itsetehdystä funktiosta
' Funktiolla voidaan laskea kahden parametrina annetun solun summan.
' Funktio
omasumma = ekaluku + tokaluku

' Jos funktion halutaan palauttavan sijaintisoluunsa tuloksen,
' niin palauttaminen voidaan toteuttaa sijoittamalla
' haluttu arvo funktion nimeen edellisen esimerkin mukaisesti.


End Function

Esimerkki funktion kutsusta ja sen palauttamasta arvosta.

Seuraava funktio on hieman edellistä käyttökelpoisempi. Funktiolla lasketaan kahden luvun tai soluarvon perusteella X^Y+1-sarjan arvoja. Kyseinen kaava voitaisiin toki toteuttaa suoraan laskutoimituksena soluviittauksilla, mutta sen tekeminen funktioksi helpottaa tulevaisuudessa kyseisen kaavan käyttöä.

Function SARJA(X As Integer, Y As Integer)
' Esimerkki omasta taulukkolaskentafunktiosta
' Funktio laskee kaavanmukaisen tuloksen annetuista luvuista
    
    SARJA = X ^ Y + 1

End Function

Esimerkkejä funktion kutsusta ja sen palauttamista arvoista.

Käyttäjien kommentit

Kommentoi tätä sivua Lisää uusi kommentti
Kurssimateriaalien käyttäminen kaupallisiin tarkoituksiin tai opetusmateriaalina ilman lupaa on ehdottomasti kielletty!
http://appro.mit.jyu.fi/doc/tiedonhallinta/vba/
© Antti Ekonoja (anjoekon@jyu.fi) <http://users.jyu.fi/~anjoekon/>
Tommi Lahtonen (tommi.j.lahtonen@jyu.fi) <http://hazor.iki.fi/>
Jukka Mäntylä (jmantyla@iki.fi) <http://www.iki.fi/jmantyla/>
© Petri Heinonen (peheinon@mit.jyu.fi)< http://www.mit.jyu.fi/peheinon/>
2005-02-03 13:41:32
Informaatioteknologia - Jyväskylän yliopiston IT-tiedekunta ja avoin yliopisto