I denna handledning lär vi oss om Excel VBA -funktion
1) Vad är Visual Basic i Excel?
2) Hur använder jag VBA i Excel?
3) Hur skapar jag en användardefinierad funktion?
4) Hur skriver man makro?
Hur man skriver VBA -kod
Excel ger användaren en stor samling färdiga funktioner, mer än tillräckligt för att tillfredsställa den genomsnittliga användaren. Många fler kan läggas till genom att installera de olika tillägg som finns tillgängliga. De flesta beräkningar kan uppnås med vad som tillhandahålls, men det dröjer inte länge innan du önskar att det fanns en funktion som gjorde ett visst jobb, och du kan inte hitta något lämpligt i listan. Du behöver en UDF. En UDF (User Defined Function) är helt enkelt en funktion som du skapar själv med VBA. UDF kallas ofta "Anpassade funktioner". En UDF kan finnas kvar i en kodmodul som är kopplad till en arbetsbok, i så fall kommer den alltid att vara tillgänglig när arbetsboken är öppen. Alternativt kan du skapa ditt eget tillägg som innehåller en eller flera funktioner som du kan installera i Excel precis som ett kommersiellt tillägg. UDF kan också nås med kodmoduler. Ofta skapas UDF: er av utvecklare för att arbeta enbart inom koden för ett VBA -förfarande och användaren är aldrig medveten om deras existens. Precis som vilken funktion som helst kan UDF vara så enkel eller så komplex som du vill. Låt oss börja med en enkel …
En funktion för att beräkna ytan av en rektangel
Ja, jag vet att du kan göra detta i ditt huvud! Konceptet är väldigt enkelt så att du kan koncentrera dig på tekniken. Antag att du behöver en funktion för att beräkna ytan på en rektangel. Du tittar igenom Excels samling funktioner, men det finns ingen som passar. Detta är beräkningen som ska göras:
OMRÅDE = LÄNGD x BREDD
Öppna en ny arbetsbok och öppna sedan Visual Basic Editor (Verktyg> Makro> Visual Basic Editor eller ALT+F11).
Du behöver en modul för att skriva din funktion så välj Infoga> Modul. In i den tomma modultypen: Funktionsområde och tryck på STIGA PÅ. Visual Basic Editor kompletterar raden för dig och lägger till en slutfunktionsrad som om du skulle skapa en underrutin. Så långt ser det ut så här …
Funktionsområde () Avsluta funktion
Placera markören mellan parenteserna efter "Area". Om du någonsin undrat vad parenteserna är till för, kommer du att ta reda på det! Vi kommer att specificera de "argument" som vår funktion kommer att ta (en argument är en information som behövs för att göra beräkningen). Typ Längd som dubbel, bredd som dubbel och klicka i den tomma raden nedanför. Observera att när du skriver kommer en rullningsruta upp som visar alla saker som passar det du skriver.
Denna funktion kallas Autolista -medlemmar. Om den inte visas antingen är den avstängd (slå på kl Verktyg> Alternativ> Editor) eller så kan du ha skrivit fel tidigare. Det är en mycket användbar kontroll av din syntax. Hitta objektet du behöver och dubbelklicka på det för att infoga det i din kod. Du kan ignorera det och bara skriva om du vill. Din kod ser nu ut så här …
Funktionsområde (längd som dubbel, bredd som dubbel) Slutfunktion
Att deklarera datatypen för argumenten är inte obligatoriskt men meningsfullt. Du kunde ha skrivit Längd bredd och lämnade det som det, men varnar Excel vilken datatyp du kan förvänta dig hjälper din kod att köras snabbare och plockar upp fel vid inmatning. De dubbel datatyp avser tal (som kan vara mycket stora) och tillåter bråk. Nu till själva beräkningen. I den tomma raden trycker du först på FLIK nyckel för att indraga din kod (gör det lättare att läsa) och skriva Yta = Längd * Bredd. Här är den färdiga koden …
Funktionsområde (längd som dubbel, bredd som dubbel) yta = längd * bredd slutfunktion
Du kommer att märka att en annan av Visual Basic Editor -hjälpfunktionerna dyker upp när du skrev, Auto Quick Info…
Det är inte relevant här. Dess syfte är att hjälpa dig att skriva funktioner i VBA, genom att berätta vilka argument som krävs. Du kan testa din funktion direkt. Byt till Excel -fönstret och ange siffror för längd och bredd i separata celler. I en tredje cell anger du din funktion som om den vore en av de inbyggda. I detta exempel innehåller cell A1 längden (17) och cell B1 bredden (6,5). I C1 skrev jag = område (A1, B1) och den nya funktionen beräknade ytan (110,5) …
Ibland kan en funktions argument vara valfria. I det här exemplet kan vi göra Bredd argument valfritt. Antag att rektangeln råkar vara en kvadrat med längd och bredd lika. För att spara användaren att behöva ange två argument kan vi låta dem ange bara längden och få funktionen att använda det värdet två gånger (dvs. multiplicera längd x längd). Så funktionen vet när den kan göra detta måste vi inkludera en IF -uttalande för att hjälpa den att bestämma. Ändra koden så att den ser ut så här …
Funktionsområde (längd som dubbel, valfri bredd som variant) If IsMissing (Width) Då Area = Length * Length Other Area = Length * Width End If End Function
Observera att datatypen för Width har ändrats till Variant för att tillåta nollvärden. Funktionen tillåter nu användaren att ange bara ett argument t.ex. = område (A1). IF -uttalandet i funktionen kontrollerar om argumentet Width har levererats och beräknas därefter …
En funktion för att beräkna bränsleförbrukning
Jag gillar att hålla koll på min bils bränsleförbrukning så när jag köper bränsle noterar jag körsträckan och hur mycket bränsle det tar att fylla tanken. Här i Storbritannien säljs bränsle i liter. Bilens milometer (OK, så det är en vägmätare) registrerar avstånd i miles. Och eftersom jag är för gammal och dum för att ändra, förstår jag bara MPG (miles per gallon). Om du nu tycker att det är lite sorgligt, vad sägs om det här. När jag kommer hem öppnar jag Excel och matar in data i ett kalkylblad som beräknar MPG för mig och kartlägger bilens prestanda. Beräkningen är antalet miles bilen har rest sedan den senaste påfyllningen dividerat med antalet gallon bränsle som använts …
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF BRÄNSLE
men eftersom bränslet kommer i liter och det finns 4,546 liter i en gallon …
MPG = (MILES THIS FILL - MILES LAST FILL) / LITERS OF BRÄNSLE x 4.546
Så här skrev jag funktionen …
Funktion MPG (StartMiles As Integer, FinishMiles As Integer, Liter As Single) MPG = (FinishMiles - StartMiles) / Liter * 4.546 Slutfunktion
och så här ser det ut på kalkylbladet …
Alla funktioner utför inte matematiska beräkningar. Här är en som ger information …
En funktion som ger dagens namn
Jag får ofta frågan om det finns en datumfunktion som anger veckodagen som text (t.ex. måndag). Svaret är nej*, men det är ganska enkelt att skapa ett. (*Tillägg: Sa jag nej? Kontrollera anteckningen nedan för att se funktionen jag glömde!). Excel har WEEKDAY -funktionen, som returnerar veckodagen som ett tal från 1 till 7. Du får välja vilken dag som är 1 om du inte gillar standard (söndag). I exemplet nedan returnerar funktionen "5" som jag råkar veta betyder "torsdag".
Men jag vill inte se ett nummer, jag vill se "torsdag". Jag kan ändra beräkningen genom att lägga till en VLOOKUP -funktion som hänvisade till en tabell någonstans som innehåller en lista med siffror och en motsvarande lista med dagnamn. Eller så kan jag ha det hela fristående med flera kapslade IF-uttalanden. För komplicerat! Svaret är en anpassad funktion …
Funktion DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Välj 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 Välj slutfunktion
Jag har kallat min funktion "DayName" och det krävs ett enda argument, som jag kallar "InputDate" som (naturligtvis) måste vara ett datum. Så här fungerar det…
- Funktionens första rad deklarerar en variabel som jag har kallat "DayNumber" som kommer att vara ett heltal (dvs. ett helt tal).
- Nästa rad i funktionen tilldelar variabeln ett värde med hjälp av Excels WEEKDAY -funktion. Värdet kommer att vara ett tal mellan 1 och 7. Även om standard är 1 = söndag, har jag inkluderat det ändå för klarhetens skull.
- Slutligen a Ärendeförklaring undersöker variabelns värde och returnerar lämpligt textstycke.
Så här ser det ut i kalkylbladet …
Få tillgång till dina anpassade funktioner
Om en arbetsbok har en VBA -kodmodul kopplad till den som innehåller anpassade funktioner kan dessa funktioner enkelt adresseras i samma arbetsbok som visas i exemplen ovan. Du använder funktionsnamnet som om det vore en av Excels inbyggda funktioner.
Du kan också hitta de funktioner som anges i funktionsguiden (kallas ibland verktyget Klistra in funktioner). Använd guiden för att infoga en funktion på normalt sätt (Infoga> Funktion).
Bläddra ner i listan över funktionskategorier för att hitta Användardefinierad och välj den för att se en lista över tillgängliga UDF …
Du kan se att de användardefinierade funktionerna saknar någon annan beskrivning än det obehagliga meddelandet "Ingen hjälp tillgänglig", men du kan lägga till en kort beskrivning …
Se till att du finns i arbetsboken som innehåller funktionerna. Gå till Verktyg> Makro> Makron. Du kommer inte att se dina funktioner listade här men Excel vet om dem! I Makronamn högst upp i dialogrutan, skriv in namnet på funktionen och klicka sedan på dialogrutorna alternativ knapp. Om knappen är nedtonad antingen har du stavat funktionsnamnet fel, eller om du har fel arbetsbok, eller så finns det inte! Detta öppnar ytterligare en dialogruta där du kan ange en kort beskrivning av funktionen. Klick OK för att spara beskrivningen och (här är den förvirrande biten) klicka Avbryt för att stänga dialogrutan Makro. Kom ihåg att spara arbetsboken som innehåller funktionen. Nästa gång du går till funktionsguiden kommer din UDF att ha en beskrivning …
Precis som makron kan användardefinierade funktioner användas i alla andra arbetsböcker så länge arbetsboken som innehåller dem är öppen. Det är dock inte bra att göra detta. Att ange funktionen i en annan arbetsbok är inte enkelt. Du måste lägga till dess värdarbetsboknamn till funktionsnamnet. Detta är inte svårt om du litar på funktionsguiden, men klumpigt att skriva ut manuellt. Funktionsguiden visar alla namn på alla UDF i andra arbetsböcker …
Om du öppnar arbetsboken där du använde funktionen vid en tidpunkt då arbetsboken som innehåller funktionen är stängd, visas ett felmeddelande i cellen där du använde funktionen. Excel har glömt bort det! Öppna funktionens värdarbok, beräkna om och allt är bra igen. Lyckligtvis finns det ett bättre sätt.
Om du vill skriva användardefinierade funktioner för användning i mer än en arbetsbok är den bästa metoden att skapa en Excel Lägga in. Ta reda på hur du gör detta i självstudien Skapa ett Excel-tillägg.
Tillägg
Jag borde verkligen veta bättre! Aldrig, aldrig, säg aldrig! Efter att ha berättat att det inte finns en funktion som ger dagens namn, har jag nu kommit ihåg den som kan. Se det här exemplet …
TEXT -funktionen returnerar värdet på en cell som text i ett visst talformat. Så i exemplet kunde jag ha valt = TEXT (A1, "ddd") för att återvända "Tor", = TEXT (A1, "mmmm") för att återvända "September" etc. Excel -hjälpen har några fler exempel på sätt att använda den här funktionen.
Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook.
Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postsajt