Šis straipsnis parodys, kaip sukurti dinaminį diapazoną „Excel VBA“.
Konkretaus langelių diapazono paskelbimas kintamuoju „Excel VBA“ leidžia dirbti tik su tomis ląstelėmis. Skelbdami dinaminius diapazonus programoje „Excel“, mes įgyjame daug daugiau lankstumo dėl savo kodo ir funkcijų, kurias jis gali atlikti.
Nuorodų diapazonai ir ląstelės
Kai „Excel“ nurodome diapazoną ar langelį, paprastai nurodome juos koduodami eilutę ir stulpelius, kurių mums reikia.
Diapazono nuosavybė
Naudodami diapazono ypatybę, toliau pateiktose kodo eilučių pavyzdžiuose galime atlikti veiksmus su šiuo diapazonu, pvz., Pakeisti langelių spalvą arba paryškinti langelius.
| 12 | Diapazonas („A1: A5“). Šriftas.Spalva = vbRedDiapazonas („A1: A5“). Šriftas. Paryškintas = tiesa |
Ląstelių nuosavybė
Panašiai galime naudoti langelių ypatybę, norėdami nurodyti ląstelių diapazoną, tiesiogiai nurodydami ląstelių ypatybės eilutę ir stulpelį. Eilutė visada turi būti skaičius, bet stulpelis gali būti skaičius arba raidė, esanti kabutėse.
Pavyzdžiui, langelio adresą A1 galima nurodyti taip:
| 1 | Ląstelės (1,1) |
Arba
| 1 | Ląstelės (1, "A") |
Norėdami naudoti langelių ypatybę, norėdami nurodyti ląstelių diapazoną, turime nurodyti diapazono pradžią ir diapazono pabaigą.
Pavyzdžiui, A1: A6 atskaitos diapazonui galėtume naudoti šią sintaksę:
| 1 | Diapazonas (langeliai (1,1), langeliai (1,6) |
Tada mes galime naudoti ypatybę Ląstelės, kad atliktume veiksmus diapazone, kaip nurodyta toliau pateiktose kodo eilutėse:
| 12 | Diapazonas (langeliai (2, 2), langeliai (6, 2)). Šriftas. Spalva = vbRedDiapazonas (langeliai (2, 2), langeliai (6, 2)). Šriftas. Bold = tiesa |
Dinaminiai diapazonai su kintamaisiais
Kadangi „Excel“ keičiasi mūsų duomenų dydis (t. Y. Naudojame daugiau eilučių ir stulpelių, kuriuos užkodavome), būtų naudinga, jei pasikeistų ir diapazonai, kuriuos nurodome savo kode. Naudodami aukščiau esantį objektą Diapazonas galime sukurti kintamuosius, kad būtų saugomi maksimalūs naudojamo „Excel“ darbalapio srities eilučių ir stulpelių numeriai, ir naudodami šiuos kintamuosius dinamiškai koreguokite diapazono objektą, kol kodas veikia.
Pavyzdžiui
| 1234 | Dim lRow kaip sveikasis skaičiusDim lCol kaip sveikasis skaičiuslRow = Diapazonas ("A1048576"). Pabaiga (xlUp) .EilutėlCol = Diapazonas ("XFD1"). Pabaiga (xlToLeft). Stulpelis |
Paskutinė eilutė stulpelyje
Kadangi darbalapyje yra 1048576 eilučių, kintamasis lRow pereis į lapo apačią, o tada naudosis specialiu klavišo „End“ ir rodyklės aukštyn klavišų deriniu, kad pereitumėte į paskutinę darbalapyje naudojamą eilutę - tai suteiks mums eilutės, kurios mums reikia mūsų diapazone, skaičius.
Paskutinis stulpelis eilutėje
Panašiai „lCol“ pereis į XFD stulpelį, kuris yra paskutinis darbalapio stulpelis, tada naudokite specialų klavišų kombinaciją „End“ ir rodyklės kairėn klavišus, kad pereitumėte prie paskutinio darbalapyje naudojamo stulpelio - tai suteiks mums mums reikalingo stulpelio numeris.
Todėl, norėdami gauti visą diapazoną, kuris naudojamas darbalapyje, galime paleisti šį kodą:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol As IntegerDim rng As RangelRow = Diapazonas ("A1048576"). Pabaiga (xlUp) .Eilutė„naudokite„ lRow “, kad padėtumėte rasti paskutinį diapazono stulpelįlCol = Diapazonas ("XFD" ir lRow). Pabaiga (xlToLeft). StulpelisNustatyti rng = Diapazonas (langeliai (1, 1), langeliai (lRow, lCol))„msgbox“, kad parodytumėte mums diapazoną„MsgBox“ diapazonas yra „& rng.Address“Pabaiga Sub |
Specialios ląstelės - „LastCell“
Taip pat galime naudoti diapazono objekto „SpecialCells“ metodą, kad paskutinė eilutė ir stulpelis būtų naudojami darbalapyje.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol As IntegerDim rng As RangeDim rngBegin As RangeNustatyti rngBegin = Diapazonas („A1“)lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell). StulpelisNustatyti rng = Diapazonas (langeliai (1, 1), langeliai (lRow, lCol))„msgbox“, kad parodytume diapazoną„MsgBox“ diapazonas yra „& rng.Address“Pabaiga Sub |
„UsedRange“
Naudojamo diapazono metodas apima visas ląsteles, kuriose yra reikšmių dabartiniame darbalapyje.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeNustatykite rng = ActiveSheet.UsedRange„msgbox“, kad parodytumėte mums diapazoną„MsgBox“ diapazonas yra „& rng.Address“Pabaiga Sub |
Dabartinis regionas
Dabartinis regionas skiriasi nuo „UsedRange“ tuo, kad jis žiūri į ląsteles supančias ląsteles, kurias paskelbėme kaip pradinį diapazoną (ty kintamąjį „rngBegin“ žemiau pateiktame pavyzdyje), ir tada apžiūri visas „prijungtas“ ar susietas ląsteles į tą deklaruotą kamerą. Jei eilutėje ar stulpelyje atsiranda tuščių langelių, „CurrentRegion“ nustos ieškoti kitų langelių.
| 12345678 | Sub CurrentRegion ()Dim rng As RangeDim rngBegin As RangeNustatyti rngBegin = Diapazonas („A1“)Nustatykite rng = rngBegin.CurrentRegion„msgbox“, kad parodytumėte mums diapazoną„MsgBox“ diapazonas yra „& rng.Address“Pabaiga Sub |
Jei naudojame šį metodą, turime įsitikinti, kad visos reikiamo diapazono ląstelės yra sujungtos, be jų nėra tuščių eilučių ar stulpelių.
Pavadintas diapazonas
Savo kode taip pat galime nurodyti pavadintus diapazonus. Pavadinti diapazonai gali būti dinamiški, nes kai duomenys atnaujinami arba įterpiami, diapazono pavadinimas gali būti pakeistas, įtraukiant naujus duomenis.
Šiame pavyzdyje diapazono pavadinimo „sausis“ šriftas bus paryškintas
| 12345 | Antrasis diapazonasPavadinimas ()Dim rng kaip diapazonasNustatyti rng = Diapazonas („sausis“)rng.Font.Bold = = TiesaPabaiga Sub |
Kaip matysite paveikslėlyje žemiau, jei prie diapazono pavadinimo pridedama eilutė, diapazono pavadinimas automatiškai atnaujinamas, įtraukiant tą eilutę.
Jei mes vėl paleistume pavyzdinį kodą, kodo paveiktas diapazonas būtų C5: C9, o iš pradžių tai būtų buvęs C5: C8.
Lentelės
Savo kode galime nurodyti nuorodas į lenteles (spustelėkite, jei norite gauti daugiau informacijos apie lentelių kūrimą ir valdymą VBA). Kai „Excel“ lentelės duomenys yra atnaujinami arba keičiami, kodas, nurodantis lentelę, nurodys atnaujintus lentelės duomenis. Tai ypač naudinga, kai kalbama apie „Pivot“ lenteles, prijungtas prie išorinio duomenų šaltinio.
Naudodami šią lentelę savo kode, galime remtis lentelės stulpeliais pagal kiekvieno stulpelio antraštes ir atlikti veiksmus su stulpeliu pagal jų pavadinimą. Kadangi lentelės eilutės didėja arba mažėja pagal duomenis, lentelės diapazonas atitinkamai koreguosis, o mūsų kodas vis tiek veiks visam lentelės stulpeliui.
Pavyzdžiui:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Tiekėjas"). IštrintiPabaiga Sub |
