Sisällys
Vaikka et osaa ohjelmoida, on monia paikkoja (kirjat, verkkosivustot, foorumit), joista voit löytää valmiita VBA-makrokoodia suurelle määrälle tyypillisiä Excelin tehtäviä. Kokemukseni mukaan useimmat käyttäjät keräävät ennemmin tai myöhemmin henkilökohtaisen makrokokoelmansa rutiiniprosessien automatisoimiseksi, olipa kyseessä sitten kaavojen kääntäminen arvoiksi, summien näyttäminen sanoilla tai solujen yhteenlaskeminen värin mukaan. Ja tässä syntyy ongelma – Visual Basicin makrokoodi on tallennettava jonnekin, jotta sitä voidaan käyttää myöhemmin työssä.
Helpoin vaihtoehto on tallentaa makrokoodi suoraan työtiedostoon siirtymällä Visual Basic -editoriin pikanäppäimellä muut+F11 ja uuden tyhjän moduulin lisääminen valikon kautta Insert – moduuli:
Tällä menetelmällä on kuitenkin useita haittoja:
- Jos työtiedostoja on paljon ja makroa tarvitaan kaikkialla, kuten makro kaavojen muuntamiseksi arvoiksi, sinun on kopioitava koodi jokaisessa kirjassa.
- Ei pidä unohtaa tallenna tiedosto makroja tukevassa muodossa (xlsm) tai binäärikirjamuodossa (xlsb).
- Kun avaat tällaisen tiedoston makrosuojaus antaa joka kerta varoituksen, joka on kuitattava (no, tai poista suojaus kokonaan käytöstä, mikä ei välttämättä aina ole toivottavaa).
Tyylikkäämpi ratkaisu olisi luoda oma apuohjelmasi (Excel-apuohjelma) – erillinen erityismuotoinen tiedosto (xlam), joka sisältää kaikki "suosikkimakrosi". Tämän lähestymistavan edut:
- Se riittää liitä lisäosa kerran Excelissä – ja voit käyttää sen VBA-menettelyjä ja toimintoja missä tahansa tämän tietokoneen tiedostossa. Työtiedostojen uudelleentallentaminen xlsm- ja xlsb-muodoissa ei siis ole tarpeen, koska. lähdekoodia ei tallenneta niihin, vaan apuohjelmatiedostoon.
- suojaus makrot eivät myöskään häiritse sinua. lisäosat ovat määritelmän mukaan luotettavia lähteitä.
- Onnistuu erillinen välilehti Excel-nauhassa mukavilla painikkeilla lisämakrojen suorittamista varten.
- Apuohjelma on erillinen tiedosto. Hänen helppo kuljettaa tietokoneelta tietokoneelle, jaa se työtovereiden kanssa tai jopa myy 😉
Käydään läpi koko oman Microsoft Excel -apuohjelman luomisprosessi vaihe vaiheelta.
Vaihe 1. Luo apuohjelmatiedosto
Avaa Microsoft Excel tyhjällä työkirjalla ja tallenna se millä tahansa sopivalla nimellä (esim MyExcelAddin) apuohjelmamuodossa komennolla Tiedosto – Tallenna nimellä tai avaimet F12, joka määrittää tiedostotyypin Excelin lisäosa:
Huomaa, että oletusarvoisesti Excel tallentaa apuohjelmat C:UsersYour_nameAppDataRoamingMicrosoftAddIns-kansioon, mutta periaatteessa voit määrittää minkä tahansa muun sinulle sopivan kansion.
Vaihe 2. Yhdistämme luodun apuohjelman
Nyt viimeisessä vaiheessa luomamme lisäosa MyExcelAddin on liitettävä Exceliin. Voit tehdä tämän siirtymällä valikkoon Tiedosto – Asetukset – Lisäosat (Tiedosto — Asetukset — Apuohjelmat), napsauta painiketta Meistä (Mennä) ikkunan alareunassa. Napsauta avautuvassa ikkunassa -painiketta Arvostelu (Selaa) ja määritä apuohjelmatiedostomme sijainti.
Jos teit kaiken oikein, niin meidän MyExcelAddin pitäisi näkyä saatavilla olevien lisäosien luettelossa:
Vaihe 3. Lisää makroja apuohjelmaan
Apuohjelmamme on yhdistetty Exceliin ja toimii hyvin, mutta siinä ei ole vielä yhtään makroa. Täytetään se. Voit tehdä tämän avaamalla Visual Basic -editorin pikanäppäimellä muut+F11 tai painikkeella Visual Basic kieleke kehittäjä (Kehittäjä). Jos välilehdet kehittäjä ei näy, se voidaan näyttää läpi Tiedosto – Asetukset – Nauhan asetukset (Tiedosto — Asetukset — Mukauta nauhaa).
Editorin vasemmassa yläkulmassa pitäisi olla ikkuna projekti (jos se ei ole näkyvissä, ota se käyttöön valikon kautta Näytä — Project Explorer):
Tämä ikkuna näyttää kaikki avoimet työkirjat ja käynnissä olevat Microsoft Excel -apuohjelmat, mukaan lukien meidän. VBAProject (MyExcelAddin.xlam) Valitse se hiirellä ja lisää siihen uusi moduuli valikon kautta Insert – moduuli. Tässä moduulissa tallennamme lisämakrojemme VBA-koodit.
Voit joko kirjoittaa koodin tyhjästä (jos osaa ohjelmoida) tai kopioida sen jostain valmiista (mikä on paljon helpompaa). Syötetään testausta varten yksinkertaisen mutta hyödyllisen makron koodi lisättyyn tyhjään moduuliin:
Kun olet syöttänyt koodin, älä unohda napsauttaa tallennuspainiketta (levyke) vasemmassa yläkulmassa.
Meidän makromme FormulasToValues, kuten voit helposti kuvitella, muuntaa kaavat arvoiksi ennalta valitulla alueella. Joskus näitä makroja kutsutaan myös menettelyt. Suorittaaksesi sen, sinun on valittava solut kaavoilla ja avattava erityinen valintaikkuna Makrot välilehdeltä kehittäjä (Kehittäjä – makrot) tai pikanäppäimellä muut+F8. Normaalisti tämä ikkuna näyttää käytettävissä olevat makrot kaikista avoimista työkirjoista, mutta apuohjelmamakrot eivät näy tässä. Tästä huolimatta voimme syöttää kenttään menettelymme nimen makron nimi (Makron nimi)ja napsauta sitten painiketta ajaa (juosta) – ja makromme toimii:
| |
Täällä voit myös määrittää pikanäppäimen makron käynnistämiseksi nopeasti – painike on vastuussa tästä parametrit (Vaihtoehdot) edellisessä ikkunassa Makro:
Kun määrität näppäimiä, muista, että ne erottelevat kirjainkoosta ja näppäimistön asettelusta. Joten jos määrität yhdistelmän, kuten Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally siirtyminensaadaksesi ison kirjaimen.
Mukavuuden vuoksi voimme myös lisätä makromme painikkeen pikatyökaluriville ikkunan vasemmassa yläkulmassa. Voit tehdä tämän valitsemalla Tiedosto – Asetukset – Pikakäyttötyökalupalkki (Tiedosto — Asetukset — Mukauta pikatyökaluriviä)ja sitten ikkunan yläosassa olevasta avattavasta luettelosta vaihtoehto Makrot. Sen jälkeen meidän makro FormulasToValues voidaan asettaa paneeliin painikkeella Lisää (Lisätä) ja valitse sille kuvake -painikkeella Muutos (Muokata):
Vaihe 4. Lisää toimintoja apuohjelmaan
Mutta makromenettelyt, siellä on myös funktiomakroja tai kuten niitä kutsutaan UDF (User Defined Function = käyttäjän määrittämä funktio). Luodaan erillinen moduuli lisäosaan (valikkokomento Insert – moduuli) ja liitä siihen seuraavan funktion koodi:
On helppo nähdä, että tätä toimintoa tarvitaan arvonlisäveron poistamiseen arvonlisäveron sisältävästä summasta. Ei tietenkään Newtonin binomi, mutta se sopii meille esimerkkinä perusperiaatteiden näyttämiseen.
Huomaa, että funktion syntaksi eroaa proseduurista:
- rakentamista käytetään Toiminto…. Lopeta toiminto sen sijaan Sub … End Sub
- funktion nimen jälkeen sen argumentit on merkitty suluissa
- funktion rungossa suoritetaan tarvittavat laskelmat ja sitten tulos liitetään muuttujaan funktion nimellä
Huomaa myös, että tätä toimintoa ei tarvita, ja se on mahdotonta suorittaa edellisen makroproseduurin tapaan valintaikkunan kautta Makrot ja -painiketta ajaa. Tällaista makrofunktiota tulee käyttää tavallisena taulukkofunktiona (SUMMA, JOS, VHAKU…), eli kirjoita vain mihin tahansa soluun ja määritä summan arvo argumenttina ALV:
… tai syötä vakiovalintaikkunan kautta funktion lisäämistä varten (painike fx kaavapalkissa) valitsemalla luokan Käyttäjän määrittelemä (Käyttäjän määrittelemä):
Ainoa epämiellyttävä hetki tässä on toiminnon tavanomaisen kuvauksen puuttuminen ikkunan alareunasta. Jos haluat lisätä sen, sinun on tehtävä seuraavat:
- Avaa Visual Basic Editor pikanäppäimellä muut+F11
- Valitse apuohjelma Project-paneelista ja paina -näppäintä F2avataksesi Object Browser -ikkunan
- Valitse apuohjelmasi ikkunan yläreunassa olevasta avattavasta luettelosta
- Napsauta hiiren kakkospainikkeella näkyviin tulevaa toimintoa ja valitse komento Kiinteistöt.
- Kirjoita toiminnon kuvaus ikkunaan Kuvaus
- Tallenna lisäosatiedosto ja käynnistä excel uudelleen.
Uudelleenkäynnistyksen jälkeen toiminnon pitäisi näyttää antamamme kuvaus:
Vaihe 5. Luo käyttöliittymään lisäosavälilehti
Viimeinen, vaikkakaan ei pakollinen, mutta miellyttävä kosketus on erillisen välilehden luominen painikkeella makromme suorittamista varten. Tämä välilehti tulee näkyviin Excel-käyttöliittymään apuohjelman liittämisen jälkeen.
Tietoja oletusarvoisesti näytettävistä välilehdistä on kirjassa, ja ne on muotoiltava erityiseen XML-koodiin. Helpoin tapa kirjoittaa ja muokata tällaista koodia on erikoisohjelmien - XML-editorien avulla. Yksi kätevimmistä (ja ilmaisista) on Maxim Novikovin ohjelma Ribbon XML -editori.
Sen kanssa työskentelyn algoritmi on seuraava:
- Sulje kaikki Excel-ikkunat, jotta tiedostoristiriita ei synny, kun muokkaamme apuohjelman XML-koodia.
- Käynnistä Ribbon XML Editor -ohjelma ja avaa siinä MyExcelAddin.xlam-tiedosto
- Painikkeella välilehdet lisää vasemmassa yläkulmassa uuden välilehden koodinpätkä:
- Sinun on laitettava tyhjiä lainausmerkkejä id välilehtemme ja ryhmämme (kaikki yksilölliset tunnisteet) ja sisään etiketti – välilehtemme ja siinä olevien painikkeiden nimet:
- Painikkeella nappia Lisää vasemmassa paneelissa tyhjä koodi painikkeelle ja lisää siihen tunnisteet:
- etiketti on painikkeen teksti
- imageMso — tämä on painikkeessa olevan kuvan ehdollinen nimi. Käytin punaista painikekuvaketta nimeltä AnimationCustomAddExitDialog. Kaikkien käytettävissä olevien painikkeiden nimet (ja niitä on useita satoja!) löytyvät useilta Internetin sivustoilta, jos haet avainsanoja "imageMso". Aluksi voit mennä tänne.
- onAction – tämä on takaisinsoittomenettelyn nimi – erityinen lyhyt makro, joka suorittaa päämakromme FormulasToValues. Voit kutsua tätä menettelyä miksi haluat. Lisäämme sen hieman myöhemmin.
- Voit tarkistaa kaiken tehdyn oikein painikkeella, jossa on vihreä valintamerkki työkalupalkin yläosassa. Napsauta samassa paikassa levykkeen painiketta tallentaaksesi kaikki muutokset.
- Sulje Ribbon XML -editori
- Avaa Excel, siirry Visual Basic -editoriin ja lisää takaisinsoittomenettely makroomme KillFormulasniin, että se suorittaa päämakromme kaavojen korvaamiseksi arvoilla.
- Tallennamme muutokset ja palaamme Exceliin tarkistamme tuloksen:
Siinä kaikki – apuohjelma on valmis käytettäväksi. Täytä se omilla menettelyilläsi ja toiminnoillasi, lisää kauniita painikkeita – ja makrojen käyttäminen työssäsi on paljon helpompaa.
- Mitä makrot ovat, miten niitä käytetään työssäsi, mistä saa makrokoodia Visual Basicissa.
- Aloitusnäytön luominen työkirjaa avattaessa Excelissä
- Mikä on henkilökohtainen makrokirja ja miten sitä käytetään