Denne artikkelen vil demonstrere hvordan du oppretter et dynamisk område i Excel VBA.
Å erklære et spesifikt celleområde som en variabel i Excel VBA begrenser oss til å jobbe bare med de bestemte cellene. Ved å deklarere dynamiske områder i Excel får vi langt mer fleksibilitet i forhold til koden vår og funksjonaliteten den kan utføre.
Refererer til områder og celler
Når vi refererer til Range eller Cell -objektet i Excel, refererer vi vanligvis til dem ved å skrive inn harddiskoding i raden og kolonnene vi trenger.
Rekkevidde
Ved å bruke områdeegenskapen, i eksempellinjene med kode nedenfor, kan vi utføre handlinger på dette området, for eksempel å endre fargen på cellene eller gjøre cellene fet.
| 12 | Område ("A1: A5"). Font.Color = vbRedOmråde ("A1: A5"). Font.Bold = True |
Cells Property
På samme måte kan vi bruke Cells -egenskapen til å referere til en rekke celler ved å direkte referere til raden og kolonnen i celleegenskapen. Raden må alltid være et tall, men kolonnen kan være et tall eller en bokstav med anførselstegn.
For eksempel kan celleadressen A1 refereres til som:
| 1 | Celler (1,1) |
Eller
| 1 | Celler (1, "A") |
For å bruke Cells -egenskapen til å referere til et celleområde, må vi angi starten på området og slutten av området.
For eksempel til referanseområde A1: A6 kan vi bruke denne syntaksen nedenfor:
| 1 | Område (celler (1,1), celler (1,6) |
Vi kan deretter bruke Cells -egenskapen til å utføre handlinger på området i henhold til eksempler på kodelinjer nedenfor:
| 12 | Område (celler (2, 2), celler (6, 2)). Font.Color = vbRedRange (Cells (2, 2), Cells (6, 2)). Font.Bold = True |
Dynamiske områder med variabler
Ettersom størrelsen på dataene våre endres i Excel (det vil si at vi bruker flere rader og kolonner som områdene vi har kodet), ville det være nyttig hvis områdene vi refererer til i koden vår også skulle endres. Ved å bruke Range -objektet ovenfor kan vi lage variabler for å lagre maksimale rad- og kolonnetall for området i Excel -regnearket som vi bruker, og bruke disse variablene til å dynamisk justere Range -objektet mens koden kjører.
For eksempel
| 1234 | Dim lRow som heltallDim lCol som heltalllRow = Range ("A1048576"). Slutt (xlUp) .RowlCol = Range ("XFD1"). End (xlToLeft) .Column |
Siste rad i kolonne
Siden det er 1048576 rader i et regneark, vil variabelen lRow gå til bunnen av arket og deretter bruke den spesielle kombinasjonen av avslutningstasten og pil opp -tasten for å gå til den siste raden som ble brukt i regnearket - dette vil gi oss nummeret på raden vi trenger i vårt sortiment.
Siste kolonne på rad
På samme måte vil lCol flytte til kolonne XFD, som er den siste kolonnen i et regneark, og deretter bruke den spesielle tastekombinasjonen Avslutt -tasten pluss venstre piltast for å gå til den siste kolonnen som ble brukt i regnearket - dette vil gi oss nummeret på kolonnen vi trenger i vårt utvalg.
Derfor, for å få hele området som brukes i regnearket, kan vi kjøre følgende kode:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol som heltallDim rng As RangelRow = Range ("A1048576"). Slutt (xlUp) .Row'bruk lRow for å finne den siste kolonnen i områdetlCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnSett rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox for å vise oss rekkeviddenMsgBox "Range is" & rng.AddressSlutt Sub |
SpecialCells - LastCell
Vi kan også bruke SpecialCells -metoden for områdeobjektet for å få den siste raden og kolonnen brukt i et regneark.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol som heltallDim rng As RangeDim rngBegin As RangeSett rngBegin = Range ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnSett rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox for å vise oss rekkeviddenMsgBox "Range is" & rng.AddressSlutt Sub |
UsedRange
Metoden for brukt område inkluderer alle cellene som har verdier i det gjeldende regnearket.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeSett rng = ActiveSheet.UsedRange'msgbox for å vise oss rekkeviddenMsgBox "Range is" & rng.AddressSlutt Sub |
CurrentRegion
Den nåværende regionen skiller seg fra UsedRange ved at den ser på cellene rundt en celle som vi har erklært som et startområde (dvs. variabelen rngBegin i eksemplet nedenfor), og deretter ser på alle cellene som er 'festet' eller tilknyttet til den deklarerte cellen. Skulle det oppstå en tom celle i en rad eller kolonne, slutter CurrentRegion å lete etter ytterligere celler.
| 12345678 | Sub CurrentRegion ()Dim rng As RangeDim rngBegin As RangeSett rngBegin = Range ("A1")Sett rng = rngBegin.CurrentRegion'msgbox for å vise oss rekkeviddenMsgBox "Range is" & rng.AddressSlutt Sub |
Hvis vi bruker denne metoden, må vi sørge for at alle cellene i området du trenger er tilkoblet uten blanke rader eller kolonner.
Navngitt Range
Vi kan også referere til navngitte områder i koden vår. Navngitte områder kan være dynamiske så langt når data oppdateres eller settes inn, kan områdenavnet endres for å inkludere de nye dataene.
Dette eksemplet vil endre skriften til fet skrift for områdets navn "januar"
| 12345 | Sub RangeNameExample ()Dim rng som RangeSett rng = Range ("januar")rng.Font.Bold = = SantSlutt Sub |
Som du vil se på bildet nedenfor, hvis en rad legges til i områdetavnet, oppdateres områdetavnet automatisk for å inkludere den raden.
Skulle vi deretter kjøre eksempelkoden igjen, vil området som påvirkes av koden være C5: C9, mens det i første omgang ville ha vært C5: C8.
Tabeller
Vi kan referere til tabeller (klikk for mer informasjon om hvordan du oppretter og manipulerer tabeller i VBA) i vår kode. Etter hvert som en tabelldata i Excel oppdateres eller endres, vil koden som refererer til tabellen referere til de oppdaterte tabelldataene. Dette er spesielt nyttig når det refereres til pivottabeller som er koblet til en ekstern datakilde.
Ved å bruke denne tabellen i koden vår, kan vi referere til kolonnene i tabellen ved overskriftene i hver kolonne, og utføre handlinger på kolonnen i henhold til navnet. Etter hvert som radene i tabellen øker eller reduseres i henhold til dataene, vil tabellområdet justeres deretter, og koden vår vil fortsatt fungere for hele kolonnen i tabellen.
For eksempel:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). SlettSlutt Sub |
