Makrojen ohjelmointi Excelissä: Täydellinen vaiheittainen opas

Informatec Digital » Ohjelmointi » Makrojen ohjelmointi Excelissä: Täydellinen vaiheittainen opas

Jos huomaat suorittavasi samoja tehtäviä Excelissä toistuvasti, makrot ovat paras ystäväsi ajan säästämisessä. Niiden avulla voit automatisoida kokonaisia ​​toimintosarjoja (lue lisää VBA-ohjelmoinnista ), muotoilusta tietojen tuomiseen tai laskelmien suorittamiseen, ja käynnistää ne milloin tahansa napsautuksella tai pikanäppäimellä.

Kun luot makron, Excel tallentaa napsautuksesi ja näppäinpainalluksesi ja suorittaa ne tarkasti uudelleen. Voit sitten hienosäätää koodia ja tutustua Excelin resursseihin . On tavallista aloittaa makrontallennimella ja sitten tarkentaa sitä VBA:lla, mikä parantaa hallintaa, joustavuutta ja poistaa huolimattomuudesta johtuvat virheet.

Mikä oikein on makro Excelissä?

Makro on luettelo käskyistä, jotka Excel suorittaa määritellyssä järjestyksessä . Se voi olla niin yksinkertaista kuin prosenttityylin käyttäminen tai niin monimutkaista kuin raportin laatiminen pivot-taulukoilla , sen muotoilu, laskentataulukoiden luominen tai sähköpostin lähettäminen Outlookissa. Makrot tallennetaan työkirjaasi ja ne voidaan suorittaa aina tarvittaessa.

Sinun ei tarvitse osata ohjelmoida päästäksesi alkuun: tallennin muuntaa toimintasi VBA-koodiksi. Siitä huolimatta VBA-osaaminen antaa sinulle mahdollisuuden luoda mukautettuja funktioita, lomakkeita ja jopa minisovelluksia Excelissä.

Miksi käyttää makroja? Todellisia etuja

Makron päätarkoitus on välttää toistuvia tehtäviä ja minimoida virheitä . Jos laadit saman raportin joka kuukausi, makro säästää sinut mekaanisilta vaiheilta ja antaa sinun keskittyä olennaiseen.

Sen etuihin kuuluvat ajansäästö, johdonmukainen suoritus (ilman ohitettuja vaiheita) ja mahdollisuus luoda pikakuvakkeita ja painikkeita prosessien nopeuttamiseksi . Jos käytät Exceliä vain satunnaisesti, se ei ehkä ole vaivan arvoista; mutta jos käytät sitä päivittäin, huomaat eron ensimmäisestä päivästä lähtien.

Ota käyttöön Kehittäjä-välilehti (Windows ja Mac)

Kehittäjä-välilehti on oletusarvoisesti piilotettu. Windowsissa napsauta valintanauhaa hiiren kakkospainikkeella ja valitse "Mukauta valintanauhaa". Valitse "Kehittäjä" ja napsauta OK. Sen jälkeen näet Kehittäjä-välilehden Näytä-kohdan vieressä.

Mac-tietokoneella siirry kohtaan Excel > Asetukset > Työkalurivit ja valintanauha. Valitse "Päävälilehdet" -kohdasta "Kehittäjä" ja tallenna. Tämä antaa sinulle pääsyn tallentimeen, VBA:han ja ohjausobjekteihin . Jos olet uusi Excelin käyttäjä, tutustu valintanauhaan ja sen asetuksiin Excelin perusoppaassa .

Makrojen luominen Tallentimella

Tallennin on täydellinen aloittelijan apu. Valitse Kehittäjä > Nauhoita makro (tai Alt+T+M+R Windowsissa) ja anna sille kuvaava nimi. Muista: nimen on alettava kirjaimella ilman välilyöntejä tai symboleja.

Valitse tallennuspaikka: "Tämä työkirja" toimii useimmissa tapauksissa. Jos haluat pitää sen aina saatavilla, valitse "Henkilökohtainen makrotyökirja", jolloin Excel luo/tallentaa sen tiedostoon Personal.xlsb , joka avautuu piilotettuna Excelissä.

Voit halutessasi lisätä pikakuvakkeen. Ristiriitojen (esimerkiksi Ctrl+Z-näppäinyhdistelmän katoamisen) välttämiseksi on suositeltavaa käyttää pikakuvakkeita Ctrl+Shift-näppäinyhdistelmän kanssa. Voit myös kirjoittaa lyhyen ja selkeän kuvauksen (erittäin hyödyllinen, kun sinulla on useita makroja).

Napsauta OK ja suorita tallennettavat toimenpiteet. Esimerkiksi: valitse A1 ja käytä "Prosenttiosuus"-muotoa. Vältä tarpeettomia napsautuksia tai valintamuutoksia, jotka eivät ole osa aiottua prosessia, koska tallennin tallentaa lähes kaiken.

Kun olet valmis, lopeta tallennus. Suorita se valitsemalla Kehittäjä > Makrot (tai painamalla Alt+F8), valitsemalla makron ja napsauttamalla Suorita. Näet Excelin toistavan vaiheet täsmälleen samassa järjestyksessä.

Makrojen suorittaminen, muokkaaminen ja hallinta

Valitsemalla Kehittäjä > Makrot (Alt+F8) voit suorittaa, muokata tai poistaa makroita. Jos valitset Muokkaa, Visual Basic Editor avautuu tallennetulla koodilla, jota voit siivota ja optimoida (se sisältää usein tarpeettomia vaiheita).

Makron määrittäminen painikkeelle tai muodolle on erittäin kätevää: lisää muoto, napsauta hiiren kakkospainikkeella > Määritä makro, valitse makro ja olet valmis. Kun napsautat muotoa, se suoritetaan välittömästi.

Jotta se pysyisi helposti saatavilla pikatyökalurivillä, avaa työkalupalkin valikko (alanuoli) > Lisää komentoja > valitse "Käytettävissä olevat komennot" -kohdasta "Makrot", lisää omasi ja muuta kuvaketta halutessasi. Nyt sinulla on pysyvä painike, vaikka et olisi kehittäjätilassa.

Jos sinun on käytettävä makroa uudelleen toisessa työkirjassa, voit kopioida moduulin Visual Basic Editorista. Avaa VBE (Alt+F11), vedä moduuli toiseen avoimeen työkirjaan tai käytä .bas-tiedoston vienti-/tuontitoimintoa.

Makrojen ohjelmointi Excelissä Visual Basic (VBA) -editorilla

Päästäksesi tallentimen ulkopuolelle, avaa editori painamalla Alt+F11. Lisää moduuli (Lisää > Moduuli) ja luo ensimmäinen aliohjelmasi. Perusmalli on Sub Name() … End Sub.

Esimerkiksi viestin näyttämiseksi ja arvon kirjoittamiseksi soluun A1 voisit aloittaa jostain yksinkertaisesta. Tällä tavoin ymmärrät rakenteen ja sen, miten makroa kutsutaan Excelistä (Alt+F8) tai miten se liitetään painikkeeseen, hyvin vähällä koodirivillä :

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

Toinen nopea esimerkki prosenttityylin soveltamisesta nykyiseen valintaan. Tämä makro toistaa tallentimella tekemäsi , mutta kirjoitettuna suoraan:

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

Ihanteellinen yhdistelmä on: tallenna asioiden nopeuttamiseksi ja avaa sitten VBE poistaaksesi tarpeettomat vaiheet, lisätäksesi muuttujia, ehtoja tai silmukoita. Tällä tavoin muutat "literaalisen" tallennuksen vankaksi ja uudelleenkäytettäväksi makroksi.

Absoluuttiset vs. suhteelliset viittaukset

Oletusarvoisesti tallennin luo makrot absoluuttisessa tilassa: se vaikuttaa aina samoihin soluihin (esimerkiksi B3) riippumatta siitä, missä olet sitä suorittaessasi. Jos datan sijainti muuttuu prosessissasi, tämä ei välttämättä ole se, mitä tarvitset.

"Käytä suhteellisia viittauksia" -asetuksella makro toimii suhteessa aktiiviseen soluun suorituksen alkaessa. Jos esimerkiksi aloitit tallentamisen solusta B3 ja kirjoitit "Etunimi", "Sukunimi" jne., siirryttäessä oikealle solusta C4 se kirjoittaa samat tiedot suhteessa soluihin C4, D4, E4 jne.

Tyypillinen esimerkki: otsikoiden lisääminen taulukkoon, joka alkaa joskus solusta B3 ja joskus solusta C5. Absoluuttisessa tilassa se kirjoittaisi aina soluun B3. Suhteellisessa tilassa se mukautuu valittuun aloitussoluun.

Yleisiä virheitä prosessien tallentamisessa

Yleinen sudenkuoppa on kaavan tallentaminen esimerkiksi kolmen rivin päähän ja sen luuleminen toimivan pidemmissä taulukoissa. Tallennin rekisteröi jotain vastaavaa kuin " täytä kolme solua alaspäin ", ei "taulukon loppuun asti".

Ratkaisu on muuttaa lähestymistapaasi: käytä dynaamisia alueita (esimerkiksi viimeisen rivin tunnistamista), silmukoita tai CurrentRegion-metodia tai säädä makroa VBA:ssa sen tallentamisen jälkeen niin, että se skaalautuu mihin tahansa kokoon.

Hyvät käytännöt tallentaessa

Ennen äänitystä, suorita prosessi manuaalisesti muutaman kerran läpi hioaksesi sitä. Tee lyhyitä, täsmällisiä äänitteitä yhden suuren sijaan; tämä helpottaa niiden ylläpitoa ja yhdistämistä.

Vältä tarpeettomia napsautuksia, valintanauhan selaamista ja valintoja, joilla ei ole mitään merkitystä. Pidä makron nimi ja kuvaus selkeinä, jotta sinä tai kuka tahansa työtoverisi voitte heti ymmärtää sen tarkoituksen.

Suorita makroja monella tavalla

Makrot-valintaikkunan (Alt+F8) lisäksi voit käynnistää makroita muodon, pikatyökalurivin painikkeen tai työkirjan avaamisen avulla. Voit jopa määrittää Excelin suorittamaan makron muista Office-sovelluksista, jolloin voit automatisoida sovellusten välisiä tehtäviä (esimerkiksi taulukon päivittämisen ja sähköpostin lähettämisen Outlookin kautta).

Mieti pikanäppäimiä huolellisesti: jos määrität makrolle Ctrl+Z-näppäinyhdistelmän, menetät Kumoa-toiminnon, kun työkirja on auki. Siksi on suositeltavaa käyttää Ctrl+Vaihto- näppäinyhdistelmiä ristiriitojen välttämiseksi.

Työkirjojen tallentaminen makroilla

Kun tiedosto sisältää VBA-koodia, tallenna se .xlsm-tiedostona, jotta koodi säilyy ja se voidaan suorittaa. .xlsx-tiedostona tallentaminen tuhoaa VBA-projektin . Jos haluat globaaleja makroja, käytä henkilökohtaista makrotyökirjaa (Personal.xlsb).

Tietoturva ja makrojen käyttöönotto

Makrot voidaan estää suojausasetuksistasi riippuen. Tarkista luottamuskeskuksesta, otatko ne käyttöön valikoivasti, äläkä koskaan ota käyttöön tuntemattomista lähteistä peräisin olevia makroja. Suojaustasosi ymmärtäminen auttaa sinua tasapainottamaan suojauksen ja tuottavuuden.

Makrojen määrittäminen painikkeille, muodoille ja ohjausobjekteille

Jos haluat käyttäjien suorittavan makroja automaattisesti, määritä makro painikkeelle, muodolle tai ohjausobjektille. Napsauta objektia hiiren kakkospainikkeella > Määritä makro > valitse. Voit myös sijoittaa kuvakkeen valintanauhaan tai pikatyökaluriville ja linkittää sen makroon.

Jos käytät lomakkeita tai ActiveX-komponentteja, voit liittää niihin makroita tai tapahtumaproseduureja (esimerkiksi napsautettaessa). Tämä mahdollistaa käyttäjäystävällisemmät käyttöliittymät ja ohjatut prosessit.

Hyödyllisiä makroesimerkkejä (VBA)

Nämä esimerkit havainnollistavat yleisiä tehtäviä. Voit liittää ne moduuliin (Alt+F11 > Lisää > Moduuli) ja mukauttaa niitä. Muista allekirjoittaa työkirjat tai muuttaa suojausasetuksia, jos organisaatiosi sitä vaatii, jotta voit suorittaa ne ilman varoituksia.

Kopioi valittu sisältö toiseen paikkaan samalla arkilla: 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

Tulosta aktiivinen taulukko: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Tallenna tiedosto makroita tukevana työkirjana (.xlsm): ActiveWorkbook.SaveAs

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

Muotoilun käyttäminen alueeseen (säädä aluetta tarpeidesi mukaan): Alue(…)-funktiolla

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

Etsi arvo alueelta ja näytä ensimmäinen osuma: Range(…).Find

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

Käytännön tapaus: muotoilu prosentteina

Tallennus: Aloita tallennus, nimeä se, valitse tallennuspaikka ja halutessasi lisää pikakuvake ja kuvaus. Valitse solut ja käytä Aloitus-välilehden "Prosentti"-toimintoa. Lopeta tallennus ja käynnistä se uudelleen painamalla Alt+F8. Jos haluat toistaa sen minimalistisella koodilla, käytä VBA:ssa Selection.Style = "Prosentti".

Vinkki: Vältä solujen tarpeetonta vaihtamista tallennuksen aikana. Nämä edestakaiset liikkeet tallentuvat ja voivat hidastaa ja vioittaa makroa.

Automatisoi pitkät tehtävät: pienet makrot ovat parempia

Jos prosessissasi on useita vaiheita, harkitse sen jakamista useisiin pienempiin makroihin ja niiden kutsumista peräkkäin. Tämä helpottaa ylläpitoa, mahdollistaa lohkojen uudelleenkäytön ja yksinkertaistaa virheenkorjausta.

Esimerkiksi yksi makro datan valmisteluun, toinen muotoiluun, kolmas tulosteen luomiseen ja kolmas PDF-muotoon vientiin tai sähköpostitse lähettämiseen . Voit kutsua niitä yksi kerrallaan tai kaikkia kerralla.

Muokkaa ja siivoa tallennettua koodia

Tallennin tallentaa "kaiken": valinnat, napsautukset ja sarkainvaihdot. Kun VBE avataan, se poistaa tarpeettomat valinnat (Valitse/Valinta) korvaamalla ne suorilla viittauksilla (esimerkiksi Range("A1").Value = ... ) ja ryhmittelee muotoilut Viittaus-/Lopeta-lohkoihin.

Lisäksi se lisää muuttujia ja perusvirheiden käsittelyn. Muutama if-lauseke ja yksinkertaiset tarkistukset estävät virheitä, kun alue on tyhjä tai valinta ei ole odotetun mukainen.

Usein kysytyt kysymykset

Miten luon UserForm-lomakkeen Excelissä? Avaa Visual Basic Editor (Alt+F11), valitse Lisää > UserForm ja lisää ohjausobjekteja (tekstiruutuja, painikkeita jne.). Voit näyttää sen makron avulla.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Sieltä voit määrittää proseduurit ohjausobjektien tapahtumille (esimerkiksi painikkeen napsautukselle) tarvitsemasi logiikan suorittamiseksi.

Miten suojaan/poistan suojauksen taulukosta makroilla? Käytä näitä rutiineja vaihtamalla salasana haluamallasi tavalla. Niiden avulla voit aktivoida tai poistaa suojauksen yhdellä napsautuksella.

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

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

Miten poistan makron? Siirry kohtaan Kehittäjä > Makrot, valitse makro ja paina Delete-näppäintä. Jos haluat poistaa kaiken koodin moduulista, avaa VBE (Alt+F11), etsi moduuli projektista, napsauta hiiren kakkospainikkeella ja valitse Poista moduuli (voit viedä sen ensin, jos haluat tallentaa sen).

Vielä yksi käytännön vinkki: Jos muotoilet kuukausiraportin siten, että asiakkaat, joilla on maksamatta olevia saldoja, tulisi korostaa punaisella ja lihavoituna, tallenna sarja kerran, säädä VBA-koodia käyttämään oikeaa aluetta (käyttäen viimeisen rivin tunnistusta tai suodattimia) ja tallenna makro henkilökohtaiseen makrotyökirjaasi. Seuraavalla kerralla yksinkertainen pikakuvake tai painike riittää muotoilun käyttöönottoon välittömästi, virheettömästi ja sekunneissa.