U ovom ćemo vodiču naučiti o Excel VBA funkciji
1) Što je Visual Basic u Excelu?
2) Kako koristiti VBA u Excelu?
3) Kako stvoriti korisnički definiranu funkciju?
4) Kako napisati makro?
Kako napisati VBA kod
Excel pruža korisniku veliku zbirku gotovih funkcija, više nego dovoljnu da zadovolji prosječnog korisnika. Mnogo više se može dodati instaliranjem različitih dostupnih dodataka. Većina izračuna može se postići s ponuđenim, ali nedugo zatim zaželjeli ste da postoji funkcija koja je obavila određeni posao, a na popisu ne možete pronaći ništa prikladno. Trebate UDF. UDF (Korisnički definirana funkcija) jednostavno je funkcija koju sami stvarate pomoću VBA -a. UDF -ovi se često nazivaju "prilagođene funkcije". UDF može ostati u kodnom modulu priključenom na radnu knjigu, u tom slučaju uvijek će biti dostupan kada je ta radna knjiga otvorena. Alternativno, možete stvoriti vlastiti dodatak koji sadrži jednu ili više funkcija koje možete instalirati u Excel, poput komercijalnog dodatka. UDF -ovima se može pristupiti i pomoću modula koda. Programeri često stvaraju UDF -ove kako bi radili samo unutar koda VBA postupka, a korisnik nikada nije svjestan njihovog postojanja. Kao i svaka funkcija, UDF može biti jednostavan ili složen koliko želite. Počnimo s lakim…
Funkcija za izračunavanje površine pravokutnika
Da, znam da bi to mogao učiniti u svojoj glavi! Koncept je vrlo jednostavan pa se možete koncentrirati na tehniku. Pretpostavimo da vam je potrebna funkcija za izračunavanje površine pravokutnika. Pregledavate zbirku funkcija programa Excel, ali ne postoji jedna prikladna. Ovo je izračun koji treba napraviti:
PODRUČJE = DUŽINA x ŠIRINA
Otvorite novu radnu knjigu, a zatim otvorite uređivač Visual Basic (Alati> Makro> Visual Basic Editor ili ALT+F11).
Trebat će vam modul u koji ćete napisati svoju funkciju pa odaberite Umetni> Modul. U prazan modul upišite: Područje funkcija i pritisnite UNESI.Visual Basic Editor dovršava redak umjesto vas i dodaje redak završne funkcije kao da stvarate potprogram. Zasad izgleda ovako …
Područje funkcija () Kraj funkcije
Postavite kursor između zagrada iza "Area". Ako ste se ikada zapitali čemu služe zagrade, tek ćete to saznati! Navest ćemo "argumente" koje će naša funkcija uzeti (an argument je podatak potreban za izračun). Tip Dvostruka dužina, širina dvostruka i kliknite u prazan red ispod. Imajte na umu da se tijekom tipkanja pojavljuje okvir za pomicanje s popisom svih stvari koje odgovaraju vašem upisu.
Ova se značajka naziva Članovi automatskog popisa. Ako se ne pojavi, isključen je (uključite ga na Alati> Opcije> Uređivač) ili ste možda ranije pogriješili u tipkanju. To je vrlo korisna provjera vaše sintakse. Pronađite stavku koja vam je potrebna i dvaput kliknite na nju da biste je umetnuli u svoj kôd. Možete zanemariti i samo upisati ako želite. Vaš kôd sada izgleda ovako …
Područje funkcije (Duljina kao dvostruka, širina kao dvostruka) Kraj funkcije
Deklariranje vrste podataka argumenata nije obavezno, ali ima smisla. Mogao si tipkati Dužina širina i ostavili tako, ali upozorenje Excelu koji tip podataka možete očekivati pomaže vašem kodu da se brže izvodi i hvata greške pri unosu. The dvostruko vrsta podataka odnosi se na broj (koji može biti vrlo velik) i dopušta razlomke. Sada o samom izračunu. U praznom retku prvo pritisnite Kartica ključ za uvlačenje koda (olakšava čitanje) i upišite Područje = Duljina * Širina. Evo dovršenog koda …
Područje funkcije (Duljina kao dvostruka, širina kao dvostruka) Područje = Duljina * Funkcija završetka širine
Primijetit ćete još jednu od značajki pomoći programa Visual Basic Editor kako se pojavljuju dok ste tipkali, Automatske brze informacije…
Ovdje to nije relevantno. Njegova je svrha pomoći vam u pisanju funkcija u VBA, govoreći vam koji su argumenti potrebni. Možete odmah testirati svoju funkciju. Prebacite se na prozor Excel i unesite brojke za dužinu i širinu u zasebne ćelije. U treću ćeliju unesite svoju funkciju kao da je jedna od ugrađenih. U ovom primjeru ćelija A1 sadrži duljinu (17), a ćelija B1 širinu (6,5). U C1 sam upisao = površina (A1, B1) a nova funkcija izračunala je površinu (110,5) …
Ponekad argumenti funkcije mogu biti neobavezni. U ovom primjeru mogli bismo napraviti Širina argument neobavezan. Pretpostavimo da je pravokutnik kvadrat s jednakom dužinom i širinom. Kako bismo sprečili korisnika da unese dva argumenta, mogli bismo mu dopustiti da unese samo dužinu i funkcija bi tu vrijednost koristila dva puta (tj. Pomnožiti duljinu x duljinu). Dakle, funkcija zna kada to može učiniti moramo uključiti i IF Izjava kako bi mu pomogao u odluci. Promijenite kôd tako da izgleda ovako …
Područje funkcije (duljina dvostruka, opcionalna širina kao varijanta) Ako nedostaje (širina), tada područje = duljina * duljina drugog područja = duljina * širina završava ako funkcija završava
Imajte na umu da je tip podataka za Width promijenjen u Varijanta dopustiti null vrijednosti. Funkcija sada omogućuje korisniku da unese samo jedan argument, npr. = površina (A1).Izjava IF u funkciji provjerava je li dostavljen argument Širina i izračunava u skladu s tim …
Funkcija za izračunavanje potrošnje goriva
Volim provjeravati potrošnju goriva u automobilu pa prilikom kupnje goriva bilježim kilometražu i količinu goriva koja je potrebna za punjenje spremnika. Ovdje se u Velikoj Britaniji gorivo prodaje u litrama. Milometar automobila (u redu, pa to je kilometar) bilježi udaljenost u miljama. A budući da sam prestar i glup da bih se mijenjao, razumijem samo MPG (milje po galonu). Pa ako mislite da je sve to pomalo tužno, što kažete na ovo. Kad dođem kući, otvorim Excel i unesem podatke na radni list koji za mene izračunava MPG i prikazuje performanse automobila. Izračun je broj milja koje je automobil prešao od posljednjeg punjenja podijeljen s brojem galona potrošenog goriva …
MPG = (MILIJE OVO ISPUNJAVANJE - MILIJE POSLJEDNJE ISPUNJENJE) / GALONI GORIVA
ali zato što gorivo dolazi u litrama, a u galonu ima 4,546 litara …
MPG = (MILIJE OVO ISPUNJAVANJE - MILIJE POSLJEDNJE PUNJENJE) / LITARI GORIVA x 4,554
Evo kako sam napisao funkciju …
Funkcija MPG (StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / Litara * 4.546 Krajnja funkcija
a evo kako to izgleda na radnom listu …
Ne obavljaju sve funkcije matematičke izračune. Evo jedan koji daje informacije …
Funkcija koja daje ime dana
Često me pitaju postoji li funkcija datuma koja dan u tjednu daje kao tekst (npr. Ponedjeljak). Odgovor je ne*, ali vrlo je jednostavno stvoriti ga. (*Dodatak: Jesam li rekao ne? Provjerite donju bilješku da vidite funkciju koju sam zaboravio!). Excel ima funkciju WEEKDAY, koja dan u tjednu vraća kao broj od 1 do 7. Možete odabrati koji je dan 1 ako vam se ne sviđa zadana vrijednost (nedjelja). U donjem primjeru funkcija vraća "5" za koje znam da znači "četvrtak".
Ali ne želim vidjeti broj, želim vidjeti "četvrtak". Mogao bih izmijeniti izračun dodavanjem funkcije VLOOKUP koja se odnosila na tablicu koja negdje sadrži popis brojeva i odgovarajući popis imena dana. Ili bih cijelu stvar mogao imati samostalno s više ugniježđenih IF izraza. Prekomplicirano! Odgovor je prilagođena funkcija …
Funkcija DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Dan u tjednu (InputDate, vbSunday) Odaberite Broj dana u danu Case Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Slučaj 5 DayName = "Četvrtak" Slučaj 6 DayName = "Petak" Slučaj 7 DayName = "Subota" Kraj Odaberite funkciju završetka
Svoju sam funkciju nazvao "DayName" i za nju je potreban jedan argument, koji nazivam "InputDate" koji (naravno) mora biti datum. Evo kako to funkcionira…
- Prvi redak funkcije deklarira varijablu koju sam nazvao "DayNumber" koja će biti cijeli broj (tj. Cijeli broj).
- Sljedeći redak funkcije dodjeljuje vrijednost toj varijabli pomoću Excelove funkcije WEEKDAY. Vrijednost će biti broj između 1 i 7. Iako je zadana vrijednost 1 = nedjelja, svejedno sam je uključio radi jasnoće.
- Konačno a Izjava o slučaju ispituje vrijednost varijable i vraća odgovarajući dio teksta.
Evo kako to izgleda na radnom listu…
Pristup vašim prilagođenim funkcijama
Ako radna knjiga ima priključeni modul VBA koda koji sadrži prilagođene funkcije, te se funkcije mogu lako adresirati unutar iste radne knjige kao što je pokazano u gornjim primjerima. Naziv funkcije koristite kao da je jedna od ugrađenih funkcija programa Excel.
Također možete pronaći funkcije navedene u čarobnjaku za funkcije (ponekad se naziva i alat za lijepljenje funkcija). Pomoću čarobnjaka umetnite funkciju na uobičajen način (Umetni> Funkcija).
Pomaknite se prema dolje po popisu kategorija funkcija koje želite pronaći Korisnik definiran i odaberite ga da biste vidjeli popis dostupnih UDF -ova …
Možete vidjeti da korisnički definiranim funkcijama nedostaje bilo kakav opis osim beskorisne poruke "No help available", ali možete dodati kratak opis …
Provjerite jeste li u radnoj knjizi koja sadrži funkcije. Ići Alati> Makro> Makronaredbe. Ovdje nećete vidjeti svoje funkcije, ali Excel zna za njih! U Naziv makronaredbe okvir pri vrhu dijaloga upišite naziv funkcije, a zatim kliknite dijaloški okvir Opcije dugme. Ako je gumb zasivljen, ili ste krivo napisali naziv funkcije, ili ste u pogrešnoj radnoj knjizi, ili ne postoji! Ovo otvara drugi dijalog u koji možete unijeti kratak opis funkcije. Klik u redu da biste spremili opis i (evo zbunjujućeg bita) kliknite Otkazati da biste zatvorili dijaloški okvir Makro. Ne zaboravite spremiti radnu knjigu koja sadrži funkciju. Sljedeći put kada odete do čarobnjaka za funkcije, vaš UDF će imati opis …
Poput makronaredbi, korisnički definirane funkcije mogu se koristiti u bilo kojoj drugoj radnoj knjizi sve dok je radna knjiga koja ih sadrži otvorena. Međutim, to nije dobra praksa. Unos funkcije u drugu radnu knjigu nije jednostavan. U naziv funkcije morate dodati naziv radne knjige hosta. Ovo nije teško ako se oslanjate na čarobnjaka za funkcije, ali nespretno je ručno pisati. Čarobnjak funkcija prikazuje puna imena svih UDF -ova u drugim radnim knjigama …
Ako otvorite radnu knjigu u kojoj ste koristili funkciju u vrijeme kada je radna knjiga koja sadrži tu funkciju zatvorena, vidjet ćete poruku o pogrešci u ćeliji u kojoj ste koristili funkciju. Excel je zaboravio na to! Otvorite radnu knjigu domaćina funkcije, ponovno izračunajte i opet je sve u redu. Srećom, postoji bolji način.
Ako želite napisati korisnički definirane funkcije za korištenje u više od jedne radne knjige, najbolji način je stvaranje Excela Dodatak. Saznajte kako to učiniti u vodiču Izgradnja programskog dodatka za Excel.
Dodatak
Stvarno bih trebao znati bolje! Nikad, nikad, nikad ne reci! Rekavši vam da ne postoji funkcija koja daje naziv dana, sad sam se sjetila one koja to može. Pogledajte ovaj primjer…
Funkcija TEXT vraća vrijednost ćelije kao tekst u određenom formatu broja. Dakle, u primjeru koji sam mogao izabrati = TEKST (A1, "ddd") vratiti "Čet", = TEKST (A1, "mmmm") da biste vratili "rujan" itd. Pomoć u Excelu ima još nekoliko primjera načina korištenja ove funkcije.
Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku.
Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web mjesto e -pošte