Bereiken in VBA-Excel

Werkboeken:

Eén methode om een bestand te activeren.

Workbooks("Map1.xls").Activate ‘ Let er op dat bij een reeds opgeslagen

‘ bestand ook de extentie moet vermeld worden

Werkbladen:

Drie methoden om een werkblad te selecteren: Eén via de naam, één via het indexnummer (Het hoeveelste blad in de werkmap), en één via de CodeName van het werkblad. De CodeName is de linkernaam van het werkblad wat je ziet in het venster ‘projectverkenner’. Bij het aanmaken van een nieuw werkblad zijn de Name en de CodeName identiek. Je kan echter de naam van het werkblad wijzigen, terwijl de codenaam niet zal gewijzigd worden. Tuurlijk kan je de CodeName ook veranderen, maar dat moet wel via de VBA-editor.

Worksheets("Blad1").Select

Worksheets(1).Select

Blad1.Select

Eveneens drie methoden om een verzameling van werkbladen te selecteren:

Worksheets(Array("Blad1", "Blad2")).Select

Worksheets(Array(1, 2)).Select

Worksheets(Array(Blad1.Name, Blad2.Name)).Select

En drie methoden waar een berekening in de selectie is toegepast. Het eerste voorbeeld selecteert het laatste blad. Daarvoor wordt het aantal werkbladen geteld. Het tweede voorbeeld selecteert het werkblad na Blad1. Let er op dat bij berekeningen altijd gewerkt wordt met het indexnummer van de werkbladen!!! Excel is tenslotte niet voor niets een rekenprogramma.

Worksheets(Worksheets.Count).Select

Worksheets(Worksheets("Blad1").Index + 1).Select

Worksheets(Blad1.Index + 1).Select

En tenslotte: een verzameling werkbladen selecteren, via een lus:

Dim i As Integer

For i = 1 To 5

Sheets(i).Select (False)

Next

Door het argument False achter Select te zetten verliest Excel de overige geselecteerde bladeren niet. Dit is enkel toepasselijk bij werkbladen. Op die manier is het mogelijk een aantal bladeren te selecteren zonder onmiddellijk de naam of het indexnummer te noemen.

Cellen:

We beginnen bij de makkelijkste manier, waarbij via het sleutelwoord ‘Range’ verwezen wordt naar een duidelijk afleesbaar celbereik. De methode ‘Range’ wordt eveneens door de macrorecorder gebruikt, en heeft als grote voordeel dat het auto-aanvullen in VBA ondersteund wordt.

Range("A1").Select

Range("A1,E5").Select

Range("A1:E5").Select

Range("A1:E5, G1:J5").Select

Tweede groep is een snelle manier om de code te schrijven. Hierbij wordt gebruik gemaakt van rechthoekige haakjes. Ook deze manier is duidelijk leesbaar. Helaas kan men bij het schrijven van de code geen gebruik meer maken van auto-aanvullen.

[A1].Select

[A1:E5].Select

[A1,E5].Select

[A1:E5,G1:J5].Select

Een derde groep maakt gebruik van het index-nummer van de cellen. Iets onduidelijker dan beide voorgaande methodes, maar enorm veel praktischer zo er berekeningen moeten gebeuren met de cellen. Ik verdenk er Excel sterk van dat dit eigenlijk zijn natuurlijke manier is van werken, waarbij intern beide voorgaande manieren eerst omgezet worden naar deze notatie.

Je moet een Excel-blad eigenlijk zien als een verzameling van 16777216 celletjes. Niet één minder, niet één meer. Elk celletje heeft zijn eigen uniek nummer, wat oploopt van links naar rechts, en van onder naar boven. Op die manier is het mogelijk één cel te selecteren aan de hand van één nummer.

In de meeste gevallen is het echter praktischer te werken met het rij-index-nummer, en het kolom-index-nummer. Hier is wel enige oplettendheid geboden. Immers, de volgorde is net omgekeerd als in de twee voorgaande notaties. B.v.: Range(“A2”) is hetzelfde als Cells(2, 1).

Een handige methode om hier een gebied te selecteren dat groter is dan één cel, is het gebruik van de methode: Resize. Deze methode vergroot het gebied met x aantal rijen, en x aantal kolommen.

Cells(1).Select

Cells(1, 1).Select

Range(Cells(1), Cells(775)).Select

Range(Cells(1, 1), Cells(5, 5)).Select

Cells(1, 1).Resize(2, 5).Select

Een vierde groep vertrekt vanuit een willekeurig punt, en gaat dan zoveel plaatsen naar links, naar rechts, naar onder, of naar boven.

Ook hier dient opgemerkt te worden dat het eerste getal verwijst naar de verschuiving in rijen, en het tweede getal de verschuiving in kolommen.

Range("A1").Offset(4, 4).Select

[A1].Offset(4, 4).Select

Cells(1).Offset(4, 4).Select

In plaats van Offset kan men eveneens de korte notatie gebruiken. Hierbij wordt het woord Offset weggelaten, en… hier even opletten: men begint de telling bij 1, terwijl dit in het voorgaande voorbeeld begon bij 0.

Volgende voorbeelden verwijzen telkens naar cel “B2”

Range("A1")(2, 2).Select

Cells(1, 1)(2, 2).Select

Volgend voorbeeld werkt niet!!!

[A1](2, 2).Select

Volgende voorbeelden selecteren een bereik, afhankelijk van de reeds gevulde cellen in een werkblad. Let hier vooral op de sleutelwoorden:

CurrentRegion = Een aaneengesloten gebied, begrensd door de eerste lege kolom, en de eerste lege rij.

Rows = Rijen. Ook deze hebben een indexnummer, die oploopt van boven naar beneden.

Columns = Kolommen. Hebben eveneens een indexnummer dat oploopt van links naar rechts.

Count = Telt het aantal rijen/Kolommen/Cellen … afhankelijk van het argument dat er aan voorafgaat. Dit geeft dus een geheel getal als resultaat. Count is vaak zeer handig om te verwerken in berekeningen met bereiken.

[A1].CurrentRegion.Select

[A1].CurrentRegion.Rows(1).Select

[A1].CurrentRegion.Columns(1).Select

[A1].CurrentRegion _

.Columns([A1].CurrentRegion.Columns.Count).Select

[A1].CurrentRegion _

.Columns([A1].CurrentRegion.Columns.Count + 1).Select

[A1].CurrentRegion _

.Rows([A1].CurrentRegion.Rows.Count).Select

[A1].CurrentRegion _

.Rows([A1].CurrentRegion.Rows.Count + 1).Select

Cells([A1].CurrentRegion.Rows.Count + 1, 1).Select

Het volgende voorbeeld selecteert het gebruikte gebied in het actieve werkblad. Dit hoeft niet noodzakelijk de eerste rij of kolom te zijn. Wel moet het geen aaneengesloten gebied zijn, zoals in CurrentRegion.

ActiveSheet.UsedRange.Select

Vorige voorbeelden werkten steeds vanuit een bereik van een gans werkblad. Dezelfde methodes kunnen echter ook werken vanuit een beperkt bereik. Zo is Range(“A1”) niet steeds cel A1 die je op je werkblad vind. Een voorbeeld maakt dit duidelijk: Indien je Range(“C3:D4”) hebt geselecteerd, dan is Selection.Range(“A1”) gelijk aan cel C3 in je werkblad… of de linkerbovencel uit je bereik.

Neem even de test:

Vul het bereik (“C3:D4”) met de waarden: “Linkerbovenhoek; Rechterbovenhoek; Linkeronderhoek en Rechteronderhoek”

Selecteer daarna het bereik: (“C3:D4”) en voer volgende macro’s uit:

MsgBox "Range... " & Selection.Range("A1").Value

Dit geeft “Rechterbovenhoek” als resultaat (Of de cel rechtsboven van uw bereik. Dit is hier cel C3)

MsgBox "Cells... " & Selection.Cells(1, 1).Value

Dit geeft eveneens “Rechterbovenhoek” als resultaat.

DIT werkt echter niet:

MsgBox "[ ]... " & Selection.[A1].Value

Rijen en kolommen.

Mogelijk moet je echter niet werken met hele werkbladen; cellen of celbereiken, maar wens je te werken met de ganse rij of kolom.

Volgende codes selecteren de ganse eerste rij:

Range("1:1").Select

Rows(1).Select

Range("A1").EntireRow.Select ‘ Vooral deze methode is zeer handig

Cells(1, 1).EntireRow.Select

[A1].EntireRow.Select

En volgende codes werken dus voor de kolommen (Telkens de A kolom in het voorbeeld)

Range("A:A").Select

Columns(1).Select

Range("A1").EntireColumn.Select

Cells(1, 1).EntireColumn.Select

[A1].EntireColumn.Select

Een groep geïsoleerde bereiken.

Nog iets handig om te weten is de methode Areas.

Je kan werken met een aaneengesloten bereik, en daar allerlei bewerkingen op uitvoeren.

Dim myRange As Range

Set myRange = Range("A1:D4")

MsgBox myRange.Cells.Count

Deze macro geeft het resultaat ‘16’… het aantal cellen binnen de Range(“A1:D4”).

Het is echter niet noodzakelijk dat een Range ook een aaneengesloten gebied inhoud. Het volgende is ook mogelijk:

Dim myRange As Range

Set myRange = Range("A1:D4, F1:I4")

MsgBox myRange.Cells.Count

Deze macro geeft het resultaat ‘32’… het aantal cellen binnen de Range(“A1:D4, F1:I4”). Maar deze Range heeft meer eigenschappen dan enkel het aantal cellen. Deze Range heeft namelijk 2 geïsoleerde gebieden. Hoe zou je dit kunnen tellen?

Volgende macro telt het aantal gebieden (Elk geïsoleerd gebied noemt men een Area).

Dim myRange As Range

Set myRange = Range("A1:D4, F1:I4")

MsgBox myRange.Areas.Count

Deze macro geeft dus het resultaat 2. Iets waar we ook rekening mee moeten houden, is dat beide bereiken elkaar mogen overlappen, en toch afzonderlijk behandeld worden.

Dim myRange As Range

Set myRange = Range("A1:D4, C2:F5")

MsgBox myRange.Cells.Count

Deze macro bevat eveneens 2 Areas, ook al zijn ze niet echt gescheiden. We hebben hier de overlappende cellen C2; C3; C4; D2; D3 en D4. Deze cellen worden gewoon dubbel geteld, zodat we hier ook een resultaat van 32 krijgen. (Het aantal cellen in het eerste gebied + het aantal cellen in het tweede gebied).

De Areas waarover hier sprake is, krijgen ook een index-nummer (zoals vrijwel alles in Excel). Als test kan je volgende macro uitvoeren:

Dim myRange As Range

Set myRange = Range("A1:D4, F2:I5")

MsgBox myRange.Areas(2).Address

Deze macro geeft adres: ‘$F$2:$I$5’ terug. Dit is het tweede bereik binnen de Range. De dollartekens zijn er bij gezet, vermits Excel dit als absoluut beschouwd.

Het is dus mogelijk om deze Areas ook afzonderlijk te benaderen, en één Area een behandeling te geven die je niet toepast op de andere.

Dim myRange As Range

Set myRange = Range("A1:D4, F2:I5")

myRange.Areas(2).Select

Deze macro selecteert het gebied ‘F2:I5’.

Snijpunten tussen 2 (of meerdere) bereiken.

Om gezamenlijke cellen tussen 2 bereiken te bewerken kan je dan weer gebruik maken van de methode intersect. Volgende macro selecteert de Range(“C3:D4”).

Intersect(Range("A1:D4"), Range("C3:F6")).Select

Bereiken samenvoegen.

Met de functie Union is het mogelijk om 2 of meerdere bereiken samen te voegen tot 1 bereik.

Volgende code selecteert 2 kolommen zonder de functie Union.

Range("A:A, C:C").Select

Om nu hetzelfde te bereiken via Rows of Columns, kan je de functie Union gebruiken.

Union(Columns(1), Columns(3)).Select

Zo is het mogelijk deze functie te gebruiken in samenwerking met de vorige: ‘Intersect’.

Volgende code kleurt de cellen rood die deel uitmaken van (of de 3de rij; of de 5de rij) én de B kolom.

Intersect(Union(Rows(3), Rows(5)), Columns(2)).Interior.ColorIndex = 3

Een praktijkvoorbeeld.

Als slot geef ik een voorbeeld waaruit blijkt dat het van belang is te kunnen werken met bereiken. Dit voorbeeld selecteert het gebied te beginnen bij cel A1, en begrensd door de eerste lege rij, en de eerste lege kolom (Mogelijk een database dus). Daarvan telt hij het aantal rijen, om tenslotte de cel te selecteren in kolom A, en één rij onder het gebruikte gebied.

In die cel wordt een formule geplaast, die de som maakt van alle bovenliggende cellen in kolom A. Hiervoor wordt gebruik gemaakt van het adres van alle gebruikte cellen in kolom A.

Cells([A1].CurrentRegion.Rows.Count + 1, 1).Formula = _

"=SUM(" & [A1].CurrentRegion.Columns(1).Address & ")"