Programiranje makroa u Excelu: Kompletan vodič korak po korak

Informatec Digital » Programiranje » Programiranje makroa u Excelu: Kompletan vodič korak po korak

Ako se nađete u situaciji da u Excelu stalno izvršavate iste zadatke, makroi su vaš najbolji prijatelj za uštedu vremena. Pomoću njih možete automatizirati cijele nizove radnji (saznajte više o VBA programiranju ), od formatiranja do uvoza podataka ili izvođenja proračuna, i pokrenuti ih kad god želite klikom ili prečicom na tastaturi.

Kada kreirate makro, Excel snima vaše klikove i pritiske tipki i vjerno ih ponovo izvršava. Zatim možete prilagoditi kod kako biste preciznije podesili izvršavanje i konsultovali resurse za Excel . Uobičajeno je da se počne sa snimanjem makroa, a zatim se to usavršava pomoću VBA, postižući veću kontrolu, fleksibilnost i nula grešaka zbog nepažnje.

Šta je zapravo makro u Excelu?

Makro je lista instrukcija koje Excel izvršava definisanim redoslijedom . Može biti jednostavno kao primjena procentualnog stila ili složeno kao priprema izvještaja sa pivot tabelama , njegovo formatiranje, kreiranje radnih listova i slanje e-pošte u Outlooku. Makroi se čuvaju u vašoj radnoj svesci i mogu se pokrenuti kad god je to potrebno.

Ne morate znati programirati da biste započeli: snimač prevodi vaše radnje u VBA kod. Uprkos tome, neko znanje o VBA će vam omogućiti da kreirate prilagođene funkcije, obrasce, pa čak i mini-aplikacije unutar Excela.

Zašto koristiti makroe? Prave prednosti

Glavna svrha makroa je izbjegavanje ponavljajućih zadataka i minimiziranje grešaka . Ako svaki mjesec pripremate isti izvještaj, makro će vas uštedjeti mehaničkih koraka i omogućiti vam da se fokusirate na ono što je važno.

Njegove prednosti uključuju uštedu vremena, dosljedno izvršavanje (bez propuštenih koraka) i mogućnost kreiranja prečica i dugmadi za ubrzavanje procesa . Ako Excel koristite samo povremeno, možda se ne isplati; ali ako ga koristite svakodnevno, primijetit ćete razliku od prvog dana.

Omogućite karticu Razvojni programer (Windows i Mac)

Kartica Razvojni programer je prema zadanim postavkama skrivena. U Windowsu, kliknite desnim tasterom miša na traku i odaberite "Prilagodi traku". Označite "Razvojni programer" i kliknite U redu. Nakon toga, vidjet ćete karticu Razvojni programer pored Prikaza.

Na Mac računaru, idite na Excel > Postavke > Alatne trake i vrpca. Pod "Glavne kartice" odaberite "Programer" i sačuvajte. Ovo će vam dati pristup snimaču, VBA i kontrolama . Ako ste novi u Excelu, pregledajte osnovni vodič za Excel da biste se upoznali s vrpcom i njenim opcijama.

Kako kreirati makroe pomoću Snimatora

Snimač je savršen za početak. U Programer > Snimi makro (ili Alt+T+M+R na Windowsu), dodijelite opisni naziv. Zapamtite: naziv mora počinjati slovom, bez razmaka ili simbola.

Odaberite gdje želite sačuvati: "Ova radna sveska" će funkcionirati u većini slučajeva. Ako želite da uvijek bude dostupna, odaberite "Lična radna sveska makroa" i Excel će je kreirati/sačuvati u datoteci Personal.xlsb , koja se otvara skrivena s Excelom.

Opcionalno, dodajte prečicu. Da biste izbjegli konflikte (na primjer, gubitak Ctrl+Z), preporučuje se korištenje prečica sa Ctrl+Shift. Također možete napisati kratak, jasan opis (vrlo korisno kada imate mnogo makroa).

Kliknite OK i izvršite radnje koje će se snimiti. Na primjer: odaberite A1 i primijenite format "Procenat". Izbjegavajte nepotrebne klikove ili promjene odabira koje nisu dio predviđenog procesa, jer snimač snima gotovo sve.

Kada završite, zaustavite snimanje. Da biste ga pokrenuli, idite na Razvojni programer > Makroi (ili Alt+F8), odaberite makro i kliknite Pokreni. Vidjet ćete da Excel ponavlja korake istim redoslijedom.

Pokretanje, uređivanje i upravljanje makroima

Iz menija Programer > Makroi (Alt+F8) možete pokretati, uređivati ​​ili brisati makroe. Ako odaberete Uredi, otvorit će se Visual Basic Editor sa snimljenim kodom koji možete očistiti i optimizirati (često uključuje suvišne korake).

Dodjeljivanje makroa dugmetu ili obliku je vrlo praktično: umetnite oblik, kliknite desnim tasterom miša > Dodijeli makro, odaberite makro i gotovi ste. Kada kliknete na oblik, on će se odmah pokrenuti.

Da bi vam dugme bilo lako dostupno na alatnoj traci za brzi pristup, otvorite meni alatne trake (strelica prema dolje) > Više naredbi > pod "Naredbe dostupne u", odaberite "Makroi", dodajte svoje i promijenite ikonu ako želite. Sada ćete imati trajno dugme čak i kada niste u načinu rada za razvojne programere.

Ako trebate ponovo upotrijebiti makro u drugoj radnoj knjizi, možete kopirati modul iz Visual Basic Editora. Otvorite VBE (Alt+F11), prevucite modul u drugu otvorenu radnu knjigu ili koristite Izvoz/Uvoz .bas datoteke.

Programiranje makroa u Excelu pomoću Visual Basic (VBA) editora

Da biste otišli dalje od snimača, otvorite Editor pomoću Alt+F11. Umetnite modul (Umetni > Modul) i kreirajte svoju prvu potprogramu. Osnovni obrazac je Sub Name() … End Sub.

Na primjer, da biste prikazali poruku i upisali vrijednost u ćeliju A1, možete početi s nečim jednostavnim. Na ovaj način razumijete strukturu i kako pozvati makro iz Excela (Alt+F8) ili ga dodijeliti dugmetu, uz vrlo malo linija koda :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Još jedan brz primjer primjene procentualnog stila na trenutni odabir. Ovaj makro replicira ono što ste uradili sa snimačem , ali ste direktno upisali:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Idealna kombinacija je: snimite da biste ubrzali stvari, a zatim otvorite VBE da biste očistili nepotrebne korake, dodali varijable, uslove ili petlje. Na ovaj način, "doslovni" snimak pretvarate u robustan i ponovo upotrebljiv makro.

Apsolutne i relativne reference

Podrazumevano, snimač kreira makroe u apsolutnom režimu: uvek deluje na iste ćelije (na primer, B3) bez obzira na to gde se nalazite kada ga pokrećete. Ako podaci promene poziciju u vašem procesu, ovo možda nije ono što vam treba.

Sa opcijom "Koristi relativne reference", makro radi relativno u odnosu na aktivnu ćeliju kada počne izvršavanje. Na primjer, ako ste započeli snimanje u ćeliji B3 i upisali "Ime", "Prezime" itd., pomičući se udesno, kada se pokreće iz ćelije C4, upisat će se iste informacije u odnosu na ćelije C4, D4, E4 itd.

Tipičan primjer: dodavanje naslova tabeli koja ponekad počinje u ćeliji B3, a ponekad u ćeliji C5. Kod apsolutnog načina rada, uvijek bi se pisalo u ćeliju B3. Kod relativnog načina rada, prilagodit će se odabranoj početnoj ćeliji.

Uobičajene greške prilikom snimanja procesa

Uobičajena zamka je snimanje "povlačenja" formule, recimo, za tri reda, i pretpostavka da će to raditi za duže tabele. Snimač će registrovati nešto što je ekvivalentno " popuni tri ćelije do kraja ", a ne "do kraja tabele".

Rješenje je promijeniti pristup: koristiti dinamičke raspone (na primjer, detektirati posljednji red), petlje ili metodu CurrentRegion ili prilagoditi makro u VBA nakon snimanja tako da se skalira na bilo koju veličinu.

Dobre prakse prilikom snimanja

Prije snimanja, ručno prođite kroz proces nekoliko puta kako biste ga usavršili. Napravite kratke, specifične snimke umjesto jednog ogromnog; to ih čini lakšim za održavanje i kombinovanje.

Izbjegavajte nepotrebne klikove, pomicanje kroz vrpcu i odabire koji ne doprinose ničemu. Naziv i opis makroa neka budu jasni kako biste vi ili bilo koji kolega mogli odmah prepoznati njegovu svrhu.

Pokretanje makroa na mnogo načina

Pored dijaloga Makroi (Alt+F8), makroe možete pokrenuti pomoću oblika, dugmeta na traci sa alatkama za brzi pristup ili otvaranjem radne sveske. Excel čak može pokrenuti makro iz drugih Office aplikacija, što vam omogućava automatizaciju zadataka između aplikacija (na primjer, ažuriranje tabele i slanje e-pošte putem Outlooka).

Dobro razmislite o prečicama na tastaturi: ako makrou dodijelite Ctrl+Z, izgubit ćete mogućnost poništavanja dok je radna knjiga otvorena. Stoga se preporučuje korištenje kombinacija Ctrl+Shift kako biste izbjegli konflikte.

Spremanje radnih knjiga pomoću makroa

Kada datoteka sadrži VBA kod, sačuvajte je kao .xlsm datoteku kako bi se kod sačuvao i mogao pokrenuti. Spremanje kao .xlsx datoteke uništit će VBA projekt . Ako želite globalne makroe, koristite ličnu radnu knjigu makroa (Personal.xlsb).

Sigurnost i omogućavanje makroa

Makroi mogu biti blokirani ovisno o vašim sigurnosnim postavkama. Provjerite Centar za povjerenje da biste ih selektivno omogućili i nikada ne omogućavajte makroe iz nepoznatih izvora. Razumijevanje vašeg nivoa sigurnosti pomaže vam da uravnotežite zaštitu i produktivnost.

Dodijelite makroe dugmadima, oblicima i kontrolama

Da biste korisnicima omogućili automatsko pokretanje makroa, dodijelite makro dugmetu, obliku ili kontroli. Kliknite desnim tasterom miša na objekat > Dodijeli makro > odaberi. Također možete postaviti ikonu na traku ili traku sa alatkama za brzi pristup i povezati je sa makroom.

Ako koristite obrasce ili ActiveX kontrole, možete im dodijeliti makroe ili procedure događaja (na primjer, na klik). To omogućava korisnički prilagođenije interfejse i vođene procese.

Korisni primjeri makroa (VBA)

Ovi primjeri ilustruju uobičajene zadatke. Možete ih zalijepiti u modul (Alt+F11 > Umetni > Modul) i prilagoditi ih. Ne zaboravite potpisati radne sveske ili prilagoditi sigurnosne postavke ako vaša organizacija to zahtijeva kako biste ih mogli pokretati bez upozorenja.

Kopiraj odabrani sadržaj na drugu lokaciju na istom listu: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Ispis aktivnog lista: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Sačuvajte datoteku kao radnu svesku sa omogućenim makroima (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Primjena formatiranja na raspon (prilagođavanje raspona vašim potrebama): Sa Raspon(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Pronađite vrijednost u rasponu i prikažite prvo podudaranje: Raspon(…).Pronađi

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Praktičan slučaj: formatiranje kao procenat

Snimanje: Započnite snimanje, dajte mu ime, odaberite gdje će se sačuvati i opcionalno dodijelite prečicu i opis. Odaberite ćelije i primijenite "Procenat" iz kartice Početna. Zaustavite snimanje, a zatim ga ponovo pokrenite pomoću Alt+F8. Ako želite da ga reprodukujete s minimalističkim kodom, koristite Selection.Style = "Procenat" u VBA.

Savjet: Tokom snimanja, izbjegavajte nepotrebno prebacivanje ćelija. Ovi pokreti naprijed-nazad se snimaju i mogu usporiti i oštetiti makro.

Automatizirajte duge zadatke: mali makroi su bolji

Ako imate proces s mnogo koraka, razmislite o njegovoj podjeli na nekoliko manjih makroa i njihovom sekvencijalnom pozivanju. Ovo olakšava održavanje, omogućava vam ponovnu upotrebu blokova i pojednostavljuje otklanjanje grešaka.

Na primjer, jedan makro za pripremu podataka, drugi za formatiranje, treći za generiranje ispisa i treći za izvoz u PDF ili slanje e-poštom . Možete ih pozivati ​​jedan po jedan ili sve odjednom.

Uredite i očistite snimljeni kod

Snimač bilježi "sve": odabire, klikove i promjene tabulatora. Kada se VBE otvori, uklanja nepotrebne odabire (Odabir/Selekcija) zamjenjujući ih direktnim referencama (na primjer, Raspon("A1").Vrijednost = ... ) i grupira formate u blokove Sa/Završi sa.

Osim toga, dodaje varijable i osnovnu obradu grešaka. Nekoliko if naredbi i jednostavnih provjera sprječavaju greške kada je raspon prazan ili odabir nije onakav kakav se očekuje.

Često postavljana pitanja

Kako da kreiram korisnički obrazac (UserForm) u Excelu? Otvorite Visual Basic Editor (Alt+F11), idite na Umetanje (Insert) > Korisnički obrazac (UserForm) i dodajte kontrole (okvire za tekst, dugmad itd.). Možete ga prikazati pomoću makroa (makra).

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Odatle, dodijelite procedure događajima kontrola (na primjer, klik na dugme) kako biste izvršili logiku koja vam je potrebna.

Kako da zaštitim/uklonim zaštitu lista pomoću makroa? Koristite ove rutine promjenom lozinke po želji. One vam omogućavaju da aktivirate ili deaktivirate zaštitu jednim klikom.

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Kako da izbrišem makro? Idite na Programer > Makroi, odaberite makro i pritisnite Delete. Ako želite izbrisati sav kod iz modula, otvorite VBE (Alt+F11), pronađite modul u projektu, kliknite desnim tasterom miša i odaberite Ukloni modul (prvo ga možete izvesti ako ga želite sačuvati).

Još jedan praktičan savjet: Ako formatirate mjesečni izvještaj gdje kupci s neizmirenim dugovima trebaju biti označeni crvenom bojom i podebljani, snimite niz jednom, prilagodite VBA kod da se primjenjuje na ispravan raspon (koristeći detekciju posljednjeg reda ili filtere) i sačuvajte makro u svojoj ličnoj radnoj knjizi makroa. Sljedeći put, jednostavna prečica ili dugme će biti sve što je potrebno da biste formatiranje primijenili trenutno, besprijekorno, za nekoliko sekundi.