Selles õpetuses õpime tundma Exceli VBA funktsiooni
1) Mis on Excelis Visual Basic?
2) Kuidas VBA -d Excelis kasutada?
3) Kuidas luua kasutaja määratud funktsiooni?
4) Kuidas makro kirjutada?
Kuidas kirjutada VBA koodi
Excel pakub kasutajale suure hulga valmisfunktsioone, mis on tavakasutaja rahuldamiseks enam kui piisav. Saadaolevate lisandmoodulite installimisega saab lisada palju muud. Enamikku arvutusi on võimalik saavutada pakutavaga, kuid ei lähe kaua aega enne, kui leiate, et soovite, et oleks mõni funktsioon, mis tegi teatud tööd, ja te ei leia loendist midagi sobivat. Teil on vaja UDF -i. UDF (kasutaja määratud funktsioon) on lihtsalt funktsioon, mille loote ise VBA abil. UDF -e nimetatakse sageli "kohandatud funktsioonideks". UDF võib jääda töövihiku külge kinnitatud koodimoodulisse, sel juhul on see alati saadaval, kui see töövihik on avatud. Teise võimalusena saate luua oma lisandmooduli, mis sisaldab ühte või mitut funktsiooni, mille saate Excelisse installida nagu kommertslik lisandmoodul. UDF -idele pääseb juurde ka koodimoodulite abil. Sageli loovad UDF -id arendajad, et nad töötaksid ainult VBA -protseduuri koodi piires ja kasutaja pole nende olemasolust kunagi teadlik. Nagu iga funktsioon, võib ka UDF olla nii lihtne või keeruline kui soovite. Alustame lihtsast…
Funktsioon ristküliku pindala arvutamiseks
Jah, ma tean, et saate seda oma peas teha! Kontseptsioon on väga lihtne, nii et saate keskenduda tehnikale. Oletame, et vajate funktsiooni ristküliku pindala arvutamiseks. Sirvite Exceli funktsioonide kogu, kuid sobivat pole. See arvutamine tuleb teha:
PIIRKOND = LENGTH x WIDTH
Avage uus töövihik ja seejärel Visual Basic Editor (Tööriistad> Makro> Visual Basic Editor või ALT+F11).
Teil on vaja moodulit, kuhu oma funktsioon kirjutada, nii et valige Lisa> Moodul. Tühja mooduli tüüpi: Funktsioonipiirkond ja vajutage SISENEMA.Visual Basic Editor täiendab teie eest rida ja lisab lõppfunktsiooni rea nii, nagu looksite alamprogrammi. Siiani näeb see välja selline…
Funktsiooniala () Lõppfunktsioon
Asetage kursor sulgude vahele pärast ala. Kui olete kunagi mõelnud, milleks sulgud on mõeldud, saate kohe teada! Täpsustame "argumente", mida meie funktsioon võtab (an vaidlus on arvutamiseks vajalik teave). Tüüp Pikkus kahekordne, laius kahekordne ja klõpsake selle all olevat tühja rida. Pange tähele, et tippimise ajal ilmub kerimisaken, kus on loetletud kõik teie kirjutatavale sobivad asjad.
Seda funktsiooni nimetatakse Liikmete automaatne loend. Kui see ei ilmu, on see välja lülitatud (lülitage see sisse kell Tööriistad> Valikud> Redaktor) või tegite trükivea varem. See on teie süntaksi väga kasulik kontroll. Leidke vajalik element ja topeltklõpsake seda oma koodi sisestamiseks. Saate seda ignoreerida ja soovi korral lihtsalt tippida. Teie kood näeb nüüd välja selline…
Funktsioonipiirkond (pikkus kahekordne, laius kahekordne) lõppfunktsioon
Argumentide andmetüübi deklareerimine ei ole kohustuslik, kuid on mõttekas. Oleks võinud kirjutada Pikkus laius ja jättis selle selliseks, kuid Exceli hoiatamine, millist andmetüüpi oodata, aitab teie koodil kiiremini töötada ja tuvastab sisendvead. The kahekordne andmetüüp viitab arvule (mis võib olla väga suur) ja lubab murde. Nüüd arvutuse enda kohta. Vajutage tühjal real kõigepealt nuppu TAB klahvi, et sisestada kood sisse (hõlbustada lugemist) ja sisestada Ala = pikkus * laius. Siin on täielik kood…
Funktsioonipiirkond (pikkus kahekordne, laius kahekordne) Ala = pikkus * laiuse lõppfunktsioon
Märkate, et tippimise ajal ilmub veel üks Visual Basic Editori abifunktsioon, Automaatne kiire teave…
See pole siin asjakohane. Selle eesmärk on aidata teil kirjutada funktsioone VBA -s, öeldes teile, millised argumendid on vajalikud. Saate oma funktsiooni kohe testida. Lülitage Exceli aknasse ja sisestage eraldi lahtritesse arvud Pikkus ja Laius. Kolmandasse lahtrisse sisestage oma funktsioon nii, nagu oleks see üks sisseehitatud. Selles näites sisaldab lahter A1 pikkust (17) ja lahtrit B1 laiust (6.5). C1 -s kirjutasin = ala (A1, B1) ja uus funktsioon arvutas pindala (110,5)…
Mõnikord võivad funktsiooni argumendid olla valikulised. Selles näites võiksime teha Laius argument on vabatahtlik. Oletame, et ristkülik on ruut, mille pikkus ja laius on võrdsed. Et säästa kasutajat kahe argumendi sisestamisest, võiksime lasta tal sisestada ainult pikkuse ja lasta funktsioonil seda väärtust kaks korda kasutada (st korrutada pikkus x pikkus). Nii et funktsioon teab, millal seda saab teha, peame lisama IF avaldus et aidata tal otsustada. Muutke koodi nii, et see näeks välja selline…
Funktsioonipiirkond (pikkus kahekordne, valikuline laius variandina) Kui on puudu (laius), siis piirkond = pikkus * pikkus muu ala = pikkus * laius lõpp, kui funktsioon on lõpp
Pange tähele, et laiuse andmetüüp on muudetud väärtuseks Variant nullväärtuste lubamiseks. Funktsioon võimaldab kasutajal nüüd sisestada vaid ühe argumendi, nt. = ala (A1). Funktsiooni IF avaldis kontrollib, kas argument Width on esitatud, ja arvutab vastavalt…
Funktsioon kütusekulu arvutamiseks
Mulle meeldib kontrollida oma auto kütusekulu, nii et märgin kütust ostes üles läbisõidu ja selle, kui palju kütust paagi täitmiseks kulub. Siin Ühendkuningriigis müüakse kütust liitrites. Auto milomeeter (OK, seega on see läbisõidumõõdik) salvestab vahemaa miilides. Ja kuna ma olen muutmiseks liiga vana ja rumal, saan aru ainult MPG -st (miili galloni kohta). Kui nüüd arvate, et see kõik on natuke kurb, siis mis sellest. Koju jõudes avan Exceli ja sisestan andmed töölehele, mis arvutab minu jaoks MPG ja kaardistab auto jõudluse. Arvutus on auto miilide arv, mis on möödunud pärast viimast täitmist, jagatud kasutatud gallonite arvuga…
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF FUEL
aga kuna kütus on liitrites ja gallonis on 4,546 liitrit…
MPG = (MILES THIS FILL - MILES LAST FILL) / KÜTUSELITRID x 4,546
Siin on, kuidas ma funktsiooni kirjutasin …
Funktsioon MPG (StartMiles täisarvuna, FinishMiles täisarvuna, liitrit üksikuna) MPG = (FinishMiles - StartMiles) / liitrit * 4.546 Lõppfunktsioon
ja kuidas see töölehel välja näeb …
Mitte kõik funktsioonid ei soorita matemaatilisi arvutusi. Siin on üks, mis annab teavet…
Funktsioon, mis annab päeva nime
Minult küsitakse sageli, kas on olemas kuupäevafunktsioon, mis annab nädalapäeva tekstiks (nt esmaspäev). Vastus on ei*, kuid selle loomine on üsna lihtne. (*Lisand: kas ma ütlesin ei? Kontrollige allolevat märkust, et näha funktsiooni, mille ma unustasin!). Excelil on funktsioon WEEKDAY, mis tagastab nädalapäeva numbrina 1 kuni 7. Saate valida, milline päev on 1, kui vaikimisi ei meeldi (pühapäev). Allolevas näites tagastab funktsioon "5", mis minu teada tähendab "neljapäev".
Aga ma ei taha numbrit näha, vaid "neljapäeva". Ma saaksin arvutust muuta, lisades funktsiooni VLOOKUP, mis viitas kuskil tabelile, mis sisaldas numbrite loendit ja vastavat päevade nimede loendit. Või võin kogu asja mitme iseseisva IF-avaldusega iseseisvaks muuta. Liiga keeruline! Vastus on kohandatud funktsioon …
Funktsioon DayName (sisendkuupäev kuupäevana) Dim DayNumber täisarvuna DayNumber = nädalapäev (InputDate, vbSunday) Valige Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Juhtum 5 DayName = "Thursday" Case 6 DayName = "Friday" Case 7 DayName = "Saturday" End Valige lõppfunktsioon
Olen oma funktsiooni nimetanud "DayName" ja see nõuab ühte argumenti, mida ma nimetan "InputDate", mis (muidugi) peab olema kuupäev. See toimib järgmiselt.
- Funktsiooni esimene rida deklareerib muutuja, mille olen nimetanud "DayNumber" ja mis on täisarv (st täisarv).
- Funktsiooni järgmine rida määrab Exceli funktsiooni WEEKDAY abil sellele muutujale väärtuse. Väärtuseks on number vahemikus 1 kuni 7. Kuigi vaikimisi on 1 = pühapäev, olen selle selguse huvides siiski lisanud.
- Lõpuks a Juhtumi avaldus uurib muutuja väärtust ja tagastab sobiva tekstiosa.
See näeb välja töölehel…
Juurdepääs kohandatud funktsioonidele
Kui töövihikule on lisatud kohandatud funktsioone sisaldav VBA -koodimoodul, saab neid funktsioone hõlpsasti käsitleda samas töövihikus, nagu on näidatud ülaltoodud näidetes. Kasutate funktsiooni nime nii, nagu see oleks üks Exceli sisseehitatud funktsioonidest.
Funktsioonide viisardist (mida mõnikord nimetatakse ka kleepimisfunktsiooni tööriistaks) leiate funktsioonid. Kasutage viisardit funktsiooni sisestamiseks tavalisel viisil (Lisa> Funktsioon).
Kerige otsitavate funktsioonikategooriate loendit allapoole Kasutaja määratud ja valige see saadaolevate UDF -ide loendi vaatamiseks …
Näete, et kasutaja määratletud funktsioonidel puudub muu kirjeldus peale abitu teate „Abi pole saadaval”, kuid saate lisada lühikirjelduse…
Veenduge, et olete funktsioone sisaldavas töövihikus. Minema Tööriistad> Makro> Makrod. Te ei näe siin oma funktsioone, kuid Excel teab neist! Aastal Makro nimi tippige funktsiooni nimi ja klõpsake dialoogiboksi Valikud nuppu. Kui nupp on hall, kas olete kirjutanud funktsiooni nime valesti või olete vales töövihikus või seda pole olemas! See avab teise dialoogi, kuhu saate sisestada funktsiooni lühikirjelduse. Klõpsake nuppu Okei kirjelduse salvestamiseks ja (siin on segane bitt) klõps Tühista Makro dialoogiboksi sulgemiseks. Ärge unustage funktsiooni sisaldavat töövihikut salvestada. Järgmine kord, kui lähete funktsioonide viisardisse, kuvatakse teie UDF -is kirjeldus…
Sarnaselt makrodega saab kasutaja määratud funktsioone kasutada mis tahes muus töövihikus, kui neid sisaldav töövihik on avatud. Siiski ei ole hea tava seda teha. Funktsiooni sisestamine teise töövihikusse pole lihtne. Funktsiooni nimele peate lisama selle hosti töövihiku nime. See ei ole keeruline, kui loodate funktsioonide viisardile, kuid käsitsi väljakirjutamine on kohmakas. Funktsiooniviisard kuvab teiste töövihikute kõigi UDF -ide täielikud nimed …
Kui avate töövihiku, milles funktsiooni kasutasite ajal, mil funktsiooni sisaldav töövihik on suletud, näete veateadet lahtris, kus funktsiooni kasutasite. Excel on selle unustanud! Avage funktsiooni hosti töövihik, arvutage uuesti ja kõik on jälle korras. Õnneks on parem viis.
Kui soovite kirjutada kasutaja määratud funktsioone kasutamiseks mitmes töövihikus, on parim meetod Exceli loomine Lisandmoodul. Kuidas seda teha, saate teada Exceli lisandmooduli koostamise õpetusest.
Lisand
Ma tõesti peaksin paremini teadma! Ärge kunagi, kunagi, ärge kunagi öelge! Olles teile öelnud, et pole ühtegi funktsiooni, mis annaks päeva nime, meenus mulle nüüd see funktsioon, mis seda võimaldab. Vaadake seda näidet…
Funktsioon TEXT tagastab lahtri väärtuse tekstina teatud numbrivormingus. Nii et näites oleksin võinud valida = TEKST (A1, "ddd") "Thu" tagastamiseks, = TEKST (A1, "mmmm") tagastada "September" jne. Exceli abis on veel mõned näited selle funktsiooni kasutamise viisidest.
Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis.
Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile aadressil e -posti sait