Šajā rakstā tiks parādīts, kā izveidot dinamisko diapazonu programmā Excel VBA.
Konkrēta šūnu diapazona pasludināšana par mainīgo programmā Excel VBA ierobežo darbu tikai ar šīm konkrētajām šūnām. Deklarējot dinamiskos diapazonus programmā Excel, mēs iegūstam daudz lielāku elastību attiecībā uz mūsu kodu un tā funkcionalitāti.
Atsauces diapazoni un šūnas
Atsaucoties uz diapazonu vai šūnu objektā programmā Excel, mēs parasti atsaucamies uz tiem, izmantojot nepieciešamo kodu un rindu un kolonnas.
Diapazona īpašums
Izmantojot diapazona rekvizītu, tālāk norādītajās koda rindiņu rindās mēs varam veikt darbības šajā diapazonā, piemēram, mainīt šūnu krāsu vai padarīt treknrakstā šūnas.
| 12 | Diapazons ("A1: A5"). Fonts.Krāsa = vbSarkansDiapazons ("A1: A5"). Fonts. Bold = patiesa |
Šūnu īpašums
Līdzīgi mēs varam izmantot šūnu rekvizītu, lai atsauktos uz šūnu diapazonu, tieši atsaucoties uz šūnu rekvizīta rindu un kolonnu. Rindai vienmēr jābūt skaitlim, bet kolonnā var būt skaitlis vai burts, kas iekļauts pēdiņās.
Piemēram, uz šūnas adresi A1 var atsaukties šādi:
| 1 | Šūnas (1,1) |
Vai
| 1 | Šūnas (1, "A") |
Lai izmantotu šūnu rekvizītu, lai atsauktos uz šūnu diapazonu, mums jānorāda diapazona sākums un diapazona beigas.
Piemēram, atsauces diapazonam A1: A6 mēs varētu izmantot šo sintaksi zemāk:
| 1 | Diapazons (šūnas (1,1), šūnas (1,6) |
Pēc tam mēs varam izmantot rekvizītu Šūnas, lai veiktu darbības diapazonā saskaņā ar zemāk redzamajām koda rindiņām:
| 12 | Diapazons (šūnas (2, 2), šūnas (6, 2)). Fonts. Krāsa = vb sarkansDiapazons (šūnas (2, 2), šūnas (6, 2)). Fonts. Bold = patiesa |
Dinamiskie diapazoni ar mainīgajiem
Mainoties mūsu datu lielumam programmā Excel (t.i., mēs izmantojam vairāk rindu un kolonnu, kuru diapazoni ir kodēti), būtu lietderīgi mainīt arī diapazonus, uz kuriem mēs atsaucamies savā kodā. Izmantojot iepriekš minēto objektu Diapazons, mēs varam izveidot mainīgos, lai saglabātu mūsu izmantotās Excel darblapas apgabala maksimālos rindu un kolonnu numurus, un izmantot šos mainīgos, lai dinamiski pielāgotu diapazona objektu, kamēr kods darbojas.
Piemēram
| 1234 | Dim lRow kā vesels skaitlisDim lCol kā vesels skaitlislRinda = Diapazons ("A1048576"). Beigas (xlUp). RindalCol = Diapazons ("XFD1"). Beigas (xlToLeft). Kolonna |
Pēdējā rinda kolonnā
Tā kā darblapā ir 1048576 rindas, mainīgais lRow nonāks lapas apakšdaļā un pēc tam izmantos īpašo beigu taustiņa kombināciju un augšupvērsto bultiņu, lai pārietu uz pēdējo darblapā izmantoto rindu - tas mums dos iespēju rindas numurs, kas mums nepieciešams mūsu diapazonā.
Pēdējā kolonna rindā
Līdzīgi lCol pārcelsies uz XFD kolonnu, kas ir darblapas pēdējā kolonna, un pēc tam izmantojiet beigu taustiņa un kreisās bultiņas taustiņu īpašo kombināciju, lai pārietu uz pēdējo darblapā izmantoto kolonnu - tas mums dos kolonnas numurs, kas mums vajadzīgs mūsu diapazonā.
Tāpēc, lai iegūtu visu darblapā izmantoto diapazonu, mēs varam palaist šādu kodu:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol kā vesels skaitlisDim rng As RangelRinda = Diapazons ("A1048576"). Beigas (xlUp). Rinda"izmantojiet lRow, lai palīdzētu atrast diapazona pēdējo kolonnulCol = Diapazons ("XFD" & lRow) .End (xlToLeft) .SlejaIestatīt rng = diapazons (šūnas (1, 1), šūnas (lRow, lCol))'msgbox, lai parādītu mums diapazonuMsgBox "Diapazons ir" & rng.AddressBeigu apakš |
SpecialCells - LastCell
Mēs varam izmantot arī diapazona objekta metodi SpecialCells, lai iegūtu darblapā izmantoto pēdējo rindu un kolonnu.
| 123456789101112 | ApakšlietojumsSpeciālās šūnas ()Dim lRow As IntegerDim lCol kā vesels skaitlisDim rng As RangeDim rngSāciet kā diapazonuIestatīt rngBegin = Diapazons ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell). RindalCol = rngBegin.SpecialCells (xlCellTypeLastCell). KolonnaIestatīt rng = diapazons (šūnas (1, 1), šūnas (lRow, lCol))'msgbox, lai parādītu mums diapazonuMsgBox "Diapazons ir" & rng.AddressBeigu apakš |
UsedRange
Izmantotā diapazona metode ietver visas šūnas, kurām pašreizējā darblapā ir vērtības.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeIestatiet rng = ActiveSheet.UsedRange'msgbox, lai parādītu mums diapazonuMsgBox "Diapazons ir" & rng.AddressBeigu apakš |
Pašreizējais reģions
Pašreizējais reģions atšķiras no UsedRange ar to, ka tas aplūko šūnas, kas ieskauj šūnu, kuru esam deklarējuši kā sākuma diapazonu (ti, mainīgo rngBegin zemāk esošajā piemērā), un pēc tam aplūko visas šūnas, kas ir “pievienotas” vai saistītas uz šo deklarēto kameru. Ja rindā vai kolonnā parādās tukša šūna, pašreizējais reģions pārstās meklēt citas šūnas.
| 12345678 | Apakšējais pašreizējais reģions ()Dim rng As RangeDim rngSāciet kā diapazonuIestatīt rngBegin = Diapazons ("A1")Iestatiet rng = rngBegin.CurrentRegion'msgbox, lai parādītu mums diapazonuMsgBox "Diapazons ir" & rng.AddressBeigu apakš |
Ja mēs izmantojam šo metodi, mums jāpārliecinās, vai visas vajadzīgā diapazona šūnas ir savienotas, un starp tām nav tukšu rindu vai kolonnu.
Nosaukts diapazons
Mēs savā kodā varam atsaukties arī uz nosauktajiem diapazoniem. Nosauktie diapazoni var būt dinamiski tiktāl, ciktāl, kad dati tiek atjaunināti vai ievietoti, diapazona nosaukums var mainīties, iekļaujot tajā jaunos datus.
Šis piemērs mainīs diapazona nosaukuma “janvāris” fontu uz treknrakstu
| 12345 | Apakš diapazonsNosaukumsPiemērs ()Dim rng kā diapazonsIestatīt rng = Diapazons ("janvāris")rng.Font.Bold = = PatiessBeigu apakš |
Kā redzēsiet attēlā zemāk, ja diapazona nosaukumam ir pievienota rinda, diapazona nosaukums tiek automātiski atjaunināts, iekļaujot šo rindu.
Ja mēs pēc tam vēlreiz palaižam piemēra kodu, diapazons, ko ietekmē kods, būtu C5: C9, turpretī vispirms tas būtu bijis C5: C8.
Tabulas
Mēs savā kodā varam atsaukties uz tabulām (noklikšķiniet, lai iegūtu vairāk informācijas par tabulu izveidi un apstrādi VBA). Tā kā tabulas dati programmā Excel tiek atjaunināti vai mainīti, kods, kas attiecas uz tabulu, atsaucas uz atjauninātajiem tabulas datiem. Tas ir īpaši noderīgi, atsaucoties uz Pivot tabulām, kas ir savienotas ar ārēju datu avotu.
Izmantojot šo tabulu savā kodā, mēs varam atsaukties uz tabulas kolonnām pēc katras slejas virsrakstiem un veikt darbības ar kolonnu atbilstoši to nosaukumam. Tā kā tabulas rindas palielinās vai samazinās atbilstoši datiem, tabulas diapazons attiecīgi pielāgosies, un mūsu kods joprojām darbosies visā tabulas kolonnā.
Piemēram:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("piegādātājs"). IzdzēstBeigu apakš |
