Programiranje makrov v Excelu: Celoten vodnik po korakih

Informatec Digital » programiranje » Programiranje makrov v Excelu: Celoten vodnik po korakih

Če se vam zgodi, da v Excelu vedno znova izvajate ista opravila, so makri vaš najboljši prijatelj za prihranek časa. Z njimi lahko avtomatizirate celotna zaporedja dejanj (več o programiranju VBA ), od oblikovanja do uvoza podatkov ali izvajanja izračunov, in jih sprožite kadar koli želite s klikom ali bližnjico na tipkovnici.

Ko ustvarite makro, Excel zabeleži vaše klike in pritiske tipk ter jih natančno ponovno izvede. Nato lahko kodo prilagodite, da natančno prilagodite izvajanje, in si ogledate vire za Excel . Običajno se začne s snemalnikom makrov in nato izboljša z VBA, s čimer se doseže večji nadzor, prilagodljivost in ničelne napake zaradi malomarnosti.

Kaj pravzaprav je makro v Excelu?

Makro je seznam ukazov, ki jih Excel izvede v določenem vrstnem redu . Lahko je tako preprost kot uporaba odstotka ali tako zapleten kot priprava poročila z vrtilnimi tabelami , njegovo oblikovanje, ustvarjanje delovnih listov in pošiljanje e-pošte v Outlooku. Makri so shranjeni v delovnem zvezku in jih je mogoče zagnati po potrebi.

Za začetek vam ni treba znati programirati: snemalnik pretvori vaša dejanja v kodo VBA. Kljub temu vam bo nekaj znanja VBA omogočilo ustvarjanje funkcij po meri, obrazcev in celo mini aplikacij v Excelu.

Zakaj uporabljati makre? Resnične prednosti

Glavni namen makra je preprečiti ponavljajoča se opravila in zmanjšati število napak . Če vsak mesec pripravite isto poročilo, vam bo makro prihranil mehanske korake in vam omogočil, da se osredotočite na tisto, kar je pomembno.

Njegove prednosti vključujejo prihranek časa, dosledno izvajanje (brez zamujenih korakov) in možnost ustvarjanja bližnjic in gumbov za pospešitev procesov . Če Excel uporabljate le občasno, se morda ne splača; če pa ga uporabljate vsak dan, boste razliko opazili že od prvega dne.

Omogočanje zavihka Razvijalec (Windows in Mac)

Zavihek Razvijalec je privzeto skrit. V sistemu Windows z desno tipko miške kliknite trak in izberite »Prilagodi trak«. Označite »Razvijalec« in kliknite V redu. Po tem boste poleg možnosti Pogled videli zavihek Razvijalec.

V računalniku Mac pojdite v Excel > Nastavitve > Orodne vrstice in trak. V razdelku »Glavni zavihki« izberite »Razvijalec« in shranite. Tako boste imeli dostop do snemalnika, VBA in kontrolnikov . Če ste novi v Excelu, si oglejte osnovni vodnik po Excelu , da se seznanite s trakom in njegovimi možnostmi.

Kako ustvariti makre s snemalnikom

Snemalnik je odličen za začetek. V Razvijalec > Posnemi makro (ali Alt+T+M+R v sistemu Windows) dodelite opisno ime. Ne pozabite: ime se mora začeti s črko, brez presledkov ali simbolov.

Izberite mesto za shranjevanje: »Ta delovni zvezek« bo deloval v večini primerov. Če želite, da bo vedno na voljo, izberite »Osebni delovni zvezek z makri« in Excel ga bo ustvaril/shranil v datoteko Personal.xlsb , ki se odpre skrita skupaj z Excelom.

Po želji dodajte bližnjico. Da bi se izognili konfliktom (na primer izgubi Ctrl+Z), je priporočljivo uporabljati bližnjice s Ctrl+Shift. Lahko pa napišete tudi kratek, jasen opis (zelo uporabno, če imate veliko makrov).

Kliknite V redu in izvedite dejanja, ki jih želite zabeležiti. Na primer: izberite A1 in uporabite obliko »Odstotek«. Izogibajte se nepotrebnim klikom ali spremembam izbire, ki niso del predvidenega postopka, saj snemalnik beleži skoraj vse.

Ko končate, ustavite snemanje. Če ga želite zagnati, pojdite v Razvijalec > Makri (ali Alt+F8), izberite makro in kliknite Zaženi. Excel bo ponovil korake v popolnoma enakem vrstnem redu.

Zagon, urejanje in upravljanje makrov

V Razvijalec > Makri (Alt+F8) lahko zaženete, uredite ali izbrišete makre. Če izberete Uredi, se bo odprl urejevalnik Visual Basic s posneto kodo, ki jo lahko očistite in optimizirate (pogosto vključuje odvečne korake).

Dodelitev makra gumbu ali obliki je zelo priročna: vstavite obliko, kliknite z desno tipko miške > Dodeli makro, izberite makro in končali ste. Ko kliknete obliko, se bo zagnal takoj.

Če želite, da bo gumb vedno na voljo v orodni vrstici za hitri dostop, odprite meni orodne vrstice (puščica navzdol) > Več ukazov > v razdelku »Ukazi, ki so na voljo v« izberite »Makri«, dodajte svojega in po želji spremenite ikono. Zdaj boste imeli stalen gumb, tudi ko niste v načinu za razvijalce.

Če morate makro ponovno uporabiti v drugem delovnem zvezku, lahko modul kopirate iz urejevalnika Visual Basic. Odprite VBE (Alt+F11), povlecite modul v drug odprt delovni zvezek ali uporabite možnost Izvozi/Uvozi datoteko .bas.

Programiranje makrov v Excelu z urejevalnikom Visual Basic (VBA)

Če želite iti dlje od snemalnika, odprite urejevalnik s kombinacijo tipk Alt+F11. Vstavite modul (Vstavi > Modul) in ustvarite svojo prvo podprogramo. Osnovni vzorec je Sub Name() … End Sub.

Na primer, če želite prikazati sporočilo in zapisati vrednost v celico A1, lahko začnete z nečim preprostim. Tako boste z zelo malo vrsticami kode razumeli strukturo in kako poklicati makro iz Excela (Alt+F8) ali ga dodeliti gumbu :

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

Še en hiter primer uporabe odstotka za trenutni izbor. Ta makro ponovi tisto, kar ste naredili z snemalnikom , vendar ste ga neposredno vnesli:

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

Idealna kombinacija je: najprej posnemite, da pospešite stvari, nato pa odprite VBE, da odstranite nepotrebne korake, dodate spremenljivke, pogoje ali zanke. Na ta način "dobesedni" posnetek spremenite v robusten in ponovno uporaben makro.

Absolutne in relativne reference

Snemalnik privzeto ustvarja makre v absolutnem načinu: vedno deluje na iste celice (na primer B3), ne glede na to, kje se nahajate med izvajanjem. Če podatki spremenijo položaj v vašem procesu, to morda ni tisto, kar potrebujete.

Z možnostjo »Uporabi relativne reference« makro deluje relativno glede na aktivno celico, ko se izvajanje začne. Če ste na primer začeli snemati v celici B3 in vnesli »Ime«, »Priimek« itd., se premaknete v desno, bo pri zagonu iz celice C4 vnesel iste podatke glede na celice C4, D4, E4 itd.

Tipičen primer: dodajanje naslovov tabeli, ki se včasih začne v celici B3 in včasih v celici C5. Pri absolutnem načinu bi vedno pisalo v celico B3. Pri relativnem načinu se bo prilagodilo izbrani začetni celici.

Pogoste napake pri snemanju procesov

Pogosta past je snemanje formule »vleci« za, recimo, tri vrstice in prepričanje, da bo delovalo za daljše tabele. Snemalnik bo zabeležil nekaj, kar je enakovredno » zapolni tri celice navzdol «, ne pa »do konca tabele«.

Rešitev je v spremembi pristopa: uporabite dinamične obsege (na primer zaznavanje zadnje vrstice), zanke ali metodo CurrentRegion ali pa makro v VBA po snemanju prilagodite tako, da se lahko skalira na poljubno velikost.

Dobre prakse pri snemanju

Pred snemanjem postopek nekajkrat ročno ponovite, da ga izpopolnite. Namesto enega velikega posnetka naredite kratke, specifične posnetke ; tako jih je lažje vzdrževati in kombinirati.

Izogibajte se nepotrebnim klikom, pomikanju po traku in izbiram, ki ne prispevajo ničesar. Ime in opis makra naj bosta jasna, da boste vi ali kateri koli sodelavec lahko takoj prepoznali njegov namen.

Zaženite makre na več načinov

Poleg pogovornega okna Makri (Alt+F8) lahko makre zaženete tudi z obliko, gumbom v orodni vrstici za hitri dostop ali z odpiranjem delovnega zvezka. Excel lahko makro zažene celo iz drugih Officeovih aplikacij, kar vam omogoča avtomatizacijo opravil med aplikacijami (na primer posodabljanje tabele in pošiljanje e-pošte prek Outlooka).

Dobro premislite o bližnjicah na tipkovnici: če makru dodelite Ctrl+Z, boste izgubili možnost razveljavitve, ko je delovni zvezek odprt. Zato je priporočljivo uporabljati kombinacije Ctrl+Shift, da se izognete konfliktom.

Shranjevanje delovnih zvezkov z makri

Ko datoteka vsebuje kodo VBA, jo shranite kot datoteko .xlsm, da se koda ohrani in jo je mogoče zagnati. Shranjevanje kot datoteko .xlsx bo uničilo projekt VBA . Če želite globalne makre, uporabite osebni delovni zvezek z makri (Personal.xlsb).

Varnost in omogočanje makrov

Makri so lahko blokirani glede na vaše varnostne nastavitve. Preverite Center za zaupanje, da jih omogočite selektivno, in nikoli ne omogočite makrov iz neznanih virov. Razumevanje ravni varnosti vam pomaga uravnotežiti zaščito in produktivnost.

Dodeljevanje makrov gumbom, oblikam in kontrolnikom

Če želite uporabnikom omogočiti samodejno izvajanje makrov, dodelite makro gumbu, obliki ali kontrolniku. Z desno tipko miške kliknite predmet > Dodeli makro > izberi. Ikono lahko postavite tudi na trak ali v orodno vrstico za hitri dostop in jo povežete z makrom.

Če uporabljate obrazce ali kontrolnike ActiveX, jim lahko dodelite makre ali procedure dogodkov (na primer ob kliku). To omogoča uporabniku prijaznejše vmesnike in vodene postopke.

Uporabni primeri makrov (VBA)

Ti primeri ponazarjajo pogosta opravila. Lahko jih prilepite v modul (Alt+F11 > Vstavi > Modul) in jih prilagodite. Ne pozabite podpisati delovnih zvezkov ali prilagoditi varnostnih nastavitev, če vaša organizacija to zahteva, da jih lahko zaženete brez opozoril.

Kopiraj izbrano vsebino na drugo mesto na istem listu: Izbor.Cilj 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

Natisni aktivni list: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Shranite datoteko kot delovni zvezek z omogočenimi makri (.xlsm): ActiveWorkbook.SaveAs

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

Uporaba oblikovanja za obseg (prilagodite obseg svojim potrebam): Z Obseg(…)

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

Poiščite vrednost v obsegu in prikažite prvo ujemanje: Območje(…).Poišč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čni primer: oblikovanje v odstotkih

Snemanje: Začnite s snemanjem, ga poimenujte, izberite mesto za shranjevanje in po želji dodelite bližnjico in opis. Izberite celice in na zavihku Domov uporabite možnost »Odstotek«. Ustavite snemanje in ga nato znova zaženite s kombinacijo tipk Alt+F8. Če želite predvajati z minimalistično kodo, v VBA uporabite Selection.Style = »Odstotek« .

Nasvet: Med snemanjem se izogibajte nepotrebnemu preklapljanju celic. Ta premikanja naprej in nazaj se posnamejo in lahko upočasnijo in poškodujejo makro.

Avtomatizirajte dolga opravila: manjši makri so boljši

Če imate proces z veliko koraki, razmislite o njegovi razdelitvi na več manjših makrov in njihovem zaporednem klicanju. To olajša vzdrževanje, omogoča ponovno uporabo blokov in poenostavi odpravljanje napak.

Na primer, en makro za pripravo podatkov, drug za oblikovanje, tretji za ustvarjanje izpisa in tretji za izvoz v PDF ali pošiljanje po e-pošti . Pokličete jih lahko enega za drugim ali vse hkrati.

Uredite in očistite posneto kodo

Snemalnik zajame "vse": izbire, klike in spremembe tabulatorjev. Ko se VBE odpre, odstrani nepotrebne izbire (Select/Selection) tako, da jih nadomesti z neposrednimi sklici (na primer Range("A1").Value = ... ) in združi oblike v bloke With/End With.

Poleg tega dodaja spremenljivke in osnovno obravnavo napak. Nekaj ​​stavkov if in preprostih preverjanj preprečuje napake, ko je obseg prazen ali izbor ni takšen, kot je bilo pričakovano.

Pogosto zastavljena vprašanja

Kako ustvarim uporabniški obrazec v Excelu? Odprite urejevalnik Visual Basic (Alt+F11), pojdite na Vstavi > Uporabniški obrazec in dodajte kontrolnike (polja z besedilom, gumbe itd.). Prikažete ga lahko z makrom.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Od tam naprej dodelite procedure dogodkom kontrolnikov (na primer kliku gumba), da izvedete logiko, ki jo potrebujete.

Kako zaščitim/odstranim zaščito lista z makri? Uporabite te rutine tako, da po želji spremenite geslo. Omogočajo vam , da zaščito aktivirate ali deaktivirate z enim samim klikom.

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

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

Kako izbrišem makro? Pojdite v Razvijalec > Makri, izberite makro in pritisnite Izbriši. Če želite izbrisati vso kodo iz modula, odprite VBE (Alt+F11), poiščite modul v projektu, kliknite z desno tipko miške in izberite Odstrani modul (če ga želite shraniti, ga lahko najprej izvozite).

Še zadnji praktični nasvet: če oblikujete mesečno poročilo, kjer naj bodo stranke z neporavnanimi zneski označene z rdečo in krepko pisavo, zaporedje enkrat zabeležite, prilagodite kodo VBA, da se uporabi za pravilen obseg (z zaznavanjem zadnje vrstice ali filtri), in shranite makro v svoj osebni delovni zvezek z makri. Naslednjič bo za takojšnjo, brezhibno in v nekaj sekundah potrebna preprosta bližnjica ali gumb, da boste oblikovanje uporabili takoj, brezhibno in brez težav.