I denne opplæringen lærer vi om Excel VBA -funksjon
1) Hva er Visual Basic i Excel?
2) Hvordan bruke VBA i Excel?
3) Hvordan lage brukerdefinert funksjon?
4) Hvordan skrive makro?
Hvordan skrive VBA -kode
Excel gir brukeren en stor samling av ferdige funksjoner, mer enn nok til å tilfredsstille den gjennomsnittlige brukeren. Mange flere kan legges til ved å installere de forskjellige tilleggene som er tilgjengelige. De fleste beregninger kan oppnås med det som er gitt, men det er ikke lenge før du finner ut at du skulle ønske at det var en funksjon som gjorde en bestemt jobb, og du kan ikke finne noe passende på listen. Du trenger en UDF. En UDF (User Defined Function) er ganske enkelt en funksjon du lager selv med VBA. UDF kalles ofte "Tilpassede funksjoner". En UDF kan forbli i en kodemodul festet til en arbeidsbok, i så fall vil den alltid være tilgjengelig når arbeidsboken er åpen. Alternativt kan du lage ditt eget tillegg som inneholder en eller flere funksjoner som du kan installere i Excel akkurat som et kommersielt tillegg. UDF -er kan også nås med kodemoduler. Ofte opprettes UDF -er av utviklere for å arbeide utelukkende innenfor koden til en VBA -prosedyre, og brukeren er aldri klar over deres eksistens. Som enhver funksjon kan UDF være så enkel eller så kompleks som du vil. La oss starte med en enkel …
En funksjon for å beregne arealet til et rektangel
Ja, jeg vet at du kan gjøre dette i hodet ditt! Konseptet er veldig enkelt, slik at du kan konsentrere deg om teknikken. Anta at du trenger en funksjon for å beregne arealet til et rektangel. Du ser gjennom Excel's samling av funksjoner, men det er ikke en som passer. Dette er beregningen som skal gjøres:
OMRÅDE = LENGDE x BREDDE
Åpne en ny arbeidsbok og deretter Visual Basic Editor (Verktøy> Makro> Visual Basic Editor eller ALT+F11).
Du trenger en modul for å skrive funksjonen din, så velg Sett inn> modul. Inn i den tomme modultypen: Funksjonsområde og trykk på TAST INN. Visual Basic Editor fullfører linjen for deg og legger til en sluttfunksjonslinje som om du opprettet en underprogram. Så langt ser det slik ut …
Funksjonsområde () Sluttfunksjon
Plasser markøren mellom parentesene etter "Areal". Hvis du noen gang har lurt på hva parentesene er til, er du i ferd med å finne ut! Vi skal spesifisere "argumentene" som funksjonen vår vil ta (et argument er en informasjon du trenger for å gjøre beregningen). Type Lengde som dobbel, bredde som dobbel og klikk på den tomme linjen under. Vær oppmerksom på at mens du skriver, dukker det opp en rulleboks som viser alle tingene som passer til det du skriver.
Denne funksjonen kalles Autoliste medlemmer. Hvis den ikke vises, er den slått av (slå den på kl Verktøy> Alternativer> Editor) eller du kan ha skrevet en feil tidligere. Det er en veldig nyttig sjekk på syntaksen din. Finn elementet du trenger og dobbeltklikk på det for å sette det inn i koden din. Du kan ignorere det og bare skrive hvis du vil. Koden din ser nå slik ut …
Funksjonsområde (lengde som dobbel, bredde som dobbel) Sluttfunksjon
Å erklære datatypen til argumentene er ikke obligatorisk, men gir mening. Du kunne ha skrevet Lengde bredde og la det være slik, men ved å advare Excel om hvilken datatype du kan forvente, hjelper koden din raskere og får opp feil i inndata. De dobbelt datatype refererer til tall (som kan være veldig stort) og tillater brøk. Nå til selve beregningen. Trykk først på den tomme linjen TAB nøkkel for å innrykke koden (gjør det lettere å lese) og skrive Areal = lengde * bredde. Her er den ferdige koden …
Funksjonsområde (lengde som dobbel, bredde som dobbel) Område = lengde * bredde endefunksjon
Du vil legge merke til at en annen av Visual Basic Editor -hjelpefunksjonene dukker opp mens du skrev, Automatisk hurtiginformasjon…
Det er ikke relevant her. Formålet er å hjelpe deg med å skrive funksjoner i VBA, ved å fortelle deg hvilke argumenter som kreves. Du kan teste funksjonen din med en gang. Bytt til Excel -vinduet og skriv inn tall for lengde og bredde i separate celler. I en tredje celle skriver du inn funksjonen din som om den var en av de innebygde. I dette eksemplet inneholder celle A1 lengden (17) og celle B1 bredden (6,5). I C1 skrev jeg = område (A1, B1) og den nye funksjonen beregnet arealet (110,5) …
Noen ganger kan en funksjons argumenter være valgfrie. I dette eksemplet kan vi lage Bredde argument valgfritt. Anta at rektangelet tilfeldigvis er en firkant med lengde og bredde lik. For å spare brukeren for å måtte skrive inn to argumenter kan vi la dem angi bare lengden og få funksjonen til å bruke denne verdien to ganger (dvs. multiplisere lengde x lengde). Så funksjonen vet når den kan gjøre dette, vi må inkludere en IF -erklæring å hjelpe den med å bestemme. Endre koden slik at den ser slik ut …
Funksjonsområde (lengde som dobbelt, valgfri bredde som variant) Hvis det mangler (bredde), så er området = lengde * lengde annet område = lengde * bredde ende hvis sluttfunksjon
Vær oppmerksom på at datatypen for Width er endret til Variant for å tillate nullverdier. Funksjonen lar nå brukeren skrive inn bare ett argument, f.eks. = område (A1). IF -setningen i funksjonen kontrollerer om Width -argumentet er levert og beregner deretter …
En funksjon for å beregne drivstofforbruk
Jeg liker å kontrollere bilens drivstofforbruk, så når jeg kjøper drivstoff, noterer jeg kjørelengden og hvor mye drivstoff det tar å fylle tanken. Her i Storbritannia selges drivstoff i liter. Bilens milometer (OK, så det er et kilometerteller) registrerer avstand i miles. Og fordi jeg er for gammel og dum til å endre, forstår jeg bare MPG (miles per gallon). Hvis du synes det er litt trist, hva med dette. Når jeg kommer hjem, åpner jeg Excel og legger inn dataene i et regneark som beregner MPG for meg og viser bilens ytelse. Beregningen er antall miles bilen har kjørt siden den siste påfyllingen dividert med antall liter drivstoff brukt …
MPG = (MILES THIS FILL - MILES SIST FILL) / GALLONS OF DRIVEL
men fordi drivstoffet kommer i liter og det er 4,546 liter i en gallon …
MPG = (MILES THIS FILL - MILES SIST FILL) / LITERS OF DRIVSTOFF x 4.546
Slik skrev jeg funksjonen …
Funksjon MPG (StartMiles As Integer, FinishMiles As Integer, Liter As Single) MPG = (FinishMiles - StartMiles) / Liter * 4.546 Sluttfunksjon
og slik ser det ut på regnearket …
Ikke alle funksjoner utfører matematiske beregninger. Her er en som gir informasjon …
En funksjon som gir dagens navn
Jeg blir ofte spurt om det er en datofunksjon som gir ukedagen som tekst (f.eks. Mandag). Svaret er nei*, men det er ganske enkelt å lage en. (*Tillegg: Sa jeg nei? Sjekk notatet nedenfor for å se funksjonen jeg glemte!). Excel har WEEKDAY -funksjonen, som returnerer ukedagen som et tall fra 1 til 7. Du får velge hvilken dag som er 1 hvis du ikke liker standarden (søndag). I eksemplet nedenfor returnerer funksjonen "5" som jeg tilfeldigvis vet betyr "torsdag".
Men jeg vil ikke se et tall, jeg vil se "torsdag". Jeg kunne endre beregningen ved å legge til en VLOOKUP -funksjon som refererte til en tabell et sted som inneholder en liste med tall og en tilsvarende liste over dagnavn. Eller jeg kan ha det hele frittstående med flere nestede IF-utsagn. For komplisert! Svaret er en egendefinert funksjon …
Funksjon DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Velg Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Case 5 DayName = "Thursday" Case 6 DayName = "Friday" Case 7 DayName = "Saturday" End Velg sluttfunksjon
Jeg har kalt funksjonen min "DayName", og det krever et enkelt argument, som jeg kaller "InputDate" som (selvfølgelig) må være en dato. Slik fungerer det …
- Den første linjen i funksjonen erklærer en variabel som jeg har kalt "DayNumber" som vil være et heltall (dvs. et helt tall).
- Den neste linjen i funksjonen tilordner en verdi til den variabelen ved hjelp av Excels WEEKDAY -funksjon. Verdien vil være et tall mellom 1 og 7. Selv om standarden er 1 = søndag, har jeg tatt den med uansett for klarhet.
- Til slutt a Saksuttalelse undersøker verdien av variabelen og returnerer det aktuelle tekststykket.
Slik ser det ut på regnearket …
Få tilgang til dine egendefinerte funksjoner
Hvis en arbeidsbok har en VBA -kodemodul knyttet til den som inneholder egendefinerte funksjoner, kan disse funksjonene enkelt adresseres i den samme arbeidsboken som vist i eksemplene ovenfor. Du bruker funksjonsnavnet som om det var en av Excel's innebygde funksjoner.
Du kan også finne funksjonene som er oppført i funksjonsveiviseren (noen ganger kalt Lim inn funksjon -verktøyet). Bruk veiviseren for å sette inn en funksjon på normal måte (Sett inn> Funksjon).
Rull nedover listen over funksjonskategorier for å finne Brukerdefinert og velg den for å se en liste over tilgjengelige UDF -er …
Du kan se at de brukerdefinerte funksjonene mangler noen annen beskrivelse enn den lite hjelpsomme meldingen "Ingen hjelp tilgjengelig", men du kan legge til en kort beskrivelse …
Sørg for at du er i arbeidsboken som inneholder funksjonene. Gå til Verktøy> Makro> Makroer. Du vil ikke se funksjonene dine oppført her, men Excel vet om dem! I Makro navn øverst i dialogboksen, skriver du inn navnet på funksjonen, og klikker deretter på dialogboksen Alternativer knapp. Hvis knappen er gråtonet, enten har du stavet funksjonsnavnet feil, eller du er i feil arbeidsbok, eller det finnes ikke! Dette åpner en ny dialog der du kan skrive inn en kort beskrivelse av funksjonen. Klikk OK for å lagre beskrivelsen og (her er den forvirrende biten) klikk Avbryt for å lukke dialogboksen Makro. Husk å lagre arbeidsboken som inneholder funksjonen. Neste gang du går til funksjonsveiviseren har UDF en beskrivelse …
I likhet med makroer kan brukerdefinerte funksjoner brukes i en hvilken som helst annen arbeidsbok så lenge arbeidsboken som inneholder dem, er åpen. Det er imidlertid ikke god praksis å gjøre dette. Å skrive inn funksjonen i en annen arbeidsbok er ikke enkelt. Du må legge navnet til vertsarbeidsboken til funksjonsnavnet. Dette er ikke vanskelig hvis du stoler på funksjonsveiviseren, men klønete å skrive ut manuelt. Funksjonsveiviseren viser alle navnene på alle UDF -er i andre arbeidsbøker …
Hvis du åpner arbeidsboken der du brukte funksjonen på et tidspunkt da arbeidsboken som inneholder funksjonen er lukket, vil du se en feilmelding i cellen der du brukte funksjonen. Excel har glemt det! Åpne funksjonens vertsarbeidsbok, beregne på nytt, og alt er bra igjen. Heldigvis finnes det en bedre måte.
Hvis du vil skrive brukerdefinerte funksjoner for bruk i mer enn én arbeidsbok, er den beste metoden å lage en Excel Legge inn. Finn ut hvordan du gjør dette i opplæringen Bygg et Excel-tillegg.
Tillegg
Jeg burde virkelig vite bedre! Aldri, aldri, si aldri! Etter å ha fortalt deg at det ikke er en funksjon som gir dagens navn, har jeg nå husket den som kan. Se på dette eksemplet …
TEKST -funksjonen returnerer verdien av en celle som tekst i et bestemt tallformat. Så i eksemplet kunne jeg ha valgt = TEKST (A1, "ddd") å returnere "tor", = TEKST (A1, "mmmm") for å returnere "September" etc. Excel -hjelpen har noen flere eksempler på måter å bruke denne funksjonen.
Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook.
Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv oss på e -post nettsted