Excel VBA -intervaller og -celler

Ranger og celler i VBA

Excel -regneark gemmer data i celler. Celler er arrangeret i rækker og kolonner. Hver celle kan identificeres ved skæringspunktet for dens række og kolonne (eks. B3 eller R3C2).

Et Excel -område refererer til en eller flere celler (f.eks. A3: B4)

Celleadresse

A1 Notation

I A1 -notation henvises til en celle med dens kolonnebogstav (fra A til XFD) efterfulgt af dens rækkenummer (fra 1 til 1.048.576).

I VBA kan du referere til enhver celle ved hjælp af Områdeobjekt.

123456789

'Se celle B4 på det aktuelt aktive arkMsgBox -område ("B4")'Se celle B4 på arket med navnet' Data 'MsgBox -regneark ("Data"). Område ("B4")'Se celle B4 på arket med navnet' Data 'i en anden ÅBEN projektmappe'navngivet' Mine data 'MsgBox Workbooks ("Mine data"). Regneark ("Data"). Område ("B4")

R1C1 Notation

I R1C1 -notation henvises en celle med R efterfulgt af rækkenummer og derefter bogstavet 'C' efterfulgt af kolonnenummeret. f.eks. B4 i R1C1 -notation vil blive henvist med R4C2. I VBA bruger du Celler Objekt at bruge R1C1 -notation:

12	'Se celle R [6] C [4] dvs. D6Celler (6, 4) = "D6"

Rækkevidde af celler

A1 Notation

For at referere til mere end én celle skal du bruge et “:” mellem startcelleadressen og den sidste celleadresse. Det følgende vil referere til alle cellerne fra A1 til D10:

1	Område ("A1: D10")

R1C1 Notation

For at referere til mere end én celle skal du bruge et “,” mellem startcelleadressen og den sidste celleadresse. Det følgende vil referere til alle cellerne fra A1 til D10:

1	Område (celler (1, 1), celler (10, 4))

Skriv til celler

For at skrive værdier til en celle eller en sammenhængende gruppe af celler skal du blot henvise til intervallet, sætte et = -tegn og derefter skrive værdien, der skal gemmes:

12345678910

'Gem F5 i celle med adresse F6Område ("F6") = "F6"'Opbevar E6 i celle med adresse R [6] C [5] dvs. E6Celler (6, 5) = "E6"'Opbevar A1: D10 i området A1: D10Område ("A1: D10") = "A1: D10"'ellerOmråde (celler (1, 1), celler (10, 4)) = "A1: D10"

Læsning fra celler

For at læse værdier fra celler skal du blot henvise til variablen for at gemme værdierne, sætte et = -tegn og derefter henvise til det område, der skal læses:

1234567891011

Dim val1Dim val2'Læs fra celle F6val1 = Område ("F6")'Læs fra celle E6val2 = Celler (6, 5)MsgBox val1Msgbox val2

Bemærk: For at gemme værdier fra en række celler skal du bruge et array i stedet for en simpel variabel.

Ikke sammenhængende celler

For at henvise til ikke -sammenhængende celler skal du bruge et komma mellem celleadresserne:

123456

'Gem 10 i cellerne A1, A3 og A5Område ("A1, A3, A5") = 10'Gem 10 i cellerne A1: A3 og D1: D3)Område ("A1: A3, D1: D3") = 10

Skæringspunkt mellem celler

For at referere til ikke -sammenhængende celler skal du bruge et mellemrum mellem celleadresserne:

123	'Opbevar' Col D 'i D1: D10'som er fælles mellem A1: D10 og D1: F10Område ("A1: D10 D1: G10") = "Col D"

Offset fra en celle eller et område

Ved hjælp af forskydningsfunktionen kan du flytte referencen fra et givet område (celle eller gruppe af celler) med de angivne antal_række og antal_kolonner.

Offset syntaks

Område. Forskydning (antal_række, antal_kolonner)

Offset fra en celle

12345678910111213141516

'OFFSET fra en celle A1'Henvis til selve cellen'Flyt 0 rækker og 0 kolonnerOmråde ("A1"). Offset (0, 0) = "A1"'Flyt 1 rækker og 0 kolonnerOmråde ("A1"). Offset (1, 0) = "A2"'Flyt 0 rækker og 1 kolonnerOmråde ("A1"). Offset (0, 1) = "B1"'Flyt 1 rækker og 1 kolonnerOmråde ("A1"). Offset (1, 1) = "B2"'Flyt 10 rækker og 5 kolonnerOmråde ("A1"). Offset (10, 5) = "F11"

Offset fra en rækkevidde

123	'Flyt reference til område A1: D4 med 4 rækker og 4 kolonner'Ny reference er E5: H8Område ("A1: D4"). Offset (4,4) = "E5: H8"

Indstilling af reference til et område

For at tildele et område til en områdevariabel: angiv en variabel af typen Range, og brug derefter kommandoen Set til at indstille det til et område. Bemærk, at du skal bruge kommandoen SET, da RANGE er et objekt:

12345678

'Angiv en variabel for områdeDim myRange som Range'Indstil variablen til intervallet A1: D4Indstil myRange = Range ("A1: D4")'Udskriver $ A $ 1: $ D $ 4MsgBox myRange.Adresse

Ændre størrelsen på et område

Ændre størrelse på metode til områdeobjekt ændrer dimensionen af ​​referenceområdet:

1234567

Dim myRange As Range'Område for at ændre størrelseIndstil myRange = Range ("A1: F4")'Udskriver $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Adresse

Cellen øverst til venstre i det ændrede område er den samme som den øverste venstre celle i det originale område

Ændre størrelse på syntaks

Område. Størrelse (antal_række, antal_kolonner)

OFFSET vs Resize

Offset ændrer ikke områdets dimensioner, men flytter det med det angivne antal rækker og kolonner. Tilpas størrelse ændrer ikke placeringen af ​​det originale område, men ændrer dimensionerne til det angivne antal rækker og kolonner.

Alle celler i ark

Celleobjektet refererer til alle cellerne i arket (1048576 rækker og 16384 kolonner).

12	'Ryd alle celler i regnearkCeller.Ryd

UsedRange

Egenskaben UsedRange giver dig det rektangulære område fra cellen øverst til venstre i cellen til den anvendte celle i højre side i det aktive ark.

1234567

Dim ws Som regnearkIndstil ws = ActiveSheet'$ B $ 2: $ L $ 14, hvis L2 er den første celle med en hvilken som helst værdi'og L14 er den sidste celle med en hvilken som helst værdi på'aktivt arkDebug.Print ws.UsedRange.Address

CurrentRegion

CurrentRegion-egenskaben giver dig det sammenhængende rektangulære område fra cellen øverst til venstre til den højre celle-nederste celle, der indeholder den celle/område, der refereres til.

1234567891011

Dim myRange As RangeIndstil myRange = Range ("D4: F6")'Udskriver $ B $ 2: $ L $ 14'Hvis der er en fyldt sti fra D4: F16 til B2 OG L14Debug.Print myRange.CurrentRegion.Address'Du kan også henvise til en enkelt startcelleIndstil myRange = Range ("D4") 'Udskriver $ B $ 2: $ L $ 14

Områdeegenskaber

Du kan få adresse, række/kolonnummer i en celle og antal rækker/kolonner i et område som angivet nedenfor:

123456789101112131415161718192021

Dim myRange As RangeIndstil myRange = Range ("A1: F10")'Udskriver $ A $ 1: $ F $ 10Debug.Print myRange.AddressIndstil myRange = Range ("F10")'Udskriver 10 til række 10Debug.Print myRange.Row'Udskriver 6 til kolonne FDebug.Print myRange.ColumnIndstil myRange = Range ("E1: F5")'Udskriver 5 for antal rækker inden for rækkeviddeDebug.Print myRange.Rows.Count'Udskriver 2 for antallet af kolonner inden for rækkeviddeDebug.Print myRange.Columns.Count

Sidste celle i ark

Du kan bruge Rækker. Tæller og Kolonner.Tælling ejendomme med Celler objekt for at få den sidste celle på arket:

1234567891011

'Udskriv det sidste rækkenummer'Udskriver 1048576Debug.Print "Rækker i arket:" & Rows.Count'Udskriv det sidste kolonnenummer'Udskriver 16384Debug.Print "Kolonner i arket:" & Columns.Count'Udskriv adressen på den sidste celle'Udskriver $ XFD $ 1048576Debug.Print "Adresse for sidste celle i arket:" & Celler (Rows.Count, Columns.Count)

Sidst anvendte rækkenummer i en kolonne

END -egenskaben tager dig den sidste celle i området, og End (xlUp) fører dig op til den første brugte celle fra den celle.

123	Dim lastRække så længelastRow = Celler (Rows.Count, "A"). Slut (xlUp) .Row

Sidst anvendte kolonnenummer i en række

123	Dim lastCol så længelastCol = Celler (1, Columns.Count) .End (xlToLeft) .Column

END -egenskaben tager dig den sidste celle i området, og End (xlToLeft) tager dig tilbage til den første brugte celle fra den celle.

Du kan også bruge egenskaberne xlDown og xlToRight til at navigere til de første nederste eller højre brugte celler i den aktuelle celle.

Celleegenskaber

Fælles ejendomme

Her er kode til visning af almindeligt anvendte celleegenskaber

12345678910111213141516171819202122

Dim celle som områdeIndstil celle = område ("A1")celle.AktiverDebug.Print cell.Adress'Udskriv $ A $ 1Debug.Print cell.Value'Udskriver 456'AdresseDebug.Print cell.Formula'Udskriver = SUM (C2: C3)'KommentarDebug.Print cell.Comment.Text'StilDebug.Print cell.Style'CelleformatDebug.Print cell.DisplayFormat.NumberFormat

Celle skrifttype

Cell.Font -objekt indeholder egenskaber for celleskrifttypen:

1234567891011121314151617181920

Dim celle som områdeIndstil celle = område ("A1")'Almindelig, kursiv, fed og fed kursivcell.Font.FontStyle = "Fed kursiv"' Samme somcell.Font.Bold = Sandtcell.Font.Italic = Sandt'Indstil skrifttype til Couriercell.Font.FontStyle = "Courier"'Indstil skrifttypefarvecell.Font.Color = vbBlue'ellercell.Font.Color = RGB (255, 0, 0)'Indstil skrifttypecell.Font.Size = 20

Kopiere og indsætte

Indsæt alle

Områder/celler kan kopieres og indsættes fra et sted til et andet. Den følgende kode kopierer alle egenskaberne for kildeområdet til destinationsområdet (svarer til CTRL-C og CTRL-V)

1234567

'Enkel kopiOmråde ("A1: D20"). KopiArbejdsark ("Ark2"). Område ("B10"). Indsæt'eller'Kopiér fra nuværende ark til ark med navnet' Sheet2 'Område ("A1: D20"). Kopidestination: = Regneark ("Ark2"). Område ("B10")

Indsæt special

Udvalgte egenskaber for kildeområdet kan kopieres til destinationen ved at bruge indstillingen PASTESPECIAL:

123	'Indsæt området kun som værdierOmråde ("A1: D20"). KopiRegneark ("Sheet2"). Område ("B10"). PasteSpecial Paste: = xlPasteValues

Her er de mulige muligheder for indsætningen Indsæt:

12345678910111213

'Indsæt særlige typerxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulas AndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats

AutoFit indhold

Størrelse på rækker og kolonner kan ændres, så det passer til indholdet ved hjælp af nedenstående kode:

12345

'Skift størrelse på rækker 1 til 5 for at passe til indholdetRækker ("1: 5"). AutoFit'Skift størrelse på kolonner A til B, så det passer til indholdetKolonner ("A: B"). AutoFit

Flere rækkeeksempler

Det anbefales, at du bruger Macro Recorder, mens du udfører den nødvendige handling via GUI. Det hjælper dig med at forstå de forskellige muligheder, og hvordan du bruger dem.

For hver

Det er lettere at gå gennem et område ved hjælp af For hver konstruer som vist herunder:

123	For hver celle i området ("A1: B100")'Gør noget med cellenNæste celle

Ved hver iteration af sløjfen tildeles en celle i området variablen c, og sætninger i For -løkken udføres for den celle. Loop forlader, når alle cellerne behandles.

Sortere

Sort er en metode til Range -objekt. Du kan sortere et område ved at angive muligheder for at sortere til Range.Sort. Koden herunder sorterer kolonnerne A: C baseret på nøglen i celle C2. Sorteringsrækkefølge kan være xlAscending eller xlDescending. Overskrift: = xlJa skal bruges, hvis første række er overskriftsrækken.

12	Kolonner ("A: C"). Sorter nøgle1: = Område ("C2"), _ordre1: = xlAscending, Header: = xlJa

Find

Find er også en metode til Range Object. Den finder den første celle med indhold, der matcher søgekriterierne, og returnerer cellen som et områdeobjekt. Det vender tilbage Ikke noget hvis der ikke er nogen match.

Brug FindNæste metode (eller FindPrevious) for at finde den næste (forrige) forekomst.

Følgende kode ændrer skrifttypen til "Arial Black" for alle celler i området, der starter med "John":

12345

For hver c In Range ("A1: A100")Hvis c kan lide "John*" Såc.Font.Name = "Arial Black"Afslut HvisNæste c

Følgende kode vil erstatte alle forekomster af "At teste" til "Bestået" i det angivne område:

12345678910

Med rækkevidde ("a1: a500")Indstil c = .Find ("To Test", LookIn: = xlValues)Hvis ikke c er ingenting Såfirstaddress = c.AdresseGørc.Value = "Bestået"Indstil c = .FindNext (c)Loop While Not c er ingenting og c.Adresse firstaddressAfslut HvisAfslut med

Det er vigtigt at bemærke, at du skal angive et område for at bruge FindNext. Du skal også angive en standsningstilstand, ellers vil løkken udføres for evigt. Normalt lagres adressen til den første celle, der findes, i en variabel, og sløjfen stoppes, når du når denne celle igen. Du skal også kontrollere for sagen, når der ikke er fundet noget til at stoppe sløjfen.

Områdeadresse

Brug Range.Address for at få adressen i A1 Style

123	MsgBox -område ("A1: D10"). Adresse'ellerDebug.Print Range ("A1: D10"). Adresse

Brug xlReferenceStyle (standard er xlA1) for at få addres i R1C1 -stil

123	MsgBox -område ("A1: D10"). Adresse (ReferenceStyle: = xlR1C1)'ellerDebug.Print Range ("A1: D10"). Adresse (ReferenceStyle: = xlR1C1)

Dette er nyttigt, når du beskæftiger dig med områder, der er gemt i variabler og kun ønsker at behandle for bestemte adresser.

Rækkevidde til Array

Det er hurtigere og lettere at overføre et område til et array og derefter behandle værdierne. Du bør erklære arrayet som variant for at undgå at beregne den størrelse, der kræves for at udfylde området i arrayet. Array dimensioner er indstillet til at matche antallet af værdier i området.

123456789

Dim DirArray som variant'Gem værdierne i området til ArrayDirArray = Range ("a1: a5"). Værdi'Sløjfe for at behandle værdierneFor hver c i DirArrayDebug.Print cNæste

Array til rækkevidde

Efter behandling kan du skrive Array tilbage til et område. For at skrive arrayet i eksemplet ovenfor til et område skal du angive et område, hvis størrelse svarer til antallet af elementer i arrayet.

Brug koden herunder til at skrive Array til området D1: D5:

123	Område ("D1: D5"). Værdi = DirArrayOmråde ("D1: H1"). Værdi = Application.Transpose (DirArray)

Bemærk, at du skal omsætte arrayet, hvis du skriver det til en række.

Sum rækkevidde

12	SumOfRange = Application.WorksheetFunction.Sum (område ("A1: A10"))Debug.Print SumOfRange

Du kan bruge mange tilgængelige funktioner i Excel i din VBA -kode ved at angive Application.WorkSheetFunction. før funktionsnavnet som i eksemplet ovenfor.

Tæl rækkevidde

1234567

'Tæl antal celler med tal i områdetCountOfCells = Application.WorksheetFunction.Count (område ("A1: A10"))Debug.Print CountOfCells'Tæl antal ikke -tomme celler i områdetCountOfNonBlankCells = Application.WorksheetFunction.CountA (område ("A1: A10"))Debug.Print CountOfNonBlankCells

Skrevet af: Vinamra Chandra