Gamele și celulele VBA Excel

Gamele și celulele în VBA

Foi de calcul Excel stochează date în celule. Celulele sunt aranjate în rânduri și coloane. Fiecare celulă poate fi identificată prin punctul de intersecție al rândului și coloanei sale (Ex. B3 sau R3C2).

Un interval Excel se referă la una sau mai multe celule (ex. A3: B4)

Adresa celulei

Notare A1

În notația A1, o celulă este menționată prin litera coloanei sale (de la A la XFD) urmată de numărul rândului său (de la 1 la 1.048.576).

În VBA vă puteți referi la orice celulă folosind Obiectul Range.

123456789

'Consultați celula B4 din foaia activă curentăGama MsgBox („B4”)„Consultați celula B4 de pe foaia denumită„ Date ”Fișe de lucru MsgBox („Date”). Interval („B4”)„Consultați celula B4 de pe foaia denumită„ Date ”într-un alt registru de lucru DESCHIS„numit„ Datele mele ”Cărți de lucru MsgBox („Datele mele”). Foi de lucru („Date”). Interval („B4”)

Notare R1C1

În notația R1C1, o celulă este menționată de R, urmată de numărul rândului, apoi litera „C”, urmată de numărul coloanei. de exemplu, B4 în notația R1C1 va fi menționată de R4C2. În VBA utilizați Obiectul celulelor pentru a utiliza notația R1C1:

12	'Consultați celula R [6] C [4], adică D6Celule (6, 4) = "D6"

Gama de celule

Notare A1

Pentru a vă referi la mai multe celule, utilizați un „:” între adresa celulei de pornire și ultima adresă a celulei. Următoarele se vor referi la toate celulele de la A1 la D10:

1	Gama ("A1: D10")

Notare R1C1

Pentru a vă referi la mai multe celule, utilizați un „,” între adresa celulei de pornire și ultima adresă a celulei. Următoarele se vor referi la toate celulele de la A1 la D10:

1	Interval (celule (1, 1), celule (10, 4))

Scrierea către celule

Pentru a scrie valori într-o celulă sau într-un grup contiguu de celule, se referă simplu la interval, se pune un semn = și apoi se scrie valoarea de stocat:

12345678910

„Stocați F5 în celulă cu adresa F6Range ("F6") = "F6"„Stocați E6 în celulă cu adresa R [6] C [5], adică E6Celule (6, 5) = "E6"„Depozitați A1: D10 în intervalul A1: D10Range ("A1: D10") = "A1: D10"'sauGama (celule (1, 1), celule (10, 4)) = "A1: D10"

Citind din celule

Pentru a citi valorile din celule, se referă simplu la variabila pentru a stoca valorile, se pune un semn = și apoi se referă la intervalul de citit:

1234567891011

Dim val1Dim val2„Citiți din celula F6val1 = Interval ("F6")„Citiți din celula E6val2 = Celule (6, 5)MsgBox val1Msgbox val2

Notă: pentru a stoca valori dintr-o gamă de celule, trebuie să utilizați o matrice în loc de o variabilă simplă.

Celule nelimitate

Pentru a vă referi la celulele necontigue utilizați o virgulă între adresele celulei:

123456

„Stocați 10 în celulele A1, A3 și A5Gama ("A1, A3, A5") = 10„Stocați 10 în celulele A1: A3 și D1: D3)Interval („A1: A3, D1: D3”) = 10

Intersecția celulelor

Pentru a vă referi la celule necontigue utilizați un spațiu între adresele celulei:

123	„Stocați” Col D ”în D1: D10„care este comun între A1: D10 și D1: F10Gama ("A1: D10 D1: G10") = "Col D"

Decalat de la o celulă sau un interval

Folosind funcția Offset, puteți muta referința dintr-un anumit interval (celulă sau grup de celule) cu numărul_de_rânduri și numărul_de_coloane specificate.

Sintaxa Offset

Range.Offset (number_of_rows, number_of_columns)

Decalat de la o celulă

12345678910111213141516

'OFFSET de la o celulă A1- Consultați celula în sine'Mutați 0 rânduri și 0 coloaneGama ("A1"). Offset (0, 0) = "A1"'Mutați 1 rând și 0 coloaneGama ("A1"). Offset (1, 0) = "A2"'Mutați 0 rânduri și 1 coloaneGama ("A1"). Offset (0, 1) = "B1"'Mutați 1 rânduri și 1 coloaneGama ("A1"). Offset (1, 1) = "B2"'Mutați 10 rânduri și 5 coloaneGama ("A1"). Offset (10, 5) = "F11"

Decalat de la o gamă

123	„Mutați referința la gama A1: D4 cu 4 rânduri și 4 coloane„Noua referință este E5: H8Gama ("A1: D4"). Offset (4,4) = "E5: H8"

Setarea referinței la o gamă

Pentru a atribui un interval unei variabile de interval: declarați o variabilă de tip Range, apoi utilizați comanda Set pentru a o seta la un interval. Vă rugăm să rețineți că trebuie să utilizați comanda SET deoarece RANGE este un obiect:

12345678

„Declarați o variabilă RangeDim myRange ca Range'Setați variabila la intervalul A1: D4Setați gama mea = Range („A1: D4”)'Tipărește $ A $ 1: $ D $ 4MsgBox myRange.Address

Redimensionați o gamă

Metoda de redimensionare a obiectului Range modifică dimensiunea intervalului de referință:

1234567

Dim myRange As Range'Gama de redimensionareSetați gama mea = Range („A1: F4”)'Tipărește $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5). Adresă

Celula din stânga sus a gamei Redimensionate este aceeași cu celula din stânga sus a gamei originale

Redimensionați sintaxa

Range.Resize (number_of_rows, number_of_columns)

OFFSET vs Redimensionare

Decalajul nu modifică dimensiunile intervalului, ci îl deplasează cu numărul specificat de rânduri și coloane. Redimensionare nu modifică poziția intervalului original, ci modifică dimensiunile la numărul specificat de rânduri și coloane.

Toate celulele din coli

Obiectul Celule se referă la toate celulele din foaie (1048576 rânduri și 16384 coloane).

12	„Ștergeți toate celulele din foile de lucruCelule. Clar

UsedRange

Proprietatea UsedRange vă oferă intervalul dreptunghiular de la celula utilizată în stânga sus la celula utilizată în partea dreaptă-jos a foii active.

1234567

Dim ws Ca foaie de lucruSetați ws = ActiveSheet'$ B $ 2: $ L $ 14 dacă L2 este prima celulă cu orice valoare'și L14 este ultima celulă cu orice valoare pe' foaie activăDebug.Print ws.UsedRange.Address

Regiunea curentă

Proprietatea CurrentRegion vă oferă intervalul dreptunghiular contigu de la celula sus-stângă la celula dreapta-jos utilizată care conține celula / intervalul de referință.

1234567891011

Dim myRange As RangeSetați gama mea = Range („D4: F6”)'Tipărește $ B $ 2: $ L $ 14„Dacă există o cale umplută de la D4: F16 la B2 ȘI L14Debug.Print MyRange.CurrentRegion.Address„Vă puteți referi și la o singură celulă de pornireSet myRange = Range ("D4") 'Prints $ B $ 2: $ L $ 14

Proprietăți de gamă

Puteți obține adresa, numărul de rând / coloană al unei celule și numărul de rânduri / coloane dintr-un interval, după cum se arată mai jos:

123456789101112131415161718192021

Dim myRange As RangeSetați gama mea = Range („A1: F10”)'Tipărește $ A $ 1: $ F $ 10Debug.Print MyRange.AddressSet myRange = Range ("F10")'Tipărește 10 pentru rândul 10Debug.Print myRange.Row'Tipărește 6 pentru coloana FDebug.Print myRange.ColumnSetați gama mea = Range („E1: F5”)'Tipărește 5 pentru numărul de rânduri din raza de acțiuneDebug.Print myRange.Rows.Count'Tipărește 2 pentru numărul de coloane din intervalDebug.Print myRange.Columns.Count

Ultima celulă din foaie

Poți să folosești Rânduri.Cont și Coloane.Cont proprietăți cu Celulele obiectează pentru a obține ultima celulă de pe foaie:

1234567891011

'Imprimați ultimul număr de rând'Tipărește 1048576Debug.Print "Rows in the sheet:" & Rows.Count'Imprimați ultimul număr de coloană'Tipărește 16384Debug.Print "Coloane în foaia:" & Coloane'Imprimați adresa ultimei celule'Tipărește XFD $ 1048576 $Debug.Print "Adresa ultimei celule din foaie:" & Celule (Rows.Count, Columns.Count)

Ultimul număr de rând utilizat într-o coloană

Proprietatea END vă duce la ultima celulă din interval și End (xlUp) vă duce la prima celulă utilizată din acea celulă.

123	Dim lastRow As LonglastRow = Celule (Rows.Count, "A"). End (xlUp) .Row

Numărul ultimei coloane utilizate într-un rând

123	Dim lastCol As LonglastCol = Celule (1, Columns.Count) .End (xlToLeft) .Column

Proprietatea END vă duce la ultima celulă din interval și End (xlToLeft) vă duce la stânga la prima celulă utilizată din acea celulă.

De asemenea, puteți utiliza proprietățile xlDown și xlToRight pentru a naviga la prima celulă utilizată în partea de jos sau dreapta a celulei curente.

Proprietățile celulei

Proprietăți comune

Aici este codul pentru a afișa proprietățile celulei utilizate în mod obișnuit

12345678910111213141516171819202122

Dim celula ca intervalSetați celula = Interval ("A1")celula.ActivațiDebug.Print cell.Address'Imprimați $ A $ 1Debug.Print cell.Value'Tipărește 456' AbordareDebug.Print cell.Formula„Tipăriri = SUM (C2: C3)' CometariuDebug.Print cell.Comment.Text- StilDebug.Print cell.Style„Formatul celuleiDebug.Print cell.DisplayFormat.NumberFormat

Font de celulă

Obiectul Cell.Font conține proprietăți ale fontului Cell:

1234567891011121314151617181920

Dim celula ca intervalSetați celula = Interval ("A1")„Regular, Italic, Bold și Bold Italiccell.Font.FontStyle = "Italic Bold"' La fel cacell.Font.Bold = Adevăratcell.Font.Italic = Adevărat„Setați fontul la Couriercell.Font.FontStyle = "Curier"„Setați culoarea fontuluicell.Font.Color = vbBlue'saucell.Font.Color = RGB (255, 0, 0)„Setați dimensiunea fontuluicell.Font.Size = 20

Copiaza si lipeste

Lipiți toate

Gamele / celulele pot fi copiate și lipite dintr-o locație în alta. Următorul cod copiază toate proprietățile intervalului sursă în intervalul destinație (echivalent cu CTRL-C și CTRL-V)

1234567

„Copie simplăGama („A1: D20”). CopiațiFoi de lucru („Sheet2”). Range („B10”). Lipiți'sau„Copiați din foaia curentă pe foaia denumită„ foaia2 ”Range ("A1: D20"). Copiați destinația: = foi de lucru ("Sheet2"). Range ("B10")

Lipiți special

Proprietățile selectate ale intervalului sursă pot fi copiate la destinație utilizând opțiunea PASTESPECIAL:

123	'Lipiți intervalul numai ca valoriGama („A1: D20”). CopiațiFoi de lucru ("Sheet2"). Range ("B10"). PasteSpecial Paste: = xlPasteValues

Iată opțiunile posibile pentru opțiunea Lipire:

12345678910111213

„Lipiți tipuri specialexlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats

Conținut AutoFit

Dimensiunea rândurilor și coloanelor poate fi modificată pentru a se potrivi conținutului folosind codul de mai jos:

12345

„Schimbați dimensiunea rândurilor de la 1 la 5 pentru a se potrivi conținutuluiRânduri („1: 5”). AutoFit'Schimbați dimensiunea coloanelor A în B pentru a se potrivi conținutuluiColoane („A: B”). AutoFit

Mai multe exemple de gamă

Este recomandat să utilizați Macro Recorder în timp ce efectuați acțiunea necesară prin GUI. Vă va ajuta să înțelegeți diferitele opțiuni disponibile și cum să le utilizați.

Pentru fiecare

Este mai ușor să parcurgeți o gamă folosind Pentru fiecare construiți așa cum se arată mai jos:

123	Pentru fiecare celulă din interval ("A1: B100")- Fă ceva cu celulaUrmătoarea celulă

La fiecare iterație a buclei, o celulă din interval este atribuită variabilei c și instrucțiunile din bucla For sunt executate pentru acea celulă. Bucla iese când toate celulele sunt procesate.

Fel

Sortarea este o metodă a obiectului Range. Puteți sorta o gamă specificând opțiuni pentru sortare la Range.Sort. Codul de mai jos va sorta coloanele A: C pe baza cheii din celula C2. Ordinea de sortare poate fi xlAscending sau xlDescending. Header: = xlDa trebuie utilizat dacă primul rând este rândul header.

12	Coloane ("A: C"). Tasta de sortare1: = Range ("C2"), _order1: = xlAscending, Header: = xlDa

Găsi

Find este, de asemenea, o metodă de Range Object. Găsește prima celulă cu conținut care corespunde criteriilor de căutare și returnează celula ca obiect Range. Se întoarce Nimic dacă nu există meci.

Utilizare Găsește următorul metoda (sau FindPrevious) pentru a găsi următoarea apariție (anterioară).

Codul următor va schimba fontul la „Arial Black” pentru toate celulele din interval care încep cu „John”:

12345

Pentru fiecare c In Range ("A1: A100")Dacă c Ca „Ioan *” Atuncic.Font.Name = "Arial Black"End IfUrmătorul c

Codul următor va înlocui toate aparițiile „To Test” la „Passed” în intervalul specificat:

12345678910

Cu Range („a1: a500”)Setați c = .Find („To Test”, LookIn: = xlValues)Dacă nu c nu este nimic atuncifirstaddress = c.AddressDoc.Value = "Trecut"Setați c = .FindNext (c)Bucla În timp ce nu c nu este nimic Și c. Adresa întâi adresăEnd IfSe termina cu

Este important să rețineți că trebuie să specificați un interval pentru a utiliza FindNext. De asemenea, trebuie să furnizați o condiție de oprire, altfel bucla se va executa pentru totdeauna. În mod normal, adresa primei celule care este găsită este stocată într-o variabilă și bucla este oprită când ajungeți din nou la acea celulă. De asemenea, trebuie să verificați situația în care nu se găsește nimic care să oprească bucla.

Adresa intervalului

Utilizați Range.Address pentru a obține adresa în stil A1

123	MsgBox Range („A1: D10”). Adresă'sauDebug.Print Range („A1: D10”). Adresă

Utilizați xlReferenceStyle (implicit este xlA1) pentru a obține adrese în stil R1C1

123	MsgBox Range („A1: D10”). Adresă (ReferenceStyle: = xlR1C1)'sauDebug.Print Range ("A1: D10"). Adresă (ReferenceStyle: = xlR1C1)

Acest lucru este util atunci când vă ocupați de intervale stocate în variabile și doriți să procesați numai pentru anumite adrese.

Range to Array

Este mai rapid și mai ușor să transferați o gamă într-o matrice și apoi să procesați valorile. Ar trebui să declarați matricea ca Variantă pentru a evita calcularea dimensiunii necesare pentru a popula intervalul din matrice. Dimensiunile matricei sunt setate să corespundă numărului de valori din interval.

123456789

Dim DirArray ca variantă'Stocați valorile în interval în matriceDirArray = Range ("a1: a5"). Valoare'Buclă pentru procesarea valorilorPentru fiecare c În DirArrayDepanare.Imprimare cUrmătorul

Array to Range

După procesare, puteți scrie matricea înapoi într-un interval. Pentru a scrie matricea din exemplul de mai sus într-o zonă, trebuie să specificați o zonă a cărei dimensiune se potrivește cu numărul de elemente din matrice.

Utilizați codul de mai jos pentru a scrie matricea în intervalul D1: D5:

123	Gama („D1: D5”). Valoare = DirArrayGama ("D1: H1"). Valoare = Application.Transpose (DirArray)

Vă rugăm să rețineți că trebuie să transpuneți matricea dacă o scrieți pe un rând.

Gama de sumă

12	SumOfRange = Application.WorksheetFunction.Sum (Range ("A1: A10"))Debug.Print SumOfRange

Puteți utiliza multe funcții disponibile în Excel în codul dvs. VBA specificând Application.WorkSheetFunction. înainte de Numele funcției ca în exemplul de mai sus.

Gama de numărare

1234567

„Numărați numărul de celule cu numere din intervalCountOfCells = Application.WorksheetFunction.Count (Range ("A1: A10"))Debug.Print CountOfCells„Numărați numărul de celule non-blank din intervalCountOfNonBlankCells = Application.WorksheetFunction.CountA (Range ("A1: A10"))Debug.Print CountOfNonBlankCells

Scris de: Vinamra Chandra