wimgielis.com

Dynamische bereiken

• 03/01/2012

	Voorbeeldbestanden bij dit artikel:
	Dynamische bereiken

Inleiding

We gebruiken in Excel constant (cel)bereiken: =SOM(A1:B10), =MAX(Januari:December!M5), meer complexe formules zoals =IF($G$4="FOUT";0;($C7<0)*(E$13>0)*MAX(0;MIN(-$C7-SOM($A17:D17);E$13-SOM(E$15:E16)))), of nog miljoenen andere voorbeelden.

Werk je met een tabel van gegevens (en dat is ALTIJD een goed idee in Excel), dan heb je bereiken zoals: A1:E15. Dat bereik geven we meestal een naam, zodat formules (sterk) vereenvoudigd worden:

Druk ook Ctrl + F3 om naar de benoemde bereiken te gaan:

Databasefuncties (zoals DBSOM, DBGEMIDDELDE) worden duidelijker met een benoemd bereik in plaats van een hardgecodeerd bereik dat naar een andere plaats in het tabblad of zelfs bestand verwijst. Maar dit geldt voor alle functies en bij uitbreiding voor VBA-code. Bereiken met een naam zal je ook willen gebruiken bij een draaitabel of uitgebreide filter.

Bereiken zullen in de praktijk uitbreiden of krimpen: klanten komen (en gaan), offertes komen en worden uitgevoerd, enz. … De bereiken zijn echter statisch. De verleiding is groot om een "veel te groot" bereik te nemen: dan "zullen we nog wel even toekomen". Beter is om een dynamisch bereik in te stellen. We kennen hiervoor de VERSCHUIVING functie, in het Engels OFFSET:

Verschuiving

VERSCHUIVING (of OFFSET) heeft 5 argumenten:

1. de referentiecel / het referentiebereik
2. het aantal rijen naar onder (positief getal) of naar boven (negatief getal)
3. het aantal kolommen naar rechts (positief getal) of naar links (negatief getal)
4. het aantal rijen in het bereik
5. het aantal kolommen in het bereik

Je hoeft niet alle argumenten op te geven; laat het gewoon leeg zoals te zien is. Je kan andere functies nesten zoals de AANTALARG functie (COUNTA). Ik tel daar cellen in kolom A om te kijken hoe groot het bereik moet zijn. Dit is het dynamische aspect aan de hele benadering. Een goede referentie op het internet is deze pagina op Ozgrid.

Wil je bijvoorbeeld het aantal datums tellen in kolom C: =AANTAL(OFFSET(gegevens;;2;;1)) in plaats van het simpelere maar minder interessante alternatief: =AANTAL('Dynamische bereiken'!C:C)). Of ook zeer goed en ietwat geavanceerd: =AANTAL(INDEX(gegevens;0;3)): rij-index 0 geeft ALLE rijen.

VBA-code

Al een tijdje heb ik deze kleine macro in mijn Persoonlijke werkmap:

Public Sub CreateDynamicRange()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Automatic dynamic named ranges
' 03/01/12
'''''
    With Selection.CurrentRegion.Cells(1)

        .Parent.Parent.Names.Add _
            Name:=InputBox("Naam"), _
            RefersTo:="=OFFSET(" & _
                "'" & .Parent.Name & "'" & "!" & .Address & _
                ",,," & _
                "COUNTA(" & "'" & .Parent.Name & "'" & "!" & .EntireColumn.Address & ")," & _
                "COUNTA(" & "'" & .Parent.Name & "'" & "!" & .EntireRow.Address & ")" & _
                ")"

                '(gebruik .Columns.Count voor een bereik met dynamische rijen maar statische kolommen)

    End With

End Sub

Dit lijkt veel code, maar is uiteindelijk maar 1 statement: de Names.Add methode. Het line continuation character _ knipt de code op in meerdere regels ter bevordering van het lezen van de code. De gebruiker wordt gevraagd om de naam op te geven voor het bereik middels een inputbox. Je mag eender waar staan in je tabel, het aaneengesloten bereik van de CurrentRegion wordt genomen (dit is manueel hetzelfde als Ctrl + Shift + *) Je hoeft ook de naam niet te verwijderen als deze reeds bestaat: Excel overschrijft de definitie met de nieuwe definitie (functie).

De code hierboven was uitgesplitst om illustratief te zijn. De verkorte versie van dezelfde code ziet er voor de fans zo uit:

Public Sub CreateDynamicRange()
    With Selection.CurrentRegion.Cells(1)
        .Parent.Parent.Names.Add InputBox("Naam"), Replace("=OFFSET(§" & .Address & ",,,COUNTA(§" & _
        .EntireColumn.Address & ")," & "COUNTA(§" & .EntireRow.Address & "))", "§", "'" & .Parent.Name & "'!")
    End With

End Sub

Ik wijs een sneltoetscombinatie toe aan deze macro en dat bespaart alweer wat tijd. Je kan ervoor kiezen om de kolommen nog statisch te maken; in de macro hierboven zijn ook de kolommen dynamisch. Tot slot een woordje over Parent: de Parent van een bereik is het werkblad, de Parent van een werkblad is het werkboek. De Parent van de Parent van een bereik is met andere woorden het werkboek. Vandaar .Parent.Parent.Names.Add in de code.

In VBA code ga je met dynamische bereiken hetzelfde om als statische bereiken:

        Range("A1:E15").Copy

wordt:

        Range("gegevens").Copy

zowel de hardgecodeerde cellen zijn verdwenen, als het statische aspect. Uiteraard mag het bereik "gegevens" niet verwijderd worden, dat spreekt voor zich.

• « Vorig Excel artikel
• Volgend Excel artikel »