Hoe bereik en cellen in vba gebruiken?

Inleiding tot bereik en cellen in VBA

Als je rondkijkt in een Excel-werkmap, zie je dat alles rond de cellen werkt. In een cel en een celbereik slaat u uw gegevens op en dan begint het allemaal.

Om het meeste uit VBA te halen, moet u leren hoe u cellen en bereiken in uw codes gebruikt. Hiervoor moet u een goed begrip hebben van Range-objecten. Hiermee kunt u op de volgende manier naar cellen in uw codes verwijzen:

Een enkele cel.
Een reeks cellen
Een rij of kolom
Een driedimensionaal bereik

Het RANGE OBJECT maakt deel uit van de objecthiërarchie van Excel: Toepassing ➜ Werkmappen ➜ Werkbladen ➜ Bereik en elders in het werkblad. Dus als u code schrijft om naar het RANGE-object te verwijzen, zou het er als volgt uitzien:

 Application.Workbook(“Workbook-Name”).Worksheets(“Sheet-Name”).Range

Door naar een cel of een celbereik te verwijzen, kunt u het volgende doen:

Je kunt de waarde aflezen.
Daar kunt u een waarde invoeren.
En u kunt wijzigingen aanbrengen in het formaat.

Om al deze dingen te doen, moet u leren hoe u naar een cel of een celbereik kunt verwijzen. In het volgende gedeelte van deze zelfstudie leert u hoe u op verschillende manieren naar een cel kunt verwijzen. Om naar een cel of een celbereik te verwijzen, kunt u drie verschillende methoden gebruiken.

Strand eigendom
Cel eigendom
Compensatie van eigendom

Welke van deze de beste is, hangt af van uw behoeften, maar het is de moeite waard om ze alle drie te leren, zodat u degene kunt kiezen die het beste bij u past.

Dus laten we beginnen.

Strand eigendom

De eigenschap Range is de meest gebruikelijke en populaire manier om naar een bereik in uw VBA-codes te verwijzen. Met de eigenschap Range verwijst u eenvoudigweg naar het celadres. Laat me je de syntaxis vertellen.

 expression.range(address)

Hier is de expressie een variabele die een VBA-object vertegenwoordigt. Dus als u naar cel A1 moet verwijzen, is de coderegel die u moet schrijven:

 Application.Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

De bovenstaande code vertelt VBA dat u verwijst naar cel A1 die zich in werkblad “Blad1” en werkmap “Boek1” bevindt.

Opmerking: Wanneer u een celadres in het bereikobject invoert, zorg er dan voor dat u dit tussen dubbele aanhalingstekens plaatst. Maar hier is iets om te begrijpen. Omdat u VBA in Excel gebruikt, is het niet nodig om het woord “Applicatie” te gebruiken. De code zou dus zijn:

 Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

En als u in Book1 zit, kunt u uw code verder verkleinen:

 Worksheets(“Sheet1”).Range(“A1”)

Maar als u zich al in het werkblad “Blad1” bevindt, kunt u uw code verder verkleinen en alleen het volgende gebruiken:

 Range(“A1”)

Stel nu dat u naar een heel bereik van cellen wilt verwijzen (dat wil zeggen meerdere cellen), dan moet u de code als volgt schrijven:

 Range("A1:A5")

In de bovenstaande code heeft u verwezen naar het bereik A1 tot en met A5, dat uit vijf cellen bestaat. U kunt ook naar een benoemd bereik verwijzen met behulp van het bereikobject. Stel dat u een benoemd bereik heeft met de naam ‘Verkoopkorting’ om ernaar te verwijzen, dan kunt u de volgende code schrijven:

 Range("Sales Discount")

Als je naar een niet-continu bereik wilt verwijzen, moet je zoiets als dit doen:

 Range("A1:B5,D5:G10")

En als u naar een hele rij of kolom wilt verwijzen, moet u een code invoeren zoals hieronder:

 Range("1:1") Range("A:A")

Op dit punt begrijpt u duidelijk hoe u naar een cel en het bereik van cellen moet verwijzen. Maar om er de beste mee te zijn, moet je leren hoe je het voor andere dingen kunt gebruiken.

Hier hebben we een uitgebreide lijst met zelfstudies die u kunt gebruiken om te leren werken met bereiken en cellen in VBA.

1. Selecteer en activeer een cel

Als u een cel wilt selecteren, kunt u bereik gebruiken. Selecteer de methode. Stel dat u cel A5 wilt selecteren. U hoeft alleen maar het bereik op te geven en daarna “.Select” toe te voegen.

 Range(“A1”).Select

Deze code vertelt VBA om cel A5 te selecteren en als je een celbereik wilt selecteren, verwijs dan gewoon naar dat bereik en voeg daarna eenvoudigweg “.Select” toe.

 Range(“A1:A5”).Select

Er is ook een andere methode die u kunt gebruiken om een cel te activeren.

 Range(“A1”).Activate

Hier moet u onthouden dat u slechts één cel tegelijk kunt activeren. Zelfs als u een bereik opgeeft met de “.Activate ” -methode, wordt dat bereik geselecteerd, maar de actieve cel is de eerste cel in het bereik.

2. Voer een waarde in een cel in

Met behulp van de bereikeigenschap kunt u een waarde invoeren in een cel of een celbereik. Laten we begrijpen hoe het werkt aan de hand van een eenvoudig voorbeeld:

 Range("A1").Value = "Exceladvisor"

In het bovenstaande voorbeeld hebt u A1 opgegeven als bereik en daarna hebt u ” .Value ” toegevoegd, waardoor VBA toegang krijgt tot de waarde-eigenschap van de cel.

Het volgende dat u hebt, is het gelijkteken en vervolgens de waarde die u wilt invoeren (u moet dubbele aanhalingstekens gebruiken als u een tekstwaarde invoert). Voor een getal zou de code er als volgt uitzien:

 Range("A1").Value = 9988

En als u een waarde in een celbereik wilt invoeren, ik bedoel meerdere cellen, dan hoeft u alleen maar dat bereik op te geven.

 Range("A1:A5").Value = "Exceladvisor"

En hier is de code als u verwijst naar het niet-continue bereik.

 Range("A1:A5 , E2:E3").Value = "Exceladvisor"

3. Kopieer en plak een cel/bereik

Met de eigenschap Range kunt u de methode “.Copy” gebruiken om een cel te kopiëren en deze vervolgens in een doelcel te plakken. Laten we zeggen dat u cel A5 moet kopiëren, de bijbehorende code zou zijn:

 Range("A5").Copy

Wanneer u deze code uitvoert, wordt alleen cel A5 gekopieerd, maar de volgende stap is het plakken van deze gekopieerde cel in een doelcel. Om dit te doen, moet u het bestemmingstrefwoord erna toevoegen, gevolgd door de cel waarin u het wilt plakken. Dus als u cel A1 wilt kopiëren en deze vervolgens in cel E5 wilt plakken, zou de code er als volgt uitzien:

 Range("A1").Copy Destination:=Range("E5")

Op dezelfde manier, als u te maken heeft met een bereik van meerdere cellen, zou de code er als volgt uitzien:

 Range("A1:A5").Copy Destination:=Range("E5:E9")

Als u een celbereik hebt gekopieerd en vervolgens een cel als doelbereik hebt vermeld, kopieert VBA het volledige gekopieerde bereik van de cel die u als doel hebt opgegeven.

 Range("A1:A5").Copy Destination:=Range("B1")

Wanneer u de bovenstaande code uitvoert, kopieert VBA het bereik A1:A5 en plakt dit in B1:B5, ook al heeft u alleen B1 als doelbereik vermeld.

Tip: Net als bij de “.Copy”-methode kunt u de “.Cut”-methode gebruiken om een cel te knippen en vervolgens eenvoudigweg een bestemming te gebruiken om deze te plakken.

4. Gebruik de eigenschap Font met de eigenschap Range

Met de bereikeigenschap heeft u toegang tot de lettertype-eigenschap van een cel waarmee u alle lettertype-instellingen kunt wijzigen. Er zijn in totaal 18 verschillende eigenschappen voor het lettertype waartoe u toegang hebt. Stel dat u de tekst vetgedrukt wilt maken in cel A1, dan zou de code er als volgt uitzien:

 Range("A1").Font.Bold = True

Deze code vertelt VBA om toegang te krijgen tot de eigenschap BOLD van het lettertype dat zich in het A1-bereik bevindt en u hebt deze eigenschap ingesteld op TRUE. Laten we nu zeggen dat u doorhaling wilt toepassen op cel A1, deze tijdcode zou er als volgt uitzien:

Zoals ik al zei, zijn er in totaal 18 verschillende eigenschappen die u kunt gebruiken, dus zorg ervoor dat u ze allemaal bekijkt om te zien welke voor u nuttig is.

5. Wis de opmaak van een cel

Met behulp van de “.ClearFormats”-methode kunt u alleen de opmaak van een cel of een celbereik wissen. Het enige wat u hoeft te doen is “.ClearFormat” toe te voegen nadat u het bereik hebt opgegeven, zoals hieronder:

 Range("A1").ClearFormats

Wanneer je de bovenstaande code uitvoert, wordt alle opmaak van cel A1 gewist en als je dat voor een heel bereik wilt doen, weet je wat je moet doen, toch?

 Range("A1:A5").ClearFormats

Nu verwijdert de bovenstaande code eenvoudigweg de opmaak van het bereik A1 tot A5.

Cel eigendom

Naast de eigenschap RANGE kunt u de eigenschap “Cellen” gebruiken om naar een cel of celbereik in uw werkblad te verwijzen. In de celeigenschap moet u, in plaats van de celverwijzing te gebruiken, het kolomnummer en rijnummer van de cel invoeren.

 expression.Cells(Row_Number, Column_Number)

Hier is de expressie een VBA-object en Row_Number is het rijnummer van de cel en Column_Number is de kolom van de cel. Dus als u naar cel A5 wilt verwijzen, kunt u de onderstaande code gebruiken:

 Cells(5,1)

Nu vertelt deze code VBA om te verwijzen naar de cel in rij nummer vijf en kolom nummer één. Zoals de syntaxis suggereert, moet u het kolomnummer als adres invoeren, maar de realiteit is dat u ook het kolomalfabet kunt gebruiken als u dat wilt, gewoon door er dubbele aanhalingstekens omheen te plaatsen.

De onderstaande code verwijst ook naar cel A5:

 Cells(5,"A")

En om het in VBA te selecteren, voegt u aan het einde gewoon “.Select” toe.

 Cells(5,1).Select

De bovenstaande code selecteert cel A5 die zich in de 5e rij en eerste kolom van het werkblad bevindt.

OFFSET-eigenschap

Als je goed met bereiken in VBA wilt spelen, moet je weten hoe je de eigenschap OFFSET moet gebruiken. Het is handig om naar een cel te verwijzen die zich op een bepaald aantal rijen en kolommen van een andere cel bevindt.

Stel dat uw actieve cel op dit moment B5 is en u wilt naar de cel navigeren die 3 kolommen naar rechts en 1 rij lager is dan B5, dan kunt u deze SHIFT uitvoeren. Hieronder vindt u de syntaxis die u voor OFFSET moet gebruiken:

 expression.Offset (RowOffset, ColumnOffset)

RowOffset: In dit argument moet u een getal opgeven dat VBA vertelt hoeveel rijen u wilt doorlopen. Een positief getal definieert een lijn naar beneden en een negatief getal definieert een lijn naar boven.
ColumnOffset : In dit argument moet u een getal opgeven dat VBA vertelt door hoeveel kolommen u wilt navigeren. Een positief getal definieert een rechterkolom en een negatief getal definieert een linkerkolom.

Laten we bijvoorbeeld een eenvoudige code schrijven die we hierboven hebben besproken.

Allereerst moet u het bereik definiëren waaruit u wilt navigeren en daarom de onderstaande code typen:
Typ daarna “.Offset” en voer openingshaakjes in, zoals hieronder:
Vervolgens moet u het rijnummer invoeren en vervolgens het kolomnummer waarnaar u wilt navigeren.
Aan het einde moet je “.Select” toevoegen om VBA te vertellen de cel te selecteren waarnaar je wilt navigeren.

Dus wanneer u deze code uitvoert, wordt de cel geselecteerd die één rij lager ligt en drie kolommen rechts van cel B5.

Wijzig de grootte van een bereik met OFFSET

Met OFFSET kunt u niet alleen naar een cel navigeren, maar kunt u het bereik ook verder vergroten/verkleinen. Laten we doorgaan met het bovenstaande voorbeeld.

 Range("B5").Offset(1, 3).Select

De bovenstaande code verwijst u naar cel E6, en laten we nu zeggen dat u het celbereik moet selecteren dat bestaat uit de vijf kolommen en drie rijen van E6. Dus wat u moet doen is na het gebruik van OFFSET de eigenschap resize gebruiken door “.Resize” toe te voegen.

 Range("B5").Offset(1, 3).Resize

Nu moet u de rijgrootte en kolomgrootte invoeren. Typ een haakje vooraan en voer het getal in om de rijgrootte in te stellen, en vervolgens een getal om de kolomgrootte in te stellen.

 Range("B5").Offset(1, 3).Resize(3,5)

Voeg aan het einde “.Select” toe om VBA te vertellen het bereik te selecteren, en wanneer u deze code uitvoert, wordt het bereik geselecteerd.

 Range("B5").Offset(1, 3).Resize(3, 5).Select

Dus wanneer u deze code uitvoert, wordt het bereik E6 tot I8 geselecteerd.

 Range("A1").Font.Strikethrough = True