Acest articol va arăta cum să creați un interval dinamic în Excel VBA.
Declararea unui anumit interval de celule ca variabilă în Excel VBA ne limitează să lucrăm numai cu acele celule particulare. Prin declararea gamelor dinamice în Excel, câștigăm mult mai multă flexibilitate în ceea ce privește codul nostru și funcționalitatea pe care acesta le poate îndeplini.
Referirea la intervale și celule
Atunci când facem referire la obiectul Range sau Cell în Excel, în mod normal ne referim la acestea prin codare hard în rândul și coloanele de care avem nevoie.
Proprietatea Range
Folosind Proprietatea Range, în exemplele de linii de cod de mai jos, putem efectua acțiuni pe acest interval, cum ar fi schimbarea culorii celulelor sau transformarea celulelor în bold.
| 12 | Gama ("A1: A5"). Font.Color = vbRedGama („A1: A5”). Font.Bold = True |
Proprietatea celulelor
În mod similar, putem utiliza Proprietatea de celule pentru a face referire la o gamă de celule, făcând referire directă la rândul și coloana din proprietatea de celule. Rândul trebuie să fie întotdeauna un număr, dar coloana poate fi un număr sau o literă încadrată între ghilimele.
De exemplu, adresa celulei A1 poate fi menționată ca:
| 1 | Celule (1,1) |
Sau
| 1 | Celule (1, "A") |
Pentru a utiliza proprietatea Cells pentru a face referire la un interval de celule, trebuie să indicăm începutul intervalului și sfârșitul intervalului.
De exemplu, pentru domeniul de referință A1: A6 am putea folosi această sintaxă mai jos:
| 1 | Interval (celule (1,1), celule (1,6) |
Putem folosi proprietatea Cells pentru a efectua acțiuni pe interval, conform exemplelor de linii de cod de mai jos:
| 12 | Gama (celule (2, 2), celule (6, 2)). Font.Color = vbRedGama (celule (2, 2), celule (6, 2)). Font.Bold = True |
Gamele dinamice cu variabile
Pe măsură ce dimensiunea datelor noastre se schimbă în Excel (adică folosim mai multe rânduri și coloane pe care intervalele le-am codificat), ar fi util să se schimbe și intervalele la care ne referim în codul nostru. Folosind obiectul Range de mai sus putem crea variabile pentru a stoca numărul maxim de rânduri și coloane din zona foii de lucru Excel pe care o folosim și putem utiliza aceste variabile pentru a regla dinamic obiectul Range în timp ce rulează codul.
De exemplu
| 1234 | Dim lRow ca număr întregDim lCol ca întreglRow = Range ("A1048576"). End (xlUp) .RowlCol = Range ("XFD1"). End (xlToLeft) .Column |
Ultimul rând din coloană
Deoarece există 1048576 rânduri într-o foaie de lucru, variabila lRow va merge în partea de jos a foii și apoi va folosi combinația specială a tastei Sfârșit plus tasta Săgeată sus pentru a merge la ultimul rând utilizat în foaia de lucru - acest lucru ne va da numărul rândului de care avem nevoie în gama noastră.
Ultima coloană în rând
În mod similar, lCol se va muta la Coloana XFD, care este ultima coloană dintr-o foaie de lucru, apoi va folosi combinația specială de taste a tastei Sfârșit plus tasta Săgeată stânga pentru a merge la ultima coloană utilizată în foaia de lucru - aceasta ne va oferi numărul coloanei de care avem nevoie în gama noastră.
Prin urmare, pentru a obține întreaga gamă care este utilizată în foaia de lucru, putem rula următorul cod:
| 1234567891011 | Sub GetRange ()Dim lRow Ca întregDim lCol Ca întregDim rng As RangelRow = Range ("A1048576"). End (xlUp) .Row'utilizați lRow pentru a găsi ultima coloană din intervallCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnSetați rng = Range (Celule (1, 1), Celule (lRow, lCol))'msgbox pentru a ne arăta gamaMsgBox „Range is” & rng.AddressSfârșitul Sub |
SpecialCells - LastCell
De asemenea, putem folosi metoda SpecialCells a obiectului Range pentru a obține ultimul rând și coloană utilizate într-o foaie de lucru.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow Ca întregDim lCol Ca întregDim rng As RangeÎncepeți ca intervalSetați rngBegin = Range ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnSetați rng = Range (Celule (1, 1), Celule (lRow, lCol))'msgbox pentru a ne arăta gamaMsgBox „Range is” & rng.AddressSfârșitul Sub |
UsedRange
Metoda Gama utilizată include toate celulele care au valori în foaia de lucru curentă.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeSetați rng = ActiveSheet.UsedRange'msgbox pentru a ne arăta gamaMsgBox „Range is” & rng.AddressSfârșitul Sub |
Regiunea curentă
Regiunea curentă diferă de Range Utilizat prin faptul că privește celulele din jurul unei celule pe care le-am declarat ca un interval de pornire (adică variabila rngBegin din exemplul de mai jos) și apoi privește toate celulele care sunt „atașate” sau asociate la acea celulă declarată. În cazul în care apare o celulă goală într-un rând sau coloană, atunci CurrentRegion nu va mai căuta alte celule.
| 12345678 | Regiunea curentă sub ()Dim rng As RangeÎncepeți ca intervalSetați rngBegin = Range ("A1")Setați rng = rngBegin.CurrentRegion'msgbox pentru a ne arăta gamaMsgBox „Range is” & rng.AddressSfârșitul Sub |
Dacă folosim această metodă, trebuie să ne asigurăm că toate celulele din intervalul de care aveți nevoie sunt conectate fără rânduri sau coloane goale între ele.
Denumit Range
De asemenea, putem face referire la Named Ranges în codul nostru. Intervalele denumite pot fi dinamice în măsura în care datele sunt actualizate sau inserate, numele zonei se poate modifica pentru a include noile date.
Acest exemplu va schimba fontul în bold pentru numele intervalului „Ianuarie”
| 12345 | Sub RangeNameExample ()Dim rng ca RangeSetați rng = Range („ianuarie”)rng.Font.Bold = = AdevăratSfârșitul Sub |
După cum veți vedea în imaginea de mai jos, dacă un rând este adăugat în numele zonei, atunci numele zonei se actualizează automat pentru a include acel rând.
Dacă ar rula apoi exemplul de cod din nou, intervalul afectat de cod ar fi C5: C9, în timp ce în primă instanță ar fi fost C5: C8.
Mese
Putem consulta tabele (faceți clic pentru mai multe informații despre crearea și manipularea tabelelor în VBA) în codul nostru. Pe măsură ce datele unui tabel în Excel sunt actualizate sau modificate, codul care se referă la tabel se va referi apoi la datele actualizate ale tabelului. Acest lucru este util mai ales atunci când faceți referire la tabelele pivot care sunt conectate la o sursă de date externă.
Folosind acest tabel în codul nostru, ne putem referi la coloanele tabelului după titlurile din fiecare coloană și putem efectua acțiuni pe coloană în funcție de numele lor. Pe măsură ce rândurile din tabel cresc sau scad în funcție de date, intervalul tabelului se va regla corespunzător și codul nostru va funcționa în continuare pentru întreaga coloană din tabel.
De exemplu:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Furnizor"). ȘtergețiSfârșitul Sub |
