Ovaj članak će pokazati kako stvoriti dinamički raspon u Excelu VBA.
Deklariranje određenog raspona ćelija kao varijable u Excelu VBA ograničava nas na rad samo s tim određenim ćelijama. Deklariranjem dinamičkih raspona u Excelu dobivamo daleko veću fleksibilnost u odnosu na naš kôd i funkcionalnost koju može izvesti.
Referentni rasponi i ćelije
Kada se pozivamo na objekt Raspon ili ćelija u Excelu, obično se pozivamo na njih pomoću tvrdog kodiranja u retku i stupcu koji su nam potrebni.
Range Property
Koristeći svojstvo raspona, u primjerima redaka koda u nastavku, možemo izvesti radnje na ovom rasponu, kao što je promjena boje ćelija ili podebljavanje ćelija.
| 12 | Raspon ("A1: A5"). Font.Color = vbRedRaspon ("A1: A5"). Font.Bold = True |
Imovina ćelija
Slično, svojstvo ćelija možemo upotrijebiti za pozivanje na raspon ćelija izravnim upućivanjem na redak i stupac u svojstvu ćelija. Redak uvijek mora biti broj, ali stupac može biti broj ili slovo zatvoreno pod navodnicima.
Na primjer, adresa ćelije A1 može se referencirati kao:
| 1 | Stanice (1,1) |
Ili
| 1 | Ćelije (1, "A") |
Da bismo koristili svojstvo ćelija za referenciranje raspona ćelija, moramo naznačiti početak raspona i kraj raspona.
Na primjer, za referentni raspon A1: A6 mogli bismo upotrijebiti ovu sintaksu u nastavku:
| 1 | Raspon (ćelije (1,1), ćelije (1,6) |
Zatim možemo koristiti svojstvo Cells za izvođenje radnji na rasponu prema primjerima redaka koda u nastavku:
| 12 | Raspon (ćelije (2, 2), ćelije (6, 2)). Font.Color = vbRedRaspon (ćelije (2, 2), ćelije (6, 2)). Font.Bold = True |
Dinamički rasponi s varijablama
Kako se veličina naših podataka mijenja u Excelu (tj. Koristimo više redaka i stupaca od raspona koje smo kodirali), bilo bi korisno da se promijene i rasponi na koje se pozivamo u našem kodu. Pomoću gornjeg objekta Range možemo stvoriti varijable za spremanje maksimalnih brojeva redaka i stupaca područja radnog lista programa Excel koji koristimo, te koristiti te varijable za dinamičko prilagođavanje objekta Range dok je kôd pokrenut.
Na primjer
| 1234 | Dim lRow kao cijeli brojZatamnite lCol kao cijeli brojlRow = Raspon ("A1048576"). Kraj (xlUp) .RowlCol = Raspon ("XFD1"). Kraj (xlToLeft). Stupac |
Posljednji red u stupcu
Budući da na radnom listu ima 1048576 redaka, varijabla lRow otići će na dno lista, a zatim će koristiti posebnu kombinaciju tipke End i tipke sa strelicom prema gore za prelazak na zadnji redak koji se koristi na radnom listu - to će nam dati broj redaka koji nam je potreban u našem rasponu.
Posljednja kolona u redu
Slično, lCol će se premjestiti u stupac XFD koji je posljednji stupac na radnom listu, a zatim će upotrijebiti posebnu kombinaciju tipki tipke End i tipke sa strelicom ulijevo za prelazak na posljednji stupac koji se koristi na radnom listu - to će nam dati broj stupca koji nam je potreban u našem rasponu.
Stoga, da bismo dobili cijeli raspon koji se koristi na radnom listu, možemo pokrenuti sljedeći kod:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol kao cijeli brojDim rng As RangelRow = Raspon ("A1048576"). Kraj (xlUp) .Row'koristite lRow kako biste lakše pronašli zadnji stupac u rasponulCol = Raspon ("XFD" & lRow). Kraj (xlToLeft). StupacPostavi rng = raspon (ćelije (1, 1), ćelije (lRow, lCol))'msgbox da nam pokaže rasponMsgBox "Raspon je" & rng.AdresaKraj podm |
Posebne ćelije - LastCell
Također možemo upotrijebiti metodu SpecialCells za objekt raspona kako bismo dobili zadnji redak i stupac koji se koriste na radnom listu.
| 123456789101112 | Dodatna upotrebaSpecialCells ()Dim lRow As IntegerDim lCol kao cijeli brojDim rng As RangeDim rngPočnite kao rasponPostavi rngBegin = Raspon ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell). StupacPostavi rng = raspon (ćelije (1, 1), ćelije (lRow, lCol))'msgbox da nam pokaže rasponMsgBox "Raspon je" & rng.AdresaKraj podm |
UsedRange
Metoda rabljenog raspona uključuje sve ćelije koje imaju vrijednosti u trenutnom radnom listu.
| 123456 | Sub UsedRangeExample ()Dim rng As RangePostavite rng = ActiveSheet.UsedRange'msgbox da nam pokaže rasponMsgBox "Raspon je" & rng.AdresaKraj podm |
CurrentRegion
Trenutačno područje razlikuje se od UsedRange po tome što gleda ćelije koje okružuju ćeliju koju smo proglasili početnim rasponom (tj. Varijablu rngBegin u donjem primjeru), a zatim gleda sve ćelije koje su 'povezane' ili povezane u tu deklariranu ćeliju. Ako se pojavi prazna ćelija u retku ili stupcu, CurrentRegion će prestati tražiti daljnje ćelije.
| 12345678 | Pod CurrentRegion ()Dim rng As RangeDim rngPočnite kao rasponPostavi rngBegin = Raspon ("A1")Postavite rng = rngBegin.CurrentRegion'msgbox da nam pokaže rasponMsgBox "Raspon je" & rng.AdresaKraj podm |
Ako koristimo ovu metodu, moramo se pobrinuti da sve ćelije u rasponu koji vam trebaju budu povezane bez praznih redaka ili stupaca među njima.
Imenovani raspon
U našem kodu također možemo uputiti Named Ranges. Imenovani rasponi mogu biti dinamični utoliko što se prilikom ažuriranja ili umetanja naziv raspona može promijeniti tako da uključi nove podatke.
Ovaj primjer promijenit će font u podebljani za naziv raspona "siječanj"
| 12345 | PodrazmjerNazivPrimjer ()Dim rng as RangePostavi rng = Raspon ("siječanj")rng.Font.Bold = = IstinaKraj podm |
Kao što ćete vidjeti na donjoj slici, ako se u naziv raspona doda redak, tada se naziv raspona automatski ažurira tako da uključuje taj redak.
Ako bismo zatim ponovno pokrenuli primjer koda, raspon na koji utječe kôd bio bi C5: C9 dok bi u prvom slučaju to bio C5: C8.
Tablice
U našem kodu možemo se pozvati na tablice (kliknite za više informacija o stvaranju i rukovanju tablicama u VBA -i). Kako se tablični podaci u Excelu ažuriraju ili mijenjaju, kôd koji se odnosi na tablicu tada će se odnositi na ažurirane podatke tablice. To je osobito korisno kada se odnosi na zaokretne tablice koje su povezane s vanjskim izvorom podataka.
Pomoću ove tablice u našem kodu možemo se pozivati na stupce tablice prema naslovima u svakom stupcu i izvršavati radnje na stupcu prema njihovom imenu. Kako se redovi u tablici povećavaju ili smanjuju prema podacima, raspon tablice prilagođavat će se u skladu s tim, a naš će kôd i dalje raditi za cijeli stupac u tablici.
Na primjer:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). DeleteKraj podm |
