VBA:n "toiminto" ja "ali" -menettelyt

Sisäänrakennetut VBA-toiminnot

Ennen kuin aloitat omien VBA-funktioiden luomisen, on hyvä tietää, että Excel VBA:ssa on runsas kokoelma valmiita sisäänrakennettuja toimintoja, joita voit käyttää koodia kirjoittaessasi.

Luettelo näistä toiminnoista voidaan tarkastella VBA-editorissa:

• Avaa Excel-työkirja ja käynnistä VBA-editori (klikkaa tehdäksesi tämän Alt + F11) ja paina sitten F2.
• Valitse kirjasto näytön vasemmassa yläkulmassa olevasta avattavasta luettelosta VBA.
• Näkyviin tulee luettelo sisäänrakennetuista VBA-luokista ja funktioista. Napsauta funktion nimeä nähdäksesi sen lyhyen kuvauksen ikkunan alareunassa. painamalla F1 avaa kyseisen ominaisuuden online-ohjesivun.

Lisäksi täydellinen luettelo sisäänrakennetuista VBA-toiminnoista esimerkkeineen löytyy Visual Basic Developer Centeristä.

Mukautetut menettelyt "Function" ja "Sub" VBA:ssa

Excel Visual Basicissa joukko komentoja, jotka suorittavat tietyn tehtävän, sijoitetaan toimintosarjaan. Toiminto (toiminto) tai alla (alirutiini). Tärkein ero menettelyjen välillä Toiminto и alla onko se menettelytapa Toiminto palauttaa tuloksen, menettelyn alla – ei.

Siksi, jos sinun on suoritettava toimintoja ja saatava jokin tulos (esimerkiksi summaa useita numeroita), menettelyä käytetään yleensä Toiminto, ja jotta voit yksinkertaisesti suorittaa joitain toimintoja (esimerkiksi muuttaa soluryhmän muotoilua), sinun on valittava menettely alla.

argumentit

VBA-proseduureille voidaan siirtää erilaisia ​​tietoja argumenttien avulla. Argumenttiluettelo määritetään proseduuria määritettäessä. Esimerkiksi menettely alla VBA:ssa lisää annetun kokonaisluvun (Integer) jokaiseen valitun alueen soluun. Voit välittää tämän luvun menettelyyn käyttämällä argumenttia, kuten seuraava:

Sub AddToCells(i As Integer) ... End Sub

Muista, että sinulla on argumentteja menettelyille Toiminto и alla VBA:ssa on valinnainen. Jotkut menettelyt eivät vaadi argumentteja.

Valinnaiset argumentit

VBA-proseduureilla voi olla valinnaisia ​​argumentteja. Nämä ovat argumentteja, jotka käyttäjä voi halutessaan määrittää, ja jos ne jätetään pois, menettely käyttää niille oletusarvoja.

Palatakseni edelliseen esimerkkiin, jotta funktion kokonaislukuargumentti olisi valinnainen, se ilmoitettaisiin seuraavasti:

SubAdToCells (valinnainen i kokonaislukuna = 0)

Tässä tapauksessa kokonaislukuargumentti i oletusarvo on 0.

Proseduurissa voi olla useita valinnaisia ​​argumentteja, jotka kaikki on lueteltu argumenttiluettelon lopussa.

Argumenttien välittäminen arvon ja viittauksen mukaan

VBA:n argumentit voidaan siirtää prosessiin kahdella tavalla:

• By Val – argumentin välittäminen arvon mukaan. Tämä tarkoittaa, että vain arvo (eli argumentin kopio) välitetään proseduurille, ja siksi kaikki prosessin sisällä argumenttiin tehdyt muutokset menetetään, kun toiminto suljetaan.
• ByRef – argumentin välittäminen viitteellä. Toisin sanoen muistissa olevan argumentin sijainnin todellinen osoite välitetään proseduurille. Proseduurin sisällä argumenttiin tehdyt muutokset tallennetaan, kun toiminto suljetaan.

Avainsanojen käyttäminen By Val or ByRef toimintoilmoituksessa voit määrittää, kuinka argumentti välitetään proseduurille. Tämä näkyy alla olevissa esimerkeissä:

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

Muista, että VBA:n argumentit välitetään oletuksena viittauksella. Toisin sanoen, jos avainsanoja ei käytetä By Val or ByRef, argumentti välitetään viittauksella.

Ennen kuin jatkat toimenpiteitä Toiminto и alla yksityiskohtaisemmin, on hyödyllistä tarkastella uudelleen näiden kahden tyyppisten menettelyjen ominaisuuksia ja eroja. Seuraavassa on lyhyt keskustelu VBA-menettelyistä Toiminto и alla ja yksinkertaisia ​​esimerkkejä näytetään.

VBA-menettely "toiminto"

VBA-editori tunnistaa menettelyn Toimintokun se kohtaa ryhmän komentoja, jotka on suljettu seuraavien aloitus- ja sulkemiskäskyjen väliin:

Toiminto ... Lopeta toiminto

Kuten aiemmin mainittiin, menettely Toiminto VBA:ssa (toisin kuin alla) palauttaa arvon. Palautusarvoihin sovelletaan seuraavia sääntöjä:

• Palautusarvon tietotyyppi on ilmoitettava proseduurin otsikossa Toiminto.
• Palautusarvon sisältävä muuttuja on nimettävä samalla tavalla kuin toimintosarja Toiminto. Tätä muuttujaa ei tarvitse ilmoittaa erikseen, koska se on aina olemassa kiinteänä osana menettelyä. Toiminto.

Tämä näkyy hyvin seuraavassa esimerkissä.

Esimerkki VBA-funktiosta: Matemaattisen operaation suorittaminen 3 numerolle

Seuraavassa on esimerkki VBA-menettelykoodista Toiminto, joka vaatii kolme tyyppistä argumenttia Kaksinkertainen (kaksoistarkkuudet liukulukuluvut). Tämän seurauksena toiminto palauttaa toisen tyyppinumeron Kaksinkertainenyhtä suuri kuin kahden ensimmäisen argumentin summa miinus kolmas argumentti:

Funktio SumMiinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) Kuten Double SumMiinus = dNum1 + dNum2 - dNum3 Loppufunktio

Tämä erittäin yksinkertainen VBA-menettely Toiminto havainnollistaa, kuinka data välitetään prosessiin argumenttien kautta. Voit nähdä, että proseduurin palauttama tietotyyppi on määritetty muodossa Kaksinkertainen (sanat sanovat Kuten Double argumenttiluettelon jälkeen). Tämä esimerkki osoittaa myös, kuinka toimenpiteen tulos Toiminto tallennettu muuttujaan, jolla on sama nimi kuin prosessin nimi.

VBA-menettelyn kutsuminen "toiminto"

Jos yllä oleva yksinkertainen menettely Toiminto lisättynä moduuliin Visual Basic -editorissa, sitä voidaan kutsua muista VBA-proseduureista tai käyttää Excel-työkirjan laskentataulukossa.

Kutsu VBA-menettely "Function" toisesta menettelystä

menettely Toiminto voidaan kutsua toisesta VBA-proseduurista yksinkertaisesti määrittämällä se muuttujaan. Seuraava esimerkki näyttää kutsun proseduuriin Summinus, joka määriteltiin edellä.

Sub main() Dim total as Double Total = SumMiinus(5, 4, 3) End Sub

Kutsu VBA-menettely "Function" laskentataulukosta

VBA-menettely Toiminto voidaan kutsua Excel-laskentataulukosta samalla tavalla kuin mitä tahansa muuta sisäänrakennettua Excel-funktiota. Siksi edellisessä esimerkissä luotu menettely Toiminto - Summinus voidaan kutsua syöttämällä seuraava lauseke laskentataulukon soluun:

=SumMinus(10, 5, 2)

VBA-menettely «Sub»

VBA-editori ymmärtää, että sen edessä on menettely allakun se kohtaa ryhmän komentoja, jotka on suljettu seuraavien aloitus- ja sulkemiskäskyjen väliin:

Sub... End Sub

VBA-menettely "Sub": Esimerkki 1. Keskitasaus ja fonttikoko muuttuvat valitussa solualueella

Harkitse esimerkkiä yksinkertaisesta VBA-menettelystä alla, jonka tehtävänä on muuttaa valitun solualueen muotoilua. Solut keskitetään (sekä pysty- että vaakasuunnassa) ja fonttikoko muutetaan käyttäjän määrittämäksi:

Alamuoto_keskitetty_ja_koko(valinnainen iFontSize As Integer = 10) Selection.Horizontal Alignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Tämä menettely alla suorittaa toimintoja, mutta ei palauta tulosta.

Tässä esimerkissä käytetään myös valinnaista argumenttia Fonttikoko. Jos argumentti Fonttikoko ei siirretty menettelyyn alla, niin sen oletusarvo on 10. Kuitenkin, jos argumentti Fonttikoko siirretty menettelyyn alla, sitten valittu solualue asetetaan käyttäjän määrittämän fonttikoon mukaan.

VBA-alimenettely: Esimerkki 2: Keskitä ja lihavoita fontti valitulla solualueella

Seuraava menettely on samanlainen kuin juuri käsitelty, mutta tällä kertaa koon muuttamisen sijaan se käyttää lihavoitua kirjasintyyliä valittuun solualueeseen. Tämä on esimerkkimenettely alla, joka ei vaadi argumentteja:

Alimuoto_keskitetty_ja_lihavoitu() valinta.Vaakasuuntaus = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

"Sub"-menettelyn kutsuminen Excel VBA:ssa

Kutsu VBA-menettely "Sub" toisesta menettelystä

VBA-menettelyn kutsuminen alla toisesta VBA-menettelystä, sinun on kirjoitettava avainsana Puhelu, menettelyn nimi alla ja edelleen sulkeissa on menettelyn argumentit. Tämä näkyy alla olevassa esimerkissä:

Sub main() Call Format_Centered_And_Sized(20) End Sub

Jos menettely Muoto_keskitetty_ja_koko on useampi kuin yksi argumentti, ne on erotettava pilkuilla. Kuten tämä:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Kutsu VBA-menettely "Sub" laskentataulukosta

menettely alla ei voi syöttää suoraan Excel-arkin soluun, kuten voidaan tehdä toimenpiteellä Toimintokoska menettely alla ei palauta arvoa. Kuitenkin menettelyt alla, joilla ei ole argumentteja ja jotka ilmoitetaan nimellä julkinen (kuten alla näkyy) on laskentataulukon käyttäjien saatavilla. Näin ollen, jos edellä käsitellyt yksinkertaiset menettelyt alla lisätty Visual Basic Editorin moduuliin, menettely Muoto_keskitetty_ja_lihavoitu on käytettävissä Excel-laskentataulukossa ja menettelyssä Muoto_keskitetty_ja_koko – ei ole saatavilla, koska sillä on argumentteja.

Tässä on helppo tapa suorittaa (tai suorittaa) toimenpide alla, pääsee laskentataulukosta:

• lehdistö Alt + F8 (paina näppäintä muut ja pidä sitä alhaalla ja paina näppäintä F8).
• Valitse näkyviin tulevasta makrojen luettelosta se, jonka haluat suorittaa.
• lehdistö ajaa (juosta)

Toimenpiteen suorittamiseksi alla nopeasti ja helposti, voit määrittää siihen pikanäppäimen. Tätä varten:

• lehdistö Alt + F8.
• Valitse näkyviin tulevasta makroluettelosta se, jolle haluat määrittää pikanäppäimen.
• lehdistö parametrit (Asetukset) ja kirjoita pikanäppäin näkyviin tulevassa valintaikkunassa.
• lehdistö OK ja sulje valintaikkuna Makro (Makro).

Huomio: Kun määrität makrolle pikanäppäimen, varmista, että sitä ei käytetä vakiona Excelissä (esim. Ctrl + C). Jos valitset jo olemassa olevan pikanäppäimen, se määritetään uudelleen makrolle, minkä seurauksena käyttäjä voi käynnistää makron vahingossa.

VBA-menettelyn laajuus

Tämän opetusohjelman osassa 2 käsiteltiin muuttujien ja vakioiden laajuutta ja avainsanojen roolia. julkinen и yksityinen. Näitä avainsanoja voidaan käyttää myös VBA-menettelyjen kanssa:

Julkiset SubAdToCells(i Integer) ... End Sub

Yksityiset SubAdToCells(i Integer) ... End Sub

Muista, että jos ennen VBA-menettelyn ilmoittamista Toiminto or alla avainsanaa ei ole lisätty, oletusominaisuus on asetettu menettelylle julkinen (eli se on saatavilla kaikkialla tässä VBA-projektissa). Tämä on toisin kuin muuttujailmoitukset, jotka oletuksena ovat yksityinen.

Varhainen poistuminen VBA-menettelyistä "Function" ja "Sub"

Jos sinun on lopetettava VBA-menettelyn suorittaminen Toiminto or alla, odottamatta sen luonnollista loppua, niin tälle on operaattorit Poistu toiminto и Exit Sub. Näiden operaattoreiden käyttö on esitetty alla esimerkkinä yksinkertaisella menettelyllä. ToimintoA, joka odottaa saavansa positiivisen argumentin lisätoimintojen suorittamiseksi. Jos proseduurille välitetään ei-positiivinen arvo, muita toimintoja ei voida suorittaa, joten käyttäjälle tulee näyttää virheilmoitus ja toimenpiteen tulee lopettaa välittömästi:

Funktio VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 Jos sVAT_Rate <= 0 Then MsgBox "Odotettiin positiivista arvoa sVAT_Rate, mutta vastaanotettu" & sVAT_Rate Poistu funktiosta Lopeta jos... Lopeta toiminto

Huomaa, että ennen toimenpiteen suorittamista Toiminto - ALV_Summa, sisäänrakennettu VBA-toiminto lisätään koodiin MsgBox, joka näyttää varoituksen ponnahdusikkunan käyttäjälle.