Tässä opetusohjelmassa opimme Excel VBA -toiminnosta
1) Mikä on Visual Basic Excelissä?
2) Kuinka käyttää VBA: ta Excelissä?
3) Miten luodaan käyttäjän määrittämä toiminto?
4) Kuinka kirjoittaa makro?
Kuinka kirjoittaa VBA -koodi
Excel tarjoaa käyttäjälle laajan kokoelman valmiita toimintoja, enemmän kuin tarpeeksi keskivertokäyttäjän tyydyttämiseksi. Monia muita voidaan lisätä asentamalla saatavilla olevat lisäosat. Useimmat laskelmat voidaan tehdä sillä, mitä tarjotaan, mutta ei ole kauaa ennen kuin huomaat haluavasi, että jokin toiminto olisi tehnyt tietyn työn, etkä löydä luettelosta mitään sopivaa. Tarvitset UDF: n. UDF (User Defined Function) on yksinkertaisesti toiminto, jonka luot itse VBA: n avulla. UDF -tiedostoja kutsutaan usein "mukautetuiksi toiminnoiksi". UDF voi jäädä työkirjaan liitettyyn koodimoduuliin, jolloin se on aina käytettävissä, kun kyseinen työkirja on auki. Vaihtoehtoisesti voit luoda oman lisäosan, joka sisältää yhden tai useamman toiminnon, jotka voit asentaa Exceliin aivan kuten kaupallinen lisäosa. UDF -tiedostoihin pääsee myös koodimoduuleilla. Usein kehittäjät luovat UDF -tiedostot toimimaan yksinomaan VBA -menettelyn koodin puitteissa, eikä käyttäjä ole koskaan tietoinen niiden olemassaolosta. Kuten mikä tahansa toiminto, UDF voi olla niin yksinkertainen tai monimutkainen kuin haluat. Aloitetaan helpolla…
Funktio suorakulmion alueen laskemiseksi
Kyllä, tiedän, että voit tehdä tämän päähäsi! Konsepti on hyvin yksinkertainen, joten voit keskittyä tekniikkaan. Oletetaan, että tarvitset funktion suorakulmion pinta -alan laskemiseen. Selaat Excelin toimintojen kokoelmaa, mutta sopivaa ei ole. Tämä laskelma on suoritettava:
ALUE = PITUUS x LEVEYS
Avaa uusi työkirja ja avaa Visual Basic Editor (Työkalut> Makro> Visual Basic Editor tai ALT+F11).
Tarvitset moduulin, johon voit kirjoittaa funktion, joten valitse Lisää> Moduuli. Tyhjään moduulityyppiin: Toiminta -alue ja paina TULLA SISÄÄN.Visual Basic Editor täydentää rivin puolestasi ja lisää End Function -rivin ikään kuin olisit luomassa aliohjelmaa. Toistaiseksi se näyttää tältä …
Toimintoalue () Lopeta toiminto
Vie kohdistin "Area" -merkin jälkeen sulkeiden väliin. Jos olet koskaan miettinyt, mihin kiinnikkeet on tarkoitettu, saat selville! Määritämme "argumentit", joita toimintamme käyttää (an Perustelu on tieto, joka tarvitaan laskennan tekemiseen). Tyyppi Pituus kaksinkertaisena, leveys kaksinkertaisena ja napsauta alla olevaa tyhjää riviä. Huomaa, että kirjoittaessasi esiin tulee vieritysruutu, jossa luetellaan kaikki kirjoittamasi asiat.
Tätä ominaisuutta kutsutaan Automaattinen luettelo jäsenistä. Jos se ei myöskään näy, se on kytketty pois päältä (kytke se päälle klo Työkalut> Asetukset> Editor) tai olet saattanut tehdä kirjoitusvirheen aikaisemmin. Se on erittäin hyödyllinen syntaksin tarkistus. Etsi tarvitsemasi kohde ja kaksoisnapsauta sitä lisätäksesi koodisi. Voit jättää sen huomiotta ja kirjoittaa vain, jos haluat. Koodisi näyttää nyt tältä…
Toiminta -alue (pituus kaksinkertaisena, leveys kaksinkertaisena) Pääte toiminto
Argumenttien tietotyypin ilmoittaminen ei ole pakollista, mutta järkevää. Olisit voinut kirjoittaa Pituus leveys ja jätti sen sellaiseksi, mutta varoitus Excelille, mitä tietotyyppiä odottaa, auttaa koodiasi toimimaan nopeammin ja havaitsee syöttövirheet. The kaksinkertainen tietotyyppi viittaa numeroon (joka voi olla hyvin suuri) ja sallii murtoluvut. Nyt itse laskelmaan. Paina ensin tyhjän rivin näppäintä SARKAIN sisennä koodisi (helpottaa lukemista) ja kirjoita Alue = Pituus * Leveys. Tässä valmis koodi…
Toiminta -alue (pituus kaksinkertaisena, leveys kaksinkertaisena) Alue = pituus * leveysfunktio
Huomaat toisen Visual Basic Editorin ohjeominaisuuden avautuvan kirjoittaessasi, Automaattinen pika -info…
Se ei liity tähän. Sen tarkoitus on auttaa sinua kirjoittamaan funktioita VBA: ssa kertomalla, mitä argumentteja tarvitaan. Voit testata toimintaasi heti. Vaihda Excel -ikkunaan ja kirjoita lukuun Pituus ja Leveys erillisiin soluihin. Anna kolmanteen soluun toiminto, ikään kuin se olisi yksi sisäänrakennetuista. Tässä esimerkissä solu A1 sisältää pituuden (17) ja solu B1 leveyden (6.5). Kirjoitin C1: een = alue (A1, B1) ja uusi funktio laski alueen (110,5)…
Joskus funktion argumentit voivat olla valinnaisia. Tässä esimerkissä voimme tehdä Leveys argumentti valinnainen. Oletetaan, että suorakulmio on neliö, jonka pituus ja leveys ovat yhtä suuret. Jotta käyttäjä ei joutuisi syöttämään kahta argumenttia, voisimme antaa heidän syöttää vain pituuden ja käyttää funktiota käyttämään kyseistä arvoa kahdesti (eli kertomaan pituus x pituus). Joten toiminto tietää, milloin se voi tehdä tämän, meidän on sisällytettävä IF -lausunto auttamaan sen päättämisessä. Muuta koodi niin, että se näyttää tältä…
Toiminta -alue (pituus kaksinkertaisena, valinnainen leveys vaihtoehtona) If IsMissing (Width) then Area = Length * Length Else Area = Length * Width End if End Function
Huomaa, että leveyden tietotyypiksi on vaihdettu Vaihtoehto sallia nolla -arvot. Toiminnon avulla käyttäjä voi nyt syöttää vain yhden argumentin, esim. = alue (A1)Toiminnon IF -lause tarkistaa, onko Leveys -argumentti annettu, ja laskee sen mukaan…
Toiminto polttoaineen kulutuksen laskemiseksi
Haluan valvoa autoni polttoaineenkulutusta, joten kun ostan polttoainetta, merkitsen muistiin mittarilukeman ja sen, kuinka paljon polttoainetta tarvitaan tankin täyttämiseen. Täällä Isossa -Britanniassa polttoainetta myydään litroina. Auton milometri (OK, joten se on matkamittari) tallentaa etäisyyden kilometreinä. Ja koska olen liian vanha ja tyhmä muuttaakseni, ymmärrän vain MPG: n (mailia / gallona). Jos nyt luulet, että kaikki on hieman surullista, entä tämä. Kun tulen kotiin, avaan Excelin ja kirjoitan tiedot laskentataulukkoon, joka laskee MPG -arvon minulle ja kuvaa auton suorituskykyä. Laskelma on kilometrimäärä, jonka auto on kulkenut viimeisen täyttökerran jälkeen, jaettuna käytettyjen polttoaineiden määrällä…
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF FUEL
mutta koska polttoainetta tulee litroina ja gallonaa on 4,546 litraa …
MPG = (MILES THIS FILL - MILES LAST FILL) / POLTTOAINEEN Litraa x 4,546
Näin kirjoitin toiminnon …
Toiminto MPG (StartMiles kokonaislukuna, FinishMiles kokonaislukuna, litraa yksittäisenä) MPG = (FinishMiles - StartMiles) / litraa * 4.546 Lopputoiminto
ja tältä se näyttää laskentataulukossa …
Kaikki toiminnot eivät tee matemaattisia laskelmia. Tässä yksi, joka tarjoaa tietoa…
Toiminto, joka antaa päivän nimen
Minulta kysytään usein, onko olemassa päivämääräfunktiota, joka antaa viikonpäivän tekstinä (esim. Maanantai). Vastaus on ei*, mutta sellaisen luominen on melko helppoa. (*Lisäys: Sanoinko ei? Tarkista alla oleva huomautus nähdäksesi unohtuneen toiminnon!). Excelissä on WEEKDAY -funktio, joka palauttaa viikonpäivän numeroksi 1-7. Voit valita, mikä päivä on 1, jos et pidä oletusarvosta (sunnuntai). Alla olevassa esimerkissä funktio palauttaa "5", jonka tiedän tarkoittavan "torstaita".
Mutta en halua nähdä numeroa, haluan nähdä "torstain". Voisin muokata laskentaa lisäämällä VLOOKUP -funktion, joka viittasi jonnekin taulukkoon, joka sisältää numeroiden luettelon ja vastaavan luettelon päivän nimistä. Tai voisin pitää koko asian itsenäisenä useiden sisäkkäisten IF-lausekkeiden kanssa. Liian monimutkainen! Vastaus on mukautettu toiminto …
Toiminto DayName (InputDate As Date) Dim DayNumber kokonaislukuna DayNumber = Weekday (InputDate, vbSunday) Valitse Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Tapaus 5 DayName = "Thursday" Case 6 DayName = "Friday" Case 7 DayName = "Saturday" End Valitse End Function
Olen kutsunut toimintoani "DayName" ja se vaatii yhden argumentin, jota kutsun "InputDate", jonka (tietysti) on oltava päivämäärä. Näin se toimii…
- Funktion ensimmäinen rivi ilmoittaa muuttujan, jonka olen nimittänyt "DayNumber" ja joka on kokonaisluku (eli kokonaisluku).
- Funktion seuraava rivi määrittää muuttujalle arvon Excelin WEEKDAY -funktion avulla. Arvo on luku 1 ja 7. Vaikka oletusarvo on 1 = sunnuntai, olen lisännyt sen joka tapauksessa selvyyden vuoksi.
- Lopuksi a Tapausselostus tutkii muuttujan arvon ja palauttaa sopivan tekstin.
Tältä se näyttää laskentataulukossa…
Omien toimintojen käyttäminen
Jos työkirjaan on liitetty VBA -koodimoduuli, joka sisältää mukautettuja toimintoja, kyseiset toiminnot voidaan helposti käsitellä samassa työkirjassa, kuten yllä olevat esimerkit osoittavat. Käytät funktion nimeä ikään kuin se olisi yksi Excelin sisäänrakennetuista toiminnoista.
Löydät myös toiminnot, jotka on lueteltu ohjatussa toimintotoiminnossa (jota kutsutaan joskus Liitä toiminto -työkaluksi). Ohjatun toiminnon avulla voit lisätä toiminnon normaalisti (Lisää> Toiminto).
Selaa löydettävien toimintoluokkien luetteloa alaspäin Käyttäjän määrittelemä ja valitse se nähdäksesi luettelon käytettävissä olevista UDF -tiedostoista…
Voit nähdä, että käyttäjän määrittämistä toiminnoista puuttuu muu kuvaus kuin hyödytön "Apua ei ole saatavilla" -viesti, mutta voit lisätä lyhyen kuvauksen…
Varmista, että olet työkirjassa, joka sisältää toiminnot. Mene Työkalut> Makro> Makrot. Et näe toimintojasi tässä luettelossa, mutta Excel tietää niistä! Kohteessa Makron nimi kirjoita toiminnon nimi ja napsauta sitten valintaikkunaa Asetukset -painiketta. Jos painike on harmaana, olet kirjoittanut funktion nimen väärin tai olet väärässä työkirjassa tai sitä ei ole olemassa! Tämä avaa toisen valintaikkunan, johon voit kirjoittaa toiminnon lyhyen kuvauksen. Klikkaus OK kuvauksen tallentamiseksi ja (tässä hämmentävä kohta) napsauttamalla Peruuttaa sulje Makro -valintaikkuna. Muista tallentaa toiminto sisältävä työkirja. Seuraavan kerran, kun menet ohjattuun toimintoon, UDF: lläsi on kuvaus …
Makrojen tapaan käyttäjän määrittämiä toimintoja voidaan käyttää missä tahansa muussa työkirjassa, kunhan niitä sisältävä työkirja on auki. Tämä ei kuitenkaan ole hyvä käytäntö. Toiminnon syöttäminen toiseen työkirjaan ei ole helppoa. Sinun on lisättävä sen isäntätyökirjan nimi funktion nimeen. Tämä ei ole vaikeaa, jos luotat ohjattuun toimintoon, mutta hankala kirjoittaa käsin. Ohjattu toimintotoiminto näyttää muiden työkirjojen kaikkien UDF -tiedostojen täydelliset nimet …
Jos avaat työkirjan, jossa käytät toimintoa, kun toiminto sisältävä työkirja on suljettu, näet virheilmoituksen solussa, jossa käytät toimintoa. Excel on unohtanut sen! Avaa toiminnon isäntätyökirja, laske uudelleen ja kaikki on taas hyvin. Onneksi on olemassa parempi tapa.
Jos haluat kirjoittaa käyttäjän määrittämiä toimintoja käytettäväksi useammassa kuin yhdessä työkirjassa, paras tapa on luoda Excel Lisätä. Lisätietoja tämän tekemisestä on opetusohjelmassa Rakenna Excel-apuohjelma.
Lisäys
Minun pitäisi todella tietää paremmin! Älä koskaan, älä koskaan, älä koskaan! Kerroin teille, että ei ole toimintoa, joka antaa päivän nimen, muistan nyt sen, joka voi. Katso tätä esimerkkiä…
TEKSTI -funktio palauttaa solun arvon tekstinä tietyssä numeromuodossa. Joten esimerkissä olisin voinut valita = TEKSTI (A1, "ddd") palata "to", = TEKSTI (A1, "mmmm") palauttaa "Syyskuu" jne. Excelin ohjeessa on muutamia esimerkkejä tämän toiminnon käytöstä.
Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa.
Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille osoitteeseen sähköpostisivusto