Makrode programmeerimine Excelis: täielik samm-sammult juhend

Informatec Digital » programmeerimine » Makrode programmeerimine Excelis: täielik samm-sammult juhend

Kui avastad, et teed Excelis samu ülesandeid korduvalt, on makrod sinu parimad sõbrad aja kokkuhoiuks. Nende abil saad automatiseerida terveid toimingujadasid (lisateavet VBA programmeerimise kohta leiate siit ), alates vormindamisest kuni andmete importimise või arvutuste tegemiseni, ning käivitada need igal ajal klõpsu või kiirklahviga.

Makro loomisel salvestab Excel teie klõpsud ja klahvivajutused ning käivitab need usaldusväärselt uuesti. Seejärel saate koodi täpsustada, et täitmist täpsustada, ja konsulteerida Exceli ressurssidega . Tavaline on alustada makrosalvestiga ja seejärel seda VBA-ga täiustada, saavutades suurema kontrolli, paindlikkuse ja hooletusest tulenevate vigade puudumise.

Mis täpselt on makro Excelis?

Makro on käskude loend, mida Excel kindlas järjekorras täidab . See võib olla nii lihtne nagu protsendistiili rakendamine või nii keeruline nagu aruande ettevalmistamine liigendtabelitega , selle vormindamine, töölehtede loomine või e-kirja saatmine Outlookis. Makrod salvestatakse teie töövihikusse ja neid saab käivitada alati, kui vaja.

Alustamiseks ei pea programmeerimisoskusi tundma: diktofon tõlgib teie toimingud VBA-koodiks. Sellegipoolest võimaldavad mõned VBA-alased teadmised teil Excelis luua kohandatud funktsioone, vorme ja isegi minirakendusi.

Miks kasutada makrosid? Tegelikud eelised

Makro peamine eesmärk on vältida korduvaid ülesandeid ja minimeerida vigu . Kui koostate iga kuu sama aruande, säästab makro teid mehaanilistest sammudest ja võimaldab teil keskenduda olulisele.

Selle eeliste hulka kuuluvad aja kokkuhoid, järjepidev teostus (ilma vahelejäänud sammudeta) ning võimalus luua otseteid ja nuppe protsesside kiirendamiseks . Kui kasutate Exceli vaid aeg-ajalt, ei pruugi see olla seda väärt; aga kui kasutate seda iga päev, märkate erinevust esimesest päevast alates.

Arendaja vahekaardi lubamine (Windows ja Mac)

Arendaja vahekaart on vaikimisi peidetud. Windowsis paremklõpsake lindil ja valige „Kohanda linti“. Märkige ruut „Arendaja“ ja klõpsake nuppu OK. Pärast seda näete arendaja vahekaarti vaate kõrval.

Macis minge Exceli > Eelistused > Tööriistaribad ja lint. Valige jaotisest „Peamised vahekaardid” „Arendaja” ja salvestage. See annab teile juurdepääsu diktofonile, VBA-le ja juhtelementidele . Kui olete Exceli kasutamises uus, vaadake üle Exceli põhijuhend, et tutvuda lindi ja selle valikutega.

Kuidas luua makrosid diktofoniga

Diktofon on alustamiseks ideaalne. Määrake kirjeldav nimi menüüs Arendaja > Salvesta makro (või Windowsis Alt+T+M+R). Pidage meeles: nimi peab algama tähega, tühikute ja sümboliteta.

Valige salvestuskoht: enamasti sobib suvand „See töövihik”. Kui soovite selle alati kättesaadavaks teha, valige „Isiklik makrotöövihik” ja Excel loob/salvestab selle faili Personal.xlsb , mis avaneb Excelis peidetult.

Soovi korral saate lisada otsetee. Konfliktide vältimiseks (näiteks Ctrl+Z kaotsiminek) on soovitatav kasutada otseteid koos Ctrl+Shift-klahvikombinatsiooniga. Samuti võite kirjutada lühikese ja selge kirjelduse (väga kasulik, kui teil on palju makrosid).

Klõpsake nuppu OK ja tehke salvestatavad toimingud. Näiteks: valige A1 ja rakendage vormingut „Protsent”. Vältige ebavajalikke klõpse või valikumuudatusi, mis ei ole osa kavandatud protsessist, sest salvesti salvestab peaaegu kõik.

Kui olete lõpetanud, peatage salvestamine. Selle käivitamiseks minge menüüsse Arendaja > Makrod (või vajutage klahvikombinatsiooni Alt+F8), valige makro ja klõpsake nuppu Käivita. Näete, kuidas Excel kordab samme täpselt samas järjekorras.

Makrode käitamine, muutmine ja haldamine

Makrosid saab käivitada, redigeerida või kustutada menüüst Arendaja > Makrod (Alt+F8). Kui valite Redigeeri, avaneb Visual Basic Editor salvestatud koodiga, mida saab puhastada ja optimeerida (see sisaldab sageli üleliigseid samme).

Makro määramine nupule või kujule on väga mugav: sisesta kujund, paremklõpsa > Määra makro, vali makro ja ongi valmis. Kujundile klõpsates käivitub see kohe.

Selle kiirpääsu tööriistaribal hõlpsasti kättesaadavaks tegemiseks avage tööriistariba menüü (allanool) > Rohkem käske > valige jaotisest „Saadaval olevad käsud” „Makrod”, lisage oma käsud ja muutke ikooni soovi korral. Nüüd on teil püsiv nupp ka siis, kui te pole arendajarežiimis.

Kui teil on vaja makrot teises töövihikus uuesti kasutada, saate mooduli Visual Basic Editorist kopeerida. Avage VBE (Alt+F11), lohistage moodul teise avatud töövihikusse või kasutage käsku Ekspordi/Impordi .bas-fail.

Makrode programmeerimine Excelis Visual Basic (VBA) redaktoriga

Diktofonist kaugemale minemiseks avage redaktor klahvikombinatsiooniga Alt+F11. Lisage moodul (Insert > Module) ja looge oma esimene alamprogramm. Põhimuster on Sub Name() … End Sub.

Näiteks teate kuvamiseks ja väärtuse kirjutamiseks lahtrisse A1 võiksite alustada millegi lihtsaga. Nii saate aru struktuurist ja sellest, kuidas makrot Excelist (Alt+F8) kutsuda või selle nupule määrata, kasutades väga vähe koodiridasid :

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

Veel üks kiire näide protsendistiili rakendamisest praegusele valikule. See makro kordab seda, mida tegite diktofoniga , aga tippisite otse:

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

Ideaalne kombinatsioon on järgmine: salvesta asjade kiirendamiseks ja seejärel ava VBE ebavajalike sammude eemaldamiseks, muutujate, tingimuste või tsüklite lisamiseks. Nii muudad "sõnasõnalise" salvestuse robustseks ja korduvkasutatavaks makroks.

Absoluutsed vs. suhtelised viited

Vaikimisi loob diktofon makrosid absoluutrežiimis: see toimib alati samadele lahtritele (näiteks B3), olenemata sellest, kus te selle käivitamise ajal asute. Kui andmete positsioon teie protsessis muutub, ei pruugi see olla see, mida vajate.

„Kasuta suhtelisi viiteid” korral töötab makro täitmise alustamisel aktiivse lahtri suhtes. Näiteks kui alustasite salvestamist lahtris B3 ja tippisite „Eesnimi”, „Perekonnanimi” jne, siis liikudes paremale, tippib see lahtrist C4 käivitades sama teabe lahtrite C4, D4, E4 jne suhtes .

Tüüpiline näide: pealkirjade lisamine tabelile, mis mõnikord algab lahtrist B3 ja mõnikord lahtrist C5. Absoluutrežiimis kirjutatakse alati lahtrisse B3. Suhterežiimis kohandub see valitud alguslahtriga.

Levinud vead protsesside registreerimisel

Levinud lõks on valemi salvestamine ja "lohistamine" näiteks kolme rea ulatuses ning arvamine, et see töötab ka pikemate tabelite puhul. Salvesti registreerib midagi samaväärset käsuga " täida kolm lahtrit allapoole ", mitte "tabeli lõpuni".

Lahendus on lähenemisviisi muutmine: dünaamiliste vahemike kasutamine (näiteks viimase rea tuvastamine), tsüklite või meetodi CurrentRegion kasutamine või makro VBA-s salvestamise järel selle skaleerimiseks mis tahes suurusele kohandamine.

Head tavad salvestamisel

Enne salvestamist käivita protsess käsitsi paar korda, et seda täpsustada. Tee lühikesi ja spetsiifilisi salvestisi ühe suure asemel; see muudab nende haldamise ja kombineerimise lihtsamaks.

Väldi ebavajalikke klikke, lindi kerimist ja valikuid, mis ei lisa midagi. Hoia makro nimi ja kirjeldus selged, et sina või mõni kolleeg saaksid selle eesmärgi koheselt tuvastada.

Makrode käitamine mitmel viisil

Lisaks dialoogiboksile Makrod (Alt+F8) saate makrosid käivitada kujundi, kiirpääsuriba nupu või töövihiku avamise abil. Saate isegi lasta Excelil makro käivitada teistest Office'i rakendustest, mis võimaldab teil automatiseerida rakendustevahelisi ülesandeid (näiteks tabeli värskendamine ja meili saatmine Outlooki kaudu).

Mõelge hoolikalt kiirklahvidele: kui määrate makrole klahvikombinatsiooni Ctrl+Z, kaotate töövihiku avatud olekus tagasivõtmise võimaluse. Seetõttu on konfliktide vältimiseks soovitatav kasutada klahvikombinatsioone Ctrl+Shift .

Töövihikute salvestamine makrodega

Kui fail sisaldab VBA-koodi, salvestage see .xlsm-failina, et kood säiliks ja seda saaks käivitada. .xlsx-failina salvestamine hävitab VBA-projekti . Globaalsete makrode kasutamiseks kasutage isiklikku makrode töövihikut (Personal.xlsb).

Turvalisus ja makrode lubamine

Makrod võivad olla teie turvasätetest olenevalt blokeeritud. Kontrollige usalduskeskust, et lubada need valikuliselt, ja ärge kunagi lubage tundmatutest allikatest pärit makrosid. Turvataseme mõistmine aitab teil tasakaalustada kaitset ja tootlikkust.

Makrode määramine nuppudele, kujunditele ja juhtelementidele

Makrode automaatseks käivitamiseks määrake makro nupule, kujundile või juhtelemendile. Paremklõpsake objektil > Määra makro > valige. Samuti saate ikooni paigutada lindile või kiirpääsuribale ja selle makroga linkida.

Vormide või ActiveX-juhtelementide kasutamisel saate neile määrata makrosid või sündmuste protseduure (näiteks klõpsamisel). See võimaldab kasutajasõbralikumaid liideseid ja juhendatud protsesse.

Kasulikud makro näited (VBA)

Need näited illustreerivad levinud ülesandeid. Saate need moodulisse kleepida (Alt+F11 > Insert > Moodul) ja neid kohandada. Ärge unustage töövihikud allkirjastada või turvaseadeid kohandada, kui teie organisatsioon seda nõuab, et saaksite neid hoiatusteta käivitada.

Kopeeri valitud sisu sama lehe teise kohta: 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

Aktiivse lehe printimine: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Salvestage fail makrotoega töövihikuna (.xlsm): ActiveWorkbook.SaveAs

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

Rakenda vahemikule vormindust (kohanda vahemikku vastavalt oma vajadustele): Vahemiku abil (…)

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

Leia vahemikust väärtus ja kuva esimene vaste: Vahemik(…).Otsi

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

Praktiline juhtum: vormindamine protsentides

Salvestamine: Alusta salvestamist, pane sellele nimi, vali salvestuskoht ja soovi korral määra otsetee ja kirjeldus. Vali lahtrid ja rakenda menüü Avaleht valikut „Protsent”. Peata salvestamine ja seejärel käivita see uuesti klahvikombinatsiooniga Alt+F8. Kui soovid salvestamist minimalistliku koodiga esitada, kasuta VBA-s Selection.Style = "Protsent" .

Näpunäide: Salvestamise ajal vältige tarbetut lahtrite vahetamist. Need edasi-tagasi liikumised salvestatakse ja võivad makrot aeglustada ja rikkuda.

Automatiseerige pikki ülesandeid: väikesed makrod on paremad

Kui teil on paljude sammudega protsess, kaaluge selle jagamist mitmeks väiksemaks makroks ja nende järjestikust kutsumist. See lihtsustab hooldust, võimaldab plokke taaskasutada ja lihtsustab silumist.

Näiteks üks makro andmete ettevalmistamiseks, teine ​​vormindamiseks, kolmas väljatrüki genereerimiseks ja kolmas PDF-vormingusse eksportimiseks või e-postiga saatmiseks . Neid saab kutsuda ükshaaval või kõik korraga.

Salvestatud koodi redigeerimine ja puhastamine

Diktofon jäädvustab „kõik”: valikud, klõpsud ja tabulaatori muudatused. VBE avamisel eemaldab see mittevajalikud valikud (Vali/Valik), asendades need otseviidetega (näiteks Vahemik("A1").Väärtus = ... ) ja rühmitab vormingud plokkideks Koos/Lõpeta Koos.

Lisaks lisab see muutujad ja põhilise veakäsitluse. Paar if-lauset ja lihtsad kontrollid hoiavad ära vead, kui vahemik on tühi või valik ei ole ootuspärane.

Preguntas frecuentes

Kuidas luua Excelis kasutajavorm? Ava Visual Basic Editor (Alt+F11), vali Lisa > Kasutajavorm ja lisa juhtelemendid (tekstiväljad, nupud jne). Selle saab kuvada makro abil.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Sealt edasi määrake juhtelementide sündmustele (näiteks nupule klõpsamine) protseduurid, et käivitada vajalik loogika.

Kuidas ma saan lehte makrodega kaitsta/kaitse eemaldada? Kasutage neid rutiine, muutes parooli vastavalt soovile. Need võimaldavad teil kaitse ühe klõpsuga aktiveerida või deaktiveerida.

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

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

Kuidas makrot kustutada? Mine menüüsse Arendaja > Makrod, vali makro ja vajuta kustutusklahvi (Delete). Kui soovid moodulist kogu koodi kustutada, ava VBE (Alt+F11), leia projektist moodul, paremklõpsa ja vali "Eemalda moodul" (salvestamiseks võid selle esmalt eksportida).

Veel üks viimane praktiline näpunäide: kui vormindate igakuist aruannet nii, et laekumata saldodega kliendid tuleks punase ja paksu kirjaga esile tõsta, salvestage järjestus üks kord, kohandage VBA-koodi õigele vahemikule (kasutades viimase rea tuvastamist või filtreid) ja salvestage makro oma isiklikku makrode töövihikusse. Järgmisel korral piisab vormingu koheseks, veatuks ja sekunditega rakendamiseks lihtsast otseteest või nupust.