în acest articol, vă voi arăta câteva dintre cele mai uimitoare coduri VBA Excel pe care le puteți utiliza pentru a vă optimiza munca. VBA este un limbaj de programare, care poate fi folosit pentru a extinde capacitățile MS Excel și alte aplicații MS Office. Este extrem de util pentru utilizatorii MS Excel, deoarece poate fi folosit pentru a vă automatiza munca și pentru a vă îmbunătăți semnificativ eficiența. Acest articol vă va prezenta VBA și vă va arăta unele dintre cele mai utile, gata de utilizare coduri VBA acolo. Puteți utiliza aceste exemple macro pentru a crea propriile scripturi care se potrivesc nevoilor dvs.

nu aveți nevoie de experiență de programare pentru a profita de informațiile din acest articol, dar se așteaptă să aveți cunoștințe de bază despre Excel. Dacă sunteți utilizator începător, vă recomand să citiți articolul 20 formule Excel ar trebui să începeți să utilizați acum pentru a afla mai multe despre funcționalitățile de bază ale Excel.

am pregătit pentru dvs. o serie de exemple Macro VBA Excel gata de utilizare, cu funcționalități excelente pe care le puteți utiliza pentru a vă optimiza munca. Pentru a le utiliza, trebuie să le „instalați” în fișierul Excel. Următorul paragraf se referă la instalarea macro Excel. Săriți această parte dacă sunteți deja familiarizați cu acest lucru.

cum se instalează o macrocomandă

în Excel, apăsați combinația de taste alt + F11. Acest lucru vă va duce la editorul VBA din MS Excel. Apoi, faceți clic dreapta pe folderul obiecte Microsoft Excel din stânga și selectați Insert => modul. Acesta este locul în care sunt stocate macrocomenzile. Pentru a utiliza macrocomanda, trebuie să salvați documentul Excel ca Macro-activat. Din fila file = > save as, alegeți save as Macro-enabled workbook (the .extensie xlsm) acum, este timpul să scrieți prima dvs. macrocomandă!

1. Copiați datele dintr-un fișier în altul.

macro foarte util, deoarece arată cum să copiați o serie de date din interiorul vba și cum să creați și să denumiți un nou registru de lucru. Îl puteți actualiza cu ușurință pentru a se potrivi propriilor cerințe:

 sub CopyFiletoAnotherWorkbook() 'copiați fișele tehnice("Exemplul 1").Interval ("B4: C15").Copiere ' creați un nou registru de lucru registre de lucru.Adauga ' lipiți ActiveSheet de date.Paste ' opriți aplicația alerte aplicație.DisplayAlerts = False ' salvați fișierul nou. Schimbați numele directorului. ActiveWorkbook.SaveAs Nume Fișier:="C:\Temp\MyNewBook.XLSX "' întoarceți alertele aplicației înapoi la aplicație.DisplayAlerts = TrueEnd Sub 

2. Afișați rândurile ascunse

Ocazional, fișierele Excel mari conțin linii ascunse pentru o mai bună claritate. Iată o macrocomandă care va afișa toate rândurile dintr-o foaie de lucru activă:

sub ShowHiddenRows() coloane.Întreaga coloană.Ascuns = Rânduri False.Întreg acum.Ascuns = FalseEnd Sub

3. Ștergerea rândurilor și coloanelor goale

rândurile goale în Excel reprezintă o problemă cu procesarea datelor. Iată cum să scapi de ele:

sub DeleteEmptyRowsAndColumns() 'declarați variabilele. Dim MyRange ca gama dim iCounter ca lung ' defini intervalul țintă. Set MyRange = ActiveSheet.Userrange ' începe inversa looping prin gama de rânduri. Pentru iCounter = MyRange.Rânduri.Numărați la Pasul 1 -1 ' dacă întregul rând este gol, ștergeți-l. Dacă Cererea.CountA(rânduri (iCounter).EntireRow) = 0 apoi rânduri (iCounter).Delete 'Remove comment to See which are the empty rows 'MsgBox" row " & iCounter &" is empty " End If 'Increment the counter down Next iCounter 'Step 6: Start reverse looping through the range of Columns. Pentru iCounter = MyRange.Coloane.Numărați la Pasul 1 -1 ' Pasul 7: Dacă întreaga coloană este goală, ștergeți-o. Dacă Cererea.CountA(coloane (iCounter).EntireColumn) = 0 apoi coloane (iCounter).Ștergeți Sfârșitul Dacă ' Pasul 8: Incrementați contorul în jos următorul capăt iCounter sub

4. Găsiți o celulă goală

sub FindEmptyCell() ActiveCell.Offset(1, 0).Selectați Do While Not IsEmpty (ActiveCell) ActiveCell.Offset(1, 0).Selectați LoopEnd Sub 

5. Înlocuiți celulele goale cu o valoare.

după cum sa menționat anterior, celulele goale interferează cu prelucrarea datelor și crearea tabelelor pivot. Aici este un cod care înlocuiește toate celulele goale cu 0. Această macrocomandă are o aplicație foarte mare, deoarece o puteți utiliza pentru a găsi și înlocui rezultatele N/A, precum și alte caractere, cum ar fi puncte, virgule sau valori duplicate:

sub FindAndReplace() 'declarați variabilele Dim MyRange ca interval Dim MyCell ca interval 'salvați registrul de lucru înainte de a schimba celulele? Selectați Case MsgBox ("nu se poate anula această acțiune. "& _ " salvați mai întâi Registrul de lucru?", vbYesNoCancel) cazul este = vbYes ThisWorkbook.Salvare caz este = Vbcancel ieșire sub end selectați ' definiți intervalul țintă. Set MyRange = selecție ' începe looping prin intervalul. Pentru fiecare MyCell în Myrange ' verifica pentru lungimea zero, apoi se adaugă 0. Dacă Len (MyCell.Valoare) = 0 atunci MyCell = 0 End If ' obțineți următoarea celulă în intervalul următor MyCellEnd Sub

6. Sortare numere

următoarea macrocomandă Sortează în ordine crescătoare toate numerele din coloana celulei active. Doar faceți dublu clic pe orice celulă dintr-o coloană pe care doriți să o Sortați.NB: trebuie să puneți codul în foaia 1 și nu într-un modul pentru a funcționa:

privat sub Worksheet_BeforeDoubleClick (țintă ByVal ca interval, anulare ca Boolean) 'declarați variabilele Dim LastRow cât timp 'găsiți ultimul rând LastRow = celule (rânduri.Număr, 1).Sfârșit (xlUp).Rând ' Sortați ascendent pe rânduri de coloane cu dublu clic ("6:"& LastRow) .Sortare _ Key1: = Celule (6, ActiveCell.Coloana), _ Order1: = xlAscendingEnd Sub 

7. Eliminați spațiile goale

Ocazional, datele din registrul de lucru conțin spații suplimentare (spații) care pot interfera cu analiza datelor și cu coruperea formulelor. Iată o macrocomandă care va elimina toate spațiile dintr-o gamă preselectată de celule:

sub TrimTheSpaces() 'declarați variabilele Dim MyRange ca interval Dim MyCell ca interval 'salvați registrul de lucru înainte de a schimba celulele selectați cazul MsgBox("nu se poate anula această acțiune. "& _ " salvați mai întâi Registrul de lucru?", vbYesNoCancel) cazul este = vbYes ThisWorkbook.Salvare caz este = Vbcancel ieșire sub end selectați ' definiți intervalul țintă. Set MyRange = selecție ' începe looping prin intervalul. Pentru fiecare MyCell în Myrange 'tăiați spațiile. Dacă nu IsEmpty (MyCell) atunci MyCell = Trim(MyCell) End If 'obțineți următoarea celulă din intervalul următor MyCellEnd Sub

8. Evidențiați valorile dublicate

uneori există valori duplicate în mai multe coloane pe care am dori să le iluminăm. Iată o macrocomandă care face exact asta:

sub HighlightDuplicates() 'declarați variabilele Dim MyRange ca interval Dim MyCell ca interval 'definiți intervalul țintă. Set MyRange = selecție ' începe looping prin intervalul. Pentru fiecare MyCell în Myrange 'asigura celula are formatarea textului. Dacă WorksheetFunction.CountIf (MyRange, MyCell.Valoare) > 1 apoi MyCell.Interior.ColorIndex = 36 End If ' obțineți următoarea celulă în intervalul următor MyCellEnd Sub 

9. Evidențiați primele zece valori

acest cod va evidenția primele zece valori dintr-o selecție de celule:

sub Topten() selecție.Formatcondiții.AddTop10 Selecție.Formatcondiții(Selecție.Formatcondiții.Count).SetFirstPriority Cu Selecție.Formatcondiții(1).TopBottom = xlTop10Top ' schimbați rangul aici pentru a evidenția un număr diferit de valori .Rang = 10 .Procent = Sfârșit Fals Cu Selecție.Formatcondiții(1).Font .Culoare = -16752384 .TintAndShade = 0 End Cu Cu Selecție.Formatcondiții(1).Interior .PatternColorIndex = xlAutomatic .Culoare = 13561798 .TintAndShade = 0 Sfârșitul Cu Selecție.Formatcondiții(1).StopIfTrue = FalseEnd Sub 

puteți regla cu ușurință codul pentru a evidenția un număr diferit de valori.

10. Evidențiați mai mare decât valorile

când executați acest cod, se va solicita o fereastră. Acesta vă va cere valoarea pe care doriți să comparați celulele pe care le-ați selectat.

sub Highlighightgreaterthanvalues() Dim I Ca Integer i = InputBox("Enter mai mare decât valoarea", "introduceți valoarea") selecție.Formatcondiții.Ștergeți ' schimbați operatorul la xlLower pentru a evidenția selecția mai mică decât valorile.Formatcondiții.Adăugați tipul:=Xlcellvalue, Operator:=xlGreater, Formula1: = i selecție.Formatcondiții(Selecție.Formatcondiții.Count).SetFirstPriority Cu Selecție.Formatcondiții(1).Font.Culoare = RGB (0, 0, 0) .Interior.Color = RGB (31, 218, 154) End WithEnd Sub

puteți modifica acest cod pentru a evidenția și valori mai mici.

o macrocomandă simplă care evidențiază toate celulele care conțin comentarii:

Sub HighlightCommentCells() selecție.SpecialCells (xlCellTypeComments).Selectați Selecție.Style ="notă" End Sub

12. Evidențiați celulele cu cuvinte scrise greșit

acest lucru este extrem de util atunci când lucrați cu funcții care iau șiruri, cu toate acestea, cineva a intrat în șir cu o greșeală și formulele dvs. nu funcționează. Iată cum să remediați această problemă:

 sub ColorMispelledCells() pentru fiecare cl din ActiveSheet.Userrange Dacă Nu Aplicație.CheckSpelling (Cuvânt:= cl.Text) atunci _ cl.Interior.ColorIndex = 28 următorul clEnd sub

13. Crearea unui tabel pivot

Iată cum se creează un tabel pivot din MS Excel (versiunea 2007). Mai ales util, atunci când se face un raport personalizat în fiecare zi. Puteți optimiza crearea tabelului pivot în felul următor:

sub PivotTableForExcel2007() Dim SourceRange As range Set SourceRange = Sheets("Sheet1").Gama ("A3: N86") Activecarte De Lucru.PivotCaches.Creați (_SourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version: = xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion: = xlPivotTableVersion12End Sub

14. Atașați registrul de lucru activ într-un e-mail

codul meu VBA preferat. Vă permite să atașați și să trimiteți fișierul la care lucrați cu o adresă de e-mail predefinită, titlul mesajului și corpul mesajului! Mai întâi trebuie să setați referința la Microsoft Outlook (în ediția VBA, faceți clic pe Instrumente => referințe și alegeți Microsoft Outlook).

sub SendFIleAsAttachment () "declarați variabilele dvs." Setați referința la Biblioteca de obiecte Microsoft Outlook Dim OLApp ca Outlook.Aplicație Dim OLMail ca obiect ' deschideți Outlook începeți un nou set de elemente de e-mail OLApp = Outlook nou.Set De Aplicații OLMail = OLApp.CreateItem (0) OLApp.Sesiune.Logon ' construiți elementul dvs. de e-mail și trimiteți cu OLMail .To = "[email protected]; [email protected]". CC="".BCC="".Subiect = "aceasta este linia de subiect".Corp = "Bună acolo".Atașamente.Adăugați ActiveWorkbook.Nume complet .Afișare ' schimbare la .Trimite pentru a trimite fără revizuirea End cu ' memory cleanup Set OLMail = nimic set OLApp = NothingEnd Sub

15. Trimiteți toate diagramele Excel la o prezentare PowerPoint

o macrocomandă foarte utilă care vă permite să adăugați toate diagramele Excel în prezentarea Powerpoint doar cu un singur clic:

Sub SendExcelFiguresToPowerPoint() 'setați referința la Biblioteca de obiecte Microsoft Powerpoint 'declarați variabilele Dim PP ca PowerPoint.Aplicație Dim PPPres Ca PowerPoint.Prezentare Dim PPSlide Ca PowerPoint.Slide Dim i ca întreg ' verificați pentru diagrame; ieșiți dacă nu există diagrame foi ("date de diapozitive").Selectați Dacă ActiveSheet.ChartObjects.Count < 1 apoi MsgBox "nu există diagrame existente foaia activă" Exit sub End If ' Open PowerPoint and create new presentation Set PP = New PowerPoint.Set de aplicații PPPres = PP.Prezentări.Adăugați PP.Vizibil = adevărat ' începeți bucla pe baza numărului de diagrame pentru i = 1 la ActiveSheet.ChartObjects.Count ' copiați graficul ca imagine Activefoaie.ChartObjects (i).Diagramă.CopyPicture _ Dimensiune:=xlScreen, Format: = aplicație xlPicture.Așteptați (Acum + TimeValue ("0: 00:1")) " numărați diapozitivele și adăugați un diapozitiv nou ca următorul număr de diapozitive disponibil ppSlideCount = PPPres.Diapozitive.Contele Set PPSlide = PPPres.Diapozitive.Adăugați (SlideCount + 1, ppLayoutBlank) PPSlide.Selectați ' lipiți imaginea și reglați poziția acesteia; mergeți la graficul următor PPSlide.Forme.Pastă.Selectați PP.Fereastră activă.Selecție.ShapeRange.Aliniați msoAlignCenters, adevărat PP.Fereastră activă.Selecție.ShapeRange.Aliniați msoAlignMiddles, True Next I 'memorie Cleanup Set PPSlide = nimic set PPPres = nimic set PP = NothingEnd Sub

16. Trimiteți tabelul Excel în MS Word

tabelele Excel sunt de obicei puse în documente text. Iată un mod automat de a exporta tabelul Excel în MS Word:

 sub ExcelTableInWord() 'setați referința la Biblioteca de obiecte Microsoft Word 'declarați variabilele Dim MyRange ca Excel.Gama dim wd ca cuvânt.Aplicație Dim wdDoc ca cuvânt.Document Dim WdRange Ca Cuvânt.Interval ' copiați foile de interval definite ("tabelul veniturilor").Interval ("B4: F10").Cop ' de chide setul de documente Word țintă WD = cuvânt nou.Set de aplicații wdDoc = wd.Documente.Deschis _(Acestcarte De Lucru.Path && " PasteTable.docx") wd.Vizibil = adevărat ' setați focalizarea pe marcajul țintă setat WdRange = wdDoc.Marcaje ("DataTableHere").Range ' ștergeți tabelul vechi și lipiți nou pe eroare reluați următorul WdRange.Tabele(1).Ștergeți WdRange.Paste 'paste în tabel' reglați lățimile coloanei WdRange.Tabele(1).Coloane.SetWidth _(MyRange.Lățime / MyRange.Coloane.Count), wdAdjustSameWidth ' reintroduceți marcajul wdDoc.Semne de carte.Adăugați "DataTableHere", wdrange ' memory cleanup Set wd = Nothing Set Wddoc = Nothing Set WdRange = NothingEnd Sub

17. Extrageți un cuvânt specific dintr-o celulă

putem folosi formule dacă dorim să extragem un anumit număr de simboluri. Dar dacă vrem să extragem doar al doilea cuvânt dintr-o propoziție sau dintr-o serie de cuvinte dintr-o celulă? Pentru a face acest lucru, putem crea o funcție Excel personalizată cu VBA. Aceasta este una dintre cele mai importante funcționalități VBA, deoarece vă permite să creați propriile funcții care sunt inexistente în MS Excel. Să continuăm și să creăm două funcții: findword() și findwordrev(). Iată codul vba pentru asta:

funcția FindWord(sursă ca șir, poziție ca întreg) ca șir Pe eroare reluați următorul FindWord = Split(WorksheetFunction.Trim (sursă),"") (poziția - 1) Pe eroare Goto 0end FunctionFunction FindWordRev(sursă ca șir, poziție ca întreg) ca șir Dim Arr () ca șir arr = VBA.Split(WorksheetFunction.Trim(Sursa), " ") pe eroare CV - ul următor FindWordRev = Arr(UBound (Arr) - poziția + 1) Pe eroare Goto 0End funcție

foarte frumos, am creat două funcții Cstom Excel. Acum, încercați să le utilizați în Excel. Funcția = FindWordRev (A1, 1) ia ultimul cuvânt din celula A1. Funcția = FindWord (A1, 3) Ia al treilea cuvânt din celula A1 etc.

18. Protejați-vă Registrul de lucru

uneori dorim să protejăm datele din fișierul nostru, astfel încât numai noi să le putem schimba. Iată cum se face acest lucru cu VBA:

sub ProtectSheets() 'declarați variabilele Dim ws ca foaie de lucru 'începeți să parcurgeți toate foile de lucru pentru fiecare ws din ActiveWorkbook.Protejați foile de lucru și bucla la următoarea foaie de lucru ws.Protejați parola: = "1234" următorul wsEnd sub

Felicitări! Din moment ce încă citiți acest lucru, sunteți foarte dornici să învățați VBA. După cum ați văzut deja pentru dvs., limbajul de programare VBA este extrem de util și ne poate economisi mult timp. Sper că ați găsit aceste informații utile și le-ați folosit pentru a deveni master în MS Excel, VBA și software de calculator în general.