SpecialCells

Tämän artikkelin esimerkit tiedostossa specialcells.xlsm

Range.SpecialCells

Range.SpecialCells-menetelmä vastaa Excelin Home (Aloitus) -välilehden  Find – Go To Special (Etsi – Siirry määräten) -toimintoa. Jollei toiminto ole tuttu, niin kannattaa tutustua. Toiminnon käyttöä VBA:n puolella voit opetella nauhoittamalla toiminnon ensin Excelin puolella.

Tyhjien solujen korvaaminen

Seuraava ohjelma korvaa aktiivisen solun ympärillä olevan aineiston tyhjät solut x:llä.

Sub ReplaceEmpty()
    On Error Resume Next
    ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks). _
        Value = "x"
    On Error GoTo 0
End Sub

SpecialCells-menetelmän argumentilla xlCellTypeBlanks löydetään nimen omaan tyhjät solut.

On Error Resume next on tarpeen. Muutoin ohjelma pysähtyy virheeseen, jos tyhjiä soluja ei löydy.

Kaavat arvoiksi

Kaavoilla laskettujen tulosten muuttaminen pelkiksi arvoiksi onnistuu seuraavalla ohjelmalla.

Sub FormulaAsValues1()
    On Error Resume Next
    ActiveCell.CurrentRegion.SpecialCells _
        (xlCellTypeFormulas).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    On Error GoTo 0
End Sub

SpecialCells-menetelmän argumentilla xlCellTypeFormulas löydetään nimen omaan tyhjät solut.

Ohjelma heikko kohta on PasteSpecial-menetelmä. Se ei toimi toivotulla tavalla, jos kaavoja sisältävät solut eivät ole vierekkäisissä ja allekkaisissa soluissa. Seuraava ohjelma on parempi, koska se toimii vaikka Select-menetelmän valitsema alue olisi monivalinta.

Sub FormulasAsValues2()
Dim Cell As Range
    On Error Resume Next
    ActiveCell.CurrentRegion.SpecialCells _
        (xlCellTypeFormulas).Select
    For Each Cell In Selection
        Cell.Value = Cell.Value
    Next Cell
    On Error GoTo 0
End Sub

Eri tyyppisten solujen tunnistaminen

Seuraava ohjelma värjää merkkijonotiedon, numerotiedon, kaavat ja tyhjät omilla taustaväreillään

Sub ColorCells()
    On Error Resume Next
    With ActiveCell.CurrentRegion
        .SpecialCells(xlCellTypeConstants, xlNumbers). _
            Interior.Color = RGB(197, 217, 241)
        .SpecialCells(xlCellTypeConstants, xlTextValues). _
            Interior.Color = RGB(242, 220, 219)
        .SpecialCells(xlCellTypeBlanks). _
            Interior.Color = RGB(242, 242, 242)
        .SpecialCells(xlCellTypeFormulas). _
            Interior.Color = RGB(235, 241, 222)
    End With
    On Error GoTo 0
End Sub

Argumentilla xlCellTypeConstants poimitaan solut, jotka eivät sisällä laskentakaavaa. Toisella argumentilla tarkennetaan, onko kyseessä numerotieto xlNumbers vai merkkijonotieto xlTextValues.

Lisätietoa

Lisätietoa Range.SpecialCells -menetelmän argumenteista Range.SpecialCells -menetelmän ohjetoiminnosta.

Tämän artikkelin esimerkit tiedostossa specialcells.xlsm

Autofilter

Tämän artikkelin esimerkit tiedostossa autofilter.xlsm

Yksinkertainen suodatus

Range.Autofilter menetelmällä voit suodattaa aineistosta osa-ainestoja tehokkaasti. Esimerkiksi lause

ActiveCell.AutoFilter Field:=5, Criteria1:=”Korkeakoulu”

suodattaa aktiivisen solun ympärillä olevan aineiston siten että näkyville jäävät vain rivit, joissa on viidennessä sarakkeessa teksti Korkeakoulu.

Seuraavassa ohjelmassa hyödynnän suodattamista. Ohjelma kopioi korkeakoulututkinnon suorittaneiden henkilöiden tiedot uuteen laskentataulukkoon.

Sub MyFilterCopy()
Dim MyRange As Range
Dim NewSheet As Worksheet
    Set MyRange = ActiveCell
    MyRange.AutoFilter Field:=5, Criteria1:="Korkeakoulu"
    Set NewSheet = Worksheets.Add
    MyRange.CurrentRegion.Copy Destination:=NewSheet.Range("A1")
    MyRange.AutoFilter
End Sub

Huomaa erityisesti, että Range-objektin CurrentRegion-ominaisuus tavoittaa suodatetusta aineistota näkyville jääneet rivit. Ohjelman viimeisenä rivinä on AutoFilter-menetelmä ilman argumentteja. Tämä poistaa aineiston suodatukset.

Seuraava ohjelmaa värjää korkeakoulun suorittaneiden rivit vihreiksi.

Sub MyFilterGreen()
Dim MyRange As Range
Dim MyRows As Long
    Set MyRange = ActiveCell
    MyRows = MyRange.CurrentRegion.Rows.Count
    MyRange.AutoFilter Field:=5, Criteria1:="Korkeakoulu"
    MyRange.CurrentRegion.Offset(1).Resize(MyRows - 1) _
        .Interior.Color = RGB(0, 255, 0)
    MyRange.AutoFilter
End Sub

Offset(1).Resize(MyRows-1) tarvitaan, jotta ostsikkoriviä ei värjätä.

Seuraava ohjelma poistaa korkeakoulun suorittaneet aineistosta.

Sub MyFilterRowsDelete()
Dim MyRange As Range
Dim MyRows As Long
    Set MyRange = ActiveCell
    MyRows = MyRange.CurrentRegion.Rows.Count
    MyRange.AutoFilter Field:=5, Criteria1:="Korkeakoulu"
    MyRange.CurrentRegion.Offset(1).Resize(MyRows - 1) _
        .EntireRow.Delete
    MyRange.AutoFilter
End Sub

Voit käyttää Criteria1-argumentin arvoissa jokerimerkkiä *. Esimerkiksi Criteria1:=”*koulu*” jättää näkyville kaikki rivit, joissa esiintyy viidennessä sarakkeessa sana koulu.

Jos haluat hakea tyhjiä soluja, niin käytä Criteria1:=”=”

Jos haluat hakea ei-tyhjiä soluja, niin käytä Criteria1:=”<>”

Operator-argumentti

AutoFilter-menetelmän Operator-argumentin avulla voit tehdä monipuolisempia suodatuksia.

ActiveCell.AutoFilter Field:=7, Criteria1:=”3″, Operator:=xlTop10Items jättää näkyville ne rivit, joissa on seitsemännen sarakkeen kolme suurinta arvoa.

• Field-argumentti määrittää sarakkeen, jota ehto koskee.
• Operator:=xlTop10Items tarkoittaa, että sarakkeesta haetaan suuripia arvoja. On toki hämmentävää, että argumentin arvossa esiintyy luku 10 vaikka välttämättä ei olla hakemassa kymmentä suurinta arvoa.
• Criteria1-argumentin arvolla täsmennetään kuinka monta suurinta haetaan.

Vastaavalla tavalla voit käyttää Operator-argumentin arvoja xlBottom10Items, xlTop10Percent ja xlBottom10Percent. Voit jopa suodattaa värin perusteella käyttämällä argumentin arvoja xlFilterCellColor tai xlFilterFontColor. Värin voi ilmaista RGB-arvona, esimerkiksi Criteria1:=RGB(0,255,0).

Operator-argumentin xlAnd ja xlOr -arvoilla voit yhdistää ehtoja. Esimerkiksi

ActiveCell.AutoFilter Field:=8, Criteria1:=”1″, Operator:=xlOr, Criteria2:=”2″

jättää näkyville ne rivit, joissa on kahdeksannessa sarakkeessa arvo 1 tai arvo 2.

ActiveCell.AutoFilter Field:=7, Criteria1:=”>2000″, Operator:=xlAnd,  Criteria2:=”<3000″

jättää näkyville ne rivit, joissa on seitsemännessä sarakkeessa suurempi kuin 2000, mutta pienempi kuin 3000.

Jos haluat useampia kuin 2 ehtoa, niin voit antaa ehdot vektorina (Array). Tällöin Operator-argumentin arvona on xlFilterValues. Esimerkiksi lause

Range(”A1”).AutoFilter Field:=6, Criteria1:=Array(”1”, ”2”,  ”4”), Operator:=xlFilterValues

jättää näkyville ne rivit, joissa on kuudennessa sarakkeessa arvo 1, 2 tai 4.

Lisätietoa

Lisätietoa Range.AutoFilter -menetelmän ohjetoiminnosta.

Tämän artikkelin esimerkit tiedostossa autofilter.xlsm

Lajittelu eli järjestäminen

Tämän artikkelin esimerkit tiedostossa sort.xlsm

Range.Sort-menetelmä

Range.Sort on suoraviivaisin menetelmä laskentataulukon tietojen lajitteluun.  Sort-menetelmä lajittelee Range-objektin määrittelemän solualueen.

Seuraava ohjelma lajittelee aktiivisen solun ympäriltä löytyvän aineiston käyttäen lajitteluperusteena B-sarakkeen tietoja.

Sub MySort1()
Dim MyRange As Range
    Set MyRange = ActiveCell.CurrentRegion
    MyRange.sort Key1:=Range("B1"), Order1:=xlAscending, _
    Header:=xlYes, MatchCase:=True, Orientation:=xlSortColumns
End Sub

Range.Sort-menetelmän argumentteja

• Key1 määrittää sarakkeen (tai rivin), jonka perusteella lajittelu suoritetaan
• Order1 määrittää onko kyseessä nouseva xlAscending vai laskeva xlDescending järjestys
• Header määrittää onko lajiteltavalla aineistolla otsikot xlYes vai ei xlNo. Jos aineistolla on otsikot, niin on tärkeää käyttää xlYes-arvoa, jotta otsikot pysyvät ensimmäisellä rivillä. Voin myös käyttää xlGuess, jolloin annan Excelin arvata, onko aineistolla otsikot.
• MatchCase määrittää huomioidaanko isojen ja pienten kirjainte ero True vai ei False.
• Orientation määrittää lajitellaanko sarakkeita xlSortColumns vai rivejä xlSortRows.

Muita harvemmin tarvittavia argumentteja löydät Range.Sort-menetelmän ohjetiedoista.

Tarvittaessa voin käyttää jopa kolmea lajitteluperustetta. Seuraavassa on kaksi lajitteluperustetta. Ensisijainen lajittelu suoritetaan B-sarakkeen perusteella nousevaan järjestykseen ja toissijainen lajittelu C-sarakkeen perusteella laskevaan järjestykseen.

Sub MySort2()
Dim MyRange As Range
    Set MyRange = ActiveCell.CurrentRegion
    MyRange.sort Key1:=Range("B1"), Order1:=xlAscending, _
    Key2:=Range("C1"), Order2:=xlDescending, Header:=xlYes, _
    MatchCase:=True, Orientation:=xlSortColumns
End Sub

Sort-objekti

Jos nauhoitat lajittelun, niin nauhoitetussa koodissa käytetään SortObjects-kokoelmaa, SortObject-objektia ja Sort-objektia. Näitä käytettäessä tarvitaan seuraavat vaiheet:

• Laskentataulukon SortFields-kokoelman tyhjentäminen lauseella ActiveSheet.Sort.SortFields.Clear
• Uusien SortField-objektien lisääminen SortFields-kokoelmaan lauseella ActiveSheet.Sort.SortFields.Add
• Sort-objektin Apply-menetelmän käyttö

Seuraava ohjelma lajittelee aktiivisen solun ympäriltä löytyvän aineiston käyttäen ensisijaisena lajitteluperusteena B-sarakkeen tietoja ja toissijaisena lajitteluperusteena E-sarakkeen tietoja.

Sub MySort3()
Dim MyRange As Range
    Set MyRange = ActiveCell.CurrentRegion
    ActiveSheet.sort.SortFields.Clear
    ActiveSheet.sort.SortFields.Add Key:=Range("B1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending
    ActiveSheet.sort.SortFields.Add Key:=Range("E1"), _
    SortOn:=xlSortOnValues, Order:=xlDescending
    With ActiveSheet.sort
        .SetRange MyRange
        .Header = xlYes
        .MatchCase = True
        .Orientation = xlSortColumns
        .Apply
    End With
End Sub

SortField-objektille on tärkeää määrittää Key, SortOn ja Order -ominaisuudet. SortField-objektin avulla voit lajitella jopa värin perusteella, koska SortOn-ominaisuutena voi olla SortOnCellColor tai SortOnFontColor.

Sort-objektille on tärkeää määrittää lajiteltava solualue SetRange-menetelmällä.

Muita SortField-objektin ja Sort-objektin ominaisuuksia löydät ohjetoiminnosta.

Taulukkomuuttujan lajittelu

Taulukkomuuttujan (Array) arvot voit lajitella kirjoittamalla taulukkomuuttujan arvot laskentataulukkoon, jonka jälkeen voit käyttää edellä kuvattuja menetelmiä.

Joissain tilanteissa voi tuntua tarkoituksenmukaisemmalta lajitella taulukkomuuttuja kirjoittamatta sen arvoja laskentataulukkoon. VBA:ssa ei valitettavasti ole valmista menetelmää tätä varten. Tarvitset erillisen funktion, joka suorittaa lajittelun. Pienillä aineistoilla voit käyttää lajitteluun yksinkertaista BubbleSort-lajittelualgoritmia. Excel-tiedostosta sort.xlsm löydät BubbleSort-algoritmia soveltavan funktion, jonka olen kopioinut osoitteesta http://oco-carbon.com/2012/08/13/sorting-functions-for-vba/

Funktiota kutsutaan seuraavan kaltaisella lauseella

MyArray2 = BubbleSort(MyArray1, 2, True)

• MyArray2 on taulukkomuuttuja, johon lajiteltu taulukkomuuttuja sijoitetaan
• MyArray1 on lajiteltava taulukkomuuttuja
• Kolmas argumentti kertoo kuinka monennen taulukkomuuttujan sarakkeen mukaan lajitellaan (esimerkissä 2. sarakkeen mukaan)
• Viimeinen argumentti on True laskevalle järjestykselle ja False nousevalle järjestykselle.

Funktion käytössä täytyy huomioida, että taulukkomuuttujan indeksien oletetaan alkavan arvosta 0. Jos käytät taulukkomuuttujia, joiden indeksit alkavat arvosta 1, niin löydät sovitetun version BubbleSort1 tiedostosta sort.xlsm.

Jos lajiteltavana on tuhansia rivejä, niin BubbleSort on auttamatta liian hidas algoritmi. Tietoa ja keskustelua nopeammista lajittelualgoritmeista löydät netistä esimerkiksi hakusanalla vba sorting algorithms.

MyRandomOrder-ohjelma arpoo nimet satunnaiseen järjestykseen seuraavien vaiheiden kautta:

• Lukee aktiivisen solun ympärillä olevat nimet taulukkomuuttujaan MyArray1
• Arpoo taulukkomuuttujaan satunnaisluvut kunkin nimen viereen
• Lajittelee nimet MyArray2-taulukkomuuttujaan satunnaislukujen mukaiseen järjestykseen BubbleSort1-funktiota käyttäen
• Kirjoittaa taulukkomuuttujan MyArray2 sisällön laskentataulukkoon alkuperäisten nimien viereen.

Sub MyRandomOrder()
Dim MyRange As Range
Dim MyNamesCount As Long
Dim MyArray1() As Variant
Dim MyArray2() As Variant
Dim i As Integer
    Set MyRange = ActiveCell.CurrentRegion
    MyNamesCount = MyRange.Rows.Count
    ReDim MyArray1(1 To MyNamesCount, 1 To 2)
    ReDim MyArray2(1 To MyNamesCount, 1 To 2)
    MyArray1 = MyRange.Resize(, 2)
    For i = 1 To MyNamesCount
        MyArray1(i, 2) = Rnd
    Next i
    MyArray2 = BubbleSort1(MyArray1, 2, True)
    MyRange.Offset(, 2).Resize(, 2) = MyArray1
End Sub

Tämän artikkelin esimerkit tiedostossa sort.xlsm