Programiranje makronaredbi u Excelu: Potpuni vodič korak po korak

Informatec Digital » programiranje » Programiranje makronaredbi u Excelu: Potpuni vodič korak po korak

Ako se nađete u situaciji da u Excelu ponavljate 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 izračuna, i pokrenuti ih kad god želite klikom ili prečacem na tipkovnici.

Kada stvorite makro, Excel bilježi vaše klikove i pritiske tipki te ih vjerno ponovno izvršava. Zatim možete prilagoditi kôd kako biste preciznije podesili izvršavanje i konzultirali resurse za Excel . Uobičajeno je započeti sa snimačem makroa, a zatim ga poboljšati pomoću VBA-e, postižući veću kontrolu, fleksibilnost i nula pogrešaka zbog nemara.

Što je zapravo makro u Excelu?

Makronaredba je popis instrukcija koje Excel izvršava definiranim redoslijedom . Može biti jednostavna poput primjene stila postotka ili složena poput pripreme izvješća s pivot tablicama , njegovog formatiranja, izrade radnih listova i slanja e-pošte u Outlooku. Makronaredbe se spremaju u vašu radnu knjigu 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. Unatoč tome, neko znanje VBA-e omogućit će vam stvaranje prilagođenih funkcija, obrazaca, pa čak i mini-aplikacija unutar Excela.

Zašto koristiti makroe? Prave prednosti

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

Njegove prednosti uključuju uštedu vremena, dosljedno izvršavanje (bez propuštenih koraka) i mogućnost stvaranja prečaca i gumba 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 sustavu Windows desnom tipkom miša kliknite vrpcu i odaberite "Prilagodi vrpcu". Označite "Razvojni programer" i kliknite U redu. Nakon toga, vidjet ćete karticu Razvojni programer pored Prikaza.

Na Mac računalu idite na Excel > Postavke > Alatne trake i vrpca. Pod "Glavne kartice" odaberite "Razvojni programer" i spremite. To će vam dati pristup snimaču, VBA-u i kontrolama . Ako ste novi u Excelu, pregledajte osnovni vodič za Excel kako biste se upoznali s vrpcom i njezinim mogućnostima.

Kako stvoriti makroe pomoću Snimača

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

Odaberite gdje spremiti: "Ova radna knjiga" će funkcionirati u većini slučajeva. Ako želite da uvijek bude dostupna, odaberite "Osobna radna knjiga makroa" i Excel će je stvoriti/spremiti u Personal.xlsb , koja se otvara skrivena s Excelom.

Po želji dodajte prečac. Kako biste izbjegli sukobe (na primjer, gubitak Ctrl+Z), preporučuje se korištenje prečaca s Ctrl+Shift. Također možete napisati kratak , jasan opis (vrlo korisno kada imate mnogo makronaredbi).

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

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 Razvojni 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 makronaredbe gumbu ili obliku vrlo je praktično: umetnite oblik, kliknite desnom tipkom miša > Dodijeli makronaredbu, odaberite makronaredbu i gotovi ste. Kada kliknete oblik, on će se odmah pokrenuti.

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

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

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

Za rad izvan snimača, otvorite Editor pomoću Alt+F11. Umetnite modul (Umetni > Modul) i stvorite svoju prvu potprogramu. Osnovni obrazac je Sub Name() … End Sub.

Na primjer, za prikaz poruke i upisivanje vrijednosti u ćeliju A1, možete početi s nečim jednostavnim. Na taj način razumijete strukturu i kako pozvati makronaredbu iz Excela (Alt+F8) ili je dodijeliti gumbu, s vrlo malo redaka koda :

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

Još jedan brz primjer primjene stila postotka na trenutni odabir. Ovaj makro replicira ono što ste učinili s recorderom , ali ste ga izravno upisali:

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

Idealna kombinacija je: snimiti kako bi se stvari ubrzale, a zatim otvoriti VBE kako bi se očistili nepotrebni koraci, dodale varijable, uvjeti ili petlje. Na taj način "doslovnu" snimku pretvarate u robusnu i ponovno upotrebljivu makronaredbu.

Apsolutne i relativne reference

Prema zadanim postavkama, snimač stvara makroe u apsolutnom načinu rada: uvijek djeluje na iste ćelije (na primjer, B3) bez obzira na to gdje se nalazite kada ga pokrećete. Ako podaci promijene položaj u vašem procesu, to možda nije ono što vam treba.

S opcijom "Koristi relativne reference", makro radi relativno u odnosu na aktivnu ćeliju kada započ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 isti podaci u odnosu na ćelije C4, D4, E4 itd.

Tipičan primjer: dodavanje naslova tablici koja ponekad počinje u ćeliji B3, a ponekad u ćeliji C5. S apsolutnim načinom rada, uvijek bi pisalo u ćeliju B3. S relativnim načinom rada, prilagodit će se odabranoj početnoj ćeliji.

Uobičajene pogreške prilikom snimanja procesa

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

Rješenje je promijeniti pristup: koristiti dinamičke raspone (na primjer, otkrivanje zadnjeg retka), 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 postupak nekoliko puta kako biste ga usavršili. Napravite kratke, specifične snimke umjesto jedne ogromne; to ih čini lakšim za održavanje i kombiniranje.

Izbjegavajte nepotrebne klikove, pomicanje po vrpci i odabire koji ne doprinose ničemu. Naziv i opis makronaredbe neka budu jasni kako biste vi ili bilo koji kolega mogli odmah prepoznati njezinu svrhu.

Pokretanje makroa na mnogo načina

Osim dijaloškog okvira Makroi (Alt+F8), makroe možete pokrenuti pomoću oblika, gumba na alatnoj traci za brzi pristup ili otvaranjem radne knjige. Excel čak može pokrenuti makro iz drugih aplikacija sustava Office, što vam omogućuje automatizaciju zadataka u više aplikacija (na primjer, ažuriranje tablice i slanje e-pošte putem Outlooka).

Dobro razmislite o tipkovničkim prečacima: 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 sukobe.

Spremanje radnih knjiga pomoću makronaredbi

Kada datoteka sadrži VBA kôd, spremite je kao .xlsm datoteku kako bi se kôd sačuvao i mogao se pokrenuti. Spremanjem kao .xlsx datoteke uništit ćete VBA projekt . Ako želite globalne makroe, upotrijebite osobnu radnu knjigu makroa (Personal.xlsb).

Sigurnost i omogućavanje makronaredbi

Makroi mogu biti blokirani ovisno o vašim sigurnosnim postavkama. Provjerite Centar za pouzdanost kako biste ih selektivno omogućili i nikada nemojte omogućavati makroe iz nepoznatih izvora. Razumijevanje vaše razine sigurnosti pomaže vam uravnotežiti zaštitu i produktivnost.

Dodijelite makroe gumbima, oblicima i kontrolama

Da biste korisnicima omogućili automatsko pokretanje makronaredbi, dodijelite makronaredbu gumbu, obliku ili kontroli. Desnom tipkom miša kliknite objekt > Dodijeli makronaredbu > odaberi. Također možete postaviti ikonu na vrpcu ili alatnu traku za brzi pristup i povezati je s makronaredbom.

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

Korisni primjeri makroa (VBA)

Ovi primjeri ilustriraju uobičajene zadatke. Možete ih zalijepiti u modul (Alt+F11 > Umetni > Modul) i prilagoditi ih. Ne zaboravite potpisati radne knjige 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: Odabir.Odredište kopiranja

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

Spremi datoteku kao radnu knjigu s 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 (prilagodba raspona vašim potrebama): Pomoću 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 postotak

Snimanje: Započnite snimanje, dajte mu naziv, odaberite gdje će se spremiti i po želji dodijelite prečac i opis. Odaberite ćelije i primijenite "Postotak" s kartice Početna. Zaustavite snimanje i zatim ga ponovno pokrenite pomoću Alt+F8. Ako želite reproducirati s minimalističkim kodom, upotrijebite Selection.Style = "Postotak" u VBA.

Savjet: Tijekom snimanja izbjegavajte nepotrebno prebacivanje ćelija. Ovi pokreti naprijed-natrag 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. To olakšava održavanje, omogućuje vam ponovnu upotrebu blokova i pojednostavljuje ispravljanje pogreš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/Selection) zamjenjujući ih izravnim referencama (na primjer, Raspon("A1").Value = ... ) i grupira formate u blokove S/Završi s.

Osim toga, dodaje varijable i osnovno rukovanje pogreškama. Nekoliko if naredbi i jednostavnih provjera sprječavaju pogreške kada je raspon prazan ili odabir nije onakav kakav se očekuje.

Često postavljana pitanja

Kako stvoriti korisnički obrazac u Excelu? Otvorite Visual Basic Editor (Alt+F11), idite na Umetanje > Korisnički obrazac i dodajte kontrole (okvire za tekst, gumbe itd.). Možete ga prikazati pomoću makronaredbe.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

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

Kako zaštititi/ukloniti zaštitu lista makroima? Koristite ove rutine promjenom lozinke po želji. Omogućuju vam aktiviranje ili deaktiviranje zaštite jednim klikom.

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

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

Kako izbrisati makro? Idite na Razvojni programer > Makroi, odaberite makro i pritisnite Izbriši. Ako želite izbrisati sav kod iz modula, otvorite VBE (Alt+F11), pronađite modul u projektu, kliknite desnom tipkom miša i odaberite Ukloni modul (prvo ga možete izvesti ako ga želite spremiti).

Još jedan praktični savjet: Ako formatirate mjesečno izvješće u kojem kupci s nepodmirenim dugima trebaju biti označeni crvenom bojom i podebljano, zabilježite slijed jednom, prilagodite VBA kod da se primijeni na ispravan raspon (koristeći otkrivanje zadnjeg retka ili filtere) i spremite makronaredbu u svoju osobnu radnu knjigu makronaredbi. Sljedeći put, jednostavan prečac ili gumb bit će sve što je potrebno da biste formatiranje primijenili trenutno, besprijekorno, u sekundama.