Denne artikel vil demonstrere, hvordan du opretter et dynamisk område i Excel VBA.
At erklære et specifikt cellecelle som en variabel i Excel VBA begrænser os til kun at arbejde med de pågældende celler. Ved at deklarere dynamiske områder i Excel får vi langt mere fleksibilitet i forhold til vores kode og den funktionalitet, den kan udføre.
Referencer til områder og celler
Når vi refererer til området eller celleobjektet i Excel, refererer vi normalt til dem ved at hardcoding i den række og kolonner, vi har brug for.
Range Ejendom
Ved hjælp af områdeegenskaben kan vi i eksempellinjerne med kode nedenfor udføre handlinger på dette område, f.eks. Ændre farve på cellerne eller gøre cellerne fede.
| 12 | Område ("A1: A5"). Font.Color = vbRedOmråde ("A1: A5"). Font.Bold = True |
Celler ejendom
På samme måde kan vi bruge egenskaben Celler til at referere til en række celler ved direkte at referere til rækken og kolonnen i cellernes egenskab. Rækken skal altid være et tal, men kolonnen kan være et tal eller et bogstav indeholdt anførselstegn.
For eksempel kan celleadressen A1 refereres til som:
| 1 | Celler (1,1) |
Eller
| 1 | Celler (1, "A") |
For at bruge egenskaben Celler til at referere til et cellecelle, skal vi angive starten på intervallet og slutningen af intervallet.
For eksempel til referenceområde A1: A6 kunne vi bruge denne syntaks nedenfor:
| 1 | Område (celler (1,1), celler (1,6) |
Vi kan derefter bruge egenskaben Celler til at udføre handlinger på området i henhold til eksemplerne på kodelinjer herunder:
| 12 | Område (celler (2, 2), celler (6, 2)). Font.Color = vbRedRange (Celler (2, 2), Cells (6, 2)). Font.Bold = True |
Dynamiske intervaller med variabler
Da størrelsen på vores data ændres i Excel (dvs. vi bruger flere rækker og kolonner, som de områder, vi har kodet), ville det være nyttigt, hvis de områder, vi henviser til i vores kode, også skulle ændre sig. Ved hjælp af Range -objektet ovenfor kan vi oprette variabler til at gemme de maksimale række- og kolonnetal for området i Excel -regnearket, som vi bruger, og bruge disse variabler til dynamisk at justere Range -objektet, mens koden kører.
For eksempel
| 1234 | Dim lRække som heltalDim lCol som heltallRække = rækkevidde ("A1048576"). Afslut (xlUp) .RækkelCol = Range ("XFD1"). Slut (xlToLeft) .Column |
Sidste række i kolonne
Da der er 1048576 rækker i et regneark, vil variablen lRow gå til bunden af arket og derefter bruge den særlige kombination af afslutningstasten plus pil op -tasten til at gå til den sidste række, der blev brugt i regnearket - dette vil give os nummeret på rækken, som vi har brug for i vores sortiment.
Sidste kolonne i rækken
På samme måde vil lCol flytte til kolonne XFD, som er den sidste kolonne i et regneark, og derefter bruge den særlige tastekombination af afslutningstasten plus venstre pil til at gå til den sidste kolonne, der blev brugt i regnearket - dette vil give os nummeret på den kolonne, vi har brug for i vores sortiment.
For at få hele området, der bruges i regnearket, kan vi derfor køre følgende kode:
| 1234567891011 | Sub GetRange ()Dim lRække som heltalDim lCol som heltalDim rng Som områdelRække = rækkevidde ("A1048576"). Afslut (xlUp) .Række'brug lRow til at hjælpe med at finde den sidste kolonne i områdetlCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnIndstil rng = Range (Celler (1, 1), Celler (lRow, lCol))'msgbox for at vise os rækkeviddenMsgBox "Range er" & rng.AddressAfslut Sub |
Specialceller - LastCell
Vi kan også bruge SpecialCells metode til områdeobjektet til at få den sidste række og kolonne brugt i et regneark.
| 123456789101112 | Sub UseSpecialCells ()Dim lRække som heltalDim lCol som heltalDim rng Som områdeDim rngBegyn As RangeIndstil rngBegin = Range ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnIndstil rng = Range (Celler (1, 1), Celler (lRow, lCol))'msgbox for at vise os rækkeviddenMsgBox "Range er" & rng.AddressAfslut Sub |
UsedRange
Metoden Brugt område omfatter alle de celler, der har værdier i det aktuelle regneark.
| 123456 | Sub UsedRangeExample ()Dim rng Som områdeIndstil rng = ActiveSheet.UsedRange'msgbox for at vise os rækkeviddenMsgBox "Range er" & rng.AddressAfslut Sub |
CurrentRegion
Den aktuelle region adskiller sig fra UsedRange ved, at den ser på de celler, der omgiver en celle, som vi har erklæret som et startområde (dvs. variablen rngBegin i eksemplet herunder), og derefter ser på alle de celler, der er 'knyttet' eller tilknyttet til den erklærede celle. Hvis der opstår en tom celle i en række eller kolonne, stopper CurrentRegion med at lede efter yderligere celler.
| 12345678 | Sub CurrentRegion ()Dim rng Som områdeDim rngBegynd som områdeIndstil rngBegin = Range ("A1")Indstil rng = rngBegin.CurrentRegion'msgbox for at vise os rækkeviddenMsgBox "Range er" & rng.AddressAfslut Sub |
Hvis vi bruger denne metode, skal vi sikre os, at alle cellerne i det område, du har brug for, er forbundet uden tomme rækker eller kolonner.
Navngivet Range
Vi kan også referere til navngivne områder i vores kode. Navngivne områder kan være dynamiske, for så vidt som data opdateres eller indsættes, kan områdenavnet ændres til at omfatte de nye data.
Dette eksempel ændrer skrifttypen til fed for områdets navn "Januar"
| 12345 | Sub RangeNameExample ()Dim rng som RangeIndstil rng = Range ("januar")rng.Font.Bold = = SandtAfslut Sub |
Som du vil se på billedet herunder, hvis en række tilføjes til områdets navn, opdateres områdenavnet automatisk for at inkludere den pågældende række.
Skulle vi så køre eksempelkoden igen, ville det område, der blev påvirket af koden, være C5: C9, mens det i første omgang ville have været C5: C8.
Tabeller
Vi kan henvise til tabeller (klik for mere information om oprettelse og manipulation af tabeller i VBA) i vores kode. Når en tabel data i Excel opdateres eller ændres, refererer koden, der refererer til tabellen, derefter til de opdaterede tabeldata. Dette er især nyttigt, når der henvises til pivottabeller, der er forbundet til en ekstern datakilde.
Ved hjælp af denne tabel i vores kode kan vi henvise til kolonnerne i tabellen ved overskrifterne i hver kolonne og udføre handlinger på kolonnen i henhold til deres navn. Når rækkerne i tabellen stiger eller falder i henhold til dataene, vil tabelintervallet justeres i overensstemmelse hermed, og vores kode fungerer stadig for hele kolonnen i tabellen.
For eksempel:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). SletAfslut Sub |
