Ranges and Cells i VBA
Excel -regneark lagrer data i celler. Celler er ordnet i rader og kolonner. Hver celle kan identifiseres ved skjæringspunktet i raden og kolonnen (eks. B3 eller R3C2).
Et Excel -område refererer til en eller flere celler (eks. A3: B4)
Celleadresse
A1 Notasjon
I A1 -notasjon refereres det til en celle med kolonnebokstaven (fra A til XFD) etterfulgt av radnummeret (fra 1 til 1 048 576).
I VBA kan du referere til hvilken som helst celle ved hjelp av Rekkevidde.
| 123456789 | 'Se celle B4 på det aktive arketMsgBox Range ("B4")'Se celle B4 på arket som heter' Data 'MsgBox -regneark ("Data"). Område ("B4")'Se celle B4 på arket som heter' Data 'i en annen OPEN arbeidsbok'kalt' Mine data 'MsgBox Workbooks ("Mine data"). Regneark ("Data"). Område ("B4") |
R1C1 -notasjon
I R1C1 -notasjon refereres en celle med R etterfulgt av radnummer, deretter bokstaven 'C' etterfulgt av kolonnenummeret. f.eks. B4 i R1C1 -notasjon vil bli referert av R4C2. I VBA bruker du Celler Objekt å bruke R1C1 -notasjon:
| 12 | 'Se celle R [6] C [4] dvs. D6Celler (6, 4) = "D6" |
Utvalg av celler
A1 Notasjon
For å referere til mer enn én celle, bruk et “:” mellom startcelleadressen og siste celleadresse. Følgende vil referere til alle cellene fra A1 til D10:
| 1 | Område ("A1: D10") |
R1C1 -notasjon
For å referere til mer enn én celle, bruk en “,” mellom startcelleadressen og siste celleadresse. Følgende vil referere til alle cellene fra A1 til D10:
| 1 | Område (celler (1, 1), celler (10, 4)) |
Skriver til celler
For å skrive verdier til en celle eller sammenhengende gruppe celler, referer du enkelt til området, setter et = -tegn og skriver deretter verdien som skal lagres:
| 12345678910 | 'Lagre F5 i celle med adresse F6Område ("F6") = "F6"'Lagre E6 i celle med adresse R [6] C [5] dvs. E6Celler (6, 5) = "E6"'Lagre A1: D10 i området A1: D10Område ("A1: D10") = "A1: D10"'ellerOmråde (celler (1, 1), celler (10, 4)) = "A1: D10" |
Lese fra celler
For å lese verdier fra celler, referer du enkelt til variabelen for å lagre verdiene, setter et = -tegn og refererer deretter til området som skal leses:
| 1234567891011 | Dim val1Dim val2'Les fra celle F6val1 = Område ("F6")'Les fra celle E6val2 = Celler (6, 5)MsgBox val1Msgbox val2 |
Merk: For å lagre verdier fra en rekke celler, må du bruke en matrise i stedet for en enkel variabel.
Ikke sammenhengende celler
For å referere til ikke -sammenhengende celler, bruk et komma mellom celleadressene:
| 123456 | 'Lagre 10 i cellene A1, A3 og A5Område ("A1, A3, A5") = 10'Lagre 10 i cellene A1: A3 og D1: D3)Område ("A1: A3, D1: D3") = 10 |
Skjæringspunkt mellom celler
For å referere til ikke -sammenhengende celler, bruk et mellomrom mellom celleadressene:
| 123 | 'Lagre' Col D 'i D1: D10'som er vanlig mellom A1: D10 og D1: F10Område ("A1: D10 D1: G10") = "Col D" |
Offset fra en celle eller et område
Ved å bruke forskyvningsfunksjonen kan du flytte referansen fra et gitt område (celle eller gruppe av celler) med det angitte antallet_ruter og antall_kolonner.
Offset syntaks
Range.Offset (number_of_rows, number_of_columns)
Offset fra en celle
| 12345678910111213141516 | 'OFFSET fra en celle A1'Se til selve cellen'Flytt 0 rader og 0 kolonnerOmråde ("A1"). Forskyvning (0, 0) = "A1"'Flytt 1 rad og 0 kolonnerOmråde ("A1"). Forskyvning (1, 0) = "A2"'Flytt 0 rader og 1 kolonnerOmråde ("A1"). Forskyvning (0, 1) = "B1"'Flytt 1 rad og 1 kolonneOmråde ("A1"). Forskyvning (1, 1) = "B2"'Flytt 10 rader og 5 kolonnerOmråde ("A1"). Forskyvning (10, 5) = "F11" |
Offset fra et område
| 123 | 'Flytt referanse til område A1: D4 med 4 rader og 4 kolonner'Ny referanse er E5: H8Område ("A1: D4"). Forskyvning (4,4) = "E5: H8" |
Angi referanse til et område
For å tilordne et område til en områdevariabel: erklær en variabel av typen Range, og bruk deretter Set -kommandoen for å angi det til et område. Vær oppmerksom på at du må bruke SET -kommandoen ettersom RANGE er et objekt:
| 12345678 | 'Angi en variabel for områdeDim myRange som Range'Sett variabelen til området A1: D4Angi myRange = Range ("A1: D4")'Skriver ut $ A $ 1: $ D $ 4MsgBox myRange.Adress |
Endre størrelse på et område
Endre størrelse på metode for områdeobjekt endrer dimensjonen til referanseområdet:
| 1234567 | Dim myRange As Range'Rekkevidde for å endre størrelseAngi myRange = Range ("A1: F4")'Skriver ut $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Adress |
Cellen øverst til venstre i området Endret størrelse er den samme som cellen øverst til venstre i det opprinnelige området
Endre størrelse på syntaks
Område. Endre størrelse (antall_rader, antall_kolonner)
OFFSET vs Resize
Offset endrer ikke dimensjonene til området, men flytter det med det angitte antallet rader og kolonner. Endre størrelse endrer ikke plasseringen til det opprinnelige området, men endrer dimensjonene til det angitte antallet rader og kolonner.
Alle celler i arket
Celle -objektet refererer til alle cellene i arket (1048576 rader og 16384 kolonner).
| 12 | 'Fjern alle celler i regnearkCells.Clear |
UsedRange
UsedRange-egenskapen gir deg det rektangulære området fra cellen øverst til venstre i cellen til den brukte cellen til høyre og nederst i det aktive arket.
| 1234567 | Dim ws Som regnearkSett ws = ActiveSheet'$ B $ 2: $ L $ 14 hvis L2 er den første cellen med en verdi'og L14 er den siste cellen med en hvilken som helst verdi på' aktivt arkDebug.Print ws.UsedRange.Address |
CurrentRegion
CurrentRegion-egenskapen gir deg det sammenhengende rektangulære området fra cellen øverst til venstre til den brukte cellen som er referert til cellen/området.
| 1234567891011 | Dim myRange As RangeSett myRange = Range ("D4: F6")'Skriver ut $ B $ 2: $ L $ 14'Hvis det er en fylt bane fra D4: F16 til B2 OG L14Debug.Print myRange.CurrentRegion.Address'Du kan også referere til en enkelt startcelleSett myRange = Range ("D4") 'Skriver ut $ B $ 2: $ L $ 14 |
Områdeegenskaper
Du kan få adresse, rad/kolonnummer i en celle og antall rader/kolonner i et område som angitt nedenfor:
| 123456789101112131415161718192021 | Dim myRange As RangeSett myRange = Range ("A1: F10")'Skriver ut $ A $ 1: $ F $ 10Debug.Print myRange.AddressSett myRange = Range ("F10")'Skriver ut 10 for rad 10Debug.Print myRange.Row'Skriver ut 6 for kolonne FDebug.Print myRange.ColumnAngi myRange = Range ("E1: F5")'Skriver ut 5 for antall rader i områdetDebug.Print myRange.Rows.Count'Skriver ut 2 for antall kolonner i områdetDebug.Print myRange.Columns.Count |
Siste celle i ark
Du kan bruke Antall rader og Kolonner. Telle eiendommer med Celler objekt for å få den siste cellen på arket:
| 1234567891011 | 'Skriv ut det siste radnummeret'Skriver ut 1048576Debug.Print "Rader i arket:" & Rows.Count'Skriv ut det siste kolonnenummeret'Skriver ut 16384Debug.Print "Kolonner i arket:" & Columns.Count'Skriv ut adressen til den siste cellen'Skriver ut $ XFD $ 1048576Debug.Print "Adressen til siste celle i arket:" og celler (Rows.Count, Columns.Count) |
Sist brukte radnummer i en kolonne
END -egenskapen tar deg den siste cellen i området, og End (xlUp) tar deg opp til den første brukte cellen fra den cellen.
| 123 | Dim lastRow As LonglastRow = Celler (Rows.Count, "A"). Slutt (xlUp) .Row |
Sist brukte kolonnummer på rad
| 123 | Dim lastCol As LonglastCol = Cells (1, Columns.Count) .End (xlToLeft) .Column |
END -egenskapen tar deg den siste cellen i området, og End (xlToLeft) tar deg til venstre til den første brukte cellen fra den cellen.
Du kan også bruke egenskapene xlDown og xlToRight til å navigere til de første nederste eller høyre brukte cellene i den gjeldende cellen.
Celleegenskaper
Felles eiendommer
Her er kode for å vise vanlige celleegenskaper
| 12345678910111213141516171819202122 | Dim cell As RangeAngi celle = område ("A1")celle. AktiverDebug.Print cell.Adress'Skriv ut $ A $ 1Debug.Print cell.Value'Skriver ut 456'AdresseDebug.Print cell.Formula'Utskrifter = SUM (C2: C3)' KommentarDebug.Print cell.Comment.Text' StilDebug.Print cell.Style'CelleformatDebug.Print cell.DisplayFormat.NumberFormat |
Cell Font
Cell.Font -objektet inneholder egenskapene til celleskriften:
| 1234567891011121314151617181920 | Dim cell As RangeAngi celle = område ("A1")'Vanlig, kursiv, fet og fet kursivcell.Font.FontStyle = "Fet kursiv"' Samme somcell.Font.Bold = Truecell.Font.Italic = True'Sett skrift til Couriercell.Font.FontStyle = "Courier"'Angi skriftfargecell.Font.Color = vbBlue'ellercell.Font.Color = RGB (255, 0, 0)'Angi skriftstørrelsecell.Font.Size = 20 |
Kopiere og lime inn
Lim inn alle
Områder/celler kan kopieres og limes inn fra et sted til et annet. Følgende kode kopierer alle egenskapene til kildeområdet til destinasjonsområdet (tilsvarer CTRL-C og CTRL-V)
| 1234567 | 'Enkel kopiOmråde ("A1: D20"). KopierRegneark ("Ark2"). Område ("B10"). Lim inn'eller'Kopi fra gjeldende ark til ark med navnet' Sheet2 'Område ("A1: D20"). Kopimål: = Regneark ("Ark2"). Område ("B10") |
Lim inn spesial
Utvalgte egenskaper for kildeområdet kan kopieres til destinasjonen ved å bruke PASTESPECIAL -alternativet:
| 123 | Lim inn området bare som verdierOmråde ("A1: D20"). KopierRegneark ("Sheet2"). Område ("B10"). PasteSpecial Paste: = xlPasteValues |
Her er de mulige alternativene for alternativet Lim inn:
| 12345678910111213 | 'Lim inn spesielle typerxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
AutoFit -innhold
Størrelsen på rader og kolonner kan endres for å passe innholdet ved hjelp av koden nedenfor:
| 12345 | 'Endre størrelsen på rad 1 til 5 for å passe innholdetRader ("1: 5"). AutoFit'Endre størrelsen på kolonnene A til B for å passe innholdetKolonner ("A: B"). AutoFit |
Flere rekkeviddeeksempler
Det anbefales at du bruker makroopptaker mens du utfører den nødvendige handlingen gjennom GUI. Det vil hjelpe deg å forstå de forskjellige alternativene som er tilgjengelige og hvordan du bruker dem.
For hver
Det er lettere å gå gjennom et område med For hver konstruer som vist nedenfor:
| 123 | For hver celle i området ("A1: B100")'Gjør noe med cellenNeste celle |
Ved hver iterasjon av løkken blir en celle i området tilordnet variabelen c, og setninger i For -løkken utføres for den cellen. Sløyfe går ut når alle cellene er behandlet.
Sortere
Sort er en metode for Range -objekt. Du kan sortere et område ved å angi alternativer for sortering til Range.Sort. Koden nedenfor vil sortere kolonnene A: C basert på nøkkelen i celle C2. Sorteringsrekkefølge kan være xlAscending eller xlDescending. Overskrift: = xlJa skal brukes hvis første rad er overskriftsrad.
| 12 | Kolonner ("A: C"). Sorter nøkkel1: = Område ("C2"), _order1: = xlAscending, Header: = xlJa |
Finne
Find er også en metode for Range Object. Den finner den første cellen som har innhold som samsvarer med søkekriteriene og returnerer cellen som et Range -objekt. Det kommer tilbake Ingenting hvis det ikke er noen match.
Bruk Finn neste metode (eller FindPrevious) for å finne neste (forrige) forekomst.
Følgende kode vil endre skriften til "Arial Black" for alle celler i området som starter med "John":
| 12345 | For hver c i rekkevidde ("A1: A100")Hvis c Like "John*" Dac.Font.Name = "Arial Black"Slutt omNeste c |
Følgende kode vil erstatte alle forekomster av "å teste" til "bestått" i det angitte området:
| 12345678910 | Med rekkevidde ("a1: a500")Sett c = .Find ("To Test", LookIn: = xlValues)Hvis ikke c er ingenting dafirstaddress = c.AdressGjørec.Value = "Bestått"Sett c = .FindNext (c)Loop While Not c is Nothing And c.Adress firstaddressSlutt omSlutt med |
Det er viktig å merke seg at du må angi et område for å bruke FindNext. Du må også gi en stopptilstand, ellers vil løkken utføres for alltid. Normalt lagres adressen til den første cellen som blir funnet i en variabel, og sløyfen stoppes når du kommer til cellen igjen. Du må også se etter saken når ingenting er funnet for å stoppe løkken.
Områdeadresse
Bruk Range.Address for å få adressen i A1 Style
| 123 | MsgBox Range ("A1: D10"). Adresse'ellerDebug.Print Range ("A1: D10"). Adresse |
Bruk xlReferenceStyle (standard er xlA1) for å få tillegg i R1C1 -stil
| 123 | MsgBox Range ("A1: D10"). Adresse (ReferenceStyle: = xlR1C1)'ellerDebug.Print Range ("A1: D10"). Adresse (ReferenceStyle: = xlR1C1) |
Dette er nyttig når du håndterer områder lagret i variabler og bare vil behandle for bestemte adresser.
Rekkevidde til Array
Det er raskere og enklere å overføre et område til en matrise og deretter behandle verdiene. Du bør deklarere matrisen som variant for å unngå å beregne størrelsen som kreves for å fylle ut området i matrisen. Arrayens dimensjoner er satt til å matche antall verdier i området.
| 123456789 | Dim DirArray som variant'Lagre verdiene i området til matrisenDirArray = Område ("a1: a5"). Verdi'Sløyfe for å behandle verdieneFor hver c i DirArrayDebug.Print cNeste |
Array to Range
Etter behandling kan du skrive Array tilbake til et område. For å skrive matrisen i eksemplet ovenfor til et område må du angi et område hvis størrelse samsvarer med antall elementer i matrisen.
Bruk koden nedenfor for å skrive matrisen til området D1: D5:
| 123 | Område ("D1: D5"). Verdi = DirArrayOmråde ("D1: H1"). Verdi = Application.Transpose (DirArray) |
Vær oppmerksom på at du må Transponere matrisen hvis du skriver den til en rad.
Sum rekkevidde
| 12 | SumOfRange = Application.WorksheetFunction.Sum (område ("A1: A10"))Debug.Print SumOfRange |
Du kan bruke mange tilgjengelige funksjoner i Excel i VBA -koden ved å spesifisere Application.WorkSheetFunction. før funksjonsnavnet som i eksemplet ovenfor.
Count Range
| 1234567 | 'Tell antall celler med tall i områdetCountOfCells = Application.WorksheetFunction.Count (område ("A1: A10"))Debug.Print CountOfCells'Antall antall ikke -tomme celler i områdetCountOfNonBlankCells = Application.WorksheetFunction.CountA (område ("A1: A10"))Debug.Print CountOfNonBlankCells |
Skrevet av: Vinamra Chandra
