Bereiken en cellen in VBA
Excel-spreadsheets slaan gegevens op in Cellen. Cellen zijn gerangschikt in rijen en kolommen. Elke cel kan worden geïdentificeerd door het snijpunt van de rij en kolom (vb. B3 of R3C2).
Een Excel-bereik verwijst naar een of meer cellen (bijv. A3:B4)
Mobiel adres
A1 Notatie
In de A1-notatie wordt naar een cel verwezen met de kolomletter (van A tot XFD) gevolgd door het rijnummer (van 1 tot 1.048.576).
In VBA kun je naar elke cel verwijzen met de Bereikobject.
123456789 | ' Raadpleeg cel B4 op het momenteel actieve bladMsgBox-bereik ("B4")' Raadpleeg cel B4 op het blad met de naam 'Gegevens'MsgBox-werkbladen ("Data"). Bereik ("B4")' Raadpleeg cel B4 op het blad met de naam 'Gegevens' in een andere OPEN-werkmap' genaamd 'Mijn gegevens'MsgBox-werkmappen ("Mijn gegevens"). Werkbladen ("Gegevens"). Bereik ("B4") |
R1C1-notatie
In R1C1-notatie wordt naar een cel verwezen door R gevolgd door rijnummer en vervolgens letter 'C' gevolgd door het kolomnummer. bijv. B4 in R1C1-notatie wordt verwezen door R4C2. In VBA gebruik je de Cellen Object om de R1C1-notatie te gebruiken:
12 | ' Raadpleeg cel R[6]C[4] d.w.z. D6Cellen (6, 4) = "D6" |
Bereik van cellen
A1 Notatie
Om naar meer dan één cel te verwijzen, gebruikt u een ":" tussen het beginceladres en het laatste celadres. Het volgende verwijst naar alle cellen van A1 tot D10:
1 | Bereik ("A1:D10") |
R1C1-notatie
Om naar meer dan één cel te verwijzen, gebruikt u een "," tussen het beginceladres en het laatste celadres. Het volgende verwijst naar alle cellen van A1 tot D10:
1 | Bereik (cellen (1, 1), cellen (10, 4)) |
Schrijven naar cellen
Om waarden naar een cel of aangrenzende groep cellen te schrijven, verwijst u eenvoudig naar het bereik, plaatst u een = teken en schrijft u vervolgens de waarde die moet worden opgeslagen:
12345678910 | ' Bewaar F5 in cel met adres F6Bereik ("F6") = "F6"' Bewaar E6 in cel met adres R[6]C[5], d.w.z. E6Cellen (6, 5) = "E6"' Bewaar A1:D10 in het bereik A1:D10Bereik ("A1:D10") = "A1:D10"' ofBereik (cellen (1, 1), cellen (10, 4)) = "A1:D10" |
Lezen uit cellen
Om waarden uit cellen te lezen, verwijst u eenvoudig naar de variabele om de waarden op te slaan, plaatst u een = -teken en verwijst u vervolgens naar het te lezen bereik:
1234567891011 | Dim val1Dim val2' Lezen uit cel F6val1 = Bereik ("F6")' Lezen uit cel E6val2 = Cellen (6, 5)MsgBox val1Msgbox val2 |
Opmerking: om waarden uit een celbereik op te slaan, moet u een matrix gebruiken in plaats van een eenvoudige variabele.
Niet-aangrenzende cellen
Gebruik een komma tussen de celadressen om naar niet-aaneengesloten cellen te verwijzen:
123456 | ' Bewaar 10 in cellen A1, A3 en A5Bereik ("A1,A3,A5") = 10' Bewaar 10 in cellen A1:A3 en D1:D3)Bereik ("A1:A3, D1:D3") = 10 |
Snijpunt van cellen
Gebruik een spatie tussen de celadressen om naar niet-aangrenzende cellen te verwijzen:
123 | ' Bewaar 'Kol D' in D1:D10' wat gebruikelijk is tussen A1:D10 en D1:F10Bereik ("A1:D10 D1:G10") = "Kol D" |
Offset van een cel of bereik
Met de Offset-functie kunt u de verwijzing van een bepaald bereik (cel of groep cellen) verplaatsen met het opgegeven aantal_rijen en aantal_kolommen.
Offset-syntaxis
Range.Offset (aantal_rijen, aantal_kolommen)
Offset vanaf een cel
12345678910111213141516 | ' OFFSET van een cel A1' Verwijs naar cel zelf' Verplaats 0 rijen en 0 kolommenBereik ("A1"). Offset (0, 0) = "A1"' Verplaats 1 rijen en 0 kolommenBereik ("A1"). Offset (1, 0) = "A2"' Verplaats 0 rijen en 1 kolommenBereik ("A1"). Offset (0, 1) = "B1"' Verplaats 1 rijen en 1 kolommenBereik ("A1"). Offset (1, 1) = "B2"' Verplaats 10 rijen en 5 kolommenBereik ("A1"). Offset (10, 5) = "F11" |
Offset van een bereik
123 | ' Verplaats referentie naar bereik A1:D4 met 4 rijen en 4 kolommen' Nieuwe referentie is E5:H8Bereik ("A1:D4").Offset(4,4) = "E5:H8" |
Referentie naar een bereik instellen
Om een bereik toe te wijzen aan een bereikvariabele: declareer een variabele van het type Bereik en gebruik vervolgens de opdracht Set om het in te stellen op een bereik. Houd er rekening mee dat u het SET-commando moet gebruiken omdat RANGE een object is:
12345678 | ' Declareer een bereikvariabeleDim myRange als bereik' Stel de variabele in op het bereik A1:D4Stel myRange = Range ("A1:D4") in' Drukt $A$1 af:$D$4MsgBox myRange.Adres |
Formaat van een bereik wijzigen
Resize methode van Range-object verandert de afmeting van het referentiebereik:
1234567 | Dim myRange als bereik' Bereik om de grootte te wijzigenStel myRange = Range ("A1:F4") in' Drukt $A$1:$E$10 afDebug.Print myRange.Resize (10, 5).Adres |
Cel in de linkerbovenhoek van het bereik met gewijzigde grootte is hetzelfde als de cel in de linkerbovenhoek van het oorspronkelijke bereik
Grootte van syntaxis wijzigen
Bereik.Resize (aantal_van_rijen, aantal_van_kolommen)
OFFSET versus formaat wijzigen
Offset verandert de afmetingen van het bereik niet, maar verplaatst het met het opgegeven aantal rijen en kolommen. Formaat wijzigen verandert niet de positie van het oorspronkelijke bereik, maar wijzigt de afmetingen in het opgegeven aantal rijen en kolommen.
Alle cellen in blad
Het object Cellen verwijst naar alle cellen in het blad (1048576 rijen en 16384 kolommen).
12 | ' Wis alle cellen in werkbladenCellen.Clear |
GebruiktBereik
De eigenschap UsedRange geeft u het rechthoekige bereik van de gebruikte cel linksboven tot de gebruikte cel rechtsonder van het actieve blad.
1234567 | Dim ws als werkbladStel ws = ActiveSheet in' $B$2:$L$14 als L2 de eerste cel is met een waarde' en L14 is de laatste cel met een waarde op de' actief bladDebug.Print ws.UsedRange.Address |
HuidigeRegio
De eigenschap CurrentRegion geeft u het aaneengesloten rechthoekige bereik van de cel linksboven tot de gebruikte cel rechtsonder die de cel/het bereik waarnaar wordt verwezen.
1234567891011 | Dim myRange als bereikStel myRange = Range ("D4:F6") in' Drukt $B$2 af:$L$14' Als er een gevuld pad is van D4:F16 naar B2 EN L14Debug.Print myRange.CurrentRegion.Address' U kunt ook verwijzen naar een enkele startcelSet myRange = Range("D4") ' Print $B$2:$L$14 |
Bereik eigenschappen
U kunt het adres, het rij-/kolomnummer van een cel en het aantal rijen/kolommen in een bereik krijgen zoals hieronder aangegeven:
123456789101112131415161718192021 | Dim myRange als bereikStel myRange = Range ("A1:F10") in' Drukt $A$1 af:$F$10Debug.Print myRange.AdresStel myRange = Range ("F10") in' Drukt 10 af voor rij 10Debug.Print myRange.Row' Print 6 voor kolom FDebug.Print myRange.ColumnStel myRange = Range ("E1:F5") in' Drukt 5 af voor het aantal rijen in het bereikDebug.Print myRange.Rows.Count' Drukt 2 af voor het aantal kolommen in het bereikDebug.Print myRange.Columns.Count |
Laatste cel in blad
Je kunt gebruiken Rijen.Tellen en Columns.Count eigenschappen met Cellen object om de laatste cel op het blad te krijgen:
1234567891011 | ' Druk het laatste rijnummer af' Afdrukken 1048576Debug.Print "Rijen in het blad: " & Rows.Count' Druk het laatste kolomnummer af' Prenten 16384Debug.Print "Kolommen in het blad: " & Columns.Count' Print het adres van de laatste cel' Drukt $ XFD $ 1048576 afDebug.Print "Adres van laatste cel in het blad: " & Cells(Rows.Count, Columns.Count) |
Laatst gebruikte rijnummer in een kolom
De eigenschap END brengt u naar de laatste cel in het bereik en End(xlUp) brengt u naar de eerste gebruikte cel van die cel.
123 | Dim laatsteRij Zo LanglastRow = Cells(Rijen.Count, "A").End(xlUp).Rij |
Laatst gebruikte kolomnummer in een rij
123 | Dim laatsteKol Zo LanglastCol = Cellen(1, Kolommen.Aantal).End(xlNaarLinks).Kolom |
De eigenschap END brengt u naar de laatste cel in het bereik en End(xlToLeft) brengt u naar links naar de eerste gebruikte cel van die cel.
U kunt ook de eigenschappen xlDown en xlToRight gebruiken om naar de eerste cellen onderaan of rechts van de huidige cel te navigeren.
Celeigenschappen
Algemene eigenschappen
Hier is code om veelgebruikte celeigenschappen weer te geven
12345678910111213141516171819202122 | Dim cel als bereikCel instellen = bereik ("A1")cel.ActiverenDebug.Print cel.Adres' Afdrukken $ A $ 1Debug.Print cel.Value' Afdrukken 456' AdresDebug.Print cel.Formule' Drukt =SOM(C2:C3) af' OpmerkingDebug.Print cel.Comment.Text' StijlDebug.Print cel.Style' CelformaatDebug.Print cel.DisplayFormat.NumberFormat |
Cellettertype
Cell.Font-object bevat eigenschappen van het Cell Font:
1234567891011121314151617181920 | Dim cel als bereikCel instellen = bereik ("A1")' Normaal, cursief, vet en vet cursiefcell.Font.FontStyle = "Vet cursief"' Hetzelfde alscel.Font.Bold = Truecel.Font.Italic = True' Stel lettertype in op Couriercell.Font.FontStyle = "Koerier"' Letterkleur instellencel.Font.Color = vbBlue' ofcel.Lettertypekleur = RGB(255, 0, 0)' Lettergrootte instellencel.Lettergrootte = 20 |
Knippen en plakken
Alles plakken
Bereiken/cellen kunnen van de ene naar de andere locatie worden gekopieerd en geplakt. De volgende code kopieert alle eigenschappen van het bronbereik naar het doelbereik (gelijk aan CTRL-C en CTRL-V)
1234567 | 'Eenvoudige kopie'Bereik ("A1:D20"). KopiërenWerkbladen ("Blad2"). Bereik ("B10"). Plakken'of' Kopieer van huidig blad naar blad met de naam 'Blad2'Bereik ("A1: D20"). Kopieerbestemming: = Werkbladen ("Blad2"). Bereik ("B10") |
Plakken speciaal
Geselecteerde eigenschappen van het bronbereik kunnen naar de bestemming worden gekopieerd met behulp van de PASTESPECIAL-optie:
123 | ' Plak het bereik als alleen waardenBereik ("A1:D20"). KopiërenWerkbladen ("Blad2").Bereik ("B10").Plakken Speciaal Plakken:=xlPasteValues |
Dit zijn de mogelijke opties voor de optie Plakken:
12345678910111213 | 'Speciale typen plakken'xlPlakkenAllesxlPlakkenAllesBehalveBordersxlPlakkenAlles SamenvoegenVoorwaardelijkeFormatenxlPlakkenAllesGebruikBronThemaxlPlakkenKolomBreedtenxlPlakkenOpmerkingenxlPlakkenFormatenxlPlakkenFormulesxlPlakkenFormulesAndNumberFormatsxlPlakkenValidatiexlPlakkenWaardenxlPlakkenValuesAndNumberFormats |
Inhoud automatisch aanpassen
De grootte van rijen en kolommen kan worden aangepast aan de inhoud met behulp van de onderstaande code:
12345 | ' Verander de grootte van rijen 1 tot 5 om in de inhoud te passenRijen ("1:5").AutoAanpassen' Verander de grootte van kolommen A naar B om in de inhoud te passenKolommen ("A:B").AutoAanpassen |
Meer bereikvoorbeelden
Het wordt aanbevolen dat u Macro Recorder gebruikt terwijl u de vereiste actie uitvoert via de GUI. Het zal u helpen de verschillende beschikbare opties te begrijpen en hoe u ze kunt gebruiken.
voor elk
Het is gemakkelijker om door een bereik te lopen met voor elk bouw zoals hieronder getoond:
123 | Voor elke cel binnen bereik ("A1:B100")'Doe iets met de cel'Volgende cel |
Bij elke iteratie van de lus wordt één cel in het bereik toegewezen aan de variabele c en worden instructies in de For-lus voor die cel uitgevoerd. Loop wordt afgesloten wanneer alle cellen zijn verwerkt.
Soort
Sorteren is een methode van Range-object. U kunt een bereik sorteren door sorteeropties op te geven in Range.Sort. De onderstaande code sorteert de kolommen A:C op basis van de sleutel in cel C2. Sorteervolgorde kan xlAscending of xlDescending zijn. Header:= xlYes moet worden gebruikt als de eerste rij de koprij is.
12 | Columns("A:C").Sorteersleutel1:=Bereik("C2"), _order1:=xlOplopend, Header:=xlJa |
Vind
Zoeken is ook een methode van Range Object. Het vindt de eerste cel met inhoud die overeenkomt met de zoekcriteria en retourneert de cel als een bereikobject. Het keert terug Niks als er geen match is.
Gebruik maken van ZoekVolgende methode (of FindPrevious) om de volgende (vorige) instantie te vinden.
De volgende code verandert het lettertype in "Arial Black" voor alle cellen in het bereik die beginnen met "John":
12345 | Voor elke c binnen bereik ("A1:A100")Als c Like "John*" Danc.Font.Name = "Arial Zwart"Stop alsvolgende c |
De volgende code vervangt alle keren dat "Te testen" voorkomt in "Geslaagd" in het opgegeven bereik:
12345678910 | Met bereik ("a1:a500")Stel c = .Find("Te testen", LookIn:=xlValues) inAls niet c is niets danfirstaddress = c.AdresDoenc.Waarde = "Geslaagd"Stel c = .FindNext (c) inLoop While Not c Is Nothing En c.Address firstaddressStop alsEindigt met |
Het is belangrijk op te merken dat u een bereik moet opgeven om FindNext te gebruiken. U moet ook een stopvoorwaarde opgeven, anders wordt de lus voor altijd uitgevoerd. Normaal gesproken wordt het adres van de eerste gevonden cel opgeslagen in een variabele en wordt de lus gestopt wanneer u die cel weer bereikt. U moet ook controleren of er niets wordt gevonden om de lus te stoppen.
Bereik adres
Gebruik Range.Address om het adres in A1-stijl te krijgen
123 | MsgBox-bereik ("A1: D10"). Adres:' ofDebug.Afdrukbereik ("A1:D10").Adres |
Gebruik xlReferenceStyle (standaard is xlA1) om adressen in R1C1-stijl te krijgen
123 | MsgBox-bereik ("A1: D10"). Adres (Referentiestijl: = xlR1C1)' ofDebug.Afdrukbereik ("A1:D10").Adres (Referentiestijl:=xlR1C1) |
Dit is handig wanneer u te maken hebt met bereiken die zijn opgeslagen in variabelen en alleen voor bepaalde adressen wilt verwerken.
Bereik tot array
Het is sneller en gemakkelijker om een bereik over te dragen naar een array en vervolgens de waarden te verwerken. U moet de array declareren als Variant om te voorkomen dat de grootte wordt berekend die nodig is om het bereik in de array te vullen. De afmetingen van de array zijn zo ingesteld dat ze overeenkomen met het aantal waarden in het bereik.
123456789 | Dim DirArray als variant' Sla de waarden op in het bereik naar de arrayDirArray = Bereik ("a1:a5"). Waarde' Loop om de waarden te verwerkenVoor elke c in DirArrayDebug.Print cVolgende |
Array naar bereik
Na verwerking kun je de Array terugschrijven naar een Range. Om de array in het bovenstaande voorbeeld naar een bereik te schrijven, moet je een bereik opgeven waarvan de grootte overeenkomt met het aantal elementen in de array.
Gebruik de onderstaande code om de array naar het bereik D1:D5 te schrijven:
123 | Bereik ("D1:D5"). Waarde = DirArrayBereik ("D1:H1"). Waarde = Toepassing. Transponeren (DirArray) |
Houd er rekening mee dat u de array moet transponeren als u deze naar een rij schrijft.
Sombereik
12 | SumOfRange = Application.WorksheetFunction.Sum(Range("A1:A10"))Debug.Print SumOfRange |
U kunt veel functies die beschikbaar zijn in Excel in uw VBA-code gebruiken door Application.WorkSheetFunction op te geven. voor de functienaam zoals in het bovenstaande voorbeeld.
Telbereik
1234567 | ' Tel het aantal cellen met getallen in het bereikCountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))Debug.Print CountOfCells' Tel het aantal niet-lege cellen in het bereikCountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))Debug.Print CountOfNonBlankCells |
Geschreven door: Vinamra Chandra