Mironov. Macrocomenzi gata făcute în VBA Excel. VBA Excel: exemple de programe. Macro-uri în Excel

Macro pentru evidențierea celulei A1 pe fiecare foaie din registrul de lucru activ. Acest lucru face, de asemenea, să se miște ecranul.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Select Next Sheets(1) .Selectați aplicația.ScreenUpdating = True End Sub

Macro pentru copierea foii curente de un anumit număr de ori. Util pentru testarea unor macrocomenzi - editări făcute, verificate pe o copie a datelor. Am rămas fără copii — rulați macrocomanda din nou

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Introduceți numărul de copii ale foii curente") Application.ScreenUpdating = False Pentru j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets) .Count) ActiveSheet .Name = „Copy” & j Next j Application.ScreenUpdating = True End Sub

Creați foi cu titluri dintr-un interval specificat pe o foaie

Sub CreateFromList() Dim cell Ca interval pentru fiecare celulă din Selection Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Celula următoare End Sub

Markros de a trimite o scrisoare cu întârziere. Macrocomandă modificată din cartea lui John Walkenbach Professional VBA Programming

Sub SendLetter() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon La eroare GoTo cleanup Set OutMail = OutApp.CreateItem(0) La eroare Reluare Next With OutMail .To = " [email protected]" .Subject = "Raport de vânzări" .Atașamente.Add "C:\Test.txt" .Body = "Text e-mail" .DeferredDeliveryTime = Înlocuire(Data, ".", "/") & " 11:00:00 " .send ".Afișează pentru a genera o scrisoare și a o deschide End With On Error GoTo 0 Set OutMail = Nimic curățare: Set OutApp = Nothing End Sub

Macro-cuprins ușor modificată de la Nikolai Pavlov.
Dacă o foaie „Cuprins” există deja în carte, macrocomandă vă solicită să o ștergeți. Dacă nu, creează o foaie „Cuprins” și inserează legături cu numele foilor

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False Cu ActiveWorkbook Pentru fiecare foaie de lucru din ActiveWorkbook.Worksheets Dacă Worksheet.Name = "Cuprins" Apoi Răspuns = MsgBox("Cartul de lucru are un foaie cu numele Cuprins?, vbYesNo) Dacă Răspuns = vbNu Apoi Ieșire Sub Dacă Răspuns = vbDa Apoi Aplicație.DisplayAlerts = Foaie de lucru fals.Delete Application.DisplayAlerts = True End If End If Next End With Sheets (Matrice). (1)).Selectați foi.Adăugați foi (1).Nume = „Cuprins” cu ActiveWorkbook Pentru fiecare foaie din ActiveWorkbook.Worksheets Dacă foaia.Nume<>„Cuprins” Apoi Setați celulă = Foi de lucru(1).Cellule(foaia.Index, 1) .Fișele de lucru(1).Hyperlinkuri.Adăugați ancora:=celulă, Adresă:="", SubAdresă:=""" și foaie .Nume și celulă """ & "!A1".Formulă = foaie.Nume Sfârșit dacă Foaia următoare se termină cu rânduri ("1:1").Delete Application.ScreenUpdating = True End Sub

Sortarea foilor din vrăjitorii VBA. Macro-ul sortează și foile ascunse. Nu va funcționa dacă cartea are o structură protejată

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " amintiți-vă starea de vizibilitate a fiecărei foi și faceți totul vizibil pentru fiecare iSht din ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " sortarea foilor vizibile Pentru i = 1 To .Sheets.Count - 1 Pentru j = i + 1 La .Sheets.Count Dacă UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Apoi .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With " restabiliți starea de vizibilitate inițială a fiecărei foi pentru fiecare iSht din ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Next Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Importarea coloanelor „Field1” și „Field2” din foaia „Sheet1” din fișierul Excel „C:\Manager.xls” prin conexiune ADODB și inserarea conținutului începând din celula A1 a foii curente

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.
Scrierea și citirea unui fișier 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.
Creați copii de rezervă ale fișierelor 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 unui 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).
Creați documente Word pe baza unui tabel 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.
Spațiu de lucru Microsoft Excel.
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ți o foaie nouă.
Creați o foaie nouă.
Îndepărtarea foilor în funcție de dată.
Copierea unei foi într-o carte.
Copierea unei foi într-un registru de lucru nou (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.
Numărează 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.
Eliminarea rândurilor ascunse.
Eliminați rândurile ascunse utilizate 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ăutare 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ăutați o celulă albastră într-un interval.
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".
Proiectarea limitelor superioare și inferioare ale gamei.
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șcarea 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 comentarii.
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.

Crearea unui 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 care rulează.
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 acum este inclus cu toate aplicațiile Office și chiar cu aplicațiile 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 produse software Microsoft. Mai mult, veți putea crea produse software cu drepturi depline care utilizează simultan funcțiile unei game largi de aplicații.

Cum să activați macrocomenzi în Excel

În mod implicit, fila responsabilă cu gestionarea și navigarea macrocomenzilor în Excel este ascunsă. Pentru 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. Acești pași sunt relevanți pentru versiunile de Excel 2010 și mai vechi.

O filă nouă va apărea pe panglică Dezvoltator cu controale de automatizare Excel.

Scrierea macrocomenzi în Excel

În filă 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. De acum înainte, 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ă. O opțiune alternativă pentru a lansa această fereastră este să apăsați 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 în VBA (Visual Basic for Applications). 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ă.

În această etapă, îmi propun să studiem mai în detaliu 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 este structurat într-un anumit fel. 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 registrul 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ă modalități mai rapide și mai eficiente de a obține rezultate similare, despre care vom discuta în continuare.

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. Calculatoarele moderne vor rula codul în cauză atât de repede încât nici nu-l vei 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ă economisească puterea de procesare a computerului și să actualizeze ecranul cu valori noi 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 executat. În consecință, dacă există un număr de celule care influențează, recalcularea poate încetini semnificativ execuția codului. Pentru a preveni acest lucru, puteți instala comanda Aplicație. Calcul la începutul codului, care va trece recalcularea formulelor în modul manual, iar apoi va returna calculul automat la sfârșitul macro-ului.

Aveți grijă să nu uitați să comutați această opțiune înapoi în modul automat la sfârșitul macrocomenzii. Î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 de înregistrare automată a macrocomenzilor, este posibil să observați că Excel utilizează foarte des comanda de selecție a celulei, de exemplu, Interval(„A1”).Selectați. În exemplul nostru, am folosit această comandă de mai multe ori pentru a selecta o celulă și a-i schimba valoarea. 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 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 cod VBA care vă vor ajuta să automatizați cele mai comune sarcini.

Pentru a automatiza sarcinile repetitive, puteți înregistra o macrocomandă folosind Macro Recorder din Microsoft Excel. Imaginați-vă că aveți date în formate aleatorii și doriți să aplicați un singur format tuturor. Acest lucru se poate face folosind o macrocomandă. Puteți înregistra o macrocomandă folosind formatul dorit și puteți reda macro-ul dacă este necesar.

Când înregistrați o macrocomandă, toate acțiunile necesare sunt înregistrate ca cod Visual Basic pentru aplicații (VBA). Aceste acțiuni pot include introducerea de text sau numere, selectarea celulelor sau comenzilor din panglică sau meniu, formatarea celulelor, rândurilor sau coloanelor și chiar importarea datelor dintr-o sursă externă, cum ar fi Microsoft Access. Visual Basic pentru aplicații (VBA) face parte din limbajul de programare Visual Basic. Este disponibil în majoritatea aplicațiilor Office. Deși VBA vă permite să automatizați procesele din cadrul și între aplicațiile Office, nu este necesar să puteți programa sau să cunoașteți limbajul VBA, deoarece macro recorderul face tot ce aveți nevoie.

Este important să știți că atunci când înregistrați o macrocomandă, aproape tot ceea ce faceți este înregistrat. Deci, dacă faceți o greșeală, cum ar fi apăsarea butonului greșit, macro recorder-ul va înregistra acea acțiune. În acest caz, puteți scrie din nou întreaga secvență sau puteți modifica codul VBA. Prin urmare, înainte de a înregistra procesul, ar trebui să îl rezolvați bine. Cu cât înregistrați mai precis secvența, cu atât mai eficient va funcționa macrocomanda.

Dezvoltator, care este ascuns în mod implicit, așa că trebuie să îl activați mai întâi. Pentru mai multe informații, consultați Afișare fila Dezvoltator.

Înregistrați o macrocomandă

Pe fila Dezvoltator clic Macro-uri pentru a vizualiza macrocomenzile asociate cu registrul de lucru. Alternativ, puteți apăsa tastele ALT+F8. Aceasta va deschide o casetă de dialog Macro.

Atenţie:

Lucrul cu codul înregistrat în Editorul Visual Basic (VBE)

Cu Visual Basic Editor (VBE), puteți adăuga propriile variabile, structuri de control și alte elemente la codul înregistrat pe care macro recorderul nu le acceptă. Deoarece reportofonul macro captează aproape fiecare pas efectuat în timpul înregistrării, este posibil să fie necesar să eliminați și codul inutil. Revizuirea codului înregistrat este o modalitate excelentă de a învăța programarea VBA sau de a vă perfecționa abilitățile.

Un exemplu de modificare a codului înregistrat poate fi găsit în articolul Noțiuni de bază cu VBA în Excel.

Înregistrați o macrocomandă

Înainte de a înregistra macrocomenzi, este util să știți următoarele:

O macrocomandă scrisă pentru a funcționa cu un interval Excel va rula numai pe celulele din acel interval. Prin urmare, dacă adăugați un nou rând în interval, macrocomanda nu se va aplica acestuia.

Dacă trebuie să înregistrați o secvență lungă de sarcini, vă recomandăm să utilizați mai multe macrocomenzi mai mici.

O macrocomandă poate conține și sarcini non-Excel. Procesul macro poate acoperi alte aplicații Office și alte programe care acceptă Visual Basic pentru aplicații (VBA). De exemplu, puteți înregistra o macrocomandă care actualizează mai întâi un tabel în Excel și apoi deschide Outlook pentru a-l trimite prin e-mail.

Macro-urile și instrumentele VBA sunt situate în filă Dezvoltator, care este ascuns în mod implicit, așa că trebuie să îl activați mai întâi.

Selectați excela > Opțiuni> Bandă și panou.

Pentru a înregistra o macrocomandă, urmați instrucțiunile de mai jos.

Lucrul cu macrocomenzi înregistrate în Excel

Pe fila Dezvoltator clic Macro-uri pentru a vizualiza macrocomenzile asociate cu registrul de lucru. Aceasta va deschide o casetă de dialog Macro.

Notă: Macro-urile nu pot fi anulate. Înainte de a rula o macrocomandă înregistrată pentru prima dată, salvați sau creați o copie a registrului de lucru pentru a preveni modificările nedorite. Dacă nu sunteți mulțumit de rezultatele macrocomenzii, puteți închide registrul de lucru fără a-l salva.

Iată mai multe informații despre lucrul cu macrocomenzi în Excel.

Informații suplimentare

Puteți oricând să puneți o întrebare de la Comunitatea Excel Tech, să cereți ajutor în comunitatea Răspunsuri sau să sugerați o nouă funcție sau îmbunătățire a site-ului

Bună ziua

Vreau să dedic acest articol unei secțiuni atât de uriașe a MS Excel precum macrocomenzile sau, mai degrabă, să începem de la început și să vedem cum să creați o macrocomandă în Excel, pentru ce este și cum să o utilizați în munca dvs.

După cum știți din propria experiență, atunci când lucrați cu o macrocomandă există o mulțime de „rutină”, adică se efectuează aceleași operațiuni și acțiuni care sunt necesare pentru a obține rezultatul, aceasta poate fi completarea aceluiași tip de tabele sau formulare, prelucrarea datelor, similare cu gemenii săptămânal, rapoarte lunare etc. Dar utilizarea macrocomenzilor vă va permite să efectuați aceste acțiuni în mod automat, folosind capabilitățile Excel la maximum, aruncând aceste operațiuni de rutină și monotone pe umerii puternici ai Excel. De asemenea, motivul utilizării macrocomenzilor poate fi adăugarea capabilităților necesare care nu sunt încă implementate în funcțiile standard Excel (de exemplu, ieșire, colectarea datelor pe o singură foaie etc.).

Dacă nu ați auzit niciodată de o macrocomandă, cea mai precisă definiție a acesteia ar fi: acestea sunt acțiuni care sunt programate pentru o anumită secvență și scrise în mediul de programare în Visual Basic pentru aplicații (VBA). Rularea unei macrocomenzi se poate face de mai multe ori și acest lucru va forța Excel să efectueze orice secvență de acțiuni de care avem nevoie și care pur și simplu nu ne place sau nu dorim să le facem manual. În ciuda varietății mari de limbaje de programare pentru întregul complex Microsoft Office, VBA este standard și funcționează în orice aplicație din suita office.

Deci, există 2 moduri de a crea o macrocomandă în Excel:

Creați o macrocomandă în Excelfolosind un reportofon macro

Mai întâi, să clarificăm ce este un macro recorder și ce legătură are o macro cu el.

Macro recorder este un mic program încorporat în Excel care interpretează orice acțiune a utilizatorului în codurile limbajului de programare VBA și scrie comenzile care au fost obținute în timpul procesului de lucru în modulul programului. Adică, dacă creăm raportul zilnic de care avem nevoie cu macro recorder pornit, macro recorder va înregistra totul în comenzile sale pas cu pas și, ca urmare, va crea o macro care va crea un raport zilnic automat.

Această metodă este foarte utilă pentru cei care nu au abilitățile și cunoștințele de lucru în mediul de limbaj VBA. Dar o astfel de ușurință de execuție și înregistrare a unei macrocomenzi are dezavantajele sale, precum și avantajele sale:

• Un macro recorder poate înregistra doar ceea ce poate atinge, ceea ce înseamnă că poate înregistra acțiuni numai atunci când butoanele, pictogramele, comenzile de meniu și orice asemenea opțiuni nu sunt disponibile;
• Dacă a fost făcută o eroare în timpul perioadei de înregistrare, aceasta va fi de asemenea înregistrată. Dar puteți folosi butonul de anulare pentru a șterge ultima comandă pe care ați scris-o incorect în VBA;
• Înregistrarea în macro recorder se efectuează numai în limitele ferestrei MS Excel, iar dacă închideți programul sau porniți altul, înregistrarea va fi oprită și nu va mai fi efectuată.

Pentru a activa macro recorder-ul pentru înregistrare, trebuie să efectuați următorii pași:

Următorul pas în lucrul cu înregistrarea macro va fi configurarea parametrilor acestuia pentru înregistrarea ulterioară a macro-ului, acest lucru se poate face în fereastră „Înregistrați o macrocomandă”, Unde:

• Câmp pentru numele macrocomenzii- puteți scrie un nume pe care îl înțelegeți în orice limbă, dar trebuie să înceapă cu o literă și să nu conțină semne de punctuație sau spații;
• Câmp de comandă rapidă de la tastatură- va fi folosit de dvs. în viitor pentru a porni rapid macrocomanda. În cazul în care trebuie să înregistrați unul nou, această opțiune va fi disponibilă în meniu „Instrumente” - „Macro” - „Macro” - „Run” sau pe filă "Dezvoltator" apăsând un buton „Macro-uri”;
• Câmpul „Salvează în...”.— puteți specifica locația în care va fi salvat textul macro (dar nu trimis) și există 3 opțiuni:
  • "Această carte"— macro-ul va fi înregistrat în modulul registrului de lucru curent și poate fi executat numai când acest registru de lucru Excel este deschis;
  • "O carte noua"— macro-ul va fi salvat în șablonul pe baza căruia se creează un nou registru de lucru gol în Excel, ceea ce înseamnă că macro-ul va deveni disponibil în toate registrele de lucru care vor fi create pe acest computer din acel moment;
  • „Cartea macro personală”- este un registru de lucru cu macrocomandă special numit „Personal.xls”și este folosit ca o bibliotecă specială de stocare a macrocomenzilor. La pornire, macrocomenzile din registrul de lucru „Personal.xls” sunt încărcate în memorie și pot fi lansate în orice registru de lucru în orice moment.
• Câmpul „Descriere”.- aici puteți descrie ce ar trebui să facă macro-ul și cum, de ce a fost creată și ce funcții are, acesta este un câmp pur informativ, care se numește memorie.

După ce ați lansat și înregistrat macrocomanda, după ce ați finalizat toate acțiunile necesare, puteți opri înregistrarea cu comanda „Opriți înregistrarea” iar macro-ul dvs. va fi creat folosind macro recorder-ul.

Creați o macrocomandă în Excelîn editorul Visual Basic.

În această metodă, ne vom uita la cum să creați o macrocomandă în Excel prin editorul de programe VBA care, așa cum am spus mai sus, este încorporat în orice versiune de Excel.

Lansarea editorului de programe VBA are loc diferit, în funcție de versiunea programului dvs. Excel:

• în versiunile de Excel 2003 și ulterioare, avem nevoie în meniu "Serviciu", selectați elementul "Macro"și apăsați „Editor Visual Basic”;
• în versiunile de Excel 2007 și ulterioare, avem nevoie de pe filă "Dezvoltator" apasa butonul „Editor Visual Basic”. Dacă nu găsiți această filă, trebuie să o activați selectând elementul de meniu „Fișier” - „Opțiuni” - „Personalizare panglică” iar în caseta de dialog, utilizați caseta de selectare pentru a activa fila "Dezvoltator".

În fereastra care se deschide se vede interfața editorului VBA, totul aici este scris în engleză și nu există localizări, nu trebuie să o cauți, ci doar să o accepți și să lucrezi, mai ales că este doar de neînțeles la început, apoi totul va fi familiar.

Deci, cum să lucrați în editorul VBA, unde este ce este stocat și cum să creați o macrocomandă în Excel. Întrebări de această natură apar imediat de îndată ce îl vedeți pe editor, iar acum le vom lua în considerare pe toate.

Toate macrocomenzile noastre vor fi stocate în așa-numitul module software. În oricare dintre cărțile oricărei versiuni de Excel, putem crea orice module de program în orice cantitate și plasăm în ele toate macrocomenzile pe care le-am creat. Un modul poate conține orice număr de macrocomenzi de care aveți nevoie sau le creați. Modulele sunt disponibile în fereastră „Explorator de proiect”și sunt situate în colțul din stânga sus al editorului de macro-uri (puteți apela și cu combinația de taste CTRL+R).

Modulele de program din editorul VBA există în mai multe tipuri și sunt utilizate pentru diferite opțiuni și situații:

De fapt, macro-ul în sine, atunci când lucrează într-un modul standard, arată astfel:

Să ne uităm la un exemplu de macrocomandă de lucru:

• Toate macrocomenzile vor începe în mod necesar cu operatorul Sub, urmat de numele macrocomenzii și de o listă de argumente între paranteze. În cazurile în care nu există argumente, parantezele trebuie lăsate goale;
• Toate macrocomenzile trebuie să se termine cu operatorul End Sub;
• Date care sunt între operatori SubȘi End Sub, este corpul macro-ului care va rula atunci când macro-ul este rulat. În exemplu, macro-ul verifică și, la introducerea datelor, le găsește în lista bazei de date și afișează valoarea specificată de criterii.

După cum puteți vedea, a doua metodă este mai dificil de utilizat și de înțeles dacă nu aveți experiență în programare în general sau în VBA în special. Este foarte dificil să înțelegeți și să vă dați seama ce comenzi sunt introduse și cum, ce argumente folosește pentru ca macro-ul să înceapă să-și facă treaba automat. Dar cel care umblă va stăpâni drumul, așa cum spuneau vechii înțelepți, și de aceea nu trebuie să renunți, ci să urmezi poruncile bunicului Lenin...

Crearea unui buton pentru a rula macrocomenzi în bara de instrumente

După cum am spus mai devreme, puteți apela o procedură macro cu o combinație de taste rapide, dar este foarte obositor să vă amintiți care combinație este atribuită cui, așa că cel mai bun pariu este să creați un buton pentru a rula macro. Butoanele pot fi create de mai multe tipuri, și anume:

Această metodă este disponibilă pentru orice versiune de MS Excel și constă în faptul că vom plasa butonul direct pe foaia noastră de lucru ca obiect grafic. Pentru a face acest lucru aveți nevoie de:

• În MS Excel 2003 și mai vechi mergi la meniu "Vedere", alege „Bara de instrumente”și apăsați butonul "Formulare".
• În MS Excel 2007 și versiuni ulterioare ai nevoie pe filă "Dezvoltator" deschide meniul drop-down "Introduce"și selectați un obiect "Buton".

După toate acestea, trebuie să desenați un buton pe foaia dvs. în timp ce țineți apăsat butonul stâng al mouse-ului. După terminarea procesului de desen, se va deschide automat o fereastră în care va trebui să selectați macrocomanda care trebuie să fie executată atunci când faceți clic pe butonul dvs.

Cum se creează funcții personalizate în VBA

În principiu, crearea așa-numitelor funcții definite de utilizator nu este foarte diferită de crearea unei macrocomenzi obișnuite într-un modul de program standard. Diferența dintre aceste concepte este că macro-ul va efectua acțiunile conținute în ea cu obiecte carte sau foaie (acestea sunt formule, celule etc.), dar funcția utilizator funcționează doar cu valorile pe care le primește de la noi. iar acestea sunt argumentele date inițiale pentru calcule.

De exemplu, pentru a crea o funcție personalizată pentru taxa pe valoarea adăugată, cunoscută și sub numele de TVA, trebuie să deschidem editorul nostru VBA și să adăugăm un nou modul, selectați din meniu "Introduce" paragraf "Modul"și introduceți acolo textul pentru funcția noastră: Trebuie remarcat faptul că principala diferență dintre o funcție și o macro este antetul Funcţieînlocuit Subși există o listă completă de argumente, în exemplul nostru aceasta este Suma. După ce codul nostru a fost introdus, acesta va deveni disponibil în fereastra standard Function Wizard, care se află în meniu "Formulele", paragraful „Inserare funcție”.
Și selectați o categorie „Definit de utilizator”în care va fi afișată funcția noastră scrisă „NDS”.
După selectarea funcției noastre, puteți plasa cursorul pe celula cu argumentul, care va conține suma pentru care calculăm TVA, totul se întâmplă ca la o funcție obișnuită.
Și asta e tot pentru mine! Chiar sper că articol despre cum se creează o macrocomandă în Excel ți-a fost clar și util. Aș fi foarte recunoscător pentru comentariile voastre, deoarece acesta este un indicator de lizibilitate și mă inspiră să scriu articole noi! Distribuie ce ai citit prietenilor tăi și dă like!