Ta članek bo pokazal, kako ustvariti dinamični razpon v Excelu VBA.
Razglasitev določenega obsega celic kot spremenljivke v Excelu VBA nas omejuje na delo samo s temi celicami. Z razglasitvijo dinamičnih razponov v Excelu pridobimo veliko večjo prilagodljivost glede kode in funkcionalnosti, ki jih lahko izvaja.
Referenčni obsegi in celice
Ko se v Excelu sklicujemo na obseg ali celico, se običajno sklicujemo na njih s trdim kodiranjem v vrstici in stolpcih, ki jih potrebujemo.
Range Property
S pomočjo lastnosti Range v spodnjih vrsticah kode lahko izvedemo dejanja na tem območju, na primer spreminjanje barve celic ali krepitev celic.
| 12 | Obseg ("A1: A5"). Pisava.Barva = vbRdečaObseg ("A1: A5"). Font.Bold = True |
Lastnost celic
Podobno lahko z lastnostjo celic uporabimo sklic na obseg celic tako, da se neposredno sklicujemo na vrstico in stolpec v lastnosti celice. Vrstica mora biti vedno številka, stolpec pa je lahko številka ali črka v narekovajih.
Na primer naslov celice A1 se lahko sklicuje na:
| 1 | Celice (1,1) |
Or
| 1 | Celice (1, "A") |
Če želite uporabiti lastnosti celic za sklicevanje na obseg celic, moramo označiti začetek obsega in konec obsega.
Na primer za referenčno območje A1: A6 bi lahko uporabili naslednjo sintakso:
| 1 | Razpon (celice (1,1), celice (1,6) |
Lastnost Celice lahko nato uporabimo za izvajanje dejanj v obsegu v skladu s spodnjimi vrsticami kode:
| 12 | Obseg (celice (2, 2), celice (6, 2)). Pisava.Barva = vbRdečaObseg (celice (2, 2), celice (6, 2)). Font.Bold = True |
Dinamični razpon s spremenljivkami
Ker se velikost naših podatkov spreminja v Excelu (tj. Uporabljamo več vrstic in stolpcev, kot so obsegi, ki smo jih kodirali), bi bilo koristno, če bi se spremenili tudi obsegi, na katere se nanašamo v naši kodi. Z zgornjim objektom Range lahko ustvarimo spremenljivke za shranjevanje največjega števila vrstic in stolpcev območja Excelovega delovnega lista, ki ga uporabljamo, in te spremenljivke uporabimo za dinamično prilagajanje predmeta Range med izvajanjem kode.
Na primer
| 1234 | Zatemni lRow kot celo številoZatemni lCol kot celo številolRow = Obseg ("A1048576"). Konec (xlUp) .RowlCol = Razpon ("XFD1"). Konec (xlToLeft). Stolpec |
Zadnja vrstica v stolpcu
Ker je na delovnem listu 1048576 vrstic, bo spremenljivka lRow šla na dno lista in nato s posebno kombinacijo tipke End in puščice navzgor prešla na zadnjo vrstico, uporabljeno na delovnem listu - to nam bo dalo številka vrstice, ki jo potrebujemo v našem obsegu.
Zadnji stolpec v vrstici
Podobno se bo lCol premaknil v stolpec XFD, ki je zadnji stolpec na delovnem listu, nato pa s posebno kombinacijo tipk tipke End in puščične tipke levo premaknil na zadnji stolpec, uporabljen na delovnem listu - tako bomo dobili številka stolpca, ki ga potrebujemo v našem obsegu.
Zato, da dobimo celoten obseg, ki se uporablja na delovnem listu, lahko zaženemo naslednjo kodo:
| 1234567891011 | Sub GetRange ()Zatemni lRow kot celo številoZatemni lCol kot celo številoDim rng As RangelRow = Obseg ("A1048576"). Konec (xlUp) .Row'uporabite lRow za pomoč pri iskanju zadnjega stolpca v obsegulCol = Razpon ("XFD" & lRow). Konec (xlToLeft). StolpecNastavi rng = obseg (celice (1, 1), celice (lRow, lCol))'msgbox, da nam prikaže obsegMsgBox "Obseg je" & rng.NaslovEnd Sub |
SpecialCells - LastCell
Za pridobitev zadnje vrstice in stolpca na delovnem listu lahko uporabimo tudi metodo SpecialCells predmeta Range.
| 123456789101112 | Sub UseSpecialCells ()Zatemni niz kot celo številoZatemni lCol kot celo številoDim rng As RangeZatemni rng Začni kot obsegNastavi rngBegin = Obseg ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell). stolpecNastavi rng = obseg (celice (1, 1), celice (lRow, lCol))'msgbox, da nam prikaže obsegMsgBox "Obseg je" & rng.NaslovEnd Sub |
Rabljeno območje
Metoda Rabljeni obseg vključuje vse celice, ki imajo vrednosti v trenutnem delovnem listu.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeNastavite rng = ActiveSheet.UsedRange'msgbox, da nam prikaže obsegMsgBox "Obseg je" & rng.NaslovEnd Sub |
CurrentRegion
Trenutno območje se od UsedRange razlikuje po tem, da gleda na celice, ki obkrožajo celico, ki smo jo razglasili za izhodiščno območje (tj. Spremenljivko rngBegin v spodnjem primeru), nato pa pogleda vse celice, ki so "pritrjene" ali povezane v to prijavljeno celico. Če pride do prazne celice v vrstici ali stolpcu, bo CurrentRegion prenehal iskati nadaljnje celice.
| 12345678 | Sub CurrentRegion ()Dim rng As RangeZatemni rngZačni kot obsegNastavi rngBegin = Obseg ("A1")Nastavite rng = rngBegin.CurrentRegion'msgbox, da nam prikaže obsegMsgBox "Obseg je" & rng.NaslovEnd Sub |
Če uporabljamo to metodo, se moramo prepričati, da so vse celice v obsegu, ki ga potrebujete, povezane brez praznih vrstic ali stolpcev.
Imenovani razpon
V naši kodi se lahko sklicujemo tudi na imenovane obsege. Imenovani obsegi so lahko dinamični, če se ob posodobitvi ali vstavljanju podatkov lahko obseg spremeni tako, da vključuje nove podatke.
Ta primer bo spremenil pisavo v krepko za ime obsega "januar"
| 12345 | Pod obsegNameExample ()Zatemni rng kot dometSet rng = Range ("januar")rng.Font.Bold = = ResEnd Sub |
Kot boste videli na spodnji sliki, če se imenu imena obsega doda vrstica, se ime obsega samodejno posodobi in vključi to vrstico.
Če bi nato znova zagnali vzorčno kodo, bi bil obseg, na katerega vpliva koda, C5: C9, v prvem primeru pa C5: C8.
Tabele
V naši kodi se lahko sklicujemo na tabele (kliknite za več informacij o ustvarjanju in upravljanju tabel v VBA). Ker se podatki tabele v Excelu posodabljajo ali spreminjajo, se bo koda, ki se nanaša na tabelo, nanašala na posodobljene podatke tabele. To je še posebej uporabno pri sklicevanju na vrtilne tabele, ki so povezane z zunanjim virom podatkov.
S to tabelo v naši kodi se lahko sklicujemo na stolpce tabele po naslovih v vsakem stolpcu in izvajamo dejanja v stolpcu glede na njihovo ime. Ker se vrstice v tabeli glede na podatke povečujejo ali zmanjšujejo, se bo obseg tabel ustrezno prilagodil in naša koda bo še vedno delovala za celoten stolpec v tabeli.
Na primer:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). DeleteEnd Sub |
