Artikkelista on sinulle hyötyä, jos nauhoitat tai koodaat Excel-makroja, joiden pitää pystyä käsittelemään kooltaan muuttuvia dynaamisia luetteloita. Jutussa oletetaan, että tunnet Excel-taulukoiden (tables) perusperiaatteet ja osaat nauhoittaa sekä muokata makroja Visual Basic Editorissa.

Jos kaipaat lisätietoja taulukoista (tables), tutustu aiempaan 5 syytä muuntaa taulukoksi -juttuuni. Jos puolestaan tarvitset makrojen nauhoitukseen ja VBA:kieleen liittyviä perusohjeita, löydät niitä esimerkiksi Aki Taanilan mainioilta Olennaiset Excel-taidot -blogin VBA-sivuilta.

Dynaamisten aineistojen ongelma

Jos olet nauhoittanut makroja, tunnistat varmasti ongelman joka liittyy tavallisiin alueviittauksiin. Mikäli koodissasi on käytetty alueita tai soluja (kuten B2:B7 tai H5), viittaukset eivät muutu vaikka laskentataulukko muuttuisi. Pahimmillaan saatat joutua muokkaamaan koodistasi kymmeniä lauseita, jos lisäät laskentataulukkoon yhden rivin tai siirrät lukuja toiseen sarakkeeseen. Lisäksi jokainen muutos voi aiheuttaa kirjoitusvirheitä, joten makrot pitää testata jokaisen muutosten jälkeen.

Nämä koodissa olevat alueet eivät muutu, vaikka laskentataulukko muuttuisi:

Range(”B2:B80”).Font.Bold = True
Range(”C2:C80”).Copy

Ongelmaa on voitu pyrkiä ratkaisemaan esimerkiksi suorittamalla operaatioita ylisuurille alueille (mutta entä jos sarakkeet siirtyvät):

Range(”B2:B100000”).Font.Bold = True
Range(”C2:C100000”).Copy

Tai antamalla alueille nimet ja käyttämällä niitä koodissa (mutta miten huomioidaan alueiden kasvaminen, jos tietoja lisätään esimerkiksi nimettyjen alueiden alapuolelle):

Range(”Tammi”).Font.Bold = True
Range(”Helmi”).Copy

Tai erilaisilla melko haastavilla dynaamisilla tekniikoilla, joista monet vaativat erinomaista VBA-taitoa.

Ratkaisuna taulukot ja jäsennetyt alueviittaukset

Tämäkään tekniikka ei ratkaise kaikkia ongelmia, mutta helpottaa monien makrojen toteutusta ja ylläpitoa.

Oletetaan, että makrojesi pitää pystyä käsittelemään luetteloa, johon tulee lisää tai siitä poistuu rivejä tai joka kokonaisuudessaan päivittyy säännöllisesti. Excel 2007 -versiota vanhemmissa versioissa tällaisten makrojen nauhoittaminen ja koodaaminen vaati erityisosaamista ja usein melko haastavaakin VBA-koodia. Nykyversioissa voit muuntaa luettelon taulukoksi ja antaa taulukolle nimen, jonka jälkeen voit viitata taulukon taulukon erilaisiin alueisiin ns. jäsennettyjen viittausten avulla (structured references).

Käytän esimerkissäni hyvin yksinkertaista taulukkoa, jonka nimeksi on annettu Lukemat ja jossa on mm. Summa-rivi (Total Row). Esimerkissä ei sinänsä tehdä mitään mullistavaa, tarkoituksena on ainoastaan esitellä taulukkoviittaukset.

 

Taulukko täytetään päivittäin ja jokaisen päivän päätteeksi makro kopioi tiedot kahteen eri luetteloon:

1. Rivit kopioidaan Lukemahistoria -nimisen taulukon jatkeeksi.
2. Summarivin arvot kopioidaan Päiväsummat -nimisen luettelon jatkeeksi.

Lisäksi Lukemat-luettelosta tyhjennetään rivit seuraavan päivän tietojen syöttöä varten.

Esimerkkimakro

Esimerkkimakro toimii vaikka taulukot siirretään tai niiden rivimäärä vaihtelee. Jäsennetyt punaiselle merkityt viittaukset edellyttävät, että taulukoiden nimet ja otsikot säilyvät samoina. Osa esimerkkikoodin lauseista edellyttää myös, että sarakkeiden järjestys ei muutu.

Huomaa, että tässä makrossa ei ole mitään virheentarkistus- tai muita tarpeellisia rakenteita! Tarkoituksena on ainoastaan esitellä muutama esimerkki taulukkoviittauksista.

’ Kopioidaan Lukemat-taulun datarivit leikepöydälle (ei otsikko- ja summariviä)
Range(”Lukemat[#Data]”).Copy

’ Siirrytään Lukemahistoria-taulun Koodi-otsikkoon 
Application.Goto reference:=”Lukemahistoria[[#Headers],[Koodi]]”

’ Siirrytään Koodi-sarakkeessa aineiston alapuolelle ja liitettään kopioidut tiedot
ActiveCell.Offset(Range(”Lukemahistoria”).Rows.Count + 1, 0).Select
ActiveSheet.Paste
’ Valitaan Lukemahistoria-taulukosta lopulta Koodi-otsikkosolu
Range(”Lukemahistoria[[#Headers],[Koodi]]”).Select

’ Kopioidaan leikepöydälle Lukemat-taulun summarivin alue [Pvm]:[Lukema C]
Range(”Lukemat[[#Totals],[Pvm]:[Lukema C]]”).Copy

’ Siirrytään Päiväsummat-taulukon Pvm-sarakkeen alapuolelle ja liitetään kaavojen arvot
Application.Goto reference:=”Päiväsummat[[#Headers],[Pvm]]”
ActiveCell.Offset(Range(”Päiväsummat”).Rows.Count + 1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
’ Valitaan Päiväsummat-taulukosta lopulta Pvm-otsikkosolu
Range(”Päiväsummat[[#Headers],[Pvm]]”).Select

’ Tyhjennetään Lukemat-taulukon tietorivit
Application.Goto reference:=”Lukemat[#Data]”
Selection.ClearContents

’ Valitaan Koodi-otsikko ja muutetaan taulukon koko 2-riviseksi
Application.Goto reference:=”Lukemat[[#Headers],[Koodi]]”
ActiveSheet.ListObjects(”Lukemat”).Resize ActiveCell.Offset(0, 0).Range(”A1:F2”)

Lataa esimerkki oikean alakulman Excel-painikkeella:

Jäsennettyjä viittauksia selityksineen:

Koko Lukemat-taulukkoon viittaaminen	Range(”Lukemat[#All]”)
Taulukon dataan viittaaminen (ilman otsikoita tai summariviä)	Range(”Lukemat[#Data]”) taiRange(”Lukemat”) Esimerkiksi lauseke Range(”Lukemat[#Data]”).Rows.Count palauttaa tuloksena Lukemat-taulukon datarivien lukumäärän
Otsikkoriviin viittaaminen	Range(”Lukemat[#Headers]”)
Pvm-otsikkoon viittaaminen	Range(”Lukemat[[#Headers],[Pvm]]”)
Summariviin viittaaminen	Range(”Lukemat[#Totals]”)
Pvm-sarakkeeseen viittaaminen	Range(”Lukemat[Pvm]”)
Lukemat-taulukon Lukema A – Lukema C –sarakkeiden valitseminen	Range(”Lukemat[[#All],[Lukema A]:[Lukema C]]”).Select
Lukemat-taulukon Totals-rivin Lukema A – Lukema C –sarakkeiden valitseminen	Range(”Lukemat[[#Totals],[Lukema A]:[Lukema C]]”).Select

Löydät lisätietoja jäsennetyistä viittauksista Microsoftin ohjeartikkelista: Kaavojen käyttäminen Excel-taulukoissa