Dit artikel laat zien hoe u een dynamisch bereik kunt maken in Excel VBA.
Het declareren van een specifiek cellenbereik als een variabele in Excel VBA beperkt ons tot het werken met alleen die specifieke cellen. Door dynamische bereiken in Excel te declareren, krijgen we veel meer flexibiliteit over onze code en de functionaliteit die deze kan uitvoeren.
Verwijzen naar bereiken en cellen
Wanneer we verwijzen naar het bereik- of celobject in Excel, verwijzen we er normaal gesproken naar door hard te coderen in de rij en kolommen die we nodig hebben.
Bereik eigenschap
Met behulp van de Range-eigenschap, in de onderstaande voorbeeldregels code, kunnen we acties op dit bereik uitvoeren, zoals het wijzigen van de kleur van de cellen of het vet maken van de cellen.
| 12 | Bereik ("A1:A5"). Lettertypekleur = vbRoodBereik ("A1:A5"). Lettertype. Vet = True |
Cellen eigenschap
Op dezelfde manier kunnen we de eigenschap Cellen gebruiken om naar een celbereik te verwijzen door rechtstreeks te verwijzen naar de rij en kolom in de eigenschap cellen. De rij moet altijd een getal zijn, maar de kolom kan een getal zijn of een letter tussen aanhalingstekens.
Er kan bijvoorbeeld naar het celadres A1 worden verwezen als:
| 1 | Cellen(1,1) |
Of
| 1 | Cellen (1, "A") |
Om de eigenschap Cellen te gebruiken om naar een celbereik te verwijzen, moeten we het begin van het bereik en het einde van het bereik aangeven.
Om bijvoorbeeld naar het bereik A1: A6 te verwijzen, kunnen we deze syntaxis hieronder gebruiken:
| 1 | Bereik(Cellen(1,1), Cellen(1,6) |
We kunnen dan de eigenschap Cellen gebruiken om acties op het bereik uit te voeren volgens de onderstaande voorbeeldregels code:
| 12 | Bereik (Cellen (2, 2), Cellen (6, 2)). Lettertype Kleur = vbRedBereik (cellen (2, 2), cellen (6, 2)). Lettertype. Vet = True |
Dynamische bereiken met variabelen
Aangezien de grootte van onze gegevens in Excel verandert (d.w.z. we gebruiken meer rijen en kolommen dan de bereiken die we hebben gecodeerd), zou het handig zijn als de bereiken waarnaar we verwijzen in onze code ook zouden veranderen. Met behulp van het bovenstaande Range-object kunnen we variabelen maken om de maximale rij- en kolomnummers van het gebied van het Excel-werkblad dat we gebruiken op te slaan, en deze variabelen gebruiken om het Range-object dynamisch aan te passen terwijl de code wordt uitgevoerd.
Bijvoorbeeld
| 1234 | Dim lRij als geheel getalDim lCol als geheel getallRij = Bereik ("A1048576"). Einde (xlOmhoog). RijlKol = Bereik ("XFD1").Einde(xlNaarLinks).Kolom |
Laatste rij in kolom
Aangezien er 1048576 rijen in een werkblad zijn, gaat de variabele lRow naar de onderkant van het werkblad en gebruikt dan de speciale combinatie van de End-toets plus de pijl-omhoog-toets om naar de laatste rij te gaan die in het werkblad is gebruikt - dit geeft ons het nummer van de rij die we nodig hebben in ons assortiment.
Laatste kolom in rij
Op dezelfde manier gaat de lCol naar Kolom XFD, de laatste kolom in een werkblad, en gebruikt dan de speciale toetsencombinatie van de End-toets plus de linkerpijltoets om naar de laatste kolom te gaan die in het werkblad wordt gebruikt - dit geeft ons de nummer van de kolom die we nodig hebben in ons assortiment.
Daarom kunnen we de volgende code uitvoeren om het volledige bereik te krijgen dat in het werkblad wordt gebruikt:
| 1234567891011 | Sub GetRange()Dim lRij als geheel getalDim lCol As IntegerDim rng als bereiklRij = Bereik ("A1048576"). Einde (xlOmhoog). Rij'gebruik de lRow om de laatste kolom in het bereik te vinden'lKol = Bereik ("XFD" & lRij).Einde(xlNaarLinks).KolomStel rng = Bereik (Cellen (1, 1), Cellen (lRij, lCol))'msgbox om ons het assortiment te laten zienMsgBox "Bereik is " & rng.AdresEinde sub |
SpecialCells - LastCell
We kunnen ook de SpecialCells-methode van het Range-object gebruiken om de laatste rij en kolom te krijgen die in een werkblad wordt gebruikt.
| 123456789101112 | SubgebruikSpecialCells()Dim lRij als geheel getalDim lCol As IntegerDim rng als bereikDim rngBegin als bereikStel rngBegin = Bereik ("A1") inlRij = rngBegin.SpecialCells(xlCellTypeLastCell).RijlCol = rngBegin.SpecialCells(xlCellTypeLastCell).KolomStel rng = Bereik (Cellen (1, 1), Cellen (lRij, lCol))'msgbox om ons het assortiment te laten zienMsgBox "Bereik is " & rng.AdresEinde sub |
GebruiktBereik
De methode Gebruikte bereik omvat alle cellen die waarden bevatten in het huidige werkblad.
| 123456 | Sub GebruiktBereikVoorbeeld()Dim rng als bereikStel rng = ActiveSheet.UsedRange in'msgbox om ons het assortiment te laten zienMsgBox "Bereik is " & rng.AdresEinde sub |
HuidigeRegio
Het huidige gebied verschilt van het UsedRange doordat het kijkt naar de cellen die een cel omringen die we als startbereik hebben gedeclareerd (dwz de variabele rngBegin in het onderstaande voorbeeld), en vervolgens kijkt naar alle cellen die 'bijgevoegd' of geassocieerd zijn naar die aangegeven cel. Mocht er een lege cel in een rij of kolom voorkomen, dan stopt de CurrentRegion met zoeken naar verdere cellen.
| 12345678 | Sub huidige regio()Dim rng als bereikDim rngBegin als bereikStel rngBegin = Bereik ("A1") inStel rng = rngBegin.CurrentRegion in'msgbox om ons het assortiment te laten zienMsgBox "Bereik is " & rng.AdresEinde sub |
Als we deze methode gebruiken, moeten we ervoor zorgen dat alle cellen in het bereik dat u nodig hebt, zijn verbonden zonder lege rijen of kolommen ertussen.
Benoemd bereik
We kunnen ook verwijzen naar Named Ranges in onze code. Benoemde bereiken kunnen dynamisch zijn voor zover wanneer gegevens worden bijgewerkt of ingevoegd, de bereiknaam kan worden gewijzigd om de nieuwe gegevens op te nemen.
In dit voorbeeld wordt het lettertype vetgedrukt voor de bereiknaam "Januari"
| 12345 | Sub RangeNameExample()Dim rng als bereikStel rng = Bereik ("januari") inrng.Font.Bold = = TrueEinde sub |
Zoals u in de onderstaande afbeelding zult zien, wordt als een rij wordt toegevoegd aan de bereiknaam, de bereiknaam automatisch bijgewerkt om die rij op te nemen.
Als we vervolgens de voorbeeldcode opnieuw zouden uitvoeren, zou het bereik dat door de code wordt beïnvloed C5:C9 zijn, terwijl dit in eerste instantie C5:C8 zou zijn.
Tafels
We kunnen verwijzen naar tabellen (klik voor meer informatie over het maken en manipuleren van tabellen in VBA) in onze code. Als tabelgegevens in Excel worden bijgewerkt of gewijzigd, verwijst de code die naar de tabel verwijst naar de bijgewerkte tabelgegevens. Dit is met name handig wanneer wordt verwezen naar draaitabellen die zijn verbonden met een externe gegevensbron.
Door deze tabel in onze code te gebruiken, kunnen we verwijzen naar de kolommen van de tabel met de koppen in elke kolom en acties op de kolom uitvoeren op basis van hun naam. Naarmate de rijen in de tabel toenemen of afnemen volgens de gegevens, wordt het tabelbereik dienovereenkomstig aangepast en werkt onze code nog steeds voor de hele kolom in de tabel.
Bijvoorbeeld:
| 123 | Sub DeleteTableColumn()ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").ListColumns("Leverancier").VerwijderenEinde sub |
