Wanneer u met Excel werkt, brengt u het grootste deel van uw tijd door in het werkbladgebied - het omgaan met cellen en bereiken.
En als u uw werk in Excel wilt automatiseren met VBA, moet u weten hoe u met cellen en bereiken werkt met VBA.
Er zijn veel verschillende dingen die u kunt doen met bereiken in VBA (zoals selecteren, kopiëren, verplaatsen, bewerken, enz.).
Dus om dit onderwerp te behandelen, zal ik deze tutorial in secties opbreken en je laten zien hoe je met cellen en bereiken in Excel VBA kunt werken aan de hand van voorbeelden.
Laten we beginnen.
Alle codes die ik in deze tutorial noem, moeten in de VB Editor worden geplaatst. Ga naar het gedeelte 'Waar de VBA-code plaatsen' om te weten hoe het werkt.Als je geïnteresseerd bent om VBA op een gemakkelijke manier te leren, bekijk dan mijn Online Excel VBA-training.
Een cel / bereik selecteren in Excel met VBA
Als u met VBA met cellen en bereiken in Excel wilt werken, hoeft u dit niet te selecteren.
In de meeste gevallen kunt u beter geen cellen of bereiken selecteren (zoals we zullen zien).
Desondanks is het belangrijk dat u dit gedeelte doorneemt en begrijpt hoe het werkt. Dit zal cruciaal zijn bij het leren van VBA en veel concepten die hier worden behandeld, zullen in deze tutorial worden gebruikt.
Dus laten we beginnen met een heel eenvoudig voorbeeld.
Een enkele cel selecteren met VBA
Als u een enkele cel in het actieve blad wilt selecteren (zeg A1), dan kunt u de onderstaande code gebruiken:
Sub SelectCell() Range("A1").Select End Sub
De bovenstaande code heeft het verplichte gedeelte 'Sub' en 'End Sub' en een regel code die cel A1 selecteert.
Range ("A1") vertelt VBA het adres van de cel waarnaar we willen verwijzen.
Selecteer is een methode van het Range-object en selecteert de cellen/het bereik gespecificeerd in het Range-object. De celverwijzingen moeten tussen dubbele aanhalingstekens staan.
Deze code geeft een fout weer als een grafiekblad een actief blad is. Een kaartblad bevat grafieken en wordt niet veel gebruikt. Omdat het geen cellen / bereiken bevat, kan de bovenstaande code het niet selecteren en zou er uiteindelijk een fout worden weergegeven.
Merk op dat aangezien u de cel in het actieve blad wilt selecteren, u alleen het celadres hoeft op te geven.
Maar als u de cel in een ander blad wilt selecteren (laten we zeggen Blad2), moet u eerst Blad2 activeren en vervolgens de cel erin selecteren.
Sub SelectCell () Worksheets ("Sheet2"). Activeer Range ("A1"). Selecteer End Sub
Op dezelfde manier kunt u ook een werkmap activeren, vervolgens een specifiek werkblad erin activeren en vervolgens een cel selecteren.
Sub SelectCell() Workbooks("Book2.xlsx").Worksheets("Sheet2").Activate Range("A1").Select End Sub
Merk op dat wanneer u naar werkmappen verwijst, u de volledige naam samen met de bestandsextensie (.xlsx in de bovenstaande code) moet gebruiken. Als de werkmap nog nooit is opgeslagen, hoeft u de bestandsextensie niet te gebruiken.
Nu zijn deze voorbeelden niet erg handig, maar je zult later in deze tutorial zien hoe we dezelfde concepten kunnen gebruiken om cellen in Excel te kopiëren en plakken (met VBA).
Net zoals we een cel selecteren, kunnen we ook een bereik selecteren.
In het geval van een bereik kan dit een bereik met een vaste maat zijn of een bereik met een variabele maat.
In een bereik met een vaste grootte weet u hoe groot het bereik is en kunt u de exacte grootte in uw VBA-code gebruiken. Maar met een bereik van variabele grootte heb je geen idee hoe groot het bereik is en moet je een beetje VBA-magie gebruiken.
Laten we eens kijken hoe dit te doen.
Een bereik met vaste grootte selecteren
Hier is de code die het bereik A1:D20 selecteert.
Sub SelectRange() Range("A1:D20").Select End Sub
Een andere manier om dit te doen is door de onderstaande code te gebruiken:
Sub SelectRange() Range("A1", "D20").Select End Sub
De bovenstaande code neemt het celadres linksboven (A1) en het celadres rechtsonder (D20) en selecteert het volledige bereik. Deze techniek wordt handig wanneer u werkt met bereiken van variabele grootte (zoals we zullen zien wanneer de eigenschap End later in deze zelfstudie wordt behandeld).
Als u wilt dat de selectie in een andere werkmap of een ander werkblad plaatsvindt, moet u VBA de exacte namen van deze objecten vertellen.
De onderstaande code zou bijvoorbeeld het bereik A1: D20 selecteren in het werkblad Blad2 in de Boek2-werkmap.
Sub SelectRange() Workbooks("Book2.xlsx").Worksheets("Sheet1").Activate Range("A1:D20").Select End Sub
Nu, wat als u niet weet hoeveel rijen er zijn. Wat als u alle cellen wilt selecteren die een waarde bevatten.
In deze gevallen moet u de methoden gebruiken die in de volgende sectie worden getoond (bij het selecteren van een bereik met variabele grootte).
Een bereik van variabele grootte selecteren
Er zijn verschillende manieren om een celbereik te selecteren. Welke methode u kiest, hangt af van hoe de gegevens zijn gestructureerd.
In deze sectie zal ik enkele handige technieken behandelen die erg handig zijn als je met bereiken in VBA werkt.
Selecteer CurrentRang-eigenschap gebruiken
In gevallen waarin u niet weet hoeveel rijen/kolommen de gegevens bevatten, kunt u de eigenschap CurrentRange van het Range-object gebruiken.
De eigenschap CurrentRange omvat alle aaneengesloten gevulde cellen in een gegevensbereik.
Hieronder vindt u de code die de huidige regio met cel A1 selecteert.
Sub SelectCurrentRegion() Range("A1").CurrentRegion.Select End Sub
De bovenstaande methode is goed als u alle gegevens als een tabel hebt zonder lege rijen/kolommen erin.
Maar als u lege rijen/kolommen in uw gegevens heeft, worden de rijen/kolommen na de lege rijen/kolommen niet geselecteerd. In de onderstaande afbeelding selecteert de CurrentRegion-code gegevens tot rij 10, aangezien rij 11 leeg is.
In dergelijke gevallen wilt u misschien de eigenschap UsedRange van het werkbladobject gebruiken.
Selecteer Use UsedRange Property
Met UsedRange kunt u verwijzen naar alle cellen die zijn gewijzigd.
Dus de onderstaande code zou alle gebruikte cellen in het actieve blad selecteren.
Sub SelectUsedRegion() ActiveSheet.UsedRange.Select End Sub
Merk op dat in het geval dat u een verre cel heeft die is gebruikt, deze door de bovenstaande code wordt beschouwd en alle cellen totdat die gebruikte cel wordt geselecteerd.
Selecteer De eindeigenschap gebruiken
Dit deel is echt handig.
Met de eigenschap End kunt u de laatst gevulde cel selecteren. Hiermee kunt u het effect nabootsen van de Ctrl-pijltjestoets omlaag/omhoog of de Ctrl-toets rechts/links.
Laten we proberen dit te begrijpen aan de hand van een voorbeeld.
Stel je hebt een dataset zoals hieronder weergegeven en je wilt snel de laatst gevulde cellen in kolom A selecteren.
Het probleem hier is dat gegevens kunnen veranderen en dat u niet weet hoeveel cellen zijn gevuld. Als u dit met het toetsenbord moet doen, kunt u cel A1 selecteren en vervolgens Control + pijl-omlaag gebruiken en de laatst gevulde cel in de kolom selecteren.
Laten we nu eens kijken hoe we dit kunnen doen met VBA. Deze techniek is handig wanneer u snel naar de laatst gevulde cel in een kolom met variabele grootte wilt springen
Sub GoToLastFilledCell() Range("A1").End(xlDown).Select End Sub
De bovenstaande code springt naar de laatst gevulde cel in kolom A.
Op dezelfde manier kunt u End(xlToRight) gebruiken om naar de laatst gevulde cel in een rij te springen.
Sub GoToLastFilledCell() Range("A1").End(xlToRight).Select End Sub
Wat als u nu de hele kolom wilt selecteren in plaats van naar de laatst gevulde cel te springen.
Dat doe je met onderstaande code:
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown)).Select End Sub
In de bovenstaande code hebben we de eerste en de laatste verwijzing gebruikt van de cel die we moeten selecteren. Het maakt niet uit hoeveel gevulde cellen er zijn, de bovenstaande code selecteert alles.
Onthoud het bovenstaande voorbeeld waarin we het bereik A1:D20 hebben geselecteerd met behulp van de volgende regel code:
Bereik ("A1″,"D20")
Hier was A1 de cel linksboven en D20 de cel rechtsonder in het bereik. We kunnen dezelfde logica gebruiken bij het selecteren van bereiken met variabele afmetingen. Maar omdat we het exacte adres van de cel rechtsonder niet weten, hebben we de eigenschap End gebruikt om het te krijgen.
In Range(“A1”, Range(“A1”).End(xlDown)), verwijst “A1” naar de eerste cel en Range(“A1”).End(xlDown) verwijst naar de laatste cel. Omdat we beide referenties hebben opgegeven, selecteert de Select-methode alle cellen tussen deze twee referenties.
Op dezelfde manier kunt u ook een volledige gegevensset selecteren die meerdere rijen en kolommen heeft.
De onderstaande code zou alle gevulde rijen / kolommen selecteren vanaf cel A1.
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select End Sub
In de bovenstaande code hebben we Range ("A1").End(xlDown).End(xlToRight) gebruikt om de verwijzing te krijgen van de rechtsonder gevulde cel van de dataset.
Verschil tussen het gebruik van CurrentRegion en End
Als je je afvraagt waarom je de eigenschap End gebruikt om het gevulde bereik te selecteren als we de eigenschap CurrentRegion hebben, laat me je het verschil vertellen.
Met de eigenschap End kunt u de startcel specificeren. Als u bijvoorbeeld uw gegevens in A1:D20 hebt, maar de eerste rij koppen zijn, kunt u de eigenschap End gebruiken om de gegevens zonder de koppen te selecteren (met behulp van de onderstaande code).
Sub SelectFilledCells() Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select End Sub
Maar de CurrentRegion zou automatisch de hele dataset selecteren, inclusief de headers.
Tot nu toe hebben we in deze zelfstudie gezien hoe u op verschillende manieren naar een reeks cellen kunt verwijzen.
Laten we nu eens kijken naar enkele manieren waarop we deze technieken daadwerkelijk kunnen gebruiken om wat werk gedaan te krijgen.
Kopieer cellen / bereiken met VBA
Zoals ik aan het begin van deze tutorial al zei, is het niet nodig om een cel te selecteren om er acties op uit te voeren. U zult in dit gedeelte zien hoe u cellen en bereiken kunt kopiëren zonder deze zelfs maar te selecteren.
Laten we beginnen met een eenvoudig voorbeeld.
Enkele cel kopiëren
Als u cel A1 wilt kopiëren en in cel D1 wilt plakken, is de onderstaande code voldoende.
Sub CopyCell() Range("A1").Copy Range("D1") End Sub
Merk op dat de kopieermethode van het bereikobject de cel kopieert (net als Controle +C) en deze in de opgegeven bestemming plakt.
In de bovenstaande voorbeeldcode wordt de bestemming opgegeven in dezelfde regel waar u de methode Kopiëren gebruikt. Als je je code nog leesbaarder wilt maken, kun je onderstaande code gebruiken:
Sub CopyCell() Range("A1").Copy Destination:=Range("D1") End Sub
De bovenstaande codes kopiëren en plakken de waarde en de opmaak/formules erin.
Zoals je misschien al hebt opgemerkt, kopieert de bovenstaande code de cel zonder deze te selecteren. Waar u zich ook op het werkblad bevindt, de code kopieert cel A1 en plakt deze op D1.
Merk ook op dat de bovenstaande code elke bestaande code in cel D2 zou overschrijven. Als u wilt dat Excel u laat weten of er al iets in cel D1 staat zonder het te overschrijven, kunt u de onderstaande code gebruiken.
Sub CopyCell() If Range("D1") "" Then Response = MsgBox("Wilt u de bestaande gegevens overschrijven", vbYesNo) End If If Response = vbYes Then Range("A1").Copy Range("D1 ") End If End Sub
Een bereik met vaste grootte kopiëren
Als u A1:D20 in J1:M20 wilt kopiëren, kunt u de onderstaande code gebruiken:
Sub CopyRange() Range("A1:D20").Copy Range("J1") End Sub
In de doelcel hoeft u alleen het adres van de cel linksboven op te geven. De code kopieert automatisch het exacte gekopieerde bereik naar de bestemming.
U kunt dezelfde constructie gebruiken om gegevens van het ene blad naar het andere te kopiëren.
De onderstaande code kopieert A1:D20 van het actieve blad naar Blad2.
Sub CopyRange() Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Het bovenstaande kopieert de gegevens van het actieve blad. Zorg er dus voor dat het blad met de gegevens het actieve blad is voordat u de code uitvoert. Voor de zekerheid kunt u ook de naam van het werkblad opgeven tijdens het kopiëren van de gegevens.
Sub CopyRange() Worksheets("Blad1").Range("A1:D20").Copy Worksheets("Blad2").Range("A1") End Sub
Het goede aan de bovenstaande code is dat ongeacht welk blad actief is, het altijd de gegevens van Blad1 zal kopiëren en in Blad2 zal plakken.
U kunt een benoemd bereik ook kopiëren door de naam ervan te gebruiken in plaats van de verwijzing.
Als u bijvoorbeeld een benoemd bereik heeft met de naam 'SalesData', kunt u de onderstaande code gebruiken om deze gegevens naar Blad2 te kopiëren.
Sub CopyRange() Range("SalesData").Copy Worksheets("Sheet2").Range("A1") End Sub
Als het bereik van het benoemde bereik de hele werkmap is, hoeft u zich niet op het blad met het benoemde bereik te bevinden om deze code uit te voeren. Aangezien het genoemde bereik voor de werkmap is bedoeld, kunt u deze met deze code vanaf elk blad openen.
Als u een tabel met de naam Table1 heeft, kunt u de onderstaande code gebruiken om deze naar Blad2 te kopiëren.
Sub CopyTable() Range("Table1[#All]").Copy Worksheets("Sheet2").Range("A1") End Sub
U kunt een bereik ook naar een andere werkmap kopiëren.
In het volgende voorbeeld kopieer ik de Excel-tabel (Tabel1) naar de Book2-werkmap.
Sub CopyCurrentRegion() Range("Table1[#All]").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub
Deze code werkt alleen als de werkmap al is geopend.
Een bereik met variabele grootte kopiëren
Een manier om bereiken van variabele grootte te kopiëren, is deze om te zetten in benoemde bereiken of Excel-tabel en de codes te gebruiken zoals weergegeven in de vorige sectie.
Maar als u dat niet kunt, kunt u de eigenschap CurrentRegion of End van het bereikobject gebruiken.
De onderstaande code zou het huidige gebied in het actieve blad kopiëren en in Blad2 plakken.
Sub CopyCurrentRegion() Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1") End Sub
Als u de eerste kolom van uw dataset tot de laatst gevulde cel wilt kopiëren en in Sheet2 wilt plakken, kunt u de onderstaande code gebruiken:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Blad2").Range("A1") End Sub
Als u zowel de rijen als de kolommen wilt kopiëren, kunt u de onderstaande code gebruiken:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Blad2").Range("A1") End Sub
Merk op dat al deze codes de cellen niet selecteren terwijl ze worden uitgevoerd. Over het algemeen vindt u slechts een handvol gevallen waarin u daadwerkelijk een cel/bereik moet selecteren voordat u eraan werkt.
Bereiken toewijzen aan objectvariabelen
Tot nu toe hebben we het volledige adres van de cellen gebruikt (zoals Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”)).
Om uw code beter beheersbaar te maken, kunt u deze bereiken toewijzen aan objectvariabelen en deze variabelen vervolgens gebruiken.
In de onderstaande code heb ik bijvoorbeeld het bron- en doelbereik toegewezen aan objectvariabelen en deze variabelen vervolgens gebruikt om gegevens van het ene bereik naar het andere te kopiëren.
Sub CopyRange() Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets("Sheet1").Range("A1:D20") Set DestinationRange = Worksheets("Sheet2").Range("A1") SourceRange.Copy DestinationRange Einde sub
We beginnen met het declareren van de variabelen als Range-objecten. Vervolgens wijzen we het bereik aan deze variabelen toe met behulp van de Set-instructie. Nadat het bereik aan de variabele is toegewezen, kunt u de variabele gewoon gebruiken.
Voer gegevens in de volgende lege cel in (met invoervak)
U kunt de invoervakken gebruiken om de gebruiker toe te staan de gegevens in te voeren.
Stel dat u onderstaande dataset heeft en u wilt het verkooprecord invoeren, dan kunt u het invoerveld in VBA gebruiken. Met behulp van een code kunnen we ervoor zorgen dat deze de gegevens in de volgende lege rij vult.
Sub EnterData() Dim RefRange As Range Set RefRange = Range("A1").End(xlDown).Offset(1, 0) Set ProductCategory = RefRange.Offset(0, 1) Set Quantity = RefRange.Offset(0, 2 ) Bedrag instellen = RefRange.Offset(0, 3) RefRange.Value = RefRange.Offset(-1, 0).Value + 1 ProductCategory.Value = InputBox("Product Category") Hoeveelheid.Value = InputBox("Quantity") Amount.Value = InputBox("Bedrag") End Sub
De bovenstaande code gebruikt het VBA-invoervak om de invoer van de gebruiker te krijgen en voert vervolgens de invoer in de opgegeven cellen in.
Merk op dat we geen exacte celverwijzingen hebben gebruikt. In plaats daarvan hebben we de eigenschap End en Offset gebruikt om de laatste lege cel te vinden en de gegevens erin in te vullen.
Deze code is verre van bruikbaar. Als u bijvoorbeeld een tekenreeks invoert wanneer het invoervak om hoeveelheid of bedrag vraagt, zult u merken dat Excel dit toestaat. U kunt een If-voorwaarde gebruiken om te controleren of de waarde numeriek is of niet en deze vervolgens dienovereenkomstig toestaan.
Door cellen/bereiken doorlussen
Tot nu toe hebben we gezien hoe we de gegevens in cellen en bereiken kunnen selecteren, kopiëren en invoeren.
In deze sectie zullen we zien hoe u door een reeks cellen/rijen/kolommen in een bereik kunt bladeren. Dit kan handig zijn wanneer u elke cel wilt analyseren en op basis daarvan een actie wilt uitvoeren.
Als u bijvoorbeeld elke derde rij in de selectie wilt markeren, moet u doorlopen en het rijnummer controleren. Evenzo, als u alle negatieve cellen wilt markeren door de letterkleur in rood te wijzigen, moet u de waarde van elke cel doorlopen en analyseren.
Hier is de code die door de rijen in de geselecteerde cellen loopt en alternatieve rijen markeert.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selectie voor elke Myrow In Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
De bovenstaande code gebruikt de MOD-functie om het rijnummer in de selectie te controleren. Als het rijnummer even is, wordt het gemarkeerd in cyaan.
Hier is nog een voorbeeld waarbij de code door elke cel gaat en de cellen markeert die een negatieve waarde bevatten.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Mycell As Range Set Myrange = Selectie voor elke Mycell In Myrange Als Mycell < 0 Dan Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Merk op dat u hetzelfde kunt doen met voorwaardelijke opmaak (wat dynamisch is en een betere manier om dit te doen). Dit voorbeeld is alleen bedoeld om u te laten zien hoe looping werkt met cellen en bereiken in VBA.
Waar de VBA-code te plaatsen
Vraagt u zich af waar de VBA-code naartoe gaat in uw Excel-werkmap?
Excel heeft een VBA-backend, de VBA-editor. U moet de code kopiëren en plakken in het codevenster van de VB Editor-module.
Hier zijn de stappen om dit te doen:
- Ga naar het tabblad Ontwikkelaars.
- Klik op de Visual Basic-optie. Dit opent de VB-editor in de backend.
- Klik in het deelvenster Projectverkenner in de VB-editor met de rechtermuisknop op een object voor de werkmap waarin u de code wilt invoegen.Als u de Projectverkenner niet ziet, gaat u naar het tabblad Weergave en klikt u op Projectverkenner.
- Ga naar Invoegen en klik op Module. Hiermee wordt een moduleobject voor uw werkmap ingevoegd.
- Kopieer en plak de code in het modulevenster.
