Macro-uri interesante vba excel gata făcute. Lucru eficient în MS Office. Creați o macrocomandă în Excel folosind Macro Recorder

Următorul exemple simple Macrocomenzile Excel ilustrează unele dintre caracteristicile și tehnicile descrise în tutorialul Excel VBA.

Macrocomandă Excel: Exemplul 1

Inițial această procedură Sub a fost dat ca exemplu de utilizare a comentariilor în codul VBA. Totuși, aici puteți vedea, de asemenea, cum sunt declarate variabilele, cum funcționează referințele de celule Excel și utilizarea unei bucle Pentru, operator condițional Dacăși afișarea unei ferestre de mesaj.

„Procedura Sub caută o celulă care conține șirul specificat „în intervalul de celule A1:A100 din foaia activă Sub Find_String(sFindText As String) Dim i As Integer „Un număr întreg de tip Integer, utilizat într-o buclă For Dim iRowNumber Ca Integer „Un număr întreg de tip Integer pentru stocarea rezultatului iRowNumber = 0 „Se uită prin celulele A1:A100 una câte una până când șirul este găsit sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then” Dacă se găsește o potrivire cu șirul specificat, salvați numărul linia curentă si plecam de la Pentru buclă iRowNumber = i Exit For End If Next i "Informăm utilizatorul într-o fereastră pop-up dacă a fost găsit rândul necesar "Dacă șir dat găsit, indicați în ce celulă a fost găsită potrivirea Dacă iRowNumber = 0 Apoi MsgBox „Rândul „ & sFindText & „ nu a fost găsit” Altfel MsgBox „Rândul „ & sFindText & „ a fost găsit în celula A” & iRowNumber End If End Sub

Macrocomandă Excel: Exemplul 2

Următoarea procedură Sub– exemplu de utilizare a unei bucle Face în timp ce. De asemenea, puteți vedea cum sunt declarate variabilele, lucrând cu referințe de celule Excel și utilizând o instrucțiune condiționată. Dacă.

„Procedura Sub scoate numere Fibonacci care nu depășesc 1000 Sub Fibonacci() Dim i As Integer „Un contor pentru a indica poziția unui element în secvența Dim iFib As Integer „Stochează valoarea curentă a secvenței Dim iFib_Next As Integer „Stochează următoarea valoare a secvenței Dim iStep As Integer „Stochează dimensiunea următorului increment „Inițializați variabilele i și iFib_Next i = 1 iFib_Next = 0 „Bucla Do While va fi executată până când valoarea „numărului actual Fibonacci depășește 1000 Do While iFib_Next< 1000 If i = 1 Then "Un caz special pentru primul element al secvenței iStep = 1 iFib = 0 Altfel „Salvați dimensiunea următorului increment înainte de a suprascrie „valoarea curentă a secvenței iStep = iFib iFib = iFib_Next End If „Ieșiți numărul actual Fibonacci în coloana A din foaia de lucru activă „în rândul cu index i Cells(i, 1).Value = iFib „Calculați următorul număr Fibonacci și creșteți indicele de poziție al elementului cu 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Macrocomandă Excel: Exemplul 3

Această procedură Sub scanează celulele unei coloane A foaie activă până când întâlnește o celulă goală. Valorile sunt scrise într-o matrice. Această macrocomandă simplă Excel arată cum să lucrați cu matrice dinamiceși, de asemenea, folosind o buclă Fă până la. ÎN în acest exemplu nu vom efectua nicio acțiune cu matricea, deși în practica reală de programare, după ce datele sunt scrise în matrice, astfel de acțiuni sunt de obicei efectuate asupra lor.

„Procedura Sub stochează valorile celulei coloanei A a foii active în tabloul Sub GetCellValues() Dim iRow As Integer „Stochează numărul rândului curent Dim dCellValues() As Double „O matrice pentru stocarea valorilor celulei ​​iRow = 1 ReDim dCellValues(1 la 10) „Do Loop Until iterează secvențial prin celulele coloanei A a foii active” și extrage valorile acestora într-o matrice până când se întâlnește o celulă goală. Do Until IsEmpty(Cells( iRow, 1)) „Verificați dacă matricea dCellValues ​​​​are o dimensiune suficientă „Dacă nu, măriți matricea de dimensiune cu 10 folosind ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Macrocomandă Excel: Exemplul 4

În acest exemplu, procedura Sub citește valori dintr-o coloană A fisa de lucru Foaia 2și o face cu ei operatii aritmetice. Rezultatele sunt introduse în celulele coloanei A pe foaia de lucru activă. Această macrocomandă demonstrează utilizarea obiectelor Excel. În special, contestația se realizează prin procedură Sub la obiect Coloane, și arată cum este accesat acest obiect prin intermediul obiectului Fisa de lucru. De asemenea, se arată că la accesarea unei celule sau a unui interval de celule de pe foaia activă, nu este necesar să specificați numele acestei foi atunci când scrieți linkul.

„Procedura Sub, folosind o buclă, citește valorile din coloana A a foii de lucru Sheet2, „efectuează operații aritmetice cu fiecare valoare și scrie rezultatul în „coloana A a foii de lucru active (Sheet1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Atribuiți variabilei Col coloana A a foii de lucru Sheet 2 Set Col = Sheets("Sheet2"). Columns("A") i = 1 "Folosind o buclă, citim valorile ​​din celulele coloanei Col până când „până când se întâlnește o celulă goală Do Until IsEmpty(Col.Cells(i))” „Efectuați operații aritmetice pe valoarea celulei curente dVal = Col.Cells(i).Value * 3 - 1" Următoarea comandă scrie rezultatul în coloana A a foii de lucru active „Nu este nevoie să indicați numele foii în link, deoarece aceasta este foaia activă. Cells(i, 1) = dVal i = i + 1 Loop End Sub

Macrocomandă Excel: Exemplul 5

Această macrocomandă arată un exemplu de cod VBA care monitorizează un eveniment Excel. Evenimentul la care este atașată macrocomanda are loc de fiecare dată când o celulă sau un interval de celule este selectată în foaia de lucru. În cazul nostru, atunci când selectați o celulă B1, pe ecran apare o fereastră de mesaj.

„Acest cod arată o casetă de mesaj dacă celula B1 este selectată în foaia de lucru curentă. Private Sub Worksheet_SelectionChange(ByVal Target As Range) „Verificați dacă celula B1 este selectată Dacă Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Apoi „Dacă este selectată celula B1, efectuați acțiunea necesară MsgBox „Ați selectat celula B1” End If End Sub

Macrocomandă Excel: Exemplul 6

Această procedură ilustrează utilizarea operatorilor La EroareȘi Relua pentru tratarea erorilor. Acest cod arată, de asemenea, un exemplu de deschidere și citire a datelor dintr-un fișier.

„Procedura Sub atribuie argumentelor Val1 și Val2 valorile celulelor A1 și B1” din registrul de lucru Data.xlsx aflat în folderul C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Registrul de lucru la eroare GoTo ErrorHandling " Deschidere registrul de lucru cu date Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Atribuiți variabilelor Val1 și Val2 valori din registrul de lucru dat Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets( „Sheet1”).Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: „Dacă fișierul nu este găsit, utilizatorului i se va cere să plaseze fișierul pe care îl caută” în folderul doritși după aceea continuați să executați macrocomanda MsgBox „Fișierul Data.xlsx nu a fost găsit!” & _ „Vă rugăm să adăugați registrul de lucru în folderul C:\Documents and Settings și faceți clic pe OK” Reluare End Sub

Manualul conține macrocomenzi pe următoarele subiecte:
Rularea unei macrocomenzi pentru a căuta o celulă.
Rulați o macrocomandă când deschideți un registru de lucru.
Rulați o macrocomandă când introduceți „2” în celulă.
Rulați o macrocomandă când apăsați Enter.
Adăugați propria filă „Suplimente” (Format celulă) la panou.
Lucrul cu fișiere (adică schimbul de date cu TXT, RTF, XLS etc.).
Verificarea prezenței unui fișier la calea specificată.
Căutați fișierul necesar.
Automatizarea ștergerii fișierelor.
Text liber în bara de stare.
Restabilirea barei de stare.
Linie târâtoare în bara de stare.

Schimbați rapid titlul ferestrei.
Schimbarea titlului ferestrei (ascunderea numelui fișierului).
Reveniți la titlul original.
Ce este deschis în acest moment.
Lucrul cu fișiere text.
Scrie și citește fisier text.
Procesarea mai multor fișiere text.
Determinarea sfârșitului unei linii într-un fișier text.
Copierea dintr-un fișier text în Excel.

Copiați conținutul într-un fișier text.
Exportați datele în txt.
Exportați datele în html.
Importați date care necesită mai mult de 256 de coloane.
Creare copii de rezervă dosare valoroase.
Numărarea de câte ori a fost deschis un fișier.
Ieșiți calea fișierului către celula activă.
Copierea conținutului Fișier RTFîn Excel.
Copierea datelor dintr-un registru de lucru închis.
Extragerea datelor dintr-un fișier închis.
Căutați un cuvânt în fișiere.
Creați un fișier text și introduceți text în fișier.
Creați un fișier text și introduceți text (sfârșitul detectării fișierului).
Creare Documente Word bazat tabele Excel.
Comenzi pentru crearea și ștergerea directoarelor.
Obțineți directorul curent.
Schimbați directorul.

Vizualizați toate fișierele dintr-un director.
Vizualizați toate fișierele dintr-un director.
Lucru zona Microsoft Excela.
Caiet de lucru.
Numărul de nume de caiete de lucru.
Protecția caietului de lucru.
Interzicerea tipăririi cărților.
Deschiderea unei cărți (sau fișiere text).
Deschideți cartea și adăugați text în celula A1.
Câte cărți sunt deschise?
Închiderea tuturor cărților.
Închiderea unui registru de lucru numai atunci când este îndeplinită o condiție.
Salvează registrul de lucru cu un nume care reprezintă data curentă.
Este salvat registrul de lucru?
Creați un registru de lucru cu o singură foaie.
Creați o carte.
Eliminarea numelor inutile.
Reproducerea rapidă a unui registru de lucru.
Sortarea foilor.
Găsirea valorii maxime pe toate foile registrului de lucru.
Fisa de lucru.
Verificarea dacă foaia de lucru este protejată.
Lista de foi sortate.
Crea frunză nouă.
Creați o foaie nouă.
Îndepărtarea foilor în funcție de dată.
Copierea unei foi într-o carte.
Copierea unei foi în carte noua(creată).
Mutarea unei foi într-o carte.
Mutați mai multe foi într-un nou registru de lucru.
Înlocuiți un fișier existent.
„Întoarcerea” cărții.
Introduceți un antet și un subsol cu ​​numele registrului de lucru, foaia și data curentă.
Foaia există?
Foaia există?
Afișează numărul de foi din registrul de lucru activ.
Afișează numărul de foi din registrul de lucru activ sub formă de hyperlinkuri.
Afișează numele foilor active unul câte unul.
Afișează numele și numerele foilor cărții curente.
Faceți foaia invizibilă.
Câte pagini sunt pe toate foile?
Celulă și interval (coloane și rânduri).
Copiați rândurile pe altă foaie.
Copiați coloanele pe altă foaie.
Numărează numărul de celule care conțin valorile specificate.
Numărează numărul de celule dintr-un interval care conține valorile specificate.
Contorizează numărul de celule vizibile dintr-un interval.
Determinarea numărului de celule dintr-un interval și a sumei valorilor acestora.
Numărarea numărului de celule.
Recalcularea automată a datelor din tabel atunci când valorile acestuia se schimbă.
Introducerea datelor în celule.
Introducerea datelor folosind formule.
Intrare de date seriale.
Introducerea datelor text în celule.
Afișează numele cărții, foaia și numărul de foi din celule.
Eliminarea liniilor goale.
Eliminarea liniilor goale.
Eliminarea liniilor goale.
Ștergerea unui rând după condiție.
Îndepărtarea rânduri ascunse.
Eliminați rândurile ascunse sau cu înălțimea zero.
Eliminarea duplicatelor folosind o mască.

Selectează un interval deasupra celulei curente.
Selectați o celulă și plasați un număr acolo.
Evidențierea valorilor negative.
Selectarea unui interval și utilizarea adreselor absolute.

Selectarea celulelor la intervale.
Selectarea mai multor intervale.
Mișcarea prin celule.
Găsește cea mai apropiată celulă goală dintr-o coloană.
Găsirea valorii maxime.
Căutați și înlocuiți după model.
Căutați o valoare și afișați rezultatul într-o fereastră separată.

Căutați cu evidențierea datelor găsite.
Căutați după condiție într-un interval.
Găsește ultima celulă negoală dintr-un interval.
Găsește ultima celulă care nu este goală dintr-o coloană.
Găsește ultima celulă care nu este goală dintr-un rând.
Căutare de celule de culoare albastrăîn intervalul.
Găsirea unei valori negative într-un interval și evidențierea acesteia în albastru.
Găsirea prezenței unei valori într-o coloană.
Găsirea potrivirilor într-un interval.
Căutați o celulă dintr-un interval.
Căutați o celulă dintr-un interval.
Găsirea unei valori aproximative într-un interval.
Găsește începutul și sfârșitul unui interval care conține date.
Găsirea începutului datelor.
Înlocuirea automată a valorilor.
Umplerea rapidă a unui interval (matrice).
Umplerea prin interval (matrice).
Completează intervalul specificat (matrice).
Umpleți intervalul (matrice).
Calculul sumei primelor valori ale intervalului.
Plasarea într-o celulă a unui ceas electronic.
"Alarma".
Designul vârfului și limite inferioare gamă.
Adresa celulei active.
Coordonatele celulei active.
Formula celulară activă.
Obținerea unei formule dintr-o celulă.
Tipul de date al celulei.
Ieșiți adresa de la sfârșitul intervalului.
Obținerea de informații despre intervalul selectat.
Luați cuvântul cu al 13-lea caracter din celulă.
Crearea unei liste editabile (tabel).
Verificați valoarea goală.
Intersecția celulelor.
Înmulțirea intervalului selectat cu.
Înmulțiți simultan toate datele dintr-un interval.
Împărțiți gama în.
Punerea la pătrat a fiecărei celule din interval.
Rezumă datele numai din celulele vizibile.
Suma celulelor cu valori numerice.
La însumare, cursorul se află în interiorul intervalului.

Dobânzi acumulate în funcție de sumă.
Dobânzi acumulate în funcție de sumă.
Exemplu rezumat de calcul al comisionului.
Mișcare de-a lungul intervalului.
Offset față de celula selectată.
Iterați prin celule în jos pe coloană.
Crearea unui interval de umplere.
Selectarea unui parametru de celulă.
Divizarea intervalului.
Îmbinați datele intervalului.
Îmbinați datele intervalului.
Aflați coloana sau rândul maxim.
Limitarea posibilelor valori ale intervalului.
Testarea vitezei intervalelor de citire și scriere.
Deschideți MsgBox când este selectată o celulă.
Ascunzând un rând.
Ascunderea mai multor rânduri.
Ascunderea unei coloane.
Ascunderea mai multor coloane.
Ascunderea unui rând după numele celulei.
Ascunderea mai multor rânduri după adresele celulelor.
Ascunderea unei coloane după numele celulei.
Ascunderea mai multor coloane după adresele celulelor.
Celula intermitent.
Lucrul cu note.
Afișează toate notele din foaia de lucru.
Funcția de extragere a comentariilor.
Lista de note pentru foile protejate.

Lista de note într-o listă separată.
Lista de note într-o listă separată.

Numărarea numărului de note.
Numărarea notelor.
Selectați celulele cu comentarii.
Afișează toate notele.
Schimbați culoarea notelor.
Adăugarea de note.
Adăugarea de note la un interval în funcție de condiție.
Mutați un comentariu într-o celulă și înapoi.

Transferarea valorilor dintr-o celulă într-un comentariu.
File personalizate pe panglică.
Adăugarea barei de instrumente.
Adăugarea unui buton la bara de instrumente.
Panoul cu un singur buton.
Panou cu două butoane.
Crearea unui panou în dreapta.

Creare meniu personalizat(Opțiunea 1).
Crearea unui meniu personalizat (opțiunea 2).
Crearea unui meniu personalizat (opțiunea 3).
Crearea unui meniu personalizat (opțiunea 4).
Crearea unui meniu personalizat (opțiunea 5).
Crearea unui meniu personalizat (opțiunea 6).
Crearea unei liste de elemente din meniul principal Excel.
Crearea unei liste de elemente din meniul contextual.
Afișați bara de instrumente într-o anumită condiție.
Ascunderea și afișarea barelor de instrumente.
Creați un sfat pentru butoanele mele.
Creați un meniu bazat pe datele din foaia de lucru.
Crearea unui meniu contextual.
Blocarea meniului contextual.
Adăugarea unei comenzi în meniul Instrumente.
Adăugarea unei comenzi în meniul Vizualizare.
Crearea unui panou de listă.
Un desen animat cu un asistent.
Adăugați text, titlu, buton și pictogramă la asistent.
Noi opțiuni de asistent.
Utilizarea asistentului pentru a selecta o culoare de umplere.
DIALOG WINDOWS.
Funcția INPUTBOX (prin introducerea valorii).
Previzualizare apel.
Configurarea introducerii datelor în caseta de dialog.

Deschide caseta de dialog (Deschide fișier).
Deschide caseta de dialog (Imprimare).
Alte casete de dialog.
Apelarea browserului din Excel.
Caseta de dialog pentru introducerea datelor.
Caseta de dialog pentru setările fontului.
Valori implicite.
Formatarea textului. Mese. BORNIERE ȘI UMPLEARE.
Afișează o listă de fonturi disponibile.
Selectarea tuturor numerelor din text.
Scrieți cu majuscule doar la începutul textului.
Numărarea numărului de repetări ale textului de căutare.
Selectarea unui element arbitrar din text.
Afișează textul înapoi.
Textul în limba engleză este cu majuscule.
Lansarea unui tabel de simboluri din Excel.
informații despre utilizator, computer, imprimantă etc.
Obțineți numele de utilizator.
Ieșire de rezoluție a monitorului.
Obțineți informații despre imprimanta pe care o utilizați.
Vizualizați informații despre unitățile computerului dvs.
FORME DE UTILIZATOR.
DIAGRAME.
Construirea unei diagrame folosind o macrocomandă.
Salvarea diagramei într-un fișier separat.
Creați și ștergeți o diagramă cu un clic pe un buton.
Afișați o listă de diagrame într-o fereastră separată.
Aplicarea unei palete de culori aleatorii.
Efectul de transparență a graficului.
Construiți o diagramă pe baza datelor din mai multe foi de lucru.
Creați legende pentru datele diagramei.
DIFERITE PROGRAME.
Program pentru compunerea cuvintelor încrucișate.
Creați o copertă pentru DVD.
Jocul „Câmp minat”.
Jocul „Ghicește animalul”.
Calcul bazat pe celule de o anumită culoare.
ALTE FUNCȚII ȘI MACRO.
Apelarea tastelor funcționale.
Calculul mediei aritmetice.
Transformarea numerelor în „bani”.
Căutați cea mai apropiată zi de luni.
Numărarea numărului de ani întregi.
Calculul mediei ponderate.
Convertirea numărului lunii în numele său.
Folosind link-uri relative.
Conversia unui tabel Excel în format HTML.
Generator de numere aleatorii.
Numere aleatorii - bazate pe interval.
Aplicarea unei funcții fără a o introduce într-o celulă.
Numărarea obiectelor numite.
Activarea unui filtru automat folosind o macrocomandă.
Crearea unei linii târâtoare.
Crearea unei imagini de rulare.
Autoforme rotative.
Apelarea tabelului de culori.
Crearea unui calculator.
Declinarea numelui, prenumelui și patronimului.
DATA SI ORA.
Data și ora de ieșire.
Data și ora de ieșire.
Obținerea datei sistemului.
Se preia data și orele.
Funcția DateFull Versiunea de MS Office utilizată nu este specificată.

VBA este considerat limbajul de scripting standard pentru aplicațiile Microsoft și este acum inclus în toate Aplicații de birouși chiar aplicații de la alte companii. Prin urmare, odată ce stăpâniți VBA pentru Excel, puteți trece imediat la crearea de macrocomenzi pentru alte software-uri. produse Microsoft. Mai mult, vei putea crea cu drepturi depline produse software, folosind simultan funcțiile unei varietăți de aplicații.

Cum să activați macrocomenzi în Excel

În mod implicit, fila responsabilă cu gestionarea și navigarea macrocomenzilor în Excel este ascunsă. A activa această opțiune accesați fila Fişier la grup Opțiuni.În caseta de dialog care apare Opțiuniexcela accesați fila Personalizare panglică,în caseta combinată din dreapta, plasați un marcator vizavi de filă Dezvoltator. Aceste acțiuni sunt relevante pentru versiuni Excel 2010 și mai vechi.

Apare pe bandă inserție nouă Dezvoltator cu controale de automatizare Excel.

Scrierea macrocomenzi în Excel

În fila Dezvoltator in grup Cod, faceți clic pe butonul Înregistrați o macrocomandă. Va apărea o casetă de dialog Înregistrați o macrocomandă, care solicită unele informații despre viitorul cod care se scrie. Dacă este prima dată când creați o macrocomandă, puteți pur și simplu să faceți clic pe butonul BINE. CU în acest moment Excel va înregistra fiecare acțiune a utilizatorului într-un modul VBA, fie că este vorba de introducerea datelor, formatarea sau crearea de diagrame. Pentru a opri înregistrarea unei macrocomenzi, faceți clic pe butonul Opriți înregistrarea care se află în același grup Cod.

De asemenea, puteți profita de o opțiune alternativă pentru înregistrarea macrocomenzi utilizând butonul Înregistrați o macrocomandă, care se află în colțul din stânga jos al registrului de lucru Excel (în dreapta stării Gata).

Acum puteți vizualiza o listă cu toate macrocomenzile create făcând clic pe butonul Macro, situat în grup Cod.În caseta de dialog care apare, puteți da nume mai descriptive codurilor sau puteți seta comenzi rapide de la tastatură care ar rula o anumită macrocomandă. Opțiune alternativă a lansa această fereastră înseamnă a apăsa Tastele Alt+ F8.

Editarea macrocomenzilor

Felicitări! Ai scris prima ta macrocomandă. Ar fi logic să verificăm acum ce cod ne-a generat Excel. Codul generat este scris limbaj VBA (Visual Basic pentru aplicații). Trebuie să-l deschizi ca să-l vezi. EditorVB(VBE), care se lansează apăsând Alt + F11 sau butonul VizualDe bază pe filă Dezvoltator.

Pentru a evita confuzia în editor, puteți lucra cu o singură filă într-un registru de lucru, foaie sau modul. Așa arată editorul în viața reală.

ofer mai departe în această etapă Aflați mai multe despre diferitele ferestre și meniuri ale editorului VBA. Acest lucru vă va ajuta să economisiți mult timp în viitor.

Pentru a vedea codul, dați clic pe fir Moduleîn fereastra proiectelor și faceți dublu clic pe ramura care apare Modul1 . Editorul va deschide o fereastră cu codul, așa cum se arată în imagine.

Aici puteți edita codul generat care a fost scris în timp ce lucrați în Excel. De exemplu, trebuie să completați o anumită coloană cu valori de la 1 la 10. Aveți deja primii trei pași, care introduc valorile 1, 2 și 3 în primele trei celule ale coloanei A. Trebuie să parcurgeți restul de șapte pași.

Dacă te uiți la codul de mai sus, vei vedea că macro-ul într-un anumit fel structurat. Aplicația mută mai întâi cursorul pe celulă folosind comanda Range(„A1”). Selectați, apoi editează conținutul utilizând ActiveCell.FormulaR1C1 = „1”. Deci pentru pașii rămași putem repeta acești pași, schimbând adresa celulei și valoarea pe care doriți să o scrieți în celula respectivă. De exemplu, pentru a seta celula A4 la 4, ați scrie:

Și repetați pași similari pentru valorile rămase.

După ce ați terminat de editat, salvați cartea. Puteți rula macrocomandă apăsând butonul F5 sau revenind la lucru Caietul de lucru Excel, accesați fila Dezvoltator la grup Cod -> Macro-uriși selectați din listă macrocomanda care vă interesează.

Luați câteva minute pentru a studia cu atenție codul generat de Excel. Dacă sunteți începător, investind câteva minute în învățarea codului va aduce rezultate uimitoare în cunoașterea obiectelor VBA mai târziu. Vă rugăm să rețineți că exemplul pe care l-am discutat este doar o ilustrație. Există mai rapide și moduri eficiente obținând rezultate similare, despre care vom discuta mai târziu.

Creșteți viteza de execuție a macrocomenzii Excel

Până acum, bine. Să ne uităm la câteva trucuri care vor ajuta la accelerarea execuției macro. Să luăm ca exemplu fragmentul de cod de mai sus. Calculatoare moderne va lucra la codul în cauză atât de repede încât nici măcar nu îl veți observa. Dar dacă trebuie să efectuați operația de 50.000 de ori. Acest lucru va dura ceva timp. Dacă macrocomanda pe care o scrieți are o lungime de sute de linii, puteți accelera execuția codului prin tăierea părții din procese care nu este utilizată în timpul execuției macrocomenzii.

Folosind comanda Application.ScreenUpdating

Primul truc este să evitați actualizarea ecranului în timp ce macro-ul rulează. Acest lucru va permite Excel să salveze putere de calcul computer și actualizați ecranul cu cele mai recente valori numai după ce tot codul a fost executat. Pentru a face acest lucru, trebuie să adăugați o comandă pentru a dezactiva reîmprospătarea ecranului la începutul macrocomenzii și o comandă pentru a activa reîmprospătarea ecranului la sfârșitul macrocomenzii.

Comanda Application.ScreenUpdating îi spune Excel să nu mai afișeze datele recalculate pe ecran și să returneze valorile terminate la sfârșitul execuției codului.

Folosind comanda Aplicație. Calcul

Al doilea truc este dezactivarea calculelor automate. Lasă-mă să explic. De fiecare dată când un utilizator sau un proces actualizează o celulă, Excel încearcă să recalculeze toate celulele care depind de aceasta. Deci, să presupunem că dacă celula pe care macro-ul încearcă să o actualizeze afectează alte 10.000 de celule, Excel va încerca să le recalculeze pe toate înainte ca codul să se termine de execuție. În consecință, dacă există întreaga linie influențând celulele, recalcularea poate încetini semnificativ execuția codului. Pentru a preveni acest lucru, puteți instala comanda Aplicație. Calcul la începutul codului, care va comuta recalcularea formulelor la mod manualși apoi returnați calculul automat la sfârșitul macro-ului.

Fiți atenți, nu uitați să comutați din nou această opțiune la mod auto la sfârșitul macro-ului. În caz contrar, va trebui să faceți acest lucru în Excel, făcând clic pe filă Formule la grup Calculși alegeți Opțiuni de calcul –> Automat.

Evitarea selectării celulelor și intervalelor

În modul înregistrare automată macrocomenzi, este posibil să observați că Excel utilizează foarte des comanda de selecție a celulelor, de exemplu, Range("A1").Select. În exemplul nostru, am folosit această comandăîn mod repetat pentru a selecta o celulă și a modifica valoarea acesteia. Puteți evita acest lucru prin simpla specificare a adresei celulei și oferindu-i valoarea necesară (Macro-ul a înregistrat mișcarea cursorului de la o celulă la alta, prin urmare inserând acești pași. Cu toate acestea, nu sunt necesari). Deci, un cod mai eficient ar arăta așa.

ÎN în acest caz, pur și simplu am făcut referire la celulă și i-am dat valoarea necesară fără a o selecta deloc. Această metodă este mai rapidă decât cea anterioară.

Exemple de macrocomandă Excel

Mai jos sunt câteva exemple de coduri VBA care vă vor ajuta să automatizați cele mai comune sarcini.

Dacă nu ești puternic în programare, dar în același timp, trebuie să implementezi uneleo sarcină care depășește specificații standardși comenzile MS Excel, puteți căuta o soluție pe Internet. Soluția va veni cel mai probabil sub formă de cod VBA pe care trebuie să-l copiați și să-l inserați în registrul de lucru, apoi faceți cumva acest cod să funcționeze pentru dvs., în acest articol vă voi spune cum să faceți acest lucru.

Să ne uităm la două exemple:

1. Găsiți și utilizați comanda

De exemplu, avem nevoie de o comandă care să insereze numărul „1” în celulele selectate. Lansăm motorul de căutare, introducem expresia de căutare, obținem rezultatele, începem să navigăm, găsim codul cam așa:

Sub Insertion1() Dim q Ca obiect la eroare Reluați următorul set q = Celule pentru fiecare q În selecție q = 1 Următorul q End Sub

Subliniem acest cod(fără numerotarea liniilor, începând cu cuvântul Sub) și apăsați Ctrl+C. Accesați registrul de lucru MS Excel și apăsați comanda rapidă de la tastatură Alt+F11, se va deschide o fereastră de editor VBA:

În fereastra din stânga „Proiect - Proiect VBA” selectăm (clic cu mouse-ul) registrul nostru de lucru în care trebuie să inserăm macro-ul, de exemplu, „VBAProject (Book2)”:

În elementul de meniu „Inserare”, selectați „Modul”:

În fereastra din stânga „Proiect - Proiect VBA” ar trebui să vedeți dosar nou„Module” și în el un nou obiect „Module1”:

Mutați cursorul în câmpul de introducere „Mare” din dreapta și faceți clic Ctrl+V, macrocomanda copiată este lipită în modul:

Macro-urile în MS Excel pot fi inserate în următoarele locuri:

• În Modul, acestea introduc de obicei codul macro-urilor, care va fi lansat atunci când utilizatorul apasă un buton (ca, de exemplu, în cazul nostru) sau codul funcțiilor (formule);
• În Foaia de lucru este de obicei inserat codul macrocomenzilor, a cărui lansare ar trebui să apară automat în funcție de acțiunile utilizatorului sau de modificările datelor din foaie (datele s-au schimbat, macro-ul este executat);
• Codul macro este de obicei inserat în registrul de lucru, care ar trebui să fie lansat automat în funcție de acțiunile efectuate pe registrul de lucru (fișier). De exemplu, o macrocomandă care rulează atunci când un registru de lucru este deschis sau închis sau când este salvat;
• Macro-urile pot face, de asemenea, parte dintr-un formular de utilizator.

De obicei, persoana care postează codul specifică unde ar trebui să fie inserat, într-un modul, foaie de lucru sau registru de lucru.

Pentru a insera codul într-o foaie de lucru, selectați foaia corespunzătoare în fereastra din stânga a editorului VBA, faceți dublu clic pe ea cu butonul stâng al mouse-ului, mutați cursorul în câmpul de intrare din dreapta și lipiți codul.

Pentru a insera un cod într-o carte, selectați „ThisBook”:

Sa exersam. Lipiți codul postat mai jos în „Sheet1”.

Private Sub Worksheet_Change(ByVal Target As Range) La eroare GoTo a If Target = 2 Then VBA.MsgBox("Cell " & Target.Address & " = 2") End If a: Exit Sub End Sub

Această macrocomandă afișează un mesaj de informare dacă introduceți „2” în orice celulă de foaie.

Reveniți la registrul de lucru, accesați „Sheet1” și introduceți numărul „2” în celula „A1” și faceți clic introduce, după care ar trebui să vedeți următorul mesaj:

Dacă vedeți acest mesaj, atunci ați făcut totul bine. Dacă nu, atunci ați introdus codul undeva în locul greșit, încercați din nou.

Când lipiți codul, trebuie să aveți grijă unde îl inserați. Acest lucru se poate face uitându-se la ceea ce este scris în titlul ferestrei editorului VBA:

2. Găsiți și utilizați funcția

Găsim pe Internet codul VBA al unei funcții care numără, de exemplu, numărul de cuvinte dintr-o celulă:

Funcție publică Numărul de cuvinte în celulă (celula ca interval) Dim q ca variantă Aplicație.Volatil q = VBA.Split(Application.WorksheetFunction.Trim(Cell.Value), " ") Numărul de cuvinte în celulă = UBound(q) + 1 End Funcţie

Copiați codul și apăsați combinația de taste Alt+F11, editorul VBA se va deschide:

Adăuga modul nou Lipiți codul copiat în carte și în acest modul:

Închideți editorul VBA și accesați registrul de lucru. Creați o foaie nouă (opțional) într-o celulă A1 introduceți textul „mama a spălat cadrul”. Mergem la celula în care dorim să obținem rezultatul (număr de cuvinte), în meniul „Formule”, faceți clic pe butonul „Inserare funcție”:

În fereastra „Inserare funcție” care se deschide, în câmpul „Categorie”, selectați „Definit de utilizator”

Pe listă funcții disponibile selectați „Număr de cuvinte în celulă”, faceți clic pe „OK”:

Introduceți argumentele necesare și faceți clic pe „OK”:

Obtinem rezultatul:

Important:

Dacă nu salvați registrul de lucru în care ați inserat macrocomandă ca „registru de lucru activat pentru macrocomandă”, toate modulele cu macrocomenzi vor fi șterse și va trebui să faceți din nou această muncă.

Dacă, când inserați o macrocomandă într-un modul, vedeți semne roșii în loc de ceva text " ???????? "

Aceasta înseamnă că aveți o problemă de codificare, care apare la copierea textului chirilic din unele browsere. Pentru a depăși această problemă, încercați să lipiți codul copiat în foaie albă MS Excel ca „Text in Codificare Unicode„. Pentru a face acest lucru, accesați registrul de lucru MS Excel, selectați sau creați o foaie goală, mergeți la celula „A1” și apăsați comenzile rapide de la tastatură Ctrl+Alt+V. Ar trebui să apară un meniu" Inserție specială”, selectați „Text Unicode” și faceți clic pe „OK”.

Codul trebuie lipit în foaia de lucru fără semnele de întrebare:

După aceea, copiați din nou codul lipit în foaie și lipiți-l în modul.

Dacă nu vedeți fereastra „Proiect - Proiect VBA” în editorul VBA, accesați fila de meniu „Vizualizare” și selectați „Explorator proiect” din listă sau apăsați comanda rapidă de la tastatură Ctrl+R:

Tabelele Excel sunt una dintre cele mai populare moduri de stocare și organizare a informațiilor. Dacă este implementat în macrocomenzi Excel, atunci capacitățile programului pot fi extinse de aproape două ori. De exemplu, macrocomenzile ajută la automatizare procese de rutinăși accelerează munca monotonă. Ele sunt adesea folosite de specialiștii care traduc rapoarte din 1C în Excel. Cu ajutorul codului încorporat, acestea pot fi ușor reduse la un singur șablon standard.

Ce sunt macrocomenzile Excel?

Macro-urile sunt folosite nu numai în tabele, ci în orice Microsoft Office. Sunt coduri create folosind Visual Basic for Applications. Dacă sunteți parțial familiarizat cu programarea web, atunci puteți face o analogie cu Javascript. Macrocomenzile Excel fac aproximativ același lucru pe care îl face Javascript cu datele HTML.

O mică parte din ceea ce poate face o macrocomandă în aplicațiile de birou:

Aliniați documentul în funcție de stil și formatați-l;
. efectuează operații cu date numerice și text;
. caută ajutor fișiere externe(text, baze de date și altele);
. creați noi documente de la zero;
. combinați mai multe acțiuni și efectuați-le automat.

Pentru a lucra cu un tabel, îl puteți crea singur sau îl puteți descărca opțiune gata făcută de pe internet. Macro-urile ar trebui să fie descărcate numai de pe site-uri de încredere, altfel puteți introduce un virus în Excel.

Cum pot fi periculoase macrocomenzile din Excel?

Deteriorează fișierele de pe computer;
. fura date confidențiale.

Virusul este introdus în timpul funcționării unuia dintre aplicații de birou- Word sau Excel. După ce încetați să lucrați, vor începe funcţionare autonomă virus și infectează întregul sistem.

Un alt mod în care funcționează o macrocomandă rău intenționată este ca intermediar pentru a introduce amenințări în sistem. În acest caz, este un fel de poartă de intrare pentru introducerea și generarea de software troian. Nu va mai fi controlat de o macrocomandă modificată, ci de sistem de operare, și, prin urmare, amenințarea devine mai mare. Nu este întotdeauna posibil să se vindece astfel de viruși; uneori trebuie să schimbați complet sistemul de operare Windows sau HDD PC (în funcție de sarcinile pe care hackerul le-a stabilit la scrierea codului).

Puteți alege orice metodă de protecție împotriva unor astfel de macrocomenzi. Cea mai populară opțiune este creșterea nivelului de securitate al Excel, dar în acest caz este posibil ca programul să nu mai suporte drepturile de autor, macro-uri utile. Nu ar trebui să aveți încredere în fișierele de la surse necunoscute, deoarece se pot infecta. Chiar și o macrocomandă primită de la computerul colegului tău poate fi periculoasă. Dacă un prieten ți-a dat un fișier cu care să lucrezi care acceptă utilizarea macrocomenzilor, atunci înainte de a permite sistemului să-l folosească, clarifică modul în care a primit această macrocomandă.

Cea mai bună opțiune este să înveți în mod independent arta de a crea macrocomenzi pentru nevoile tale. În acest caz, puteți garanta pe deplin calitatea și siguranța acestora.

Cum să înregistrați macrocomenzi în Excel

Cel mai simplu mod de a crea o macrocomandă este să programați direct în Excel. Pentru a face acest lucru, veți avea nevoie de un tabel cu datele introduse și puține cunoștințe pentru a permite înregistrarea unui astfel de cod în program.

Crearea începe cu meniul Vizualizare. În el, trebuie să selectați poziția „Macro-uri” și comanda „Înregistrare macrocomandă”. Apoi, trebuie să specificați un nume fără spații, de exemplu, „Format” (dacă codul va fi responsabil pentru modificarea formatului textului sau al celulelor).

După care toate acțiunile tale vor fi înregistrate automat și convertite în cod macro. Un buton cu un pătrat mic („Oprire”) va apărea în partea de jos a documentului, făcând clic pe care puteți opri înregistrarea. Astfel, puteți înregistra automat macrocomenzi în Excel, exemple ale cărora le puteți analiza în continuare în procesul de învățare.

Cum să activați macrocomenzi în Excel

Pentru a activa o macrocomandă înregistrată în alte documente, trebuie să o salvați după ștergerea tabelului. Formatul documentului trebuie setat ca XLTM (este cel care acceptă macrocomenzi).

Când accesați ulterior documentul pentru a activa macrocomenzi în Excel, mai întâi trebuie să le activați în fereastra pop-up linia de sus. Apoi, trebuie să importați datele care vor fi editate de macrocomandă dintr-un tabel CSV obișnuit. Odată ce importul este finalizat, trebuie să reveniți la meniul „Vizualizare”, selectați acolo „Macrocomenzi” și găsiți numele codului pe care l-ați creat în listă. În cele din urmă, faceți clic pe butonul „Run”. După care veți vedea cum programul, conform algoritmului specificat anterior, efectuează calcule și formatează documentul.

Cum pot vedea codul macro?

Toate macrocomenzile create în Excel sunt scrise automat, fără intervenția umană. Programul se compune singur codul programului, care poate fi deschis de la masă apelând folosind meniul.

Pentru a face acest lucru, trebuie să rulați secvențial comenzile:

Extindeți fila „Vizualizare”;
. deschideți „Macro-uri”;
. selectați numele macrocomenzii necesare;
. Apelați o fereastră cu codul său folosind butonul „Schimbare”.

În noua casetă de dialog puteți edita și modifica macrocomanda. Veți vedea acolo codul pe care programul l-a înregistrat automat și puteți adăuga manual orice acțiune. După salvarea informațiilor, macrocomanda va funcționa diferit atunci când se importă date noi.

Cum să porniți o macrocomandă făcând clic pe o celulă

Să presupunem că doriți ca o macrocomandă să ruleze atunci când este selectată celula A1. Pentru a selecta în celula Excel rulând macro-ul, trebuie să adăugați codul programului la primul modul:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dacă Target.Address = "$A$1" Atunci: Apelați modulul1
End Sub

Cel mai bun mod de a învăța macrocomenzi este în tutorial Excel. Există mai mult de un exemplu similar, iar pentru cei care tocmai se familiarizează cu toate posibilitățile tabelului, acesta este Cel mai bun modînvață să-și folosească pe deplin funcționalitatea.

Unde poți învăța să lucrezi cu macrocomenzi?

Există mai multe moduri de a învăța cum să creați macrocomenzi mai complexe. Ele trebuie să fie distinse prin logică și o secvență clar definită de acțiuni. Astfel de macrocomenzi pot fi create doar prin scrierea manuală a codului VBA. Pentru a crea macrocomenzi complexe, va trebui să petreceți ceva timp învățând complexitățile limbii Programare vizuală Basik.

Acum să-i explorezi pe toată lumea Capabilitati Excel Au fost create multe cursuri speciale care durează câteva săptămâni, dar pentru a le urma va trebui să vă deplasați la un centru de formare sau să participați la webinarii (ceea ce nu este întotdeauna convenabil din cauza diferenței de timp). Dar o modalitate mai eficientă și mai simplă de a învăța cum să creați macrocomenzi în Excel este un tutorial cu exemple. Îl poți studia în orice moment convenabil pentru tine, iar manualele pas cu pas te vor ajuta să te dezvolți diferite variante: din cele mai multe coduri simple la cele mai complexe. Microsoft Excel este un editor universal de foi de calcul care poate fi folosit pentru a rezolva multe probleme complexe.

Exemple

Există coduri care vă permit să realizați automatizare diverse sarcini. Deci, ca exemplu, să ne uităm la macrocomanda de inserare a rândurilor. Arata cam asa:

Sub Macro1()