Tartományok és cellák a VBA -ban
Az Excel táblázatok a cellákban tárolják az adatokat. A cellák sorokba és oszlopokba vannak rendezve. Minden cella azonosítható sora és oszlopa metszéspontjával (pl. B3 vagy R3C2).
Az Excel tartomány egy vagy több cellára vonatkozik (pl. A3: B4)
Cella címe
A1 jelölés
Az A1 jelölésben a cellára az oszlop betűje utal (A -tól XFD -ig), majd a sorszáma (1 -től 1 048 576 -ig).
A VBA -ban bármely cellára hivatkozhat a Tartomány objektum.
| 123456789 | 'Lásd a B4 cellát az éppen aktív laponMsgBox tartomány ("B4")"Lásd az" Adatok "nevű munkalap B4 cellájátMsgBox munkalapok ("Adatok"). Tartomány ("B4")'Lásd egy másik OPEN munkafüzet' Adatok 'nevű lapjának B4 celláját"Saját adatok"MsgBox munkafüzetek ("Saját adatok"). Munkalapok ("Adatok"). Tartomány ("B4") |
R1C1 jelölés
Az R1C1 jelölésben egy cellára R utal, ezt követi a sor száma, majd a „C” betű, majd az oszlop száma. pl. B4 az R1C1 jelölésben az R4C2 hivatkozni fog. A VBA -ban a Cellaobjektum R1C1 jelölés használata:
| 12 | „Lásd az R [6] C [4], azaz a D6 cellátCella (6, 4) = "D6" |
Sejtek tartománya
A1 jelölés
Egynél több cellára való hivatkozáshoz használjon „:” karaktert a kezdő cellacím és az utolsó cellacím között. A következőkben az összes cellára vonatkozik A1 -től D10 -ig:
| 1 | Tartomány ("A1: D10") |
R1C1 jelölés
Egynél több cellára való hivatkozáshoz használjon „” karaktert a kezdő cella és az utolsó cella között. A következőkben az összes cellára vonatkozik A1 -től D10 -ig:
| 1 | Tartomány (cellák (1, 1), cellák (10, 4)) |
Írás a sejtekhez
Ha értékeket szeretne írni egy cellába vagy szomszédos cellacsoportba, egyszerűen nézze meg a tartományt, tegyen egy = jelet, majd írja be a tárolni kívánt értéket:
| 12345678910 | "Tárolja az F5 -öt a cellában az F6 címmelTartomány ("F6") = "F6"„Tárolja az E6 -ot az R [6] C [5], azaz E6 -os cellábanSejtek (6, 5) = "E6"„Tárolja az A1: D10 -et az A1: D10 tartománybanTartomány ("A1: D10") = "A1: D10"'vagyTartomány (cellák (1, 1), cellák (10, 4)) = "A1: D10" |
Olvasás a cellákból
Ha értékeket szeretne olvasni a cellákból, egyszerűen nézze meg a változót az értékek tárolásához, tegyen egy = jelet, majd hivatkozzon az olvasandó tartományra:
| 1234567891011 | Dim val1Dim val2„Olvassa el az F6 cellábólval1 = Tartomány ("F6")„Olvassa el az E6 cellábólval2 = cellák (6, 5)MsgBox val1Msgbox val2 |
Megjegyzés: A cellatartomány értékeinek tárolásához tömböt kell használnia egyszerű változó helyett.
Nem összefüggő sejtek
A nem szomszédos cellákra való hivatkozáshoz vesszőt használjon a cellacímek között:
| 123456 | „Tárolja a 10 -et az A1, A3 és A5 cellákbanTartomány ("A1, A3, A5") = 10„Tárolja a 10 -et az A1: A3 és D1: D3 cellákban”Tartomány ("A1: A3, D1: D3") = 10 |
A sejtek metszéspontja
A nem szomszédos cellákra való hivatkozáshoz használjon szóközt a cellacímek között:
| 123 | „Tárolja a„ Col D ”-t a D1: D10 -ben"ami gyakori az A1: D10 és a D1: F10 közöttTartomány ("A1: D10 D1: G10") = "D oszlop" |
Eltolás egy cellából vagy tartományból
Az eltolás funkció használatával a hivatkozást áthelyezheti egy adott tartományból (cella vagy cellacsoport) a megadott sorok_száma és oszlopok száma szerint.
Offset szintaxis
Tartomány.Offset (sorok_száma, oszlopok száma)
Eltolás egy cellából
| 12345678910111213141516 | 'Eltolás az A1 cellából- Lásd magát a cellát'0 sor és 0 oszlop áthelyezéseTartomány ("A1"). Eltolás (0, 0) = "A1"'1 sor és 0 oszlop áthelyezéseTartomány ("A1"). Eltolás (1, 0) = "A2"'0 sor és 1 oszlop áthelyezéseTartomány ("A1"). Eltolás (0, 1) = "B1"'1 sor és 1 oszlop áthelyezéseTartomány ("A1"). Eltolás (1, 1) = "B2""Mozgassa 10 sort és 5 oszlopotTartomány ("A1"). Eltolás (10, 5) = "F11" |
Eltolás egy tartományból
| 123 | „Vigye a hivatkozást az A1: D4 tartományra 4 sorral és 4 oszloppal„Új hivatkozás az E5: H8Tartomány ("A1: D4"). Eltolás (4,4) = "E5: H8" |
Referencia beállítása egy tartományra
Tartomány hozzárendelése tartományváltozóhoz: deklaráljon egy Range típusú változót, majd a Set paranccsal állítsa be egy tartományra. Kérjük, vegye figyelembe, hogy a SET parancsot kell használnia, mivel a RANGE objektum:
| 12345678 | 'Határozzon meg egy tartományváltozótDim myRange mint tartomány'Állítsa be a változót az A1: D4 tartománybaMyRange = tartomány beállítása ("A1: D4")'Nyomtatás $ A $ 1: $ D $ 4MsgBox myRange.Cím |
Egy tartomány átméretezése
A Range objektum átméretezési módszere megváltoztatja a referenciatartomány méretét:
| 1234567 | Dim myRange mint tartomány'Átméretezési tartományMyRange = tartomány beállítása ("A1: F4")'Nyomtatás $ A $ 1: $ E $ 10Hibakeresés. Nyomtatás myRange. Átméretezés (10, 5). Cím |
Az átméretezett tartomány bal felső cellája megegyezik az eredeti tartomány bal felső cellájával
A szintaxis átméretezése
Tartomány. Átméretezés (sorok sora, oszlopok száma)
OFFSET vs átméretezés
Az eltolás nem változtatja meg a tartomány méreteit, hanem a megadott számú sorral és oszloppal mozgatja azt. Az átméretezés nem változtatja meg az eredeti tartomány pozícióját, de módosítja a méreteket a megadott számú sorra és oszlopra.
Az összes cella a lapon
A Cells objektum a munkalap összes cellájára utal (1048576 sor és 16384 oszlop).
| 12 | 'Törölje az összes cellát a munkalapokonSejtek. Tiszta |
UsedRange
A UsedRange tulajdonság megadja a téglalap alakú tartományt a használt bal felső cellától az aktív lap jobb alsó alsó cellájáig.
| 1234567 | Dim ws munkalapkéntÁllítsa be ws = ActiveSheet'$ B $ 2: $ L $ 14, ha L2 az első értékű cella'és az L14 az utolsó cella, amelyen bármilyen érték szerepel'aktív lapHibakeresés. Nyomtatás ws. Használt tartomány. Cím |
CurrentRégió
A CurrentRegion tulajdonság megadja a szomszédos téglalap alakú tartományt a bal felső cellától a hivatkozott cella/tartományt tartalmazó jobb alsó alsó celláig.
| 1234567891011 | Dim myRange mint tartományA myRange = tartomány beállítása ("D4: F6")'Nyomtatás $ B $ 2: $ L $ 14„Ha van kitöltött útvonal a D4: F16 és a B2 ÉS L14 közöttHibakeresés. Nyomtassa ki a myRange.CurrentRegion.Address címet'Hivatkozhat egyetlen kezdő cellára isÁllítsa be a myRange = Range ("D4") nyomtatást $ B $ 2: $ L $ 14 |
Tartomány tulajdonságai
Az alábbi tartományban kaphatja meg a Cím címet, a cella sor/oszlop számát és a sorok/oszlopok számát:
| 123456789101112131415161718192021 | Dim myRange mint tartományMyRange = tartomány beállítása ("A1: F10")'Nyomtatás $ A $ 1: $ F $ 10Hibakeresés. Nyomtassa ki a myRange -et. CímMyRange = tartomány beállítása ("F10")'10 -es nyomtatás a 10. sorraHibakeresés. MyRange.Row nyomtatása. Sor„6. nyomtatvány az F oszlophozHibakeresés. MyRange nyomtatása. OszlopMyRange = tartomány beállítása ("E1: F5")'5 nyomtatás a tartományban lévő sorok számáhozHibakeresés. Nyomtassa ki a myRange.Rows.Count lehetőséget'2 nyomtatás a tartomány oszlopainak számáhozHibakeresés. MyRange.Columns.Count nyomtatása |
A lap utolsó cella
Te tudod használni Sorok. Szám és Oszlopok. Szám tulajdonságokkal Sejtek objektum a lap utolsó cellájának megszerzéséhez:
| 1234567891011 | 'Nyomtassa ki az utolsó sor számát„Nyomtatás 1048576Debug.Print "Sorok a lapon:" & Sorok. Szám'Nyomtassa ki az utolsó oszlop számát'Nyomtatványok 16384Debug.Print "Oszlopok a lapon:" & Columns.Count'Nyomtassa ki az utolsó cella címét'Nyomtatás $ XFD $ 1048576Debug.Print "A lap utolsó cellájának címe:" & Cells (Rows.Count, Columns.Count) |
Utoljára használt sorszám egy oszlopban
Az END tulajdonság a tartomány utolsó celláját, az End (xlUp) pedig az adott cella első használt celláját jeleníti meg.
| 123 | Halvány utolsó sorlastRow = Cells (Rows.Count, "A"). End (xlUp) .Row |
Utoljára használt oszlopszám egy sorban
| 123 | Dim lastCol Amilyen hosszúlastCol = Cellak (1, Oszlopok.szám) .End (xlToLeft). Oszlop |
Az END tulajdonság a tartomány utolsó celláját, az End (xlToLeft) pedig az adott cella első használt cellájához vezet.
Az xlDown és az xlToRight tulajdonságokkal is navigálhat az aktuális cella első alsó vagy jobb oldali cellájához.
Cella tulajdonságai
Közös tulajdonságok
Itt található a gyakran használt cellatulajdonságok megjelenítésére szolgáló kód
| 12345678910111213141516171819202122 | Homályos cella mint tartományCella beállítása = Tartomány ("A1")cella. AktiváljaHibakeresés. Cella nyomtatása. Cím'Nyomtasson $ 1 dollártHibakeresés. Cella nyomtatása. Érték„Nyomtat 456' CímHibakeresés. Cella nyomtatása. Képlet'Nyomtatások = SUM (C2: C3)'MegjegyzésHibakeresés.Cella nyomtatása.Megjegyzés.Text'StílusHibakeresés. Cella nyomtatása. Stílus'CellaformátumHibakeresés.Cella nyomtatása.DisplayFormat.NumberFormat |
Cell betűtípus
A Cell.Font objektum a Cella betűtípus tulajdonságait tartalmazza:
| 1234567891011121314151617181920 | Homályos cella mint tartományCella beállítása = Tartomány ("A1")'Rendes, dőlt, félkövér és félkövér dőltcell.Font.FontStyle = "Félkövér dőlt"' Ugyanaz, mint acell.Font.Bold = Igazcell.Font.Italic = Igaz'Állítsa a betűtípust futárracell.Font.FontStyle = "Futár"'Állítsa be a betűszínétcell.Font.Color = vbKék'vagycell.Font.Color = RGB (255, 0, 0)'Állítsa be a betűméretetcell.Font.Size = 20 |
Másolás és beillesztés
Összes beillesztése
A tartományok/cellák másolhatók és beilleszthetők egyik helyről a másikra. A következő kód a forrástartomány összes tulajdonságát a céltartományba másolja (egyenértékű a CTRL-C és a CTRL-V)
| 1234567 | 'Egyszerű másolatTartomány ("A1: D20"). MásolatMunkalapok ("Sheet2"). Tartomány ("B10"). Beillesztés'vagy"Másolás az aktuális lapról a" Sheet2 "nevű lapraTartomány ("A1: D20"). Másolási cél: = Munkalapok ("Lap2"). Tartomány ("B10") |
Speciális beillesztés
A forrástartomány kiválasztott tulajdonságai másolhatók a célállomásra a PASTESPECIAL opció használatával:
| 123 | 'Illessze be a tartományt csak értékkéntTartomány ("A1: D20"). MásolatMunkalapok ("Sheet2"). Tartomány ("B10"). PasteSpecial Paste: = xlPasteValues |
Íme a Beillesztés opció lehetséges lehetőségei:
| 12345678910111213 | 'Különleges típusok beillesztésexlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
AutoFit Tartalom
A sorok és oszlopok mérete megváltoztatható, hogy illeszkedjenek a tartalomhoz az alábbi kód használatával:
| 12345 | „Módosítsa az 1–5. Sorok méretét, hogy illeszkedjen a tartalomhozSorok ("1: 5"). AutoFit„Módosítsa az A - B oszlopok méretét, hogy illeszkedjenek a tartalomhozOszlopok ("A: B"). AutoFit |
További tartományok példái
Javasoljuk, hogy használja a Macro Recorder alkalmazást a szükséges műveletek végrehajtása közben a grafikus felületen. Segít megérteni a rendelkezésre álló különféle lehetőségeket és azok használatát.
Az egyes
Könnyebb a tartomány használatával ciklusokat használni a használatával Az egyes az alábbiak szerint építse fel:
| 123 | A tartomány minden cellájához ("A1: B100")- Csináljon valamit a cellávalKövetkező cella |
A ciklus minden iterációjánál a tartomány egy cellája hozzá van rendelve a c változóhoz, és a For ciklus utasításai végrehajtásra kerülnek az adott cella számára. A hurok kilép, ha az összes cella feldolgozásra került.
Fajta
A rendezés a Range objektum metódusa. A tartományt a tartomány tartományba rendezési beállításainak megadásával rendezheti. Az alábbi kód az A: C oszlopokat rendezi a C2 cellában lévő kulcs alapján. A rendezési sorrend lehet xl növekvő vagy xl csökkenő. Fejléc: = xlIgen akkor kell használni, ha az első sor a fejléc.
| 12 | Oszlopok ("A: C"). Rendezési kulcs1: = Tartomány ("C2"), _sorrend1: = xl növekvő, fejléc: = xlIgen |
megtalálja
A Find a Range Object módszer is. Megtalálja az első cellát, amelynek tartalma megfelel a keresési feltételeknek, és Range objektumként adja vissza. Visszatér Semmi ha nincs egyezés.
Használat FindNext metódus (vagy FindPrevious) a következő (előző) előfordulás megkereséséhez.
A következő kód megváltoztatja a betűtípust „Arial Black” -re a „John” betűvel kezdődő tartomány összes cellájában:
| 12345 | Minden c tartományban ("A1: A100")Ha c Mint "John*" Akkorc.Font.Name = "Arial Black"Vége HaKövetkező c |
A következő kód lecseréli a „Tesztelni” és „Elért” összes előfordulását a megadott tartományban:
| 12345678910 | Hatótávolsággal ("a1: a500")Állítsa be a c = .Find ("Tesztelés", LookIn: = xlValues)Ha nem c akkor semmielső cím = c. CímTeddc.Value = "Sikeres"Állítsa be a c = .FindNext (c)Hurok, amíg nem c Semmi És c.Az első címVége HaVége ezzel |
Fontos megjegyezni, hogy a FindNext használatához meg kell adnia egy tartományt. Ezenkívül meg kell adnia egy leállítási feltételt, különben a hurok örökké fog futni. Általában az első megtalált cella címe egy változóban tárolódik, és a ciklus leáll, amikor ismét eléri ezt a cellát. Ellenőriznie kell azt az esetet is, ha semmi nem találja a hurok leállítását.
Tartomány címe
Használja a Range.Address címet A1 stílusú lekéréshez
| 123 | MsgBox tartomány ("A1: D10"). Cím'vagyHibakeresési nyomtatási tartomány ("A1: D10"). Cím |
Az xlReferenceStyle (alapértelmezett xlA1) használatával címeket kaphat R1C1 stílusban
| 123 | MsgBox tartomány ("A1: D10"). Cím (ReferenceStyle: = xlR1C1)'vagyHibakeresési nyomtatási tartomány ("A1: D10"). Cím (ReferenceStyle: = xlR1C1) |
Ez akkor hasznos, ha a változókban tárolt tartományokkal foglalkozik, és csak bizonyos címekre kívánja feldolgozni.
Hatótávolság
Gyorsabb és egyszerűbb egy tartomány átvitele egy tömbbe, majd az értékek feldolgozása. A tömböt változatként kell deklarálnia, hogy ne számítsa ki a tömb tartományának feltöltéséhez szükséges méretet. A tömb méretei úgy vannak beállítva, hogy megfeleljenek a tartományban lévő értékeknek.
| 123456789 | Dim DirArray mint változat'Tárolja az értékeket a tömb tartományábanDirArray = Tartomány ("a1: a5"). Érték'Hurok az értékek feldolgozásáhozMinden c In DirArrayHibakeresés. Nyomtatás cKövetkező |
Tömb tartományba
A feldolgozás után visszaírhatja a tömböt egy tartományba. Ha a fenti példában szereplő tömböt tartományba szeretné írni, meg kell adnia egy tartományt, amelynek mérete megegyezik a tömb elemeinek számával.
Az alábbi kóddal írja be a tömböt a D1: D5 tartományba:
| 123 | Tartomány ("D1: D5"). Érték = DirArrayTartomány ("D1: H1"). Érték = Application.Transpose (DirArray) |
Kérjük, vegye figyelembe, hogy a tömböt át kell ültetnie, ha sorba írja.
Összes tartomány
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Tartomány ("A1: A10"))Hibakeresés. SumOfRange nyomtatása |
Az Excelben elérhető számos funkciót használhatja a VBA -kódban az Application.WorkSheetFunction megadásával. a függvény neve előtt, mint a fenti példában.
Gróf tartomány
| 1234567 | 'Számolja a cellák számát a tartománybanCountOfCells = Application.WorksheetFunction.Count (Tartomány ("A1: A10"))Hibakeresés. Nyomtatás CountOfCells'Számolja a nem üres cellákat a tartománybanCountOfNonBlankCells = Application.WorksheetFunction.CountA (Tartomány ("A1: A10"))Hibakeresés. Nyomtatás CountOfNonBlankCells |
Írta: Vinamra Chandra
