Excel VBA-bereiken en cellen

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