wimgielis.com

Rang en evolutie

• 28/10/2011

	Voorbeeldbestanden bij dit artikel:
	Rang en evolutie

Inleiding

Relatief vaak wordt de vraag op forums gesteld om in VBA een bepaalde actie te ondernemen op het moment dat een cel of bereik aangeklikt wordt. De selectie verandert, en dit moet een bepaalde macro uitvoeren. Zo kan je bijvoorbeeld de gebruiker laten navigeren door vooraf bepaalde cellen, in de juiste volgorde.

In VBA

Zoiets kan je redelijk eenvoudig opzetten in Excel VBA middels een Worksheet_SelectionChange event: Excel detecteert dat de selectie gewijzigd werd, en laat jou toe om programmacode uit te voeren net op dat moment. Over het algemeen gebruik ik events zoals Worksheet_SelectionChange en Worksheet_Change eerder spaarzaam, maar dit keer heb ik toch mijn best gedaan om een nuttige toepassing voor te stellen.

Uitleg

Bovenaan de pagina stond alvast het voorbeeldbestand met uitgewerkte code en de afbeelding. De uitleg volgt nu.

Amerikaanse gegevens

De casus is als volgt. In kolom A staan de 51 staten van de VS, cellen A2:A52. In de kolommen B tot en K verzamelde ik de bevolkingsaantallen van de jaren 2000-2009, metingen op 1 juli (Brongegevens). Om de afbeelding niet nog groter te maken, groepeer ik kolommen C tot en J in een niveau. Vanaf kolom M zien we de procentuele bevolkingstoename of -afname. Doel is om snel inzicht te krijgen in die 51 x 10 getallen, die pakweg allemaal tussen +3% en -1% schommelen. Hoe fluctueren de toe- en afnames per staat? De bevolkingscijfers in absolute aantallen zijn van minder belang, staten hebben nu eenmaal een veel inwoners of weinig. De relatieve orde van de staten (grootste, 2de grootste, …, kleinste) gaat zeer weinig verschuiven. De jaarlijkse netto aangroei of afgroei is wel variabel.

Met een Worksheet_SelectionChange event is dan het volgende scenario mogelijk:

1. We klikken op de naam van een staat
2. De gegevens voor die staat verschijnen in rode achtergrondkleur
3. Colorado heeft tussen juli 2000 en 2001 met 2,43% de 3^de grootste toename van alle staten
4. In 2001-2002 eindigde Colorado 7de, in 2002-2003 slechts 16de, enz.

Interpretatie van de gegevens

Hoe lezen we dit af uit de Excel tabel? De 2,43% van Colorado vinden we terug in cel M7, dus dat is makkelijk te vinden. In het geel geven we de rangorde of de relatieve positie aan: hoe verhoudt 2,43% ten opzichte van de percentages voor de andere staten in dat jaar? Cel M4 heeft een gele achtergrondkleur voor Colorado: het is de derde cel in de kolom, dus Colorado kende in dat jaar de derde grootste bevolkingstoename. Zoals reeds aangehaald, het jaar erna zakte de groei absoluut gezien naar 1,61%, en relatief gezien naar de 7de grootste groei. Nog een jaar later waren er al 15 staten met een hogere groei dan Colorado, maar geleidelijk aan stegen de toenames opnieuw. Merk op: in de periode 2008-2009 was de absolute groei lager dan in de periode 2007-2008, maar relatief gezien steeg Colorado een plaats (van 5 naar 4).

Ik vind dit een vrij handige manier van presenteren, aangezien we geen 2 tabellen nodig hebben: een tabel met de percentages zoals jullie hier zien, en daarnaast nog eens een tabel met 51 x 10 cellen die de rangschikkingen weergeven (1 tot en met 51). Visueel is dit merkbaar. We hoeven gewoon te klikken op de naam van de staat, of de pijltjestoetsen gebruiken, en de informatie wordt gepresenteerd. Voor dit soort van voorstellingen van veel (enkelvoudige) getallen geloof ik niet in het gebruik van grafieken: die zouden onduidelijk worden. Wat wel kan, is een grafiek die de percentages 1 staat voorstelt. Als label tonen we dan de rang binnen de set van staten. Als je de grafiek ook dynamisch wil maken met keuze van de staat, gaat dit uiteindelijk lastiger blijken te zijn dan het Worksheet_SelectionChange en mijn voorgestelde manier van werken. Icoonsets (vanaf Excel 2007) kunnen ook handig zijn om veel getallen overzichtelijk voor te stellen.

VBA-code

Hoe gaat dit nu aan zijn werk qua code? Dat is vrij eenvoudig. Eerst doen we een rechtermuisklik op de bladtab (onderaan het scherm) en voegen we code van het event toe:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Wim Gielis
' https://www.wimgielis.com
'''''
' Worksheet_SelectionChange event code
' 28/10/11
'''''
    If Target.Count = 1 Then
        If Not Intersect(Target, Range("A1:A52")) Is Nothing Then
            Call HighlightEvolution(Target)
        End If
    End If

End Sub

De code die je plakt, is wat uitgevoerd wordt bij het veranderen van de selectie op dat bepaalde werkblad. Daarbij kan je gebruik maken van de variabele Target, wat als Range gedeclareerd wordt en aldus een bereik bevat: het is de cel of cellen die de nieuwe selectie uitmaken. We testen eerst of dat nieuwe bereik slechts uit 1 cel bestaat (je mag slechts 1 staat aanklikken). Daarna kijken we de selectie zich bevindt in het bereik A1:A52. Cel A1 moeten we aanklikken om de opmaak te verwijderen, en de overige 51 cellen zijn uiteraard voor de gegevens per staat.

Sub HighlightEvolution(rngState As Range)
' Wim Gielis
' https://www.wimgielis.com
    Dim rngYear As Range
    Dim rngPercentages As Range
    Dim lRank As Long

    'remove highlighting
    With Range("M1").CurrentRegion
        .Interior.ColorIndex = 0
        .Font.ColorIndex = 0
    End With

    'apply highlighting again
    If rngState.Row > 1 Then

        'relative rankings in yellow
        For Each rngYear In Range("M1:U1")
            Set rngPercentages = Cells(2, rngYear.Column).Resize(51)
            lRank = WorksheetFunction.Rank(Cells(rngState.Row, rngYear.Column), rngPercentages)

            With Cells(lRank + 1, rngYear.Column)
                .Interior.ColorIndex = 6
                .Font.ColorIndex = 6
            End With
        Next

        'percentage values in red
        With Range("M" & rngState.Row).Resize(, 9)
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 0
        End With

    End If

End Sub

Dit is heus niet zoveel code als het wel lijkt. Meer dan het berekenen van de rang en cellen inkleuren gebeurt er niet. Eerst wordt de bestaande opmaak ongedaan gemaakt, in alle gevallen. Vervolgens wordt er gekeken of de aangeklikte cel zich in rij 1 bevindt of niet. Indien niet, dan passen we de rode en gele opmaak opnieuw toe. Cel A1 geldt hier als reset cell. De gele cellen krijgen tijdelijk ook het percentage in de gele fontkleur om niet afgeleid te worden door het getal in de gele cel: dat getal is nl. niet van toepassing op bijvoorbeeld Colorado.

Je moet eens de staat Louisiana kiezen. Je krijgt dan volgende gegevens:

Louisiana had steeds een licht positieve bevolkingstoename, maar behoorde wel tot de staten met de kleinste aangroei. Het uitbreken van de orkaan Katrina eind 2005 zorgde voor een netto-afname van 5,72% en Louisiana eindigde sterk laatste in 2005-2006 (gele cel R52). Het jaar nadien echter was er een positieve toename van 3,20% en was Louisiana de beste leerling van de klas op dit vlak. Ook de 2 jaren die volgden zagen we nog relatief hoge stijgingscijfers in die staat vergeleken met de andere staten.

Andere toepassingen

Andere nuttige toepassingen zijn de verkopen per product of salespersoon, de rangschikkingen van je favoriete voetbalploegen over de jaren heen, of nog anders. Wees maar creatief!

• « Vorig Excel artikel
• Volgend Excel artikel »