Ez a cikk bemutatja, hogyan hozhat létre dinamikus tartományt az Excel VBA -ban.
Ha egy adott sejttartományt változónak nyilvánít az Excel VBA -ban, akkor csak az adott cellákkal dolgozhatunk. A dinamikus tartományok Excelben való deklarálásával sokkal nagyobb rugalmasságot nyerünk a kódunk és az általunk végrehajtható funkciók felett.
Tartományok és cellák hivatkozása
Amikor hivatkozunk a Tartomány vagy Cella objektumra az Excelben, általában rájuk hivatkozunk keménykódolással a kívánt sorban és oszlopban.
Tartománytulajdonság
A Tartomány tulajdonság használatával az alábbi példasorokban végrehajthatunk olyan műveleteket ezen a tartományon, mint például a cellák színének megváltoztatása vagy a cellák félkövérré tétele.
| 12 | Tartomány ("A1: A5"). Betűtípus.Szín = vbRedTartomány ("A1: A5"). Font.Bold = Igaz |
Cells Property
Hasonlóképpen használhatjuk a Cells tulajdonságot arra, hogy cellatartományra hivatkozzunk, közvetlenül hivatkozva a cellák tulajdonság sorára és oszlopára. A sornak mindig számnak kell lennie, de az oszlop lehet szám vagy idézőjelbe foglalt betű.
Például az A1 cellacím a következőképpen hivatkozhat:
| 1 | Sejtek (1,1) |
Vagy
| 1 | Cella (1, "A") |
Ahhoz, hogy a Cells tulajdonsággal cellatartományra hivatkozzunk, jeleznünk kell a tartomány kezdetét és a tartomány végét.
Például az A1: A6 referenciatartományhoz az alábbi szintaxist használhatjuk:
| 1 | Tartomány (cellák (1,1), cellák (1,6) |
Ezután a Cells tulajdonság segítségével műveleteket hajthatunk végre a tartományon, az alábbi kódsorok szerint:
| 12 | Tartomány (cellák (2, 2), cellák (6, 2)). Font.Color = vbRedTartomány (cellák (2, 2), cellák (6, 2)). Betűtípus. Félkövér = igaz |
Dinamikus tartományok változókkal
Mivel az adatok mérete megváltozik az Excelben (azaz több sort és oszlopot használunk, amelyeket a kódolt tartományok használnak), hasznos lenne, ha a kódunkban hivatkozott tartományok is megváltoznának. A fenti Range objektum használatával változókat hozhatunk létre az Excel munkalap általunk használt terület maximális sor- és oszlopszámának tárolására, és ezekkel a változókkal dinamikusan beállíthatjuk a Range objektumot a kód futása közben.
Például
| 1234 | Dim lRow egész számkéntDim lCol egész számkéntlRow = Range ("A1048576"). End (xlUp) .RowlCol = Tartomány ("XFD1"). Vége (xlToLeft) .Oszlop |
Az oszlop utolsó sora
Mivel a munkalapon 1048576 sor található, az lRow változó a lap aljára kerül, majd az End billentyű és a Fel nyíl billentyű speciális kombinációjával lép a munkalap utolsó sorába - ez megadja nekünk az a sor, amire szükségünk van a tartományunkban.
Utolsó oszlop a sorban
Hasonlóképpen, az lCol az XFD oszlopba lép, amely a munkalap utolsó oszlopa, majd az End billentyű és a Bal nyíl billentyű speciális billentyűkombinációjával léphet a munkalap utolsó oszlopába - ez megadja nekünk a annak az oszlopnak a száma, amelyre a tartományunkban szükségünk van.
Ezért a munkalapon használt teljes tartomány eléréséhez futtassuk a következő kódot:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol mint egészDim rng mint tartománylRow = Range ("A1048576"). End (xlUp) .Row'az lRow segítségével keresse meg a tartomány utolsó oszlopátlCol = Tartomány ("XFD" & lRow) .Vége (xlToLeft) .OszlopSet rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox, hogy megmutassa nekünk a tartománytAz MsgBox "tartomány" & rng.AddressEnd Sub |
SpecialCells - LastCell
A Range Object SpecialCells módszerét is használhatjuk a munkalap utolsó sorának és oszlopának lekérésére.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol mint egészDim rng mint tartományDim rngBegin As RangeSet rngBegin = Tartomány ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell). OszlopSet rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox, hogy megmutassa nekünk a tartománytAz MsgBox "tartomány" & rng.AddressEnd Sub |
UsedRange
A Használt tartomány módszer tartalmazza az összes cellát, amelyek értékeket tartalmaznak az aktuális munkalapon.
| 123456 | Alul használt tartomány: példa ()Dim rng mint tartományÁllítsa be az rng = ActiveSheet.UsedRange értéket'msgbox, hogy megmutassa nekünk a tartománytAz MsgBox "tartomány" & rng.AddressEnd Sub |
CurrentRégió
A jelenlegi régió abban különbözik a UsedRange -tól, hogy az általunk kiinduló tartományként deklarált cellákat körülvevő cellákat nézi (azaz az rngBegin változót az alábbi példában), majd megnézi az összes „csatolt” vagy társított cellát arra a deklarált cellára. Ha üres cella jelenik meg egy sorban vagy oszlopban, akkor a CurrentRegion leáll a további cellák keresésével.
| 12345678 | Régió ()Dim rng mint tartományDim rngBegin As RangeSet rngBegin = Tartomány ("A1")Állítsa be az rng = rngBegin.CurrentRegion beállítást'msgbox, hogy megmutassa nekünk a tartománytAz MsgBox "tartomány" & rng.AddressEnd Sub |
Ha ezt a módszert használjuk, meg kell győződnünk arról, hogy a kívánt tartomány összes cellája össze van kötve, és nincs üres sor vagy oszlop közöttük.
Elnevezett tartomány
A kódunkban hivatkozhatunk a megnevezett tartományokra is. A megnevezett tartományok dinamikusak lehetnek, amennyiben az adatok frissítésekor vagy beillesztésekor a tartománynév módosulhat úgy, hogy tartalmazza az új adatokat.
Ez a példa a „január” tartománynév betűkészletét félkövérre változtatja
| 12345 | AltartományNamePélda ()Dim rng mint tartománySet rng = Range ("január")rng.Font.Bold = = IgazEnd Sub |
Amint az alábbi képen látható, ha egy sort adnak hozzá a tartománynévhez, akkor a tartománynév automatikusan frissül, hogy tartalmazza ezt a sort.
Ha ezután újra futtatnánk a példakódot, a kód által érintett tartomány a C5: C9 lenne, míg első esetben a C5: C8 lett volna.
Táblázatok
Kódunkban hivatkozhatunk táblázatokra (további információért kattintson a táblázatok létrehozásáról és kezeléséről a VBA -ban). Amint az Excel táblázatadatait frissítik vagy módosítják, a táblázatra hivatkozó kód ezután a frissített táblázatadatokra utal. Ez különösen akkor hasznos, ha külső adatforráshoz csatlakoztatott Pivot táblákra hivatkozik.
Kódunkban ezt a táblázatot használva hivatkozhatunk a táblázat oszlopaira az egyes oszlopok fejlécei alapján, és a nevük szerint végezhetünk műveleteket az oszlopon. Ahogy a táblázat sorai az adatoknak megfelelően nőnek vagy csökkennek, a táblázattartomány ennek megfelelően módosul, és kódunk továbbra is működik a táblázat teljes oszlopában.
Például:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("szállító"). DeleteEnd Sub |
