Programiranje makrov v Excelu: Celoten vodnik po korakih

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

Če v Excelu vedno znova ponavljate naloge, so makri vaš najboljši zaveznik, da ne boste več izgubljali časa. Z njimi lahko avtomatizirati celotna zaporedja dejanj (več o Programiranje 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 izvede. Nato lahko kodo prilagodite, da natančneje nastavite izvajanje in se posvetujete z viri za ExcelObičajno se začne s snemalnikom in nato prilagodi z VBA, pri čemer se dobi večji nadzor, prilagodljivost in nič napak zaradi malomarnosti.

Kaj pravzaprav je makro v Excelu?

Makro je seznam ukazov, ki jih Excel izvaja v določenem vrstnem reduLahko je tako preprosto kot uporaba odstotka ali tako zapleteno kot pripravite poročilo z vrtilnimi tabelami, ga formatirajte, ustvarite preglednice in pošljite e-pošto v Outlooku. Makri so shranjeni v delovnem zvezku in jih lahko zaženete, kadar koli jih potrebujete.

Za začetek vam ni treba znati programirati: snemalnik pretvori vaša dejanja v kodo VBA. Vendar pa vam bo poznavanje VBA omogočilo Ustvarjanje funkcij po meri, obrazcev in celo programčkov v Excelu.

Zakaj uporabljati makre? Resnične prednosti

Glavni namen makra je izogibajte se ponavljajočemu se delu in zmanjšajte napakeČe vsak mesec pripravite isto poročilo, vam makro prihrani mehanske korake in vam omogoča, da se osredotočite na tisto, kar je pomembno.

Med njegovimi prednostmi so prihranek časa, dosledna izvedba (brez pozabljanja) in možnost ustvarjanja bližnjice in gumbi za pospešitev procesovČe Excel uporabljate 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)

Trak za razvijalce je privzeto skrit. V sistemu Windows z desno tipko miške kliknite trak in izberite »Prilagodi trak«. Izberite »Razvijalec« in sprejmite. Po tem Zavihek Razvijalec boste videli poleg Viste..

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 orodne vrstice. snemalnik, dostop do VBA in kontrolnikovČe šele začenjate, si oglejte osnovno delo z Excelom da vas vodijo skozi trak in možnosti.

Kako ustvariti makre s snemalnikom

Snemalnik je odlično mesto za začetek. V razdelku Programmer > Record Macro (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 delovalo v večini primerov. Če želite, da je vedno na voljo, izberite »Osebni delovni zvezek makrov« in Excel ga bo ustvaril/shranil tja. Osebno.xlsb, ki se odpre skrito z Excelom.

Po želji dodajte bližnjico. Da bi se izognili konfliktom (npr. izgubi Ctrl+Z), je priporočljivo uporabljati kombinacije s Ctrl+Shift. Bližnjico lahko tudi vnesete. kratek in 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 zapisa »Odstotek«. Izogibajte se nepotrebnim klikom ali spremembam izbire, ki niso del predvidenega postopka, ker snemalnik snema skoraj vse.

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

Zagon, urejanje in upravljanje makrov

V Programer > 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 uredite. očistite in optimizirajte (običajno 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 to je to. Ko kliknete obliko, bo izvedeno takoj.

Da ga boste imeli vedno pri roki v orodni vrstici za hitri dostop, odprite meni vrstice (puščica navzdol) > Več ukazov > pod »Ukazi, ki so na voljo v« izberite »Makri«, dodajte svojega in po želji spremenite ikono. Zdaj boste imeli trajni gumb tudi če nisi v Programmerju.

Č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 Izvoz/uvoz datoteke .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 podrutino. Osnovni vzorec je Ime podnaslova() … Konec podnaslova.

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

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 ponovite, kar ste naredili s snemalnikom, ampak zapisano neposredno:

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 makro. robusten in za večkratno uporabo.

Absolutne in relativne reference

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

Z možnostjo »Uporabi relativne reference« makro ob začetku delovanja deluje relativno glede na aktivno celico. Če ste na primer začeli snemati v celici B3 in vnesli »Ime«, »Priimek« itd., se premaknili v desno, bo pri zagonu iz celice C4 zapisal iz C4, D4, E4… relativno.

Tipičen primer: naslov tabele, ki se včasih začne v B3 in včasih v C5. Z absolutnim načinom bi vedno pisali v B3. Z relativnim načinom se bo prilagodil izbrana začetna celica.

Pogoste napake pri snemanju procesov

Pogosta past je snemanje vlečenja formule za, recimo, tri vrstice in prepričanje, da bo delovalo za daljše tabele. Snemalnik bo posnel nekaj enakovrednega "izpolnite tri celice navzdol", ne "do konca mize".

Rešitev je v spremembi pristopa: uporabi dinamični razponi (npr. zaznavanje zadnje vrstice), zanke ali metoda CurrentRegion ali pa prilagodite makro v VBA po snemanju, tako da prilagodi se poljubni velikosti.

Dobre prakse pri snemanju

Pred snemanjem nekajkrat izvedite ročni postopek, da ga izpilite. Posnemite posnetke. kratko in specifično namesto enega ogromnega; to jih olajša vzdrževanje in kombiniranje.

Izogibajte se nepotrebnim klikom, nepotrebnemu pomikanju in nepomembnim izbiram. Ime in opis makra naj bosta jasna, da ju boste vi ali kateri koli soigralec lahko razumeli. njegov namen takoj prepoznaš.

Zaženite makre na več načinov

Poleg polja Makri (Alt+F8) lahko makre zaženete tudi z obliko, gumbom v orodni vrstici za hitri dostop ali ko odprete delovni zvezek. Excel lahko makro zažene celo iz drugih Officeovih aplikacij, kar vam omogoča, da avtomatizirajte naloge v več aplikacijah (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 z Ctrl + Shift da bi se izognili konfliktom.

Shranjevanje delovnih zvezkov z makri

Ko datoteka vsebuje VBA, jo shranite kot .xlsm, da se koda ohrani in jo je mogoče izvesti. Če jo shranite kot .xlsx, izgubili boste 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 selektivno omogočite, in nikoli ne aktivirajte makrov iz neznanih virov. Poznavanje vaše ravni varnosti vam pomaga uravnoteženje zaščite in produktivnosti.

Dodeljevanje makrov gumbom, oblikam in kontrolnikom

Da jih uporabnik izvede brez razmišljanja, dodelite makro gumbu, obliki ali kontrolniku. Z desno tipko miške kliknite na predmet > Dodeli makro > izberi. Lahko tudi postavite ikono na trak ali v vrstico za hitri dostop in ga povežite z makrom.

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

Uporabni primeri makrov (VBA)

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

Kopirajte izbrano vsebino na drugo mesto na istem listu: Cilj izbire.Kopiraj

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.Izpis

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

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

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

Oblikujte obseg (prilagodite obseg svojim potrebam): Z dosegom(…)

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: Obseg(…).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ženite snemanje, ga poimenujte, izberite mesto za shranjevanje in po želji dodelite bližnjico in opis. Izberite celice in v meniju Start uporabite možnost »Odstotek«. Ustavite snemanje in ga nato zaženite s kombinacijo tipk Alt+F8. Če ga želite predvajati z minimalistično kodo, uporabite Izbor.Slog = "Odstotek" v VBA.

Nasvet: Med beleženjem ne menjajte celic brez razloga. Ti prihodi in odhodi se beležijo in jih je mogoče upočasni in umaži makro.

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

Če imate postopek z veliko koraki, razmislite o tem, da ga razdelite na več manjših makrov in jih kličete v verigi. To olajša vzdrževanje in vam omogoča, da blokov za ponovno uporabo in poenostavi odpravljanje napak.

Na primer, en makro za pripravo podatkov, drugi za oblikovanje, tretji za ustvarjanje lista za tiskanje in tretji za izvoz v PDF ali poslano prek emailaLahko jih pokličete enega za drugim ali vse hkrati.

Uredite in očistite posneto kodo

Snemalnik zajame "vse": izbire, klike in spremembe tabulatorjev. Ko odprete VBE, odstrani nepotrebne izbire (Izberi/Izbira) in jih nadomesti z neposrednimi sklici (npr. Območje("A1").Vrednost = …) in združuje formate v bloke Z/Končaj z.

Doda tudi spremenljivke in osnovno obravnavo napak. Nekaj ​​stavkov If in preprostih preverjanj preprečuje napake, ko je obseg prazen ali izbor ni takšen, kot sem pričakoval.

Pogosto zastavljena vprašanja

Kako ustvarim obrazec (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 jih lahko z makrom:

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Od tam naprej dodelite procedure dogodkom kontrolnikov (na primer klik gumba), tako da izvedite logiko, ki jo potrebujete.

Kako zaščitim/odstranim zaščito lista z makri? S temi rutinami lahko spremenite geslo po svojih željah. Omogočajo vam, da aktivirajte ali odstranite zaščito z enim 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 Programmer > Makri, izberite makro in pritisnite tipko Delete. Če želite izbrisati vso kodo v modulu, odprite VBE (Alt+F11), poiščite modul v projektu, kliknite z desno tipko miške in nato izberite Izbriši. Odstrani modul (če ga želite shraniti, ga lahko najprej izvozite).

Še zadnja praktična opomba: če oblikujete mesečno poročilo, kjer naj bodo stranke z dolgovi označene z rdečo in krepko pisavo, zaporedje enkrat zabeležite, prilagodite kodo VBA, da jo uporabite za pravilen obseg (z zaznavanjem zadnje vrstice ali filtri), in shranite makro v svoj osebni delovni zvezek z makri. Naslednjič bo za takojšnjo uporabo oblikovanja dovolj bližnjica ali gumb. brez napak in v nekaj sekundah.