Når du arbejder med Excel, bruges det meste af din tid i regnearksområdet - håndtering af celler og områder.
Og hvis du vil automatisere dit arbejde i Excel ved hjælp af VBA, skal du vide, hvordan du arbejder med celler og områder ved hjælp af VBA.
Der er mange forskellige ting, du kan gøre med områder i VBA (f.eks. Vælg, kopier, flyt, rediger osv.).
Så for at dække dette emne, vil jeg opdele denne vejledning i sektioner og vise dig, hvordan du arbejder med celler og områder i Excel VBA ved hjælp af eksempler.
Lad os komme igang.
Alle de koder, jeg nævner i denne vejledning, skal placeres i VB Editor. Gå til afsnittet 'Hvor skal man placere VBA -koden' for at vide, hvordan det fungerer.Hvis du er interesseret i at lære VBA på den nemme måde, så tjek min Online Excel VBA -træning.
Valg af celle / område i Excel ved hjælp af VBA
For at arbejde med celler og områder i Excel ved hjælp af VBA behøver du ikke at vælge det.
I de fleste tilfælde er det bedre, at du ikke vælger celler eller områder (som vi vil se).
På trods af det er det vigtigt, at du går igennem dette afsnit og forstår, hvordan det fungerer. Dette vil være afgørende for din VBA -indlæring, og mange begreber, der dækkes her, vil blive brugt i hele denne vejledning.
Så lad os starte med et meget enkelt eksempel.
Valg af en enkelt celle ved hjælp af VBA
Hvis du vil markere en enkelt celle i det aktive ark (siger A1), kan du bruge nedenstående kode:
Sub SelectCell () Range ("A1"). Vælg End Sub
Ovenstående kode har den obligatoriske del 'Sub' og 'End Sub' og en kodelinje, der vælger celle A1.
Område ("A1") fortæller VBA adressen på den celle, som vi vil henvise til.
Vælg er en metode til Range -objektet og vælger de celler/område, der er angivet i Range -objektet. Cellehenvisningerne skal være omsluttet af dobbelte anførselstegn.
Denne kode viser en fejl, hvis et diagramark er et aktivt ark. Et diagramark indeholder diagrammer og er ikke meget udbredt. Da den ikke har celler/områder i sig, kan ovenstående kode ikke vælge den og ville ende med at vise en fejl.
Bemærk, at da du vil markere cellen i det aktive ark, skal du bare angive celleadressen.
Men hvis du vil markere cellen i et andet ark (lad os sige Sheet2), skal du først aktivere Sheet2 og derefter markere cellen i den.
Sub SelectCell () Regneark ("Sheet2"). Aktiver område ("A1"). Vælg End Sub
På samme måde kan du også aktivere en projektmappe, derefter aktivere et specifikt regneark i den og derefter vælge en celle.
Sub SelectCell () Workbooks ("Book2.xlsx"). Regneark ("Sheet2"). Aktiver område ("A1"). Vælg End Sub
Bemærk, at når du refererer til projektmapper, skal du bruge det fulde navn sammen med filtypen (.xlsx i ovenstående kode). Hvis projektmappen aldrig er blevet gemt, behøver du ikke bruge filtypen.
Disse eksempler er nu ikke særlig nyttige, men du vil senere i denne vejledning se, hvordan vi kan bruge de samme begreber til at kopiere og indsætte celler i Excel (ved hjælp af VBA).
Ligesom vi vælger en celle, kan vi også vælge et område.
I tilfælde af et område kan det være et fast størrelsesinterval eller et variabelt størrelsesinterval.
I et fast størrelsesinterval ville du vide, hvor stort området er, og du kan bruge den nøjagtige størrelse i din VBA -kode. Men med en variabel størrelse har du ingen idé om, hvor stort området er, og du skal bruge en lille smule VBA-magi.
Lad os se, hvordan du gør dette.
Valg af en Fix Sized Range
Her er koden, der vælger området A1: D20.
Sub SelectRange () Område ("A1: D20"). Vælg End Sub
En anden måde at gøre dette på er at bruge nedenstående kode:
Sub SelectRange () Område ("A1", "D20"). Vælg End Sub
Ovenstående kode tager celleadressen øverst til venstre (A1) og den nederste højre celleadresse (D20) og vælger hele området. Denne teknik bliver nyttig, når du arbejder med områder med variabel størrelse (som vi vil se, når egenskaben End er dækket senere i denne vejledning).
Hvis du vil have markeringen til at ske i en anden projektmappe eller et andet regneark, skal du fortælle VBA de nøjagtige navne på disse objekter.
For eksempel ville nedenstående kode vælge området A1: D20 i Sheet2 -regnearket i Book2 -projektmappen.
Sub SelectRange () Workbooks ("Book2.xlsx"). Regneark ("Sheet1"). Aktiver område ("A1: D20"). Vælg End Sub
Hvad nu hvis du ikke ved, hvor mange rækker der er. Hvad hvis du vil markere alle de celler, der har en værdi i den.
I disse tilfælde skal du bruge metoderne vist i det næste afsnit (om valg af område med variabel størrelse).
Valg af variabel størrelse
Der er forskellige måder, du kan vælge en række celler på. Den metode, du vælger, afhænger af, hvordan dataene er opbygget.
I dette afsnit vil jeg dække nogle nyttige teknikker, der virkelig er nyttige, når du arbejder med intervaller i VBA.
Vælg Brug af CurrentRange -ejendom
I tilfælde, hvor du ikke ved, hvor mange rækker/kolonner der har dataene, kan du bruge egenskaben CurrentRange for Range -objektet.
Egenskaben CurrentRange dækker alle sammenhængende fyldte celler i et dataområde.
Nedenfor er koden, der vælger den aktuelle region, der indeholder celle A1.
Sub SelectCurrentRegion () Range ("A1"). CurrentRegion.Select End Sub
Ovenstående metode er god, når du har alle data som en tabel uden tomme rækker/kolonner i den.
Men hvis du har tomme rækker/kolonner i dine data, vælger det ikke dem efter de tomme rækker/kolonner. I billedet herunder vælger CurrentRegion -koden data indtil række 10, da række 11 er tom.
I sådanne tilfælde vil du måske bruge egenskaben UsedRange i regnearkobjektet.
Vælg Brug af UsedRange -ejendom
UsedRange giver dig mulighed for at henvise til alle celler, der er blevet ændret.
Så nedenstående kode ville vælge alle de brugte celler i det aktive ark.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Bemærk, at hvis du har en fjerntliggende celle, der er blevet brugt, ville den blive betragtet af ovenstående kode, og alle cellerne indtil den brugte celle ville blive valgt.
Vælg Brug af slutegenskaben
Nu er denne del virkelig nyttig.
End -egenskaben giver dig mulighed for at vælge den sidst udfyldte celle. Dette giver dig mulighed for at efterligne effekten af Control Down/Up -piletasten eller Control Right/Left -tasterne.
Lad os prøve at forstå dette ved hjælp af et eksempel.
Antag, at du har et datasæt som vist herunder, og du vil hurtigt vælge de sidst udfyldte celler i kolonne A.
Problemet her er, at data kan ændre sig, og du ved ikke, hvor mange celler der er fyldt. Hvis du skal gøre dette ved hjælp af tastatur, kan du vælge celle A1 og derefter bruge Ctrl + pil ned, og det vil markere den sidst udfyldte celle i kolonnen.
Lad os nu se, hvordan du gør dette ved hjælp af VBA. Denne teknik er praktisk, når du hurtigt vil springe til den sidste fyldte celle i en kolonne med variabel størrelse
Sub GoToLastFilledCell () Range ("A1"). End (xlDown) .Vælg End Sub
Ovenstående kode hopper til den sidst udfyldte celle i kolonne A.
På samme måde kan du bruge End (xlToRight) til at springe til den sidste fyldte celle i en række.
Sub GoToLastFilledCell () Range ("A1"). End (xlToRight) .Vælg End Sub
Hvad nu hvis du vil vælge hele kolonnen i stedet for at hoppe til den sidst udfyldte celle.
Du kan gøre det ved at bruge nedenstående kode:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Vælg End Sub
I ovenstående kode har vi brugt den første og den sidste reference i cellen, som vi skal vælge. Uanset hvor mange fyldte celler der er, vælger ovenstående kode alle.
Husk eksemplet ovenfor, hvor vi valgte området A1: D20 ved at bruge følgende kode linje:
Område ("A1 ″," D20 ")
Her var A1 den øverste venstre celle og D20 var den nederste højre celle i området. Vi kan bruge den samme logik til at vælge områder med variabel størrelse. Men da vi ikke kender den nøjagtige adresse i cellen nederst til højre, brugte vi egenskaben End til at hente den.
I Range ("A1", Range ("A1"). End (xlDown)) refererer "A1" til den første celle og Range ("A1"). End (xlDown) refererer til den sidste celle. Da vi har givet begge referencer, vælger metoden Select alle cellerne mellem disse to referencer.
På samme måde kan du også vælge et helt datasæt, der har flere rækker og kolonner.
Nedenstående kode vælger alle de udfyldte rækker/kolonner, der starter fra celle A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown). End (xlToRight)). Vælg End Sub
I ovenstående kode har vi brugt Range ("A1"). End (xlDown) .End (xlToRight) for at få referencen til den nederst til højre udfyldte celle i datasættet.
Forskel mellem brug af CurrentRegion og End
Hvis du undrer dig over, hvorfor bruge egenskaben End til at vælge det udfyldte område, når vi har egenskaben CurrentRegion, lad mig fortælle dig forskellen.
Med egenskaben End kan du angive startcellen. Hvis du f.eks. Har dine data i A1: D20, men den første række er overskrifter, kan du bruge egenskaben End til at vælge dataene uden overskrifterne (ved hjælp af koden herunder).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown). End (xlToRight)). Vælg End Sub
Men CurrentRegion ville automatisk vælge hele datasættet, inklusive overskrifterne.
Hidtil i denne vejledning har vi set, hvordan man henviser til en række celler på forskellige måder.
Lad os nu se nogle måder, hvor vi faktisk kan bruge disse teknikker til at få gjort noget arbejde.
Kopier celler / intervaller ved hjælp af VBA
Som jeg nævnte i begyndelsen af denne vejledning, er det ikke nødvendigt at vælge en celle for at udføre handlinger på den. Du vil se i dette afsnit, hvordan du kopierer celler og områder uden selv at vælge disse.
Lad os starte med et enkelt eksempel.
Kopiering af enkeltcelle
Hvis du vil kopiere celle A1 og indsætte den i celle D1, ville nedenstående kode gøre det.
Sub CopyCell () Range ("A1"). Copy Range ("D1") End Sub
Bemærk, at kopimetoden for områdeobjektet kopierer cellen (ligesom Control +C) og indsætter den i den angivne destination.
I ovenstående eksempelkode er destinationen angivet på samme linje, hvor du bruger kopimetoden. Hvis du vil gøre din kode endnu mere læsbar, kan du bruge nedenstående kode:
Sub CopyCell () Range ("A1"). Copy Destination: = Range ("D1") End Sub
Ovenstående koder kopierer og indsætter værdien samt formatering/formler i den.
Som du måske allerede har bemærket, kopierer ovenstående kode cellen uden at vælge den. Uanset hvor du er på regnearket, vil koden kopiere celle A1 og indsætte den på D1.
Bemærk også, at ovenstående kode ville overskrive enhver eksisterende kode i celle D2. Hvis du vil have Excel til at fortælle dig, om der allerede er noget i celle D1 uden at overskrive det, kan du bruge koden herunder.
Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Vil du overskrive de eksisterende data", vbYesNo) End If If Response = vbYes Then Range ("A1"). Copy Range ("D1 ") Slut Hvis Afslut Sub
Kopiering af et fixstørrelsesinterval
Hvis du vil kopiere A1: D20 i J1: M20, kan du bruge nedenstående kode:
Sub CopyRange () Range ("A1: D20"). Copy Range ("J1") End Sub
I destinationscellen skal du bare angive adressen på cellen øverst til venstre. Koden kopierede automatisk det nøjagtige kopierede område til destinationen.
Du kan bruge den samme konstruktion til at kopiere data fra det ene ark til det andet.
Nedenstående kode ville kopiere A1: D20 fra det aktive ark til Sheet2.
Sub CopyRange () Område ("A1: D20"). Kopier regneark ("Sheet2"). Range ("A1") End Sub
Ovenstående kopierer dataene fra det aktive ark. Så sørg for, at det ark, der har dataene, er det aktive ark, før du kører koden. For at være sikker kan du også angive regnearkets navn, mens du kopierer dataene.
Sub CopyRange () Regneark ("Ark1"). Område ("A1: D20"). Kopier regneark ("Ark2"). Område ("A1") Afslut under
Det gode ved ovenstående kode er, at uanset hvilket ark der er aktivt, vil det altid kopiere dataene fra Sheet1 og indsætte det i Sheet2.
Du kan også kopiere et navngivet område ved at bruge dets navn i stedet for referencen.
For eksempel, hvis du har et navngivet område kaldet 'SalesData', kan du bruge nedenstående kode til at kopiere disse data til Sheet2.
Sub CopyRange () Område ("SalesData"). Kopier regneark ("Sheet2"). Range ("A1") End Sub
Hvis omfanget af det navngivne område er hele projektmappen, behøver du ikke at være på det ark, der har det navngivne område for at køre denne kode. Da det navngivne område er omfattet af projektmappen, kan du få adgang til det fra ethvert ark ved hjælp af denne kode.
Hvis du har en tabel med navnet Table1, kan du bruge nedenstående kode til at kopiere den til Sheet2.
Sub CopyTable () Område ("Tabel1 [#All]"). Kopier regneark ("Sheet2"). Range ("A1") End Sub
Du kan også kopiere et område til en anden projektmappe.
I det følgende eksempel kopierer jeg Excel -tabellen (tabel1) til Book2 -projektmappen.
Sub CopyCurrentRegion () Område ("Tabel1 [#All]"). Kopier arbejdsbøger ("Bog2.xlsx"). Regneark ("Ark1"). Område ("A1") Afslut underdel
Denne kode fungerer kun, hvis projektmappen allerede er åben.
Kopiering af en variabel størrelse
En måde at kopiere områder med variabel størrelse på er at konvertere disse til navngivne områder eller Excel -tabel og bruge koderne som vist i det foregående afsnit.
Men hvis du ikke kan gøre det, kan du bruge CurrentRegion eller End -egenskaben for områdeobjektet.
Nedenstående kode ville kopiere den aktuelle region i det aktive ark og indsætte den i Sheet2.
Sub CopyCurrentRegion () Range ("A1"). CurrentRegion.Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Hvis du vil kopiere den første kolonne i dit datasæt til den sidste udfyldte celle og indsætte den i Sheet2, kan du bruge nedenstående kode:
Sub CopyCurrentRegion () Område ("A1", Område ("A1"). Afslut (xlDown)). Kopier regneark ("Sheet2"). Område ("A1") End Sub
Hvis du vil kopiere rækker såvel som kolonner, kan du bruge nedenstående kode:
Sub CopyCurrentRegion () Område ("A1", Område ("A1"). Afslut (xlDown) .End (xlToRight)). Kopier regneark ("Sheet2"). Range ("A1") End Sub
Bemærk, at alle disse koder ikke markerer cellerne, mens de udføres. Generelt finder du kun en håndfuld tilfælde, hvor du faktisk skal vælge en celle/område, før du arbejder på den.
Tildeling af intervaller til objektvariabler
Hidtil har vi brugt cellernes fulde adresse (f.eks. Workbooks ("Book2.xlsx"). Regneark ("Sheet1"). Range ("A1")).
For at gøre din kode mere håndterbar kan du tildele disse områder til objektvariabler og derefter bruge disse variabler.
For eksempel i nedenstående kode har jeg tildelt kilde- og destinationsområdet til objektvariabler og derefter brugt disse variabler til at kopiere data fra det ene område til det andet.
Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange Afslut Sub
Vi starter med at deklarere variablerne som Range -objekter. Derefter tildeler vi intervallet til disse variabler ved hjælp af sæt -sætningen. Når området er blevet tildelt variablen, kan du blot bruge variablen.
Indtast data i den næste tomme celle (ved hjælp af inputboks)
Du kan bruge indtastningsfelterne til at give brugeren mulighed for at indtaste dataene.
Antag f.eks., At du har datasættet herunder, og du vil indtaste salgsrekorden, du kan bruge inputboksen i VBA. Ved hjælp af en kode kan vi sikre os, at den udfylder dataene i den næste tomme række.
Sub EnterData () Dim RefRange As Range Set RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Indstil beløb = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("produktkategori") Quantity.Value = InputBox ("mængde") Amount.Value = InputBox ("Amount") End Sub
Ovenstående kode bruger boksen VBA -input til at hente input fra brugeren og indtaster derefter inputene i de angivne celler.
Bemærk, at vi ikke brugte nøjagtige cellereferencer. I stedet har vi brugt egenskaben Afslut og forskydning til at finde den sidste tomme celle og udfylde dataene i den.
Denne kode er langt fra anvendelig. Hvis du f.eks. Indtaster en tekststreng, når inputboksen beder om mængde eller mængde, vil du bemærke, at Excel tillader det. Du kan bruge en If -betingelse til at kontrollere, om værdien er numerisk eller ej, og derefter tillade den i overensstemmelse hermed.
Looping gennem celler / intervaller
Indtil videre har vi set, hvordan vi vælger, kopierer og indtaster data i celler og områder.
I dette afsnit vil vi se, hvordan man går gennem et sæt celler/rækker/kolonner i et område. Dette kan være nyttigt, når du vil analysere hver celle og udføre en handling baseret på den.
For eksempel, hvis du vil fremhæve hver tredje række i markeringen, skal du gå igennem og kontrollere for rækkenummeret. På samme måde, hvis du vil fremhæve alle de negative celler ved at ændre skrifttypefarven til rød, skal du gå igennem og analysere hver celles værdi.
Her er koden, der går gennem rækkerne i de markerede celler og fremhæver alternative rækker.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Valg for hver Myrow i Myrange.Rækker Hvis Myrow.Row Mod 2 = 0 Derefter Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Ovenstående kode bruger MOD -funktionen til at kontrollere rækkenummeret i markeringen. Hvis rækkenummeret er lige, fremhæves det i cyan farve.
Her er et andet eksempel, hvor koden går gennem hver celle og fremhæver de celler, der har en negativ værdi i den.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Selection For Every Mycell In Myrange If Mycell <0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Bemærk, at du kan gøre det samme ved hjælp af betinget formatering (hvilket er dynamisk og en bedre måde at gøre dette på). Dette eksempel er kun med det formål at vise dig, hvordan looping fungerer med celler og områder i VBA.
Hvor skal man placere VBA -koden
Gad vide, hvor VBA -koden går i din Excel -projektmappe?
Excel har en VBA -backend kaldet VBA -editoren. Du skal kopiere og indsætte koden i vinduet VB Editor -modulkode.
Her er trinene til at gøre dette:
- Gå til fanen Udvikler.
- Klik på indstillingen Visual Basic. Dette åbner VB -editoren i backend.
- Højreklik på et objekt til projektmappen, hvor du vil indsætte koden, i ruden Project Explorer i VB Editor.Hvis du ikke kan se Project Explorer, skal du gå til fanen View og klikke på Project Explorer.
- Gå til Indsæt og klik på Modul. Dette vil indsætte et modulobjekt til din projektmappe.
- Kopier og indsæt koden i modulvinduet.
