VBA Uitleg
  • Home
  • Boeken
  • Vuistregels, fouten, sneltoetsen
  • Macrobeveiliging instellen
  • Datums van Feestdagen
  • EXCEL macro's
  • - Alle opmerkingen opmaken
  • - Unieke getallen
  • - Naar de cel met vandaag
  • - Gekleurde cellen optellen
  • - Functies ronde vormen
  • - Gratis downloads
  • - Engels
  • WORD macro's
  • - Woord tellen
  • - Dialoogvenster etiketten
  • - Automatisch zonder macro
  • Contact
  • - Wim is...

Getallen in gekleurde cellen optellen

​Excel heeft geen ingebouwde functie om alleen getallen op te tellen in cellen die een bepaalde kleur hebben. Maar je kunt die functie wel zelf programmeren. Dat wordt een UDF genoemd, een User Defined Function.
We noemen deze functie SOMZELFDEKLEUR. Deze functie telt de waarden op in cellen die een bepaalde opvulkleur hebben. We geven aan één bepaalde cel buiten de tabel een opvulkleur, geven getallen in de tabel dezelfde kleur en de getallen met dezelfde kleur worden opgeteld. 

Je programmeert deze functie als volgt. Open de Visual Basic Editor, dubbelklik in de Projectverkenner op de werkmap waarin je deze functie wilt plaatsen. Klik op Invoegen en op Module; er verschijnt een blanco codeblad. Neem daarin de volgende code over. Deze kun je van hieruit kopiëren en plakken.

Function SOMZELFDEKLEUR(Gebied As Range, Cel As Range) As Double
Dim Kleur As Integer
  Application.Volatile
  Kleur = Cel.Interior.ColorIndex
  For Each Cel In Gebied.Cells
    If Cel.Interior.ColorIndex = Kleur And IsNumeric(Cel.Value) Then
      SOMZELFDEKLEUR = SOMZELFDEKLEUR + Cel.Value
    End If
  Next Cel
End Function
 
Tussen de haakjes staat eerst het gebied met de getallen, dan volgt de cel die de betreffende kleur heeft.
De code begint met
  Application.Volatile
Dat zorgt ervoor dat de formule automatisch reageert als je naderhand celkleuren verandert. Je hoeft dan niet in de cel met de formule op de functietoets F2 te drukken en op de Enter-toets om de formule opnieuw in te voeren en weer te laten berekenen.

De functie haalt het kleurnummer van die ene cel op met:
  Kleur = Cel.Interior.ColorIndex

De volgende code is een lus:
  For Each Cel In Gebied.Cells tot en met Next Cel
Deze gaat alle cellen in het opgegeven gebied langs en controleert of hun kleurnummer overeenkomt met het kleurnummer van de ene cel die als criterium dient, met:
  If Cel.Interior.ColorIndex = Kleurnummer
Bovendien moet de waarde in die cel een getal zijn, dat checkt:
  And IsNumeric(Cel.Value)
​
De variabele SOMZELFDEKLEUR telt de waarden in deze cellen op. Deze is in het begin nul. Als een cel een getal bevat met het betreffende kleurnummer, wordt de waarde van die cel bij de variabele opgeteld in
  SOMZELFDEKLEUR = SOMZELFDEKLEUR + Cel.Value
Vervolgens wordt de waarde van de volgende cel die aan de voorwaarden voldoet, daarbij opgeteld enzovoort. De lus doorloopt het hele gebied en telt zo alle getallen op in de cellen die dat kleurnummer hebben.

Meer eigen functies programmeren?

Deze voorbeelden komen uit het boek:
Excel VBA voor professionals, Derde editie
Foto

Formule met SOMZELFDEKLEUR opstellen

Vervolgens gebruik je deze zelfgeschreven functie in een formule. Deze formule heeft de volgende opbouw.
=SOMZELFDEKLEUR(gebied;cel met een kleur)
Je geeft tussen haken het gebied op dat wordt doorzocht; na een puntkomma verwijs je naar één cel die de betreffende kleur heeft.
Resultaat: het totaal van de getallen in dat gebied in de cellen met dezelfde kleur als die ene cel.

Je past deze functie bijvoorbeeld toe op de reeks B1 tot en met E10. Cel A1 is bijvoorbeeld oranje. Je wilt van B1 tot en met E10 de getallen optellen, die ook in een oranje cel staan (die dezelfde kleur hebben als A1). Je formule is:
=SOMZELFDEKLEUR(B1:E10;A1)
Alle getallen worden opgeteld in de cellen die dezelfde kleur als A1 hebben.
Foto
De getallen worden opgeteld in de cellen die dezelfde kleur als A1 hebben.
De cel waar je de kleur vandaan haalt, mag ook de cel zijn waarin deze formule zelf staat (dat levert geen kringverwijzing op).
Geef een aantal cellen in B1 tot en met E10 een gele opvulkleur. Kleur bijvoorbeeld cel F6 ook geel en plaats in deze cel de formule:

=SOMZELFDEKLEUR(B1:E10;F6)
Nu heeft de cel met de uitkomst dezelfde kleur als de cellen waarvan de getallen worden opgeteld.
​

* Verwijs niet naar een hele kolom of hele rij, want dan zal het werkblad trager werken, omdat deze functie duizenden cellen moet controleren. Houd de verwijzing naar het gebied zo beperkt mogelijk.
Foto
Tel de getallen op in de cellen met dezelfde kleur als waarin de formule staat (in F5).
Download gratis het voorbeeld-bestand
Je mag het Excel-bestand met deze code erin gratis downloaden.
Je mag dit bestand voor jezelf gebruiken. Doorverkopen is op grond van het auteursrecht verboden.

Tellen hoeveel cellen dezelfde kleur hebben

Je kunt ook tellen hoeveel cellen een bepaalde keur hebben. Ook die functie kun je zelf programmeren.
Dit is een variant op bovenstaande functie, we noemen deze AANTALZELFDEKLEUR.
We geven aan één bepaalde cel buiten de tabel een opvulkleur, en de formule telt hoeveel cellen in die tabel dezelfde kleur hebben. 
Neem onderstaande code over (die mag in dezelfde module komen). 

Function AANTALZELFDEKLEUR(Gebied As Range, Cel As Range) As Double
Dim Kleur As Integer
  Application.Volatile
  Kleur = Cel.Interior.ColorIndex
  For Each Cel In Gebied.Cells
    If Cel.Interior.ColorIndex = Kleur Then
      AANTALZELFDEKLEUR = AANTALZELFDEKLEUR + 1
    End If
  Next Cel
End Function

​Vervolgens gebruik je deze zelfgeschreven functie in een formule, die de volgende opbouw heeft.
=AANTALZELFDEKLEUR(gebied;cel met een kleur)
Je geeft tussen haken het gebied op dat wordt doorzocht; na een puntkomma verwijs je naar één cel die de betreffende kleur heeft.
Resultaat: het aantal cellen in dat gebied dat dezelfde kleur heeft als die ene cel.

Je past deze functie bijvoorbeeld toe op de reeks B1 tot en met E10. Cel A1 is bijvoorbeeld oranje. Je wilt weten, hoeveel cellen in B1 tot en met E10 ook oranje zijn (die dezelfde kleur hebben als A1). Je formule is:
=AANTALZELFDEKLEUR(B1:E10;A1)
Alle cellen worden geteld die dezelfde kleur als A1 hebben.

De cel waar je de kleur vandaan haalt, mag ook de cel zijn waarin deze formule zelf staat. Geef een aantal cellen in B1 tot en met E10 een gele opvulkleur. Kleur bijvoorbeeld cel F6 ook geel en plaats in deze cel de formule:

=AANTALZELFDEKLEUR(B1:E10;F6)
Nu heeft de cel met de uitkomst dezelfde kleur als de cellen die worden geteld.
Wim de Groot denkt buiten de hokjes
Auteursrecht    Disclaimer    Contact