Când lucrați cu Excel, cea mai mare parte a timpului dvs. îl petreceți în zona foii de lucru - tratând celule și intervale.
Și dacă doriți să vă automatizați munca în Excel folosind VBA, trebuie să știți cum să lucrați cu celule și intervale folosind VBA.
Există o mulțime de lucruri diferite pe care le puteți face cu intervale în VBA (cum ar fi selectați, copiați, mutați, editați etc.).
Deci, pentru a acoperi acest subiect, voi împărți acest tutorial în secțiuni și vă voi arăta cum să lucrați cu celule și intervale în Excel VBA folosind exemple.
Să începem.
Toate codurile pe care le menționez în acest tutorial trebuie plasate în Editorul VB. Accesați secțiunea „Unde se pune codul VBA” pentru a afla cum funcționează.Dacă sunteți interesat să învățați VBA într-un mod ușor, consultați Instruire online VBA Excel.
Selectarea unei celule / intervale în Excel folosind VBA
Pentru a lucra cu celule și intervale în Excel folosind VBA, nu este necesar să o selectați.
În majoritatea cazurilor, este mai bine să nu selectați celule sau intervale (așa cum vom vedea).
În ciuda acestui fapt, este important să parcurgeți această secțiune și să înțelegeți cum funcționează. Acest lucru va fi crucial în învățarea dvs. VBA și o mulțime de concepte acoperite aici vor fi utilizate pe parcursul acestui tutorial.
Deci, să începem cu un exemplu foarte simplu.
Selectarea unei singure celule folosind VBA
Dacă doriți să selectați o singură celulă în foaia activă (să zicem A1), atunci puteți utiliza codul de mai jos:
Sub SelectCell () Range ("A1"). Selectați End Sub
Codul de mai sus are partea obligatorie „Sub” și „End Sub” și o linie de cod care selectează celula A1.
Range („A1”) spune VBA adresa celulei la care dorim să ne referim.
Selectați este o metodă a obiectului Range și selectează celulele / intervalul specificat în obiectul Range. Referințele celulei trebuie să fie cuprinse între ghilimele duble.
Acest cod ar arăta o eroare în cazul în care o foaie grafică este o foaie activă. O foaie de diagrame conține diagrame și nu este utilizată pe scară largă. Deoarece nu are celule / intervale, codul de mai sus nu îl poate selecta și ar ajunge să prezinte o eroare.
Rețineți că, deoarece doriți să selectați celula din foaia activă, trebuie doar să specificați adresa celulei.
Dar dacă doriți să selectați celula dintr-o altă foaie (să presupunem Sheet2), trebuie mai întâi să activați Sheet2 și apoi să selectați celula din ea.
Sub SelectCell () Foi de lucru ("Sheet2"). Activați Range ("A1"). Selectați End Sub
În mod similar, puteți să activați și un registru de lucru, apoi să activați o foaie de lucru specifică în acesta și apoi să selectați o celulă.
Sub SelectCell () Cărți de lucru ("Book2.xlsx"). Foi de lucru ("Sheet2"). Activați intervalul ("A1"). Selectați End Sub
Rețineți că, atunci când faceți referire la registre de lucru, trebuie să utilizați numele complet împreună cu extensia de fișier (.xlsx în codul de mai sus). În cazul în care registrul de lucru nu a fost niciodată salvat, nu este nevoie să utilizați extensia de fișier.
Acum, aceste exemple nu sunt foarte utile, dar veți vedea mai târziu în acest tutorial cum putem folosi aceleași concepte pentru a copia și lipi celule în Excel (folosind VBA).
La fel cum selectăm o celulă, putem selecta și o gamă.
În cazul unui interval, acesta ar putea fi un interval de dimensiuni fixe sau un interval de dimensiuni variabil.
Într-un interval de dimensiuni fixe, veți ști cât de mare este intervalul și puteți utiliza dimensiunea exactă în codul dvs. VBA. Dar, cu o gamă de dimensiuni variabile, nu aveți idee cât de mare este gama și trebuie să utilizați un pic de magie VBA.
Să vedem cum să facem acest lucru.
Selectarea unei game de dimensiuni corecte
Iată codul care va selecta intervalul A1: D20.
Sub SelectRange () Range ("A1: D20"). Selectați End Sub
Un alt mod de a face acest lucru este utilizarea codului de mai jos:
Sub SelectRange () Range ("A1", "D20"). Selectați End Sub
Codul de mai sus preia adresa celulei din stânga sus (A1) și adresa din dreapta jos a celulei (D20) și selectează întregul interval. Această tehnică devine utilă atunci când lucrați cu intervale de dimensiuni variabile (așa cum vom vedea când proprietatea End este acoperită mai târziu în acest tutorial).
Dacă doriți ca selecția să aibă loc într-un registru de lucru diferit sau într-o altă foaie de lucru, atunci trebuie să spuneți VBA numele exacte ale acestor obiecte.
De exemplu, codul de mai jos ar selecta intervalul A1: D20 în foaia de lucru Sheet2 din registrul de lucru Book2.
Sub SelectRange () Cărți de lucru ("Book2.xlsx"). Foi de lucru ("Sheet1"). Activați gama ("A1: D20"). Selectați End Sub
Acum, dacă nu știți câte rânduri există. Ce se întâmplă dacă doriți să selectați toate celulele care au o valoare în ea.
În aceste cazuri, trebuie să utilizați metodele prezentate în secțiunea următoare (la selectarea intervalului de dimensiuni variabile).
Selectarea unui domeniu variabil
Există diferite moduri în care puteți selecta o gamă de celule. Metoda pe care o alegeți va depinde de modul în care sunt structurate datele.
În această secțiune, voi acoperi câteva tehnici utile care sunt cu adevărat utile atunci când lucrați cu intervale în VBA.
Selectați Utilizarea proprietății CurrentRange
În cazurile în care nu știți câte rânduri / coloane au datele, puteți utiliza proprietatea CurrentRange a obiectului Range.
Proprietatea CurrentRange acoperă toate celulele umplute adiacente dintr-un interval de date.
Mai jos este codul care va selecta regiunea curentă care deține celula A1.
Sub SelectCurrentRegion () Range ("A1"). CurrentRegion. Selectați End Sub
Metoda de mai sus este bună atunci când aveți toate datele sub formă de tabel fără rânduri / coloane goale.
Dar, în cazul în care aveți rânduri / coloane goale în datele dvs., nu le va selecta pe cele de după rândurile / coloanele goale. În imaginea de mai jos, codul CurrentRegion selectează date până la rândul 10, deoarece rândul 11 este gol.
În astfel de cazuri, poate doriți să utilizați proprietatea UsedRange a obiectului Foaie de lucru.
Selectați Utilizarea proprietății UsedRange
UsedRange vă permite să faceți referire la orice celule care au fost modificate.
Deci, codul de mai jos ar selecta toate celulele utilizate în foaia activă.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Rețineți că, în cazul în care aveți o celulă îndepărtată care a fost utilizată, aceasta ar fi luată în considerare de codul de mai sus și toate celulele până când celula utilizată va fi selectată.
Selectați Utilizarea proprietății finale
Acum, această parte este cu adevărat utilă.
Proprietatea End vă permite să selectați ultima celulă completată. Acest lucru vă permite să imitați efectul tastelor săgeată Control jos / sus sau Control dreapta / stânga.
Să încercăm să înțelegem acest lucru folosind un exemplu.
Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să selectați rapid ultimele celule umplute în coloana A.
Problema este că datele se pot schimba și nu știți câte celule sunt umplute. Dacă trebuie să faceți acest lucru folosind tastatura, puteți selecta celula A1, apoi utilizați tasta săgeată Control + Jos și va selecta ultima celulă completată în coloană.
Acum să vedem cum se face acest lucru folosind VBA. Această tehnică este utilă atunci când doriți să treceți rapid la ultima celulă umplută într-o coloană de dimensiuni variabile
Sub GoToLastFilledCell () Range ("A1"). End (xlDown). Selectați End Sub
Codul de mai sus ar sari la ultima celulă completată din coloana A.
În mod similar, puteți utiliza End (xlToRight) pentru a trece la ultima celulă umplută dintr-un rând.
Sub GoToLastFilledCell () Range ("A1"). End (xlToRight). Selectați End Sub
Acum, ce se întâmplă dacă doriți să selectați întreaga coloană în loc să săriți la ultima celulă umplută.
Puteți face acest lucru folosind codul de mai jos:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Selectați End Sub
În codul de mai sus, am folosit prima și ultima referință a celulei pe care trebuie să o selectăm. Indiferent cât de multe celule sunt acolo, codul de mai sus va selecta toate.
Amintiți-vă exemplul de mai sus în care am selectat intervalul A1: D20 utilizând următoarea linie de cod:
Gama („A1 ″,„ D20 ”)
Aici A1 era celula din stânga sus și D20 era celula din dreapta jos din interval. Putem folosi aceeași logică în selectarea intervalelor de dimensiuni variabile. Dar, deoarece nu cunoaștem adresa exactă a celulei din dreapta jos, am folosit proprietatea End pentru ao obține.
În Range („A1”, Range („A1”). End (xlDown)), „A1” se referă la prima celulă și Range („A1”). End (xlDown) se referă la ultima celulă. Deoarece am furnizat ambele referințe, metoda Select selectează toate celulele dintre aceste două referințe.
În mod similar, puteți selecta și un întreg set de date care are mai multe rânduri și coloane.
Codul de mai jos ar selecta toate rândurile / coloanele umplute începând de la celula A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown). End (xlToRight)). Selectați End Sub
În codul de mai sus, am folosit Range („A1”). End (xlDown) .End (xlToRight) pentru a obține referința celulei completate în dreapta jos a setului de date.
Diferența dintre utilizarea CurrentRegion și End
Dacă vă întrebați de ce utilizați proprietatea End pentru a selecta intervalul completat când avem proprietatea CurrentRegion, permiteți-mi să vă spun diferența.
Cu proprietatea End, puteți specifica celula de început. De exemplu, dacă aveți datele dvs. în A1: D20, dar primul rând sunt anteturi, puteți utiliza proprietatea End pentru a selecta datele fără anteturi (folosind codul de mai jos).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown). End (xlToRight)). Selectați End Sub
Dar CurrentRegion ar selecta automat întregul set de date, inclusiv anteturile.
Până acum, în acest tutorial, am văzut cum să ne referim la o gamă de celule folosind moduri diferite.
Acum, să vedem câteva modalități prin care putem folosi aceste tehnici pentru a face ceva lucru.
Copiați celulele / intervalele folosind VBA
După cum am menționat la începutul acestui tutorial, selectarea unei celule nu este necesară pentru a efectua acțiuni asupra acesteia. În această secțiune veți vedea cum să copiați celule și intervale fără a le selecta.
Să începem cu un exemplu simplu.
Copierea unei singure celule
Dacă doriți să copiați celula A1 și să o inserați în celula D1, codul de mai jos o va face.
Sub CopyCell () Range ("A1"). Copy Range ("D1") End Sub
Rețineți că metoda de copiere a obiectului interval copiază celula (la fel ca Control + C) și o lipeste în destinația specificată.
În exemplul de cod de mai sus, destinația este specificată în aceeași linie în care utilizați metoda de copiere. Dacă doriți să faceți codul dvs. mai ușor de citit, puteți utiliza codul de mai jos:
Sub CopyCell () Range ("A1"). Destinație de copiere: = Range ("D1") End Sub
Codurile de mai sus vor copia și lipi valoarea, precum și formatarea / formulele în ea.
Așa cum ați fi observat deja, codul de mai sus copiază celula fără a o selecta. Indiferent unde vă aflați pe foaia de lucru, codul va copia celula A1 și o va lipi pe D1.
De asemenea, rețineți că codul de mai sus ar suprascrie orice cod existent în celula D2. Dacă doriți ca Excel să vă anunțe dacă există deja ceva în celula D1 fără a-l suprascrie, puteți utiliza codul de mai jos.
Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Do you want to overwrite the existing data", vbYesNo) End If If Response = vbYes Then Range ("A1"). Copy Range ("D1") ") End If End Sub
Copierea unei game de dimensiuni corecte
Dacă doriți să copiați A1: D20 în J1: M20, puteți utiliza codul de mai jos:
Sub CopyRange () Range ("A1: D20"). Copy Range ("J1") End Sub
În celula de destinație, trebuie doar să specificați adresa celulei din stânga sus. Codul ar copia automat intervalul exact copiat în destinație.
Puteți utiliza același construct pentru a copia date de pe o foaie pe cealaltă.
Codul de mai jos ar copia A1: D20 din foaia activă în Foaia2.
Sub CopyRange () Range ("A1: D20"). Copiați foile de lucru ("Sheet2"). Range ("A1") End Sub
Cele de mai sus copiază datele din foaia activă. Deci, asigurați-vă că foaia care are datele este foaia activă înainte de a rula codul. Pentru a fi în siguranță, puteți specifica și numele foii de lucru în timp ce copiați datele.
Sub CopyRange () Foi de lucru ("Sheet1"). Range ("A1: D20"). Copy Copy Works ("Sheet2"). Range ("A1") End Sub
Partea bună a codului de mai sus este că, indiferent de foaia activă, va copia întotdeauna datele din Sheet1 și le va lipi în Sheet2.
De asemenea, puteți copia un interval numit folosind numele acestuia în locul referinței.
De exemplu, dacă aveți un interval numit numit „SalesData”, puteți utiliza codul de mai jos pentru a copia aceste date în Sheet2.
Sub CopyRange () Range ("SalesData"). Copiați foile de lucru ("Sheet2"). Range ("A1") End Sub
Dacă domeniul de aplicare al intervalului numit este întregul registru de lucru, nu este necesar să fiți pe foaia care are intervalul numit pentru a rula acest cod. Deoarece intervalul numit este cuprins pentru registrul de lucru, îl puteți accesa din orice foaie folosind acest cod.
Dacă aveți un tabel cu numele Table1, puteți utiliza codul de mai jos pentru al copia în Sheet2.
Sub CopyTable () Range ("Table1 [#All]"). Copiați foile de lucru ("Sheet2"). Range ("A1") End Sub
De asemenea, puteți copia un interval într-un alt registru de lucru.
În exemplul următor, copiez tabelul Excel (Tabelul 1), în registrul de lucru Book2.
Sub CopyCurrentRegion () Range ("Table1 [#All]"). Copy Copy Workbooks ("Book2.xlsx"). Worksheets ("Sheet1"). Range ("A1") End Sub
Acest cod ar funcționa numai dacă registrul de lucru este deja deschis.
Copierea unui interval de dimensiuni variabile
O modalitate de a copia intervale de dimensiuni variabile este de a le converti în intervale denumite sau Tabel Excel și utilizați codurile așa cum se arată în secțiunea anterioară.
Dar dacă nu puteți face acest lucru, puteți utiliza CurrentRegion sau proprietatea End a obiectului interval.
Codul de mai jos va copia regiunea curentă în foaia activă și o va lipi în Foaia2.
Sub CopyCurrentRegion () Range ("A1"). CurrentRegion.Copy Foi de lucru ("Sheet2"). Range ("A1") End Sub
Dacă doriți să copiați prima coloană a setului de date până la ultima celulă completată și să o inserați în Sheet2, puteți utiliza codul de mai jos:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Copy Files ("Sheet2"). Range ("A1") End Sub
Dacă doriți să copiați rândurile, precum și coloanele, puteți utiliza codul de mai jos:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown). End (xlToRight)). Copy Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Rețineți că toate aceste coduri nu selectează celulele în timpul executării. În general, veți găsi doar o mână de cazuri în care trebuie să selectați de fapt o celulă / o gamă înainte de a lucra la ea.
Atribuirea gamelor variabilelor de obiect
Până în prezent, am folosit adresa completă a celulelor (cum ar fi Registrele de lucru („Book2.xlsx”). Foi de lucru („Sheet1”). Range („A1”)).
Pentru a vă face codul mai ușor de gestionat, puteți atribui aceste intervale variabilelor obiect și apoi utilizați aceste variabile.
De exemplu, în codul de mai jos, am atribuit intervalul sursă și destinație variabilelor obiect și apoi am folosit aceste variabile pentru a copia date dintr-un interval în celălalt.
Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange Sfârșitul Sub
Începem prin declararea variabilelor ca obiecte Range. Apoi atribuim intervalul acestor variabile folosind instrucțiunea Set. Odată ce intervalul a fost atribuit variabilei, puteți utiliza pur și simplu variabila.
Introduceți date în următoarea celulă goală (folosind caseta de intrare)
Puteți utiliza casetele de intrare pentru a permite utilizatorului să introducă datele.
De exemplu, să presupunem că aveți setul de date de mai jos și doriți să introduceți înregistrarea de vânzări, puteți utiliza caseta de intrare în VBA. Folosind un cod, ne putem asigura că umple datele din următorul rând gol.
Sub EnterData () Dim RefRange As Range Set RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Set Suma = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Categorie produs") Quantity.Value = InputBox ("Quantity") Amount.Value = InputBox („Sumă”) Sfârșit Sub
Codul de mai sus folosește caseta de intrare VBA pentru a obține intrările de la utilizator și apoi introduce intrările în celulele specificate.
Rețineți că nu am folosit referințe de celule exacte. În schimb, am folosit proprietatea End și Offset pentru a găsi ultima celulă goală și pentru a completa datele din ea.
Acest cod este departe de a fi utilizat. De exemplu, dacă introduceți un șir de text atunci când caseta de introducere solicită cantitatea sau suma, veți observa că Excel o permite. Puteți utiliza o condiție If pentru a verifica dacă valoarea este numerică sau nu și apoi permiteți-o în consecință.
Buclare prin celule / game
Până acum am putut vedea cum să selectăm, să copiem și să introducem datele în celule și intervale.
În această secțiune, vom vedea cum să parcurgeți un set de celule / rânduri / coloane dintr-un interval. Acest lucru ar putea fi util atunci când doriți să analizați fiecare celulă și să efectuați o acțiune bazată pe aceasta.
De exemplu, dacă doriți să evidențiați fiecare al treilea rând din selecție, atunci trebuie să parcurgeți și să verificați numărul rândului. În mod similar, dacă doriți să evidențiați toate celulele negative schimbând culoarea fontului în roșu, trebuie să parcurgeți și să analizați valoarea fiecărei celule.
Iată codul care va parcurge rândurile din celulele selectate și va evidenția rândurile alternative.
Sub HighlightAlternateRows () Dim Myrange as Range Dim Myrow as Range Set Myrange = Selecție pentru fiecare Myrow din Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Codul de mai sus folosește funcția MOD pentru a verifica numărul rândului din selecție. Dacă numărul rândului este par, acesta este evidențiat în culoare cyan.
Iată un alt exemplu în care codul trece prin fiecare celulă și evidențiază celulele care au o valoare negativă în ea.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Selecție pentru fiecare Mycell din Myrange Dacă Mycell <0 Apoi Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Rețineți că puteți face același lucru folosind Formatarea condiționată (care este dinamică și o modalitate mai bună de a face acest lucru). Acest exemplu este doar pentru a vă arăta cum funcționează buclarea cu celulele și intervalele din VBA.
Unde să puneți codul VBA
Vă întrebați unde merge codul VBA în registrul dvs. de lucru Excel?
Excel are un backend VBA numit editor VBA. Trebuie să copiați și să lipiți codul în fereastra de cod a modulului VB Editor.
Iată pașii pentru a face acest lucru:
- Accesați fila Dezvoltator.
- Faceți clic pe opțiunea Visual Basic. Aceasta va deschide editorul VB în backend.
- În panoul Explorator de proiecte din Editorul VB, faceți clic dreapta pe orice obiect pentru registrul de lucru în care doriți să inserați codul.Dacă nu vedeți Project Explorer, accesați fila View și faceți clic pe Project Explorer.
- Accesați Insert și faceți clic pe Module. Aceasta va insera un obiect modul pentru registrul dvs. de lucru.
- Copiați și lipiți codul în fereastra modulului.
