I denne vejledning lærer vi om Excel VBA -funktion
1) Hvad er Visual Basic i Excel?
2) Hvordan bruges VBA i Excel?
3) Hvordan opretter man brugerdefineret funktion?
4) Hvordan skriver man makro?
Hvordan man skriver VBA -kode
Excel giver brugeren en stor samling færdige funktioner, mere end nok til at tilfredsstille den gennemsnitlige bruger. Mange flere kan tilføjes ved at installere de forskellige tilføjelser, der er tilgængelige. De fleste beregninger kan opnås med det, der tilbydes, men det er ikke længe, før du finder dig selv at ønske, at der var en funktion, der udførte et bestemt stykke arbejde, og du kan ikke finde noget passende på listen. Du har brug for en UDF. En UDF (User Defined Function) er simpelthen en funktion, som du selv opretter med VBA. UDF'er kaldes ofte "Brugerdefinerede funktioner". En UDF kan forblive i et kodemodul, der er knyttet til en projektmappe, i så fald vil den altid være tilgængelig, når projektmappen er åben. Alternativt kan du oprette dit eget tilføjelsesprogram, der indeholder en eller flere funktioner, som du kan installere i Excel ligesom et kommercielt tilføjelsesprogram. UDF'er kan også tilgås af kodemoduler. Ofte oprettes UDF'er af udviklere til udelukkende at arbejde inden for koden for en VBA -procedure, og brugeren er aldrig opmærksom på deres eksistens. Ligesom enhver funktion kan UDF være så enkel eller så kompleks, som du ønsker. Lad os starte med en let…
En funktion til at beregne arealet af et rektangel
Ja, jeg ved, du kunne gøre dette i dit hoved! Konceptet er meget enkelt, så du kan koncentrere dig om teknikken. Antag, at du har brug for en funktion til at beregne arealet af et rektangel. Du kigger igennem Excel's samling af funktioner, men der er ikke en passende. Dette er beregningen, der skal udføres:
OMRÅDE = LÆNGDE x BREDDE
Åbn en ny projektmappe, og åbn derefter Visual Basic Editor (Værktøjer> Makro> Visual Basic Editor eller ALT+F11).
Du skal bruge et modul til at skrive din funktion, så vælg Indsæt> Modul. Ind i den tomme modultype: Funktionsområde og tryk på GÅ IND. Visual Basic Editor fuldender linjen for dig og tilføjer en slutfunktionslinje, som om du var ved at oprette en underprogram. Indtil videre ser det sådan ud …
Funktionsområde () Afslut funktion
Placer markøren mellem parenteserne efter "Areal". Hvis du nogensinde har spekuleret på, hvad beslagene er til, er du ved at finde ud af det! Vi vil specificere de "argumenter", som vores funktion vil tage (et argument er et stykke information, der er nødvendig for at foretage beregningen). Type Længde som dobbelt, bredde som dobbelt og klik på den tomme linje nedenunder. Bemærk, at mens du skriver, vises en rulleboks, der viser alle de ting, der passer til det, du skriver.
Denne funktion kaldes Auto List medlemmer. Hvis den ikke vises, er den slukket (tænd den kl Værktøjer> Indstillinger> Editor) eller du har muligvis lavet en skrivefejl tidligere. Det er en meget nyttig kontrol af din syntaks. Find det element, du har brug for, og dobbeltklik på det for at indsætte det i din kode. Du kan ignorere det og bare skrive, hvis du vil. Din kode ser nu sådan ud …
Funktionsområde (længde som dobbelt, bredde som dobbelt) Afslutningsfunktion
Det er ikke obligatorisk at angive datatypen for argumenterne, men giver mening. Du kunne have skrevet Længde bredde og efterlod det sådan, men advare Excel, hvilken datatype du kan forvente, hjælper din kode med at køre hurtigere og opfanger fejl i input. Det dobbelt datatype refererer til tal (som kan være meget stort) og tillader brøker. Nu til selve beregningen. I den tomme linje skal du først trykke på TAB nøgle til at indrykke din kode (gør det lettere at læse) og skrive Område = Længde * Bredde. Her er den udfyldte kode …
Funktionsområde (længde som dobbelt, bredde som dobbelt) Areal = længde * bredde ende funktion
Du vil bemærke, at en anden af Visual Basic Editor's hjælpefunktioner dukker op, mens du skrev, Automatisk hurtig info…
Det er ikke relevant her. Dens formål er at hjælpe dig med at skrive funktioner i VBA ved at fortælle dig, hvilke argumenter der kræves. Du kan teste din funktion med det samme. Skift til Excel -vinduet, og indtast tal for længde og bredde i separate celler. I en tredje celle skal du indtaste din funktion, som var den en af de indbyggede. I dette eksempel indeholder celle A1 længden (17) og celle B1 bredden (6,5). I C1 skrev jeg = område (A1, B1) og den nye funktion beregnede arealet (110,5) …
Nogle gange kan en funktions argumenter være valgfrie. I dette eksempel kunne vi lave Bredde argument valgfrit. Antag at rektanglet tilfældigvis er en firkant med længde og bredde lig. For at spare brugeren for at skulle indtaste to argumenter kunne vi lade dem indtaste bare længden og få funktionen til at bruge denne værdi to gange (dvs. multiplicere længde x længde). Så funktionen ved, hvornår den kan gøre dette, skal vi inkludere en IF -erklæring at hjælpe det med at bestemme. Skift koden, så den ser sådan ud …
Funktionsområde (længde som dobbelt, valgfri bredde som variant) Hvis der mangler (bredde), så er område = længde * længde andet område = længde * bredde ende hvis ende funktion
Bemærk, at datatypen for Width er blevet ændret til Variant at tillade nulværdier. Funktionen giver nu brugeren mulighed for kun at indtaste et argument f.eks. = område (A1). IF -erklæringen i funktionen kontrollerer, om Width -argumentet er blevet leveret og beregner i overensstemmelse hermed …
En funktion til beregning af brændstofforbrug
Jeg kan godt lide at kontrollere min bils brændstofforbrug, så når jeg køber brændstof, noterer jeg kilometertal og hvor meget brændstof det tager at fylde tanken. Her i Storbritannien sælges brændstof i liter. Bilens milometer (OK, så det er et kilometertæller) registrerer afstand i miles. Og fordi jeg er for gammel og dum til at ændre, forstår jeg kun MPG (miles per gallon). Hvis du nu synes, det er lidt trist, hvad med det her? Når jeg kommer hjem, åbner jeg Excel og indtaster dataene i et regneark, der beregner MPG for mig og viser bilens ydelse. Beregningen er antallet af miles bilen har kørt siden den sidste påfyldning divideret med antallet af gallons brugt brændstof …
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF BRÆNDSTOF
men fordi brændstoffet kommer i liter, og der er 4.546 liter i en gallon …
MPG = (MILES THIS FILL - MILES LAST FILL) / LITERS OF BRÆNDSTOF x 4.546
Sådan skrev jeg funktionen …
Funktion MPG (StartMiles As Integer, FinishMiles As Integer, Liter As Single) MPG = (FinishMiles - StartMiles) / Liter * 4.546 Slutfunktion
og sådan ser det ud på regnearket …
Ikke alle funktioner udfører matematiske beregninger. Her er en, der giver information …
En funktion, der giver dagens navn
Jeg bliver ofte spurgt, om der er en datofunktion, der angiver ugedag som tekst (f.eks. Mandag). Svaret er nej*, men det er ret let at oprette et. (*Tillæg: Har jeg sagt nej? Tjek nedenstående note for at se den funktion, jeg glemte!). Excel har WEEKDAY -funktionen, der returnerer ugedagen som et tal fra 1 til 7. Du kan vælge, hvilken dag der er 1, hvis du ikke kan lide standarden (søndag). I eksemplet nedenfor returnerer funktionen "5", som jeg tilfældigvis kender betyder "torsdag".
Men jeg vil ikke se et nummer, jeg vil se "torsdag". Jeg kunne ændre beregningen ved at tilføje en VLOOKUP -funktion, der henviste til en tabel et sted indeholdende en liste med numre og en tilsvarende liste over dagnavne. Eller jeg kunne have det hele i sig selv med flere indlejrede IF-udsagn. For indviklet! Svaret er en brugerdefineret funktion …
Funktion DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Vælg Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Case 5 DayName = "Torsdag" Case 6 DayName = "Friday" Case 7 DayName = "Saturday" End Vælg slutfunktion
Jeg har kaldt min funktion "DayName", og det kræver et enkelt argument, som jeg kalder "InputDate", som (selvfølgelig) skal være en dato. Sådan fungerer det …
- Funktionens første linje erklærer en variabel, som jeg har kaldt "DayNumber", som vil være et heltal (dvs. et helt tal).
- Den næste linje i funktionen tildeler en værdi til denne variabel ved hjælp af Excels WEEKDAY -funktion. Værdien vil være et tal mellem 1 og 7. Selvom standarden er 1 = søndag, har jeg alligevel medtaget den for klarhedens skyld.
- Endelig a Sagserklæring undersøger værdien af variablen og returnerer det relevante stykke tekst.
Sådan ser det ud på regnearket …
Adgang til dine tilpassede funktioner
Hvis en projektmappe har et VBA -kodemodul tilknyttet, der indeholder brugerdefinerede funktioner, kan disse funktioner let adresseres inden for den samme projektmappe som vist i eksemplerne ovenfor. Du bruger funktionsnavnet som om det var en af Excel's indbyggede funktioner.
Du kan også finde de funktioner, der er angivet i funktionsguiden (undertiden kaldet værktøjet Indsæt funktion). Brug guiden til at indsætte en funktion på normal måde (Indsæt> Funktion).
Rul ned på listen over funktionskategorier for at finde Brugerdefineret og vælg det for at se en liste over tilgængelige UDF'er …
Du kan se, at de brugerdefinerede funktioner mangler en anden beskrivelse end den uhjælpelige meddelelse "Ingen hjælp tilgængelig", men du kan tilføje en kort beskrivelse …
Sørg for, at du er i den projektmappe, der indeholder funktionerne. Gå til Værktøjer> Makro> Makroer. Du kan ikke se dine funktioner angivet her, men Excel ved om dem! I Makro navn øverst i dialogboksen, indtast navnet på funktionen, og klik derefter på dialogboksen Muligheder knap. Hvis knappen er nedtonet, enten har du stavet funktionsnavnet forkert, eller du er i den forkerte projektmappe, eller det findes ikke! Dette åbner endnu en dialog, hvor du kan indtaste en kort beskrivelse af funktionen. Klik på Okay for at gemme beskrivelsen og (her er den forvirrende bit) klik Afbestille for at lukke dialogboksen Makro. Husk at gemme projektmappen, der indeholder funktionen. Næste gang du går til funktionsguiden, har din UDF en beskrivelse …
Ligesom makroer kan brugerdefinerede funktioner bruges i enhver anden projektmappe, så længe projektmappen, der indeholder dem, er åben. Det er dog ikke god praksis at gøre dette. Det er ikke enkelt at indtaste funktionen i en anden projektmappe. Du skal tilføje dens værts -projektmappes navn til funktionsnavnet. Dette er ikke svært, hvis du stoler på funktionsguiden, men klodset at skrive manuelt. Funktionsguiden viser de fulde navne på alle UDF'er i andre projektmapper …
Hvis du åbner den projektmappe, hvor du brugte funktionen, på et tidspunkt, hvor projektmappen, der indeholder funktionen, er lukket, vil du se en fejlmeddelelse i cellen, hvor du brugte funktionen. Excel har glemt det! Åbn funktionens værts -projektmappe, genberegn, og alt er fint igen. Heldigvis er der en bedre måde.
Hvis du vil skrive brugerdefinerede funktioner til brug i mere end én projektmappe, er den bedste metode at oprette en Excel Tilføje. Find ud af, hvordan du gør dette i selvstudiet Byg et Excel-tilføjelsesprogram.
Tillæg
Jeg burde virkelig vide bedre! Aldrig, aldrig, sig aldrig! Efter at have fortalt dig, at der ikke er en funktion, der giver dagens navn, har jeg nu husket den, der kan. Se dette eksempel …
TEKST -funktionen returnerer værdien af en celle som tekst i et bestemt talformat. Så i eksemplet kunne jeg have valgt = TEKST (A1, "ddd") at vende tilbage "tor", = TEKST (A1, "mmmm") at returnere "september" osv. Excel -hjælpen har nogle flere eksempler på måder at bruge denne funktion på.
Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook.
Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -websted