waarom Excel VBA?

Eén van de eerste vragen die men krijgt in een cursus Excel VBA is 'why, o why'. Waarom heeft Excel eigenlijk nog een programmeertaal en waarom zouden we die moeten leren? Hieronder worden drie voorbeelden gegeven. In het eerste voorbeeld is het inderdaad duidelijk dat u het probleem eigenlijk beter zonder VBA kunt oplossen. In het tweede voorbeeld kunt u een gelijkwaardige oplossing vinden met of zonder VBA. Het derde voorbeeld echter is niet op een elegante manier op te lossen zonder gebruik te maken van een programmeertaal, in casu Excel VBA. U kunt de onderstaande macro's uitproberen in dit Excel werkboek.

In dit voorbeeld is het inderdaad dwaas om een Excel macro te gebruiken!!!

Stel dat u een lijst heeft van 100 getallen (in de cellen A1:A100) en u wil deze sorteren van klein naar groot. In Excel kunt u dit makkelijk doen met het menu Data Sorteren .... Het neemt ongeveer 0,05 seconden in beslag; afhankelijk van de snelheid van uw computer.

U kunt ook de volgende (zeer inefficiënte) macro schrijven. Deze heeft echter 14 seconden nodig om deze 100 getallen te sorteren.

Option Explicit

Sub sorteren()
Dim i As Byte, j As Byte, temp As Single
  i = 1
  Do While i <= 99
    j = i + 1
    Do While j <= 100
      If Cells(i, 1).Value > Cells(j, 1).Value Then
        'omwisselen
        temp = Cells(i, 1).Value
        Cells(i, 1).Value = Cells(j, 1).Value
        Cells(j, 1).Value = temp
      End If
      j = j + 1
    Loop
    i = i + 1
  Loop
End Sub

Uiteraard zijn er wel situaties waarin u zelf een sorteer-macro moet schrijven, bijvoorbeeld in het geval dat de sorteervolgorde van Excel afwijkt van de door u gehanteerde volgorde. Hoe sorteert u bijvoorbeeld 'van den' ten opzichte van 'vanden' of d'haese ten opzichte van dhaese? En hoe sorteert Excel het?

Sommigen vinden een macro beter, anderen zweren bij voorwaardelijke opmaak !

U wil uw werkblad leesbaarder maken. Om de vijf rijen, wil u namelijk een licht groene rij; zie het voorbeeld hiernaast. U kunt dit oplossen met 'voorwaardelijke opmaak' of met een macro. Voor de 'voorwaardelijke opmaak' doet u het volgende. Selecteer het gebied dat u op deze manier wil opmaken. Klik op Opmaak Voorwaardelijke opmaak.... Kies uit de selectielijst 'Formule is'. Typ als voorwaarde '=REST(RIJ(), 5) = 0'; Klik op de knop Opmaak en kies lichtgroen als patroon. De functie rij() berekent het rijnummer en de functie rest() bepaalt de rest na deling door 5. Indien deze rest gelijk is aan nul, betreft het hier een veelvoud van 5 en moet de cel gekleurd worden. In de Engelstalige versie van Excel moet dit worden '=MOD(ROW(); 5) = 0'. Het scheidingsteken tussen de argumenten (komma of puntkomma) is afhankelijk van uw landinstelling in het configuratiescherm van Windows.

De macro die dit realiseert voor de eerste 20 rijen is:

Option Explicit


Sub opmaken_met_groene_balk()
  Dim rij As Integer
  rij = 5
  Do While rij <= 20
    Rows(rij).Interior.ColorIndex = 35
    rij = rij + 5
  Loop
End Sub

Het voordeel van de voorwaardelijke opmaak is dat als u rijen tussenvoegt of verwijdert de opmaak toch nog correct blijft. Met de macro heeft u expliciet de rijen 5,10,15 en 20 gekleurd en deze 'rij 5' wordt 'rij 6' als u een rij tussenvoegt. Het nadeel van de voorwaardelijke opmaak is dat deze Excel vertraagt, zeker bij extensief gebruik. Bovendien raken vele gebruikers in de war door deze voorwaardelijke opmaak. Deze heeft namelijk 'voorrang' op de 'gewone' opmaak. Het voordeel van de macro is dan weer dat u de cellen op een 'gewone' manier opmaakt en dat u ook makkelijk complexere voorwaarden kunt ingeven (bijvoorbeeld iedere vijfde rij met uitzondering van de tienvouden, ...). Dit is uiteraard ook doenbaar met voorwaardelijke opmaak maar het wordt al snel complex.

Hier zou het dwaas zijn om geen Excel macro te gebruiken!!!

Stel dat u in een werkboek de voorraad en prijzen bijhoudt van uw producten (zie de figuur hiernaast). U besluit de prijzen te verlagen met 5%, en dit bij alle producten waarvan er nog meer dan 100 stuks in voorraad zijn. De volgende macro realiseert dit.

Option Explicit


Sub verlagen_prijzen()
    Dim c As Range

    For Each c In ActiveSheet.Range("C2:C4")
        If c.Offset(0, -1).Value > 100 Then
            c.Value = c.Value * 0.95
        End If
    Next c
End Sub

U kunt dit ook manueel oplossen; hetzij door de nieuwe prijzen in te typen; hetzij door ze te berekenen in Excel en ze te plakken over de oorspronkelijke prijzen. De verschillende stappen staan uitgelegd in het handboek op blz 6. Kunt u het van de eerste keer foutloos nadoen?