Kuinka kirjoittaa makroja Exceliin. Makrot Excelissä - Käyttöohjeet. Makropainikkeiden lisääminen välilehdille

Aihe 2.3. Esitysohjelmistot ja toimistoohjelmoinnin perusteet

Aihe 2.4. Tietokannan hallintajärjestelmät ja asiantuntijajärjestelmät

2.4.11. Koulutustietokanta pääpainikkeella "Training_students" - Lataa

VBA-ohjelmointi ja makrot

2.3. Esitysohjelmistot ja toimistoohjelmoinnin perusteet

2.3.7. Office-ohjelmoinnin perusteet

BASIC visuaalinen ohjelmointikieli sovelluksille: Visual Basic for Applications (VBA)

VBA on visuaalisen ohjelmointikielen Visual Basic (VB) osajoukko, joka sisältää lähes kaikki työkalut VB-sovellusten luomiseen.

VBA eroaa VB-ohjelmointikielestä siinä, että VBA on suunniteltu toimimaan suoraan Office-objektien kanssa, eikä sitä voida käyttää Office-sovelluksista riippumattoman projektin luomiseen. VBA:ssa ohjelmointikieli on siis VB ja ohjelmointiympäristö toteutetaan VB-editorin muodossa, joka voidaan aktivoida mistä tahansa MS Office -sovelluksesta.

Esimerkiksi, jotta voit avata VBA-editorin PowerPointista, sinun on suoritettava komento Työkalut / Makro / VBA-editori. Voit palata editorista sovellukseen valitsemalla Näytä-valikosta Microsoft PowerPoint -komennon tai painamalla Alt + F11-näppäinyhdistelmää.

Käyttämällä sisäänrakennettua VBA-editorisarjaa ja lomakeeditoria käyttäjä voi luoda käyttöliittymä kanssa kehitettävään hankkeeseen näytön muoto. Ohjaimet ovat objekteja, ja jokaisella objektilla on määritetty joukko mahdollisia tapahtumia (esimerkiksi hiiren napsauttaminen tai kaksoisnapsauttaminen, näppäimen painaminen, objektin vetäminen jne.).

Jokainen tapahtuma ilmenee tietyissä ohjelman toimissa (vastaukset, reaktiot). Käyttäjälomakkeella voit luoda sovellusten valintaikkunoita. VBA-ohjelmointikieltä käytetään ohjelmakoodin kirjoittamiseen, kuten käyttäjäfunktioiden luomiseen Excelissä.

VBA-ohjelmointijärjestelmä on suunniteltu toimimaan Office-objektien kanssa, joten sitä voidaan käyttää tehokkaasti kehitystoimintojen automatisointiin. erilaisia ​​tyyppejä asiakirjoja.

Katsotaanpa algoritmia käyttäjätoimintojen luomiseksi VBA:ssa:

1. Avaa VBA-koodieditori-ikkuna suorittamalla komento Tools / Macro / Visual Basic Editor tai painamalla Alt+F11.

2. Valitse editorin valikkokohta Insert / Module.

3. Suorita seuraavaksi Lisää / Toimenpide. Kirjoita avautuvaan Add Procedure -valintaikkunaan funktion nimi (esimerkiksi SUM5) ja aseta kytkimet: Tyyppi – Function-asentoon; Scope (Scope) - kohtaan Public (Yleinen) ja napsauta OK.

Riisi. 1.

4. VBA-ohjelmoinnin editoriikkunaan ilmestyy funktiomalli: otsikko - Public Function SUM5() ja loppu - End Function, jonka väliin on sijoitettava funktion runkokoodi.

Riisi. 2.

5. Seuraavaksi syötetään luettelo funktion parametreista, esimerkiksi suluissa merkitään (x, y, z, i, j), tietotyyppi (tarkkoja laskelmia varten) ja funktion palauttaman arvon tyyppi (me ei syötä sitä tässä esimerkissä). Lisäksi esittelemme funktion rungon, esimerkiksi SUM5 = x + y + z + i + j. Tuloksena saamme seuraavan ohjelmatekstin:

Julkinen funktio SUM5(x, y, z, i, j)
SUMMA5 = x + y + z + i + j
Lopeta toiminto

6. Paluu ikkunaan Excel-sovellukset, jossa meidän on esimerkiksi laskettava viisi arvoa.

7. Suorita "Insert/Function" ja valitse avautuvassa ohjatussa toimintoikkunassa "Käyttäjän määrittämä" luokka ja "Select a function" -ikkunassa valitse SUM5 ja napsauta OK.

Riisi. 3.

Riisi. 4.

Makrot

Makron luominen

Luodaan makro, joka summaa automaattisesti kaksi lukua soluissa A1, B1 ja sijoita tuloksen soluun C1 sekä täytä solun C1 turkoosilla värillä.

Algoritmi makron luomiseksi tehtävälle:

1. Valitse Työkalut/Makro, Aloita tallennus.

2. Kirjoita makron nimi Makron nimi -kenttään. Makron nimen ensimmäisen merkin on oltava kirjain. Välilyöntejä ei sallita makron nimessä. Voit käyttää alaviivoja sanaerottimina.

3. Jos haluat suorittaa makron pikanäppäimellä, kirjoita kirjain Pikanäppäin-kenttään. Voit käyttää yhdistelmiä CTRL+ kirjain (pienet kirjaimet) tai CTRL+SHIFT+ kirjain (isot kirjaimet), joissa kirjain on mikä tahansa näppäimistön kirjainnäppäin. Älä valitse tavallista pikanäppäintä, koska valittu pikanäppäin ohittaa tavalliset pikanäppäimet Microsoft Excel tuolloin tämän kirjan kanssa.

4. Valitse "Tallenna"-kentästä työkirja, johon haluat tallentaa makron. Tallenna makro tähän työkirjaan. Luomiseen Lyhyt kuvaus makro, kirjoita tarvittava teksti Kuvaus-kenttään. Kuvakaappauksessa on esimerkki "Tallenna makro" -valintaikkunan täyttämisestä

Riisi. 5.

5. Napsauta OK.

6. Suorita makrokomennot, jotka haluat tallentaa.

Riisi. 6.

7. Napsauta Stop Recording -painiketta kelluvan käyttöjärjestelmän työkalupalkissa (Lopeta tallennus) tai Tools / Macro / Stop Macro.

Kun makro on tallentanut, se näkyy makrojen luettelossa nimensä alla.

VBA-editori tallensi automaattisesti makrokomentosarjan tai ohjelman suoritettujen toimien perusteella (kuva 7.).

Riisi. 7.

Jos haluat kutsua makron, sinun on suoritettava komento Työkalut / Makro / Makrot. Tämän jälkeen makrojen luettelon valintaikkunasta löydät sen nimen mukaan ja napsautat Suorita-painiketta.

Työkalupalkin painikkeen määrittäminen makron suorittamista varten

Makro voidaan käynnistää sisäänrakennetun työkalupalkin painikkeella; tehdäksesi tämän, sinun on tehtävä:

1. Valitse Työkalut-valikosta Asetukset.
2. Valitse Asetukset-valintaikkunassa Komennot-välilehti ja valitse Makrot-vaihtoehto Luokat-luettelosta ja valitse "Mukautettu painike" komentoluettelosta.
3. Vedä komentoluettelosta mukautettu painike työkalupalkkiin hiirellä.
4. Napsauta tätä painiketta hiiren kakkospainikkeella ja valitse Assign Macro to kontekstivalikko.
5. Kirjoita makrolle nimi Makron nimi -kenttään.

Graafisen objektin alueen määrittäminen makron suorittamista varten:

1. Luo graafinen objekti.
2. Käytä kontekstivalikkoa valittuun grafiikkaobjektiin.
3. Valitse pikavalikosta Assign makro -komento.
4. Kirjoita näkyviin tulevassa Assign Macro to Object -valintaikkunassa makron nimi Makron nimi -kenttään ja napsauta sitten OK.

Makroa muokataan VBA-editorilla; tehdäksesi tämän, sinun on tehtävä seuraava:

1. Valitse komento Työkalut / Makrot / Makrot.
2. Valitse nimiluettelosta sen makron nimi, jota haluat muuttaa.
3. Napsauta Muokkaa-painiketta, jolloin avautuu Visual Basic -ikkuna, jossa voit muokata valitun makron Visual Basicilla kirjoitettuja komentoja.

Makron poistaminen:

1. Valitse Työkalut-valikosta Makrot ja sitten Makrot.
2. Valitse nykyisen työkirjan makroluettelosta makro, jonka haluat poistaa, ja napsauta Poista-painiketta.

Makron uudelleennimeäminen

Jos haluat nimetä makron uudelleen, sinun on siirryttävä makron muokkaustilaan ja vaihdettava otsikko ohjelman tekstissä. Uusi nimi korvaa automaattisesti vanhan makroluetteloissa, ja pikanäppäimet kutsuvat makroa uudella nimellä.

Ensin vähän terminologiasta.

Makro- tämä on Excelin sisäänrakennetulla kielellä kirjoitettu koodi VBA(Visual Basic sovelluksiin). Makrot voidaan luoda joko manuaalisesti tai tallentaa automaattisesti ns. makrotallentimella.

Makrotallennin on Excelin työkalu, joka tallentaa kaikki Excelissä tekemäsi askel askeleelta ja muuntaa sen VBA-koodiksi. Makrotallennin tuottaa erittäin yksityiskohtaista koodia (kuten näemme myöhemmin), jota voit muokata myöhemmin tarvittaessa.

Tallennettua makroa voidaan ajaa rajoittamattoman määrän kertoja ja Excel toistaa kaikki tallennetut vaiheet. Tämä tarkoittaa, että vaikka et tietäisi mitään VBA:sta, voit automatisoida joitain tehtäviä yksinkertaisesti tallentamalla vaiheet ja käyttämällä niitä myöhemmin uudelleen.

Sukellaan nyt ja katsotaan kuinka makro tallennetaan Exceliin.

Näytä Kehittäjä-välilehti valikkonauhassa

Ennen kuin tallennat makron, sinun on lisättävä Excel-valikkonauhaan Kehittäjä-välilehti. Voit tehdä tämän seuraavasti:

Tämän seurauksena "Kehittäjä"-välilehti tulee näkyviin valikkonauhaan.

Makron tallentaminen Exceliin

Kirjoita nyt hyvin yksinkertainen makro, joka valitsee solun ja syöttää siihen tekstiä, kuten "Excel".

Tässä on vaiheet tällaisen makron tallentamiseen:

Onnittelut! Olet juuri tallentanut ensimmäisen makrosi Exceliin. Vaikka makro ei tee mitään hyödyllistä, se auttaa meitä ymmärtämään, kuinka makrotallennin toimii Excelissä.

Katsotaan nyt makrotallennin tallentamaa koodia. Avaa koodieditori seuraavasti:

Näet, että kun napsautat Suorita-painiketta, teksti "Excel" lisätään soluun A2 ja solu A3 valitaan. Tämä tapahtuu millisekunneissa. Mutta itse asiassa makro suoritti tallennetut toiminnot peräkkäin.

Huomautus. Voit myös suorittaa makron käyttämällä pikanäppäintä Ctrl + Shift + N (pidä Ctrl- ja Shift-näppäimiä painettuna ja paina sitten N-näppäintä). Tämä on sama nimiö, jonka määritimme makrolle tallennettaessa sitä.

Mitä makro tallentaa?

Siirrytään nyt koodieditoriin ja katsotaan, mitä saimme.

Näin voit avata VB-editorin Excelissä:

1. Napsauta Koodi-ryhmässä Visual Basic -painiketta.

Voit myös käyttää Alt + F11-näppäinyhdistelmää ja siirtyä VBA-koodieditoriin.

• Valikkopalkki: Sisältää komennot, joita voit käyttää VB-editorin kanssa työskennellessäsi.
• Työkalupalkki- näyttää paneelilta nopea pääsy Excelissä. Voit lisätä usein käyttämiäsi työkaluja.
• Project Explorer- Tässä Excel listaa kaikki työkirjat ja kaikki objektit jokaisessa työkirjassa. Jos meillä on esimerkiksi työkirja, jossa on 3 laskentataulukkoa, se näkyy Project Explorerissa. Täällä on useita lisäobjekteja, kuten moduuleja, käyttäjämuotoja ja luokkamoduuleja.
• Koodi-ikkuna- itse VBA-koodi sijaitsee tässä ikkunassa. Jokaiselle projektinhallinnassa luetellulle objektille on koodiikkuna, kuten laskentataulukot, työkirjat, moduulit jne. Tässä opetusohjelmassa näemme, että tallennettu makro on moduulin koodiikkunassa.
• Ominaisuudet-ikkuna- näet kunkin kohteen ominaisuudet tässä ikkunassa. Käytän usein tätä ikkunaa objektien merkitsemiseen tai niiden ominaisuuksien muuttamiseen.
• Välitön ikkuna(esikatseluikkuna) - Alkuvaiheessa et tarvitse sitä. Se on hyödyllinen, kun haluat testata vaiheita tai virheenkorjauksen aikana. Sitä ei näytetä oletuksena, ja voit näyttää sen napsauttamalla Näytä-välilehteä ja valitsemalla Välitön ikkuna -vaihtoehdon.

Kun tallensimme "EnterText"-makron, VB-editorissa tapahtui seuraavaa:

• Uusi moduuli on lisätty.
• Makro tallennettiin antamallamme nimellä - "InputText"
• Koodi-ikkunaan on lisätty uusi menettely.

Joten jos kaksoisnapsautat moduulia (tapauksessamme moduuli 1), koodiikkuna tulee näkyviin alla olevan kuvan mukaisesti.

Tässä on makrotallentimen tallentama koodi:

SubTextEnter() " "TextEnter Makro " " Alue("A2").Valitse ActiveCell.FormulaR1C1 = "Excel" Range("A3").Valitse End Sub

VBA:ssa "(heittomerkkiä) seuraavaa riviä ei suoriteta. Tämä on kommentti, joka on tarkoitettu vain tiedoksi. Jos poistat koodin viisi ensimmäistä riviä, makro toimii edelleen.

Käydään nyt läpi jokainen koodirivi ja kuvataan mitä ja miksi.

Koodi alkaa kirjaimella Sub perään makron nimi ja tyhjät sulut. Sub on lyhenne sanoista aliohjelma. Jokainen VBA:n aliohjelma (kutsutaan myös proseduuriksi) alkaa Sub ja päättyy Lopeta ala.

• Range("A2").Select - Tämä rivi valitsee solun A2.
• ActiveCell.FormulaR1C1 = "Excel" - Tämä rivi syöttää tekstin "Excel" aktiiviseen soluun. Koska valitsimme solun A2 ensimmäiseksi askeleeksi, siitä tulee aktiivinen solumme.
• Alue("A3").Select - valitse solu A3. Tämä tapahtuu, kun painamme Enter-näppäintä tekstin syöttämisen jälkeen, jolloin valitaan solu A3.

Toivottavasti sinulla on perustiedot makron tallentamisesta Exceliin.

Huomaa, että makrotallentimella tallennettu koodi ei yleensä ole tehokasta tai optimoitua koodia. Makrotallennin lisää usein ylimääräisiä tarpeettomia vaiheita. Mutta tämä ei tarkoita, ettei sinun tarvitse käyttää makrotallenninta. Niille, jotka vain opettelevat VBA:ta, makrotallennin voi olla loistava tapa analysoida ja ymmärtää, kuinka kaikki toimii VBA:ssa.

Absoluuttinen ja suhteellinen makrotallennus

Tiedätkö jo absoluuttisista ja suhteellisista linkeistä Excelissä? Jos käytät absoluuttista viittausta makron tallentamiseen, VBA-koodi viittaa aina samoihin soluihin, joita käytit. Jos esimerkiksi valitset solun A2 ja kirjoitat tekstin "Excel", niin joka kerta - riippumatta siitä, missä olet laskentataulukossa ja mikä solu on valittuna, koodisi kirjoittaa tekstin "Excel" soluun A2.

Jos käytät makron tallentamiseen suhteellista viittausta, VBA ei sitoudu tiettyyn soluosoitteeseen. Tässä tapauksessa ohjelma "liikkuu" suhteessa aktiiviseen soluun. Oletetaan esimerkiksi, että olet jo valinnut solun A1 ja alat tallentaa makroa suhteellisessa linkkitilassa. Nyt valitset solun A2, kirjoita Excel teksti ja paina Enter. Jos nyt suoritat tämän makron, se ei palaa soluun A2, vaan se liikkuu suhteessa aktiiviseen soluun. Jos esimerkiksi valitaan solu B3, se siirtyy kohtaan B4, kirjoittaa tekstin "Excel" ja siirtyy sitten soluun K5.

Tallennetaan nyt makro suhteellisessa linkkitilassa:

Suhteellisen linkin tilassa oleva makro tallennetaan.

Tee nyt seuraava.

1. Valitse mikä tahansa solu (paitsi A1).
2. Siirry "Kehittäjä"-välilehdelle.
3. Napsauta Koodi-ryhmässä Makrot-painiketta.
4. Napsauta Makro-valintaikkunassa tallennettua Suhteelliset viittaukset -makroa.
5. Napsauta "Suorita"-painiketta.

Kuten huomaat, makro ei kirjoittanut "Excel"-tekstiä soluihin A2. Tämä tapahtui, koska olet tallentanut makron suhteellisessa viitetilassa. Siten kohdistin liikkuu suhteessa aktiiviseen soluun. Jos teet tämän esimerkiksi solun B3 ollessa valittuna, se siirtyy Excel-tekstiin - soluun B4 ja valitsee lopulta solun B5.

Tässä on koodi, jonka makrotallennin tallensi:

Huomaa, että koodi ei viittaa soluihin B3 tai B4. Makro käyttää Activecelliä viittaamaan nykyiseen soluun ja siirtymään kyseiseen soluun.

Ohita koodin Range("A1") -osa. Tämä on yksi niistä tapauksista, joissa makrotallennin lisää tarpeetonta koodia, jolla ei ole tarkoitusta ja joka voidaan poistaa. Ilman sitä koodi toimii hyvin.

Mitä et voi tehdä makrotallentimella?

Makrotallennin sopii sinulle erinomaisesti Excelissä ja tallentaa tarkat vaiheet, mutta se ei välttämättä ole oikea valinta sinulle, kun sinun on tehtävä enemmän.

• Et voi suorittaa koodia valitsematta objektia. Jos esimerkiksi haluat makron siirtyvän seuraavaan laskentataulukkoon ja korostavan kaikki sarakkeen A täytetyt solut poistumatta nykyisestä laskentataulukosta, makrotallennin ei voi tehdä tätä. Tällaisissa tapauksissa sinun on muokattava koodi manuaalisesti.
• Et voi luoda mukautettua toimintoa makrotallentimen avulla. VBA:lla voit luoda mukautettuja toimintoja, joita voidaan käyttää laskentataulukossa normaaleina funktioina.
• Et voi luoda silmukoita makrotallentimella. Mutta voit tallentaa yhden toiminnon ja lisätä silmukan manuaalisesti koodieditorissa.
• Et voi analysoida ehtoja: voit tarkistaa ehdot koodista makrotallentimella. Jos kirjoitat VBA-koodin käsin, voit käyttää IF Then Else -lauseita arvioimaan ehdon ja suorittamaan koodin, jos tosi (tai muun koodin, jos epätosi).

Makroja sisältävien Excel-tiedostojen laajennus

Kun tallennat makron tai kirjoitat manuaalisesti VBA-koodia Excelissä, sinun on tallennettava tiedosto makroa tukevalla tiedostotunnisteella (.xlsm).

Ennen Excel 2007:ää yksi tiedostomuoto riitti - .xls. Vuodesta 2007 lähtien .xlsx on kuitenkin otettu käyttöön vakiotiedostopäätteenä. .xlsx-muodossa tallennetut tiedostot eivät voi sisältää makroa. Joten jos sinulla on tiedosto, jonka tunniste on .xlsx, ja kirjoitat/tallennat makron ja tallennat sen, se varoittaa sinua sen tallentamisesta makroa tukevaan muotoon ja näyttää seuraavan valintaikkunan:

Jos valitset Ei, Excel tallentaa tiedoston makroja tukevassa muodossa. Mutta jos napsautat Kyllä, Excel poistaa automaattisesti kaiken koodin työkirjastasi ja tallentaa tiedoston työkirjana .xlsx-muodossa. Joten jos työkirjassasi on makro, sinun on tallennettava se .xlsm-muodossa, jotta makro voidaan tallentaa.

Mikä on makro? Makro on tallennettu toimintosarja tai VBA:ssa (Visual Basic for Application) luotu ohjelma. Usein kysytty kysymys miten kirjoitetaan makro Exceliin? Vain.

Nuo. Jos meidän on suoritettava samat toiminnot useita kertoja, voimme muistaa nämä toiminnot ja suorittaa ne yhdellä painikkeella. En pakota sinua oppimaan VBA:ta tai edes ehdota monia vakiomakroja tässä artikkelissa. Todellisuudessa kuka tahansa voi luoda/kirjoittaa makron Excelissä. Tätä tarkoitusta varten on Excelin mielenkiintoisin ja epätavallisin ominaisuus - Makrotallennin(tallentaa toimintasi koodin muodossa). Nuo. voit tallentaa toimintasi, kuten videolle, ja kääntää ne koodiksi (sekvenssiksi).

Lyhyesti sanottuna, jos teet samat toiminnot joka päivä, kannattaa miettiä, kuinka tämä prosessi automatisoidaan. Lue lisää makron kirjoittamisesta Excelissä?

1.Salli makrot

Valikko (pyöreä painike vasemmassa yläkulmassa) - Excel-asetukset— Luottamuskeskus — Luottamuskeskuksen asetukset — Makroasetukset. Valitse Ota kaikki makrot käyttöön -valintaruutu.

Tai tee sama Kehittäjä-välilehdellä

2. Ota Kehittäjä-valikko käyttöön nopeaa työtä makrojen kanssa

Valikko (pyöreä painike vasemmassa yläkulmassa) - Excel-asetukset - Yleiset - Näytä kehittäjä-välilehti.

Jotta luotu makro voidaan tallentaa työkirjaan, sinun on tallennettava tiedosto sisään .xlsm tai .xlsb. Napsauta Tallenna nimellä - makrokäyttöinen Excel-työkirja tai binaarityökirja.

Joten kuinka kirjoittaa makro Exceliin?

Se on yksinkertaista. Mennään Exceliin. Sivun alareunassa arkkien otsikoiden alla on "Tallenna makro" -painike.

Napsauta painiketta ja "Tallenna makro" -ikkuna avautuu. Nimeämme makron haluamallamme tavalla Makron nimi: -kentässä. Asetamme näppäinyhdistelmän, jota käytämme myöhemmin kutsumaan sitä (valinnainen).

Napsauta OK. Macro Recorder on aloittanut tallennuksen.

Kaikki. Nyt teemme toiminnot, jotka meidän on muistettava makrona. Meidän on esimerkiksi poistettava yksi rivi ja värjättävä solu A1 keltaiseksi.

Näiden manipulointien aikana arkkien alla olevan "Tallenna makro" -painikkeen sijaan syttyy neliö, jonka napsauttaminen pysäyttää makron suorittamisen.

Kuinka käynnistää tapahtunut? Napsauta painiketta, Valitse makro -ikkuna tulee näkyviin, valitse haluamasi makro ja napsauta sitten Suorita-painiketta.

Kuinka voin nähdä mitä tapahtui? Napsauta painiketta. Valitse haluamasi makro ja napsauta Muokkaa. Tallenna makro -ikkuna (VBA-ikkuna) avautuu.

Makrokoodin pitäisi näyttää suunnilleen tältä.

Alaesimerkki1() " Esimerkki1 Makrorivit("2:2").Valitse valinta.Poista Shift:=xlUp Range("A1").Valitse valinnalla.Sisäpuoli .Kuvio = xlSolid .PatternColorIndex = xlAutomatic.Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

’ on kommenttisymboli, ts. tämä rivi ei sisälly koodiin. Makrolla on oltava nimi (Sub Esimerkki1()) ja pääte (End Sub).

Voit lukea painikkeen luomisesta makrolle.

Joten voit tallentaa minkä tahansa toiminnosi, jotta voit toistaa ne niin monta kertaa kuin haluat!

Voit lukea makron tallentamisesta kaikille tietokoneesi kirjoille.

Käytä tätä varten Henkilökohtaista.

Voit lukea makron suorittamisesta ehtojen mukaan

Onnea, tästä on varmasti sinulle hyötyä!

Jaa artikkelimme sosiaalisessa mediassa:

Taulukoiden tai muiden Office-objektien kanssa syntyy usein tilanteita, joissa sinun on tehtävä samat toiminnot. Tämä ei vain vie ylimääräistä aikaa, vaan on myös erittäin ärsyttävää. Onneksi on makroja tämän ongelman ratkaisemiseksi.

VBA-ympäristö on suunniteltu makrojen luomiseen, mutta sinun ei tarvitse olla ohjelmoija tai opiskella Visual Basic for Applicationia oppiaksesi kirjoittamaan niitä. Tätä tarkoitusta varten on olemassa erityisesti kehitettyjä työkaluja, jotka luovat VBA-koodin käskystäsi ilman, että sinulta vaaditaan lisätietoa. Tämän kielen hallitseminen ei kuitenkaan ole niin vaikeaa.

Makrot luodaan sovelluksissa tallennustyökaluilla. Tässä tapauksessa tietylle näppäinyhdistelmälle määritetään haluttu toimintosarja. Avaa Office-sovellus. Valitse muotoiltava fragmentti.

Valitse valikosta "Työkalut" -> "Makro" -> "Aloita tallennus" (Office 2007:ssä "Näytä" -> "Makrot" -> "Tallenna makro"). Aseta näkyviin tulevassa "Tallenna makro" -ikkunassa uuden makron nimi; oletuksena se on "Makro1", mutta on parempi antaa sille nimi, varsinkin jos makroja on useita. Suurin koko nimikentät – 255 merkkiä, pisteet ja välilyönnit eivät ole sallittuja.

Päätä, mitä painiketta tai näppäinyhdistelmää makrosi käyttää tulevaisuudessa. Yritä valita kätevin vaihtoehto, varsinkin jos se on tarkoitettu toistuvaan käyttöön. Valitse "Assign makro" -kentästä sopiva kohde: "painike" tai "avaimet".

Jos valitset "painikkeen", "Pika-asetukset"-ikkuna avautuu. Kun valitset "näppäimet", sinun tarvitsee vain kirjoittaa yhdistelmä näppäimistöltä. Tarkista "Nykyiset yhdistelmät" toiston välttämiseksi. Napsauta "Assign".

Wordissa ja PowerPointissa luotu makro on jatkossa voimassa kaikissa asiakirjoissa. Jotta Excel-makro on kaikkien asiakirjojen käytettävissä, tallenna se personal.xls-tiedostoon, joka käynnistyy automaattisesti, kun avaat sovelluksen. Suorita komento "Ikkuna" -> "Näyttö" ja valitse näkyviin tulevasta ikkunasta rivi personal.xls-tiedoston nimellä.

Kirjoita makron kuvaus Kuvaus-kenttään. Napsauta OK ja palaat asiakirjaan, mutta näet nyt tallennuskuvakkeen hiiren osoittimessa. Muotoile teksti toimintosarjalla, jonka haluat automatisoida. Ole erittäin varovainen äläkä tee tarpeettomia toimia, koska makro tallentaa ne kaikki, ja tämä vaikuttaa sen suoritusaikaan tulevaisuudessa.

Suorita komento "Työkalut" -> "Makro" -> "Lopeta tallennus". Loit VBA-objektin kirjoittamatta itse yhtään koodiriviä. Jos kuitenkin joudut vielä tekemään muutoksia manuaalisesti, syötä objekti "Makrot"-osiossa, "Muuta"-komennolla tai käyttämällä Alt+F8-näppäinyhdistelmää.

Harvat ihmiset tietävät, että suositun Microsoft Excel -tuotteen ensimmäinen versio ilmestyi vuonna 1985. Siitä lähtien se on käynyt läpi useita muutoksia, ja se on kysytty miljoonien käyttäjien keskuudessa ympäri maailmaa. Samaan aikaan monet ihmiset työskentelevät vain pienellä osalla tämän taulukkolaskentaprosessorin ominaisuuksista eivätkä edes ymmärrä, kuinka Excelin ohjelmointikyky voisi helpottaa heidän elämäänsä.

Mikä on VBA

Ohjelmointi Excelissä suoritetaan Visual Basic for Application -ohjelmointikielellä, joka oli alun perin rakennettu Microsoftin tunnetuimpaan taulukkolaskentaprosessoriin.

Asiantuntijat pitävät sen suhteellisen helppoutta oppia sen eduista. Kuten käytäntö osoittaa, jopa käyttäjät, joilla ei ole ammatillisia ohjelmointitaitoja, voivat hallita VBA: n perusteet. VBA:n ominaisuuksiin kuuluu komentosarjan suorittaminen toimistosovellusympäristössä.

Ohjelman haittana ovat eri versioiden yhteensopivuusongelmat. Ne johtuvat siitä, että VBA-ohjelmakoodi pääsee käsiksi toiminnallisuutta, jotka ovat läsnä uusi versio tuote, mutta puuttuu vanhasta. Haittoja ovat myös koodin liian korkea avoimuus ulkopuolisen muutoksille. tästä huolimatta Microsoft Office, sekä IBM Lotus Symphony, sallivat käyttäjän salata alkuperäisen koodin ja asettaa salasanan sen tarkastelua varten.

Objektit, kokoelmat, ominaisuudet ja menetelmät

Nämä ovat käsitteitä, jotka VBA-ympäristössä työskentelevien on ymmärrettävä. Ensinnäkin sinun on ymmärrettävä, mikä esine on. Excelissä nämä funktiot ovat taulukko, työkirja, solu ja alue. Näillä objekteilla on erityinen hierarkia, ts. tottele toisiaan.

Tärkein niistä on Sovellus, joka vastaa Excel ohjelma. Sitten tulevat työkirjat, laskentataulukot ja alue. Jos esimerkiksi haluat käyttää tietyn laskentataulukon solua A1, sinun on määritettävä polku, joka ottaa huomioon hierarkian.

Mitä tulee käsitteeseen "kokoelma", tämä on ryhmä saman luokan kohteita, jonka merkinnässä on muotoa ChartObjects. Sen yksittäiset elementit ovat myös esineitä.

Seuraava käsite on ominaisuudet. Ne ovat minkä tahansa esineen välttämätön ominaisuus. Esimerkiksi Range-kohdassa se on arvo tai kaava.

Menetelmät ovat komentoja, jotka osoittavat, mitä on tehtävä. VBA:ssa koodia kirjoitettaessa ne on erotettava objektista pisteellä. Esimerkiksi, kuten myöhemmin näytetään, hyvin usein Excelissä ohjelmoitaessa käytetään Cells(1,1).Select-komentoa. Se tarkoittaa, että sinun on valittava solu, jossa on koordinaatit

Selection.ClearContentsia käytetään usein yhdessä sen kanssa. Tämän suorittaminen tarkoittaa valitun solun sisällön tyhjentämistä.

Kuinka aloittaa

Sitten sinun on siirryttävä VB-sovellukseen, jota varten sinun tarvitsee vain käyttää näppäinyhdistelmää "Alt" ja "F11". Edelleen:

• napsauta ikkunan yläreunassa olevasta valikkopalkista Excel-kuvakkeen vieressä olevaa kuvaketta;
• valitse Mudule-komento;
• tallenna napsauttamalla kuvaketta;
• he kirjoittavat esimerkiksi koodiluonnoksen.

Se näyttää tältä:

Alaohjelma()

"Koodimme

Huomaa, että rivi "Koodimme" on korostettu eri värillä (vihreä) Syynä on rivin alkuun sijoitettu heittomerkki, joka osoittaa, että seuraavaa kommenttia.

Nyt voit kirjoittaa minkä tahansa koodin ja luoda itsellesi uuden työkalun VBA Excel(katso esimerkkejä ohjelmista alla). Tietysti se on paljon helpompaa niille, jotka tuntevat Visual Basicin perusteet. Kuitenkin myös ne, joilla niitä ei ole, voivat halutessaan tottua siihen riittävän nopeasti.

Makrot Excelissä

Tämä nimi piilottaa Visual Basic for Applicationissa kirjoitetut ohjelmat. Näin ollen ohjelmointi Excelissä on makrojen luomista tarvittava koodi. Tämän ominaisuuden ansiosta Microsoftin taulukkolaskentaprosessori kehittää itseään mukautuen tietyn käyttäjän vaatimuksiin. Kun olet selvittänyt kuinka luoda moduuleja makrojen kirjoittamista varten, voit alkaa harkita konkreettisia esimerkkejä VBA Excel ohjelmat. On parasta aloittaa alkeellisimmista koodeista.

Esimerkki 1

Tehtävä: kirjoita ohjelma, joka kopioi yhden solun sisällön arvon ja kirjoittaa sen sitten toiseen.

Tätä varten:

• avaa "Näytä"-välilehti;
• siirry "Makrot" -kuvakkeeseen;
• napsauta "Tallenna makro";
• täytä avautuva lomake.

Jätä yksinkertaisuuden vuoksi "Makro1" "Makron nimi" -kenttään ja lisää "Pikanäppäin"-kenttään esimerkiksi hh (tämä tarkoittaa, että voit käynnistää ohjelman pikakomennolla "Ctrl+h"). Paina Enter.

Nyt kun makrotallennus on jo alkanut, solun sisältö kopioidaan toiseen. Palaa alkuperäiseen kuvakkeeseen. Napsauta "Tallenna makro". Tämä toiminto tarkoittaa ohjelman päättymistä.

• palaa "Makrot" -riville;
• valitse luettelosta "Makro 1";
• napsauta "Suorita" (sama toiminto käynnistetään käynnistämällä näppäinyhdistelmä "Ctrl+hh").

Tämän seurauksena makron tallennuksen aikana suoritettu toiminto tapahtuu.

On järkevää nähdä, miltä koodi näyttää. Voit tehdä tämän palaamalla "Makrot" -riville ja napsauttamalla "Muuta" tai "Kirjaudu sisään". Tämän seurauksena he joutuvat VBA-ympäristöön. Itse asiassa makrokoodi itse sijaitsee rivien Sub Macro1() ja End Sub välissä.

Jos kopiointi tehtiin esimerkiksi solusta A1 soluun C1, yksi koodiriveistä näyttää tältä Range(“C1”). Valitse. Käännettynä se näyttää "Range("C1").Select, eli se menee VBA Exceliin, soluun C1.

Koodin aktiivinen osa täydentyy ActiveSheet.Paste-komennolla. Se tarkoittaa valitun solun sisällön (tässä tapauksessa A1) kirjoittamista valittuun soluun C1.

Esimerkki 2

VBA-silmukat auttavat sinua luomaan erilaisia ​​makroja Excelissä.

VBA-silmukat auttavat luomaan erilaisia ​​makroja. Oletetaan, että meillä on funktio y=x + x 2 + 3x 3 - cos(x). Sinun on luotava makro saadaksesi sen kaavion. Tämä voidaan tehdä vain VBA-silmukoilla.

Funktioargumentin alku- ja loppuarvot ovat x1=0 ja x2=10. Lisäksi sinun on syötettävä vakio - argumentin muutosvaiheen arvo ja laskurin alkuarvo.

Kaikki VBA Excel -makroesimerkit luodaan edellä esitetyllä tavalla. Tässä tapauksessa koodi näyttää tältä:

Alaohjelma()

askel = 0,1

Tee kun x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Solut(i, 1). Arvo = x1 (arvo x1 kirjoitetaan soluun, jossa on koordinaatit (i,1))

Solut(i, 2). Arvo = y (y-arvo kirjoitetaan soluun koordinaatteilla (i,2))

i = i + 1 (laskuri on voimassa);

x1 = x1 + shag (argumentti muuttuu askelarvon verran);

Lopeta ala.

Tämän makron suorittamisen tuloksena Excelissä saamme kaksi saraketta, joista ensimmäinen sisältää x:n ja toinen y:n arvot.

Sitten niistä rakennetaan kaavio Excelille tavallisella tavalla.

Esimerkki 3

Silmukoiden toteuttamiseksi VBA Excel 2010:ssä, kuten muissakin versioissa, yhdessä jo annetun Do While -rakenteen kanssa käytetään For.

Harkitse ohjelmaa, joka luo sarakkeen. Jokaiseen sen soluun kirjoitetaan vastaavan rivinumeron neliöt. For-rakenteen avulla voit kirjoittaa sen hyvin lyhyesti ilman laskuria.

Ensin sinun on luotava makro edellä kuvatulla tavalla. Seuraavaksi kirjoitamme itse koodin muistiin. Oletamme, että olemme kiinnostuneita 10 solun arvoista. Koodi näyttää tältä.

Jos i = 1 - 10 Seuraava

Komento käännetään "ihmiskieleksi" seuraavasti: "Toista 1 - 10 yhden askelin".

Jos tehtävänä on saada sarake neliöillä, esimerkiksi kaikista parittomista luvuista välillä 1-11, kirjoitamme:

Jos i = 1 - 10, vaihe 1 Seuraava.

Tässä askel on askel. Tässä tapauksessa se on yhtä suuri kuin kaksi. Oletusarvoisesti tämän sanan puuttuminen syklistä tarkoittaa, että vaihe on yksittäinen.

Saadut tulokset on tallennettava soluihin, jotka on numeroitu (i,1). Sitten joka kerta kun silmukka käynnistetään ja i kasvaa askelkoolla, rivin numero kasvaa automaattisesti. Tällä tavalla koodi optimoidaan.

Kaiken kaikkiaan koodi näyttää tältä:

Alaohjelma()

Jos i = 1 - 10, vaihe 1 (voit kirjoittaa i = 1 - 10)

Solut(i, 1). Arvo = i ^ 2 (eli neliön i arvo kirjoitetaan soluun (i,1)

Seuraava (jollain tavalla se toimii laskurina ja tarkoittaa silmukan uutta alkua)

Lopeta ala.

Jos kaikki on tehty oikein, mukaan lukien makron tallennus ja suorittaminen (katso ohjeet yllä), niin kun kutsut sitä, saat joka kerta määritetyn kokoisen sarakkeen (tässä tapauksessa 10 solusta koostuvan).

Esimerkki 4

SISÄÄN Jokapäiväinen elämä Melko usein syntyy tarve tehdä yksi tai toinen päätös tilanteesta riippuen. Et voi tehdä ilman niitä VBA Excelissä. Esimerkkejä ohjelmista, joissa algoritmin jatkosuorituksen kulku valitaan eikä alun perin ole ennalta määrätty, käytetään useimmiten If ...Then -konstruktiota (monimutkaisissa tapauksissa) If ...Then ...END If.

Tarkastellaanpa yksittäistä tapausta. Oletetaan, että sinun on luotava makro Excelille, jotta seuraava kirjoitetaan soluun, jossa on koordinaatit (1,1):

1, jos argumentti on positiivinen;

0, jos argumentti on tyhjä;

-1, jos argumentti on negatiivinen.

Tällaisen makron luominen Excelille alkaa tavallisella tavalla, käyttämällä Alt- ja F11-pikanäppäimiä. Seuraavaksi kirjoitetaan seuraava koodi:

Alaohjelma()

x= Solut(1, 1).Arvo (tämä komento määrittää x:lle solun sisällön arvon koordinaateissa (1, 1))

Jos x>0, sitten Solut(1, 1).Arvo = 1

Jos x=0, sitten Solut(1, 1).Arvo = 0

Jos x<0 Then Cells(1, 1).Value = -1

Lopeta ala.

Jäljelle jää vain suorittaa makro ja saada argumentille vaadittu arvo Excelissä.

VBA-toiminnot

Kuten olet ehkä jo huomannut, ohjelmointi Microsoftin tunnetuimmalla taulukkolaskentaprosessorilla ei ole niin vaikeaa. Varsinkin jos opit käyttämään VBA-toimintoja. Tällä ohjelmointikielellä, joka on luotu erityisesti sovellusten kirjoittamiseen Excelissä ja Wordissa, on yhteensä noin 160 toimintoa. Ne voidaan jakaa useisiin suuriin ryhmiin. Tämä:

• Matemaattiset funktiot. Kun niitä sovelletaan argumenttiin, he saavat kosinin arvon, luonnollisen logaritmin, kokonaislukuosan jne.
• Taloudelliset toiminnot. Niiden saatavuuden ja Excel-ohjelmoinnin ansiosta saat tehokkaat työkalut kirjanpitoon ja talouslaskelmiin.
• Taulukon käsittelytoiminnot. Näitä ovat Array, IsArray; LBound; UBound.
• Excelin VBA-funktiot merkkijonolle. Tämä on melko suuri ryhmä. Tämä sisältää esimerkiksi funktiot Space, jolla luodaan merkkijono, jonka välilyöntien määrä on yhtä suuri kuin kokonaislukuargumentti, tai Asc, joka muuntaa merkit ANSI-koodiksi. Kaikkia niitä käytetään laajalti, ja niiden avulla voit työskennellä riveillä Excelissä luomalla sovelluksia, jotka helpottavat huomattavasti näiden taulukoiden kanssa työskentelyä.
• Tietotyyppien muunnostoiminnot. Esimerkiksi CVar palauttaa Lauseke-argumentin arvon muuntamalla sen Variant-tietotyypiksi.
• Toiminnot päivämäärien kanssa työskentelemiseen. Ne laajentavat huomattavasti tavallisia, joten WeekdayName-funktio palauttaa viikonpäivän nimen (koko tai osittainen) sen numerolla. Vielä hyödyllisempi on ajastin. Se ilmoittaa sekuntien lukumäärän, joka on kulunut keskiyöstä tiettyyn pisteeseen vuorokaudessa.
• Funktiot numeerisen argumentin muuntamiseksi erilaisiksi lukujärjestelmiksi. Esimerkiksi Oct tulostaa numerot oktaalimuodossa.
• Muotoilutoiminnot. Tärkein niistä on muoto. Se palauttaa Variant-arvon lausekkeella, joka on muotoiltu muotoilmoituksessa määritettyjen ohjeiden mukaan.
• jne.

Näiden funktioiden ominaisuuksien ja niiden soveltamisen tutkiminen laajentaa merkittävästi Excelin soveltamisalaa.

Esimerkki 5

Yritetään edetä monimutkaisempien ongelmien ratkaisemiseen. Esimerkiksi:

Toimitetaan paperiasiakirja, joka kertoo yrityksen todellisen kustannustason. Edellytetään:

• kehittää sen malliosa Excel-laskentataulukon avulla;
• luo VBA-ohjelma, joka pyytää alkutietoja sen täyttämiseksi, suorittaa tarvittavat laskelmat ja täyttää vastaavat mallisolut niillä.

Mietitään yhtä ratkaisuvaihtoehtoa.

Luo malli

Kaikki toiminnot suoritetaan vakiolomakkeella Excelissä. Vapaat solut on varattu tietojen syöttämiseen kuluttajayrityksen nimestä, kustannusten määrästä, tasosta ja liikevaihdosta. Koska raporttia laadittavien yritysten (yhdistysten) lukumäärä ei ole kiinteä, soluja tulosten ja asiantuntijan nimeen perustuvien arvojen syöttämiseen ei varata etukäteen. Tehtävälistalle annetaan uusi nimi. Esimerkiksi "raportit".

Muuttujat

Jotta voit kirjoittaa ohjelman, joka täyttää mallin automaattisesti, sinun on valittava merkinnät. Näitä käytetään muuttujille:

• NN - nykyisen taulukon rivin numero;
• TP ja TF - suunniteltu ja todellinen kaupan liikevaihto;
• SF ja SP - toteutuneiden ja suunniteltujen kustannusten määrä;
• IP ja IF - suunniteltu ja todellinen kustannustaso.

Merkitään tämän sarakkeen kokonaissumman kertymistä samoilla kirjaimilla, mutta "etuliitteellä" Itog. Esimerkiksi ItogTP - viittaa taulukon sarakkeeseen, jonka otsikko on "suunniteltu liikevaihto".

Ongelman ratkaiseminen VBA-ohjelmoinnin avulla

Esitettyjen merkintöjen avulla saadaan kaavat poikkeamille. Jos sinun on laskettava prosentteina, meillä on (F - P) / P * 100 ja yhteensä - (F - P).

Näiden laskelmien tulokset voidaan parhaiten syöttää suoraan Excel-laskentataulukon sopiviin soluihin.

Todelliset ja ennustetut kokonaissummat saadaan käyttämällä kaavoja ItogP=ItogP + P ja ItogF=ItogF+ F.

Käytä poikkeamien osalta = (ItogF - ItogP) / ItogP * 100, jos laskenta suoritetaan prosentteina, ja kokonaisarvon tapauksessa - (ItogF - ItogP).

Tulokset kirjoitetaan jälleen välittömästi sopiviin soluihin, joten niitä ei tarvitse liittää muuttujiin.

Ennen luodun ohjelman suorittamista sinun on tallennettava työkirja esimerkiksi nimellä "Raportti1.xls".

"Luo raporttitaulukko" -painiketta tarvitsee painaa vain kerran otsikkotietojen syöttämisen jälkeen. On muitakin sääntöjä, jotka sinun pitäisi tietää. Erityisesti "Lisää rivi" -painiketta on napsautettava joka kerta, kun olet syöttänyt arvot kullekin toiminnolle taulukkoon. Kun olet syöttänyt kaikki tiedot, sinun on napsautettava "Valmis"-painiketta ja vaihdettava sitten Excel-ikkunaan.

Nyt tiedät kuinka ratkaista Excel-ongelmia makrojen avulla. Vba excelin käyttötaitoa (katso esimerkkejä ohjelmista yllä) saattaa myös tarvita toimiakseen tällä hetkellä suosituimmassa tekstieditorissa Word. Erityisesti voit luoda valikkopainikkeita kirjoittamalla, kuten artikkelin alussa näkyy, tai kirjoittamalla koodia, jonka ansiosta monet tekstin toiminnot voidaan suorittaa painamalla tehtävänäppäimiä tai "Näytä"-välilehden kautta ja "Makrot" -kuvaketta.