Šioje pamokoje sužinosime apie „Excel VBA“ funkciją
1) Kas yra „Visual Basic“ programoje „Excel“?
2) Kaip naudoti VBA programoje „Excel“?
3) Kaip sukurti vartotojo apibrėžtą funkciją?
4) Kaip parašyti makrokomandą?
Kaip parašyti VBA kodą
„Excel“ suteikia vartotojui didelę paruoštų funkcijų kolekciją, daugiau nei pakankamai, kad patenkintų paprastą vartotoją. Įdiegus įvairius galimus priedus galima pridėti daug daugiau. Daugumą skaičiavimų galima atlikti naudojant tai, kas pateikta, tačiau netrukus pastebėsite, kad norite, kad būtų funkcija, atliekanti tam tikrą darbą, ir sąraše nerandate nieko tinkamo. Jums reikia UDF. UDF (vartotojo apibrėžta funkcija) yra tiesiog funkcija, kurią sukuriate patys naudodami VBA. UDF dažnai vadinamos „pasirinktinėmis funkcijomis“. UDF gali likti kodo modulyje, prijungtame prie darbaknygės, tokiu atveju jis visada bus pasiekiamas, kai ta darbaknygė bus atidaryta. Arba galite sukurti savo priedą, kuriame yra viena ar daugiau funkcijų, kurias galite įdiegti į „Excel“, kaip ir komercinį priedą. UDF taip pat galima pasiekti naudojant kodo modulius. Dažnai UDF kūrėjai sukuria norėdami dirbti tik pagal VBA procedūros kodą, o vartotojas niekada nežino apie jų egzistavimą. Kaip ir bet kuri funkcija, UDF gali būti toks paprastas ar sudėtingas, kokio norite. Pradėkime nuo lengvo…
Funkcija, skirta apskaičiuoti stačiakampio plotą
Taip, aš žinau, kad tu galėtum tai padaryti savo galva! Koncepcija yra labai paprasta, todėl galite sutelkti dėmesį į techniką. Tarkime, jums reikia funkcijos, kad apskaičiuotumėte stačiakampio plotą. Jūs peržiūrite „Excel“ funkcijų kolekciją, tačiau nėra vienos tinkamos. Tai skaičiavimas, kurį reikia atlikti:
Sritis = LENGTH x WIDTH
Atidarykite naują darbaknygę, tada atidarykite „Visual Basic“ redaktorių (Įrankiai> Makrokomanda> Visual Basic Editor arba ALT+F11).
Jums reikės modulio, kuriame parašysite savo funkciją, todėl pasirinkite Įterpti> Modulis. Į tuščio modulio tipą: Funkcijų sritis ir paspauskite Įveskite. „Visual Basic“ redaktorius užbaigia eilutę už jus ir prideda pabaigos funkcijos eilutę, tarsi kuriate paprogramę. Kol kas atrodo taip …
Funkcijų sritis () Pabaigos funkcija
Užveskite žymeklį tarp skliaustų po „Sritis“. Jei kada nors susimąstėte, kam skirti skliausteliai, tuoj sužinosite! Mes nurodysime „argumentus“, kurių turės mūsų funkcija (an argumentas yra informacija, reikalinga skaičiavimui atlikti). Tipas Ilgis dvigubas, plotis - dvigubas ir spustelėkite tuščią eilutę apačioje. Atminkite, kad kai rašote tekstą, pasirodo slinkties laukas, kuriame išvardijami visi įvedami dalykai.
Ši savybė vadinama Automatinis narių sąrašas. Jei jis neatsiranda, jis yra išjungtas (įjunkite jį Įrankiai> Parinktys> Redaktorius) arba galbūt padarėte spausdinimo klaidą anksčiau. Tai labai naudinga jūsų sintaksės patikra. Raskite reikiamą elementą ir dukart spustelėkite jį, kad įterptumėte jį į savo kodą. Galite to nepaisyti ir tiesiog įvesti, jei norite. Jūsų kodas dabar atrodo taip …
Funkcijos sritis (ilgis kaip dvigubas, plotis kaip dvigubas) Pabaigos funkcija
Argumentų duomenų tipo deklaravimas nėra privalomas, bet prasmingas. Galėjai rašyti Ilgis Plotis ir paliko tai, bet įspėjimas „Excel“, kokio tipo duomenų tikėtis, padeda jūsų kodui veikti greičiau ir suranda įvesties klaidas. The dvigubas duomenų tipas nurodo skaičių (kuris gali būti labai didelis) ir leidžia trupmenas. Dabar apie patį skaičiavimą. Tuščioje eilutėje pirmiausia paspauskite TAB klavišą, kad įtrauktumėte kodą (kad būtų lengviau skaityti) ir įveskite Plotas = ilgis * plotis. Štai užpildytas kodas…
Funkcijų sritis (ilgis kaip dvigubas, plotis kaip dvigubas) Plotas = ilgis * plotis pabaigos funkcija
Pastebėsite, kad rašant atsirado dar viena „Visual Basic“ redaktoriaus pagalbos funkcija, Automatinė greita informacija…
Čia tai neaktualu. Jo tikslas yra padėti jums parašyti funkcijas VBA, nurodant, kokių argumentų reikia. Galite iš karto patikrinti savo funkciją. Perjunkite į „Excel“ langą ir atskiruose langeliuose įveskite ilgio ir pločio skaičius. Trečiame langelyje įveskite savo funkciją taip, tarsi ji būtų viena iš integruotų. Šiame pavyzdyje langelis A1 turi ilgį (17), o langelis B1 - plotį (6.5). C1 įvedžiau = plotas (A1, B1) ir nauja funkcija apskaičiavo plotą (110,5) …
Kartais funkcijos argumentai gali būti neprivalomi. Šiame pavyzdyje galėtume padaryti Plotis argumentas neprivalomas. Tarkime, kad stačiakampis yra kvadratas, kurio ilgis ir plotis yra lygūs. Norėdami sutaupyti vartotojui įvesti du argumentus, galime leisti jiems įvesti tik ilgį ir funkciją naudoti tą reikšmę du kartus (t. Y. Dauginti ilgį x ilgį). Taigi funkcija žino, kada tai gali padaryti, turime įtraukti an IF pareiškimas kad padėtų apsispręsti. Pakeiskite kodą taip, kad jis atrodytų taip …
Funkcijos sritis (ilgis kaip dvigubas, pasirinktinis plotis kaip variantas) Jei trūksta (plotis), tada sritis = ilgis * ilgis, kita sritis = ilgis * plotis, pabaiga, jei funkcija baigiama
Atminkite, kad pločio duomenų tipas pakeistas į Variantas kad būtų galima nustatyti nulines vertes. Funkcija dabar leidžia vartotojui įvesti tik vieną argumentą, pvz. = plotas (A1)Funkcijos IF teiginys patikrina, ar pateiktas argumentas Plotis, ir atitinkamai apskaičiuoja …
Funkcija kuro sąnaudoms apskaičiuoti
Man patinka nuolat tikrinti savo automobilio degalų sąnaudas, todėl pirkdamas degalus užsirašau ridą ir kiek degalų reikia užpildyti baką. Čia JK kuras parduodamas litrais. Automobilio milometras (gerai, tai yra odometras) fiksuoja atstumą myliomis. Kadangi aš esu per senas ir kvailas, kad galėčiau keistis, suprantu tik MPG (mylių už galoną). Dabar, jei manote, kad visa tai šiek tiek liūdna, kaip apie tai? Grįžęs namo atsidarau „Excel“ ir įvedu duomenis į darbalapį, kuris apskaičiuoja man MPG ir apibrėžia automobilio veikimą. Skaičiavimas yra kilometrų, kuriuos automobilis nuvažiavo nuo paskutinio pripildymo, skaičius, padalytas iš galonų sunaudoto degalų skaičiaus …
MPG = (MILES THIS FILL - MILES LAST FILL) / GALONAI KURO
bet kadangi degalai yra litrais, o galone - 4,546 litrai …
MPG = (MILES THIS FILL - MILES LAST FILL) / LITRA OF FUEL x 4.546
Štai kaip aš parašiau funkciją …
Funkcija MPG („StartMiles“ kaip sveikasis skaičius, „FinishMiles“ kaip sveikasis skaičius, litrai kaip vienas) MPG = („FinishMiles“ - „StartMiles“) / litrai * 4.546 Pabaigos funkcija
ir štai kaip tai atrodo darbalapyje …
Ne visos funkcijos atlieka matematinius skaičiavimus. Štai vienas, kuriame pateikiama informacija…
Funkcija, suteikianti dienos vardą
Manęs dažnai klausia, ar yra datos funkcija, kuri savaitės dieną nurodo kaip tekstą (pvz., Pirmadienį). Atsakymas yra ne*, bet jį sukurti yra gana paprasta. (*Papildymas: ar aš pasakiau ne? Patikrinkite toliau pateiktą pastabą, kad pamatytumėte funkciją, kurią pamiršau!). „Excel“ turi funkciją WEEKDAY, kuri grąžina savaitės dieną kaip skaičių nuo 1 iki 7. Galite pasirinkti, kuri diena yra 1, jei jums nepatinka numatytasis (sekmadienis). Žemiau pateiktame pavyzdyje funkcija grąžina „5“, kuri, kaip aš žinau, reiškia „ketvirtadienis“.
Bet aš nenoriu matyti skaičiaus, aš noriu pamatyti „ketvirtadienį“. Galėčiau pakeisti skaičiavimą, pridėdamas funkciją VLOOKUP, nurodančią lentelę, kurioje yra skaičių sąrašas ir atitinkamas dienų pavadinimų sąrašas. Arba aš galėčiau visa tai laikyti savarankiškai su keliais įdėtais IF teiginiais. Per daug komplikuota! Atsakymas yra pasirinktinė funkcija…
Funkcija DayName (įvesties data kaip data) Dim DayNumber kaip sveikasis skaičius DayNumber = Savaitės diena (InputDate, vbSunday) Pasirinkite Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" 5 atvejis DayName = "Thursday" Case 6 DayName = "Friday" Case 7 DayName = "Saturday" End Select End Function
Aš pavadinau savo funkciją „DayName“ ir tai reikalauja vieno argumento, kurį vadinu „InputDate“, kuris (žinoma) turi būti data. Štai kaip tai veikia…
- Pirmoje funkcijos eilutėje nurodomas kintamasis, kurį pavadinau „DayNumber“, kuris bus sveikasis skaičius (t. Y. Visas skaičius).
- Kitoje funkcijos eilutėje tam kintamajam priskiriama vertė naudojant „Excel“ WEEKDAY funkciją. Reikšmė bus skaičius nuo 1 iki 7. Nors numatytoji reikšmė yra 1 = sekmadienis, aš vis tiek jį įtraukiau, kad būtų aiškiau.
- Galiausiai a Atvejo pareiškimas nagrinėja kintamojo reikšmę ir grąžina atitinkamą teksto dalį.
Štai kaip tai atrodo darbalapyje …
Prieiga prie jūsų pasirinktinių funkcijų
Jei prie darbaknygės prijungtas VBA kodo modulis, kuriame yra pasirinktinių funkcijų, tas funkcijas galima lengvai išspręsti toje pačioje darbaknygėje, kaip parodyta aukščiau pateiktuose pavyzdžiuose. Funkcijos pavadinimą naudojate taip, tarsi tai būtų viena iš „Excel“ integruotų funkcijų.
Funkcijas taip pat galite rasti funkcijų vedlyje (kartais vadinamas Įklijavimo funkcijos įrankiu). Naudokite vedlį, kad įterptumėte funkciją įprastu būdu (Įterpti> Funkcija).
Slinkite žemyn norimų rasti funkcijų kategorijų sąrašu Naudotojo apibrėžta ir pasirinkite jį, kad pamatytumėte galimų UDF sąrašą …
Matote, kad vartotojo apibrėžtose funkcijose nėra jokio kito aprašymo, išskyrus nenaudingą pranešimą „Pagalbos nėra“, tačiau galite pridėti trumpą aprašymą…
Įsitikinkite, kad esate darbaknygėje, kurioje yra funkcijos. Eiti į Įrankiai> Makrokomandos> Makrokomandos. Čia nematysite savo funkcijų, tačiau „Excel“ apie jas žino! Viduje konors Makro pavadinimas dialogo lango viršuje, įveskite funkcijos pavadinimą, tada spustelėkite dialogo langą Galimybės mygtuką. Jei mygtukas yra pilkas, arba neteisingai parašėte funkcijos pavadinimą, arba esate netinkamoje darbaknygėje, arba jos nėra! Atidaromas kitas dialogo langas, kuriame galite įvesti trumpą funkcijos aprašymą. Spustelėkite Gerai Norėdami išsaugoti aprašymą ir (čia yra paini dalis), spustelėkite Atšaukti Norėdami uždaryti dialogo langą Makro. Nepamirškite išsaugoti darbaknygės, kurioje yra ši funkcija. Kai kitą kartą eisite į funkcijų vedlį, jūsų UDF turės aprašymą …
Kaip ir makrokomandos, vartotojo apibrėžtos funkcijos gali būti naudojamos bet kurioje kitoje darbaknygėje, kol atidaryta darbaknygė, kurioje jos yra. Tačiau tai nėra gera praktika. Įvesti funkciją į kitą darbo knygą nėra paprasta. Prie funkcijos pavadinimo turite pridėti jos pagrindinės darbaknygės pavadinimą. Tai nėra sunku, jei pasikliaujate funkcijų vedliu, bet nerangu rašyti rankiniu būdu. Funkcijų vedlys rodo visus visų darbo knygų UDF pavadinimus …
Jei atidarysite darbaknygę, kurioje naudojote funkciją, tuo metu, kai darbo knyga, kurioje yra funkcija, yra uždaryta, langelyje, kuriame naudojote šią funkciją, pamatysite klaidos pranešimą. „Excel“ apie tai pamiršo! Atidarykite funkcijos pagrindinį darbaknygę, perskaičiuokite ir vėl viskas gerai. Laimei, yra geresnis būdas.
Jei norite parašyti vartotojo nustatytas funkcijas, skirtas naudoti daugiau nei vienoje darbaknygėje, geriausias būdas yra sukurti „Excel“ Pridėti. Sužinokite, kaip tai padaryti, pamokoje „Sukurti„ Excel “priedą“.
Priedas
Aš tikrai turėčiau žinoti geriau! Niekada, niekada, nesakyk niekada! Pasakęs jums, kad nėra funkcijos, suteikiančios dienos pavadinimą, dabar prisiminiau tą funkciją, kuri gali. Pažvelkite į šį pavyzdį…
Funkcija TEKSTAS grąžina langelio vertę kaip tekstą tam tikru skaičių formatu. Taigi pavyzdyje galėjau pasirinkti = TEKSTAS (A1, "ddd") grąžinti „ketvirtadienį“, = TEKSTAS (A1, "mmmm") grąžinti „Rugsėjis“ ir pan. „Excel“ žinyne yra dar keletas šios funkcijos naudojimo būdų pavyzdžių.
Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“.
Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Rašykite mums adresu pašto svetainę