V tej vadnici bomo spoznali funkcijo Excel VBA
1) Kaj je Visual Basic v Excelu?
2) Kako uporabljati VBA v Excelu?
3) Kako ustvariti uporabniško določeno funkcijo?
4) Kako napisati makro?
Kako napisati kodo VBA
Excel uporabniku ponuja veliko zbirko že pripravljenih funkcij, več kot dovolj, da zadovolji povprečnega uporabnika. Z namestitvijo različnih razpoložljivih dodatkov lahko dodate še veliko več. Večino izračunov je mogoče doseči s tem, kar je priloženo, vendar ne boste dolgo čakali, da želite, da obstaja funkcija, ki je opravila določeno delo, in na seznamu ne najdete ničesar primernega. Potrebujete UDF. UDF (User Defined Function) je preprosto funkcija, ki jo ustvarite sami z VBA. UDF -ji se pogosto imenujejo "funkcije po meri". UDF lahko ostane v kodnem modulu, priloženem delovnemu zvezku, v tem primeru bo vedno na voljo, ko je ta delovni zvezek odprt. Druga možnost je, da ustvarite lasten dodatek, ki vsebuje eno ali več funkcij, ki jih lahko namestite v Excel, tako kot komercialni dodatek. Do UDF -jev lahko dostopate tudi s kodnimi moduli. Razvijalci pogosto ustvarjajo UDF -je, ki delujejo izključno v kodi postopka VBA, uporabnik pa se nikoli ne zaveda njihovega obstoja. Kot vsaka funkcija je lahko tudi UDF tako preprost ali zapleten, kot želite. Začnimo z enostavnim…
Funkcija za izračun površine pravokotnika
Ja, vem, da bi to lahko naredil v glavi! Koncept je zelo preprost, zato se lahko osredotočite na tehniko. Recimo, da potrebujete funkcijo za izračun površine pravokotnika. Ogledate si zbirko funkcij programa Excel, vendar ni nobene primerne. To je izračun, ki ga je treba narediti:
OBMOČJE = DOLŽINA x ŠIRINA
Odprite nov delovni zvezek in nato urejevalnik Visual Basic (Orodja> Makro> Urejevalnik Visual Basic ali ALT+F11).
Potrebovali boste modul, v katerega boste zapisali svojo funkcijo, zato izberite Vstavi> Modul. V prazen modul vnesite: Področje delovanja in pritisnite ENTERUrejevalnik Visual Basic vam dokonča vrstico in doda vrstico Končna funkcija, kot da ustvarjate podprogram. Zaenkrat izgleda tako …
Področje funkcije () Končaj funkcijo
Kazalec postavite med oklepaje za "Area". Če ste se kdaj vprašali, čemu služijo oklepaji, boste izvedeli! Določili bomo "argumente", ki jih bo prevzela naša funkcija (an prepir je podatek, potreben za izračun). Vrsta Dolžina dvojna, širina dvojna in kliknite v prazno vrstico spodaj. Upoštevajte, da se med tipkanjem prikaže drsno polje, v katerem so navedene vse stvari, ki ustrezajo temu, kar vnašate.
Ta funkcija se imenuje Člani samodejnega seznama. Če se ne prikaže, je izklopljen (vklopite ga pri Orodja> Možnosti> Urejevalnik) ali pa ste morda prej naredili tipkarsko napako. To je zelo uporaben pregled vaše skladnje. Poiščite element, ki ga potrebujete, in ga dvokliknite, da ga vstavite v kodo. Lahko ga prezrete in samo vtipkate, če želite. Vaša koda je zdaj videti tako …
Funkcijsko območje (dolžina kot dvojna, širina kot dvojna) Končna funkcija
Navedba podatkovnega tipa argumentov ni obvezna, vendar je smiselna. Lahko bi napisal Dolžina širina in pustili tako, vendar opozarjanje Excela, kakšen tip podatkov pričakujete, pomaga, da se vaša koda hitreje zažene in odkrije napake pri vnosu. The dvojno podatkovni tip se nanaša na število (ki je lahko zelo veliko) in dopušča ulomke. Zdaj pa sam izračun. V prazni vrstici najprej pritisnite TAB ključ za zamik kode (za lažje branje) in tip Območje = Dolžina * Širina. Tukaj je dokončana koda …
Območje funkcije (dolžina kot dvojna, širina kot dvojna) Območje = dolžina * funkcija širine
Opazili boste, da se je med tipkanjem pojavila še ena funkcija pomoči urejevalnika Visual Basic, Samodejne hitre informacije…
Tu ni relevantno. Njegov namen je, da vam pomaga pri pisanju funkcij v VBA, tako da vam pove, kateri argumenti so potrebni. Svojo funkcijo lahko preizkusite takoj. Preklopite na okno Excel in vnesite številke za dolžino in širino v ločene celice. V tretjo celico vnesite svojo funkcijo, kot bi bila ena od vgrajenih. V tem primeru celica A1 vsebuje dolžino (17), celica B1 pa širino (6,5). V C1 sem tipkal = površina (A1, B1) in nova funkcija je izračunala površino (110,5) …
Včasih so argumenti funkcije neobvezni. V tem primeru bi lahko naredili Premer argument neobvezen. Recimo, da je pravokotnik kvadrat z enako dolžino in širino. Da uporabniku ne bi bilo treba vnesti dveh argumentov, bi mu lahko dovolili, da vnese samo dolžino, funkcija pa bi morala to vrednost dvakrat uporabiti (tj. Pomnožiti dolžino x dolžino). Torej funkcija ve, kdaj lahko to stori, moramo vključiti Izjava IF za pomoč pri odločitvi. Kodo spremenite tako, da bo videti tako …
Območje funkcije (dolžina kot dvojna, izbirna širina kot varianta) Če manjka (širina), potem območje = dolžina * dolžina drugega območja = dolžina * konec širine, če funkcija End
Upoštevajte, da je bil podatkovni tip za Width spremenjen v Različica omogočiti ničelne vrednosti. Funkcija zdaj omogoča uporabniku, da vnese le en argument, npr. = površina (A1).Izjava IF v funkciji preveri, ali je bil podan argument širine, in ustrezno izračuna …
Funkcija za izračun porabe goriva
Rad preverjam porabo goriva v avtu, zato si pri nakupu goriva zapisujem kilometrino in koliko goriva porabim za polnjenje rezervoarja. V Združenem kraljestvu se gorivo prodaja v litrih. Milometer avtomobila (v redu, torej je števec kilometrov) beleži razdaljo v miljah. In ker sem prestar in neumen, da bi se spreminjal, razumem le MPG (milje na galono). Zdaj, če mislite, da je vse to malo žalostno, kaj pa tole. Ko pridem domov, odprem Excel in vnesem podatke na delovni list, ki zame izračuna MPG in prikaže zmogljivosti avtomobila. Izračun je število kilometrov, ki jih je avto prevozil od zadnjega polnjenja, deljeno s številom galonov porabljenega goriva …
MPG = (MILE TO POLNJENJE - MILE ZADNJE POLNJE) / GALONI GORIVA
ker pa gorivo prihaja v litrih, v galoni pa 4,554 litra …
MPG = (MILE TO POLNJENJE - MILE ZADNJE POLNJE) / LITRI GORIVA x 4,554
Takole sem zapisal funkcijo …
Funkcija MPG (StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / Litri * 4.546 Končna funkcija
in tako izgleda na delovnem listu …
Vse funkcije ne izvajajo matematičnih izračunov. Tukaj je tisti, ki ponuja informacije …
Funkcija, ki daje ime dnevu
Pogosto me vprašajo, ali obstaja funkcija datuma, ki dan v tednu poda kot besedilo (npr. Ponedeljek). Odgovor je ne*, vendar ga je zelo enostavno ustvariti. (*Dodatek: Ali sem rekel ne? Preverite spodnjo opombo, da vidite funkcijo, ki sem jo pozabil!). Excel ima funkcijo WEEKDAY, ki dan v tednu vrne kot številko od 1 do 7. Izberete lahko, kateri dan je 1, če vam privzeto ni všeč (nedelja). V spodnjem primeru funkcija vrne "5", za kar vem, da pomeni "četrtek".
Nočem videti številke, želim videti "četrtek". Izračun lahko spremenim tako, da dodam funkcijo VLOOKUP, ki se nanaša na tabelo, kjer je nekje seznam številk in ustrezen seznam imen dni. Ali pa bi lahko imel vse skupaj samostojno z več ugnezdenimi stavki IF. Preveč zapleteno! Odgovor je funkcija po meri …
Funkcija DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Dan v tednu (InputDate, vbSunday) Izberite Day Case Number Day Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Primer 5 DayName = "Četrtek" Primer 6 DayName = "Petek" Primer 7 DayName = "Konec sobote" Izberite Končaj funkcijo
Svojo funkcijo sem poklical "DayName" in potrebuje en sam argument, ki ga imenujem "InputDate", ki mora (seveda) biti datum. Evo, kako to deluje…
- Prva vrstica funkcije razglaša spremenljivko, ki sem jo poimenoval "DayNumber", ki bo celo število (torej celo število).
- Naslednja vrstica funkcije dodeli vrednost tej spremenljivki z uporabo Excelove funkcije WEEKDAY. Vrednost bo število med 1 in 7. Čeprav je privzeto 1 = nedelja, sem jo zaradi jasnosti vseeno vključil.
- Končno a Izjava o primeru preuči vrednost spremenljivke in vrne ustrezen del besedila.
Evo, kako to izgleda na delovnem listu …
Dostop do vaših funkcij po meri
Če ima delovni zvezek priključen kodni modul VBA, ki vsebuje funkcije po meri, jih je mogoče preprosto obravnavati v istem delovnem zvezku, kot je prikazano v zgornjih primerih. Ime funkcije uporabljate, kot da je ena izmed vgrajenih funkcij programa Excel.
Prav tako lahko najdete funkcije, navedene v čarovniku za funkcije (včasih imenovano tudi orodje za prilepi funkcijo). S čarovnikom vstavite funkcijo na običajen način (Vstavi> Funkcija).
Pomaknite se navzdol po seznamu kategorij funkcij, ki jih želite poiskati Uporabnik definiran in ga izberite, če si želite ogledati seznam razpoložljivih UDF -jev …
Vidite lahko, da uporabniško definirane funkcije nimajo nobenega opisa razen nepotrebnega sporočila "No help available", vendar lahko dodate kratek opis …
Prepričajte se, da ste v delovnem zvezku, ki vsebuje funkcije. Pojdi do Orodja> Makro> Makri. Tu ne boste videli svojih funkcij, vendar Excel ve za njih! V Ime makra polje na vrhu pogovornega okna, vnesite ime funkcije in kliknite pogovorno okno Opcije gumb. Če je gumb obarvan sivo, ste ime funkcije napisali napačno ali pa ste v napačnem delovnem zvezku ali pa ne obstaja! Odpre se drugo pogovorno okno, v katerega lahko vnesete kratek opis funkcije. Kliknite v redu da shranite opis in (tukaj je zmeden bit) kliknite Prekliči zapreti pogovorno okno Makro. Ne pozabite shraniti delovnega zvezka, ki vsebuje funkcijo. Ko naslednjič obiščete čarovnika za funkcije, bo vaš UDF imel opis …
Tako kot makri lahko uporabniško določene funkcije uporabite v katerem koli drugem delovnem zvezku, dokler je delovni zvezek, ki jih vsebuje, odprt. Vendar to ni dobra praksa. Vnos funkcije v drug delovni zvezek ni preprost. Imenu funkcije morate dodati ime njegovega delovnega zvezka gostitelja. To ni težko, če se zanesete na čarovnika za funkcije, vendar je nerodno ročno pisati. Čarovnik za funkcije prikazuje polna imena vseh UDF -jev v drugih delovnih zvezkih …
Če odprete delovni zvezek, v katerem ste uporabljali funkcijo, v času, ko je delovni zvezek, ki vsebuje funkcijo, zaprt, se v celici, v kateri ste uporabili funkcijo, prikaže sporočilo o napaki. Excel je na to pozabil! Odprite delovni zvezek gostitelja funkcije, ponovno izračunajte in spet je vse v redu. Na srečo obstaja boljši način.
Če želite napisati uporabniško določene funkcije za uporabo v več kot enem delovnem zvezku, je najboljši način, da ustvarite Excel Dodatek. Kako to storiti, preberite v vadnici Zgradite dodatek za Excel.
Dodatek
Res bi moral vedeti bolje! Nikoli, nikoli, nikoli ne reci! Ko sem vam povedal, da ni funkcije, ki bi navedla ime dneva, sem se spomnil tiste, ki lahko. Poglejte ta primer…
Funkcija TEXT vrne vrednost celice kot besedilo v določeni številčni obliki. Torej v primeru, ki bi ga lahko izbral = BESEDILO (A1, "ddd") vrniti "čet", = BESEDILO (A1, "mmmm") za vrnitev "September" itd. Excelova pomoč vsebuje še nekaj primerov načinov uporabe te funkcije.
Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku.
Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na e -poštno mesto