Skapa anpassade funktioner i Excel

Även om Excel innehåller en mängd inbyggda funktioner för kalkylblad finns risken att det inte finns en funktion för alla de beräkningstyper som du vill utföra. Excel-utvecklarna kunde helt enkelt inte förutse varenda användares beräkningsbehov. I stället ger Excel dig möjlighet att skapa anpassade funktioner som beskrivs i den här artikeln.

Anpassa funktioner, som makron, med programmeringsspråket Visual Basic for Applications (VBA). Dessa skiljer sig från makron på två väsentliga sätt. Först måste de använda Function-procedurer i stället för Sub-procedurer. Det vill säga att de börjar med ett Function-uttryck i stället för ett Sub-uttryck och avslutas med End Function i stället för End Sub. För det andra gör de beräkningar i stället för att genomföra åtgärder. Vissa typer av uttryck, till exempel uttryck som väljer och formaterar intervall, är exkluderade från anpassade funktioner. I den här artikeln får du lära dig hur du skapar och använder anpassade funktioner. Om du vill skapa funktioner och makron kan du arbeta med Visual Basic Editor (VBE), som öppnas i ett nytt fönster separat från Excel.

Föreställ dig att ditt företag har mängdrabatt på 10 procent på en produkt, förutsatt att ni beställer fler än 100 enheter. I följande stycken visar vi en funktion för att räkna ut rabatten.

Exemplet nedan visar ett orderformulär där varje artikel, antal, pris, rabatt (om någon) och det resulterande utökade priset visas i en lista.

Exempel på orderformulär utan en anpassad funktion

Skapa en anpassad RABATT-funktion i den här arbetsboken med hjälp av följande steg:

  1. Öppna Visual Basic Editor genom att trycka Alt+F11 (på Mac trycker du FN+ALT+F11). Klicka sedan på Infoga > Modul. Ett nytt modulfönster visas till höger i Visual Basic Editor.

  2. Kopiera och klistra in följande kod till den nya modulen.

    Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If

    DISCOUNT = Application.Round(Discount, 2)
    End Function

Obs!: Med Tabb-tangenten kan du göra koden enklare att läsa med hjälp indragna rader. Indrag ger bara fördelar och är valfritt, eftersom koden körs med eller utan indraget. När du har gjort en rad indragen förutsätter Visual Basic Editor att nästa rad ska vara indragen på samma sätt. Flytta ut ett tabbtecken (dvs. flytta det åt vänster) genom att klicka på Skift+Tabb.

Nu är du redo att använda den nya funktionen RABATT. Stäng Visual Basic Editor, markera cell G7 och skriv följande:

=RABATT(D7,E7)

Excel beräknar 10 procent rabatt på 200 enheter om 475 kr per enhet och returnerar 9 500 kr.

I den första raden i VBA-koden angav du att funktionen RABATT (antal, pris) kräver två argument: antal och pris. När du namnger en funktion i en kalkylbladscell måste du inkludera de två argumenten. I formeln =RABATT(D7,E7) är D7 argumentet för antal och E7 är argumentet för pris. Nu kan du kopiera formeln RABATT till G8:G13 och få de resultat som visas nedan.

Fundera över hur Excel tolkar funktionsproceduren. När du trycker på RETUR söker Excel efter namnet RABATT i den aktuella arbetsboken och upptäcker att det är en anpassad funktion i en VBA-modul. I argumentnamn inom parenteser fungerar antal och pris som platshållare för värden som beräkningen av rabatten baseras på.

Exempel på orderformulär med en anpassad funktion

Uttrycket Om i följande kodblock undersöker argumentet för antal och avgör om antalet sålda artiklar är större än eller lika med 100:

If quantity >= 100 Then
DISCOUNT = quantity * price * 0.1
Else
DISCOUNT = 0
End If

Om antalet objekt som sålts är större än eller lika med 100 kör VBA följande uttryck som multiplicerar värdet för antal med värdet för pris. Resultatet multipliceras sedan med 0,1:

Discount = quantity * price * 0.1

Resultatet lagras som variabeln Rabatt. Ett VBA-uttryck som lagrar ett värde i en variabel kallas för tilldelningssats, eftersom det utvärderar uttrycket på höger sida av ett likhetstecken och tilldelar resultatet till det variabla namnet till vänster. Eftersom variabeln Rabatt har samma namn som funktionsproceduren returneras det värde som lagrats i variabeln till kalkylbladsformeln som anropade funktionen RABATT.

Om antal är mindre än 100 kör VBA följande uttryck:

Discount = 0

Slutligen avrundar följande uttryck det värde som tilldelats variabeln Rabatt till två decimaler:

Discount = Application.Round(Discount, 2)

VBA har ingen funktion för att AVRUNDA, men Excel har det. Om du vill använda AVRUNDA i det här uttrycket måste du därför uppmana VBA till att leta efter metoden Avrunda (funktion) i programobjektet (Excel). Det gör du genom att lägga till ordet Program före ordet Avrunda. Använd följande syntax när du behöver komma åt en Excel-funktionen från en VBA-modul.

En anpassad funktion måste börja med ett Function-uttryck och sluta med ett End Function-uttryck. Förutom funktionsnamnet anger Function-uttrycket vanligtvis ett eller flera argument. Du kan dock skapa en funktion utan argument. Excel innehåller flera inbyggda funktioner – exempelvis SLUMP och NU, som inte använder argument.

Efter Function-uttrycket innehåller en funktionsprocedur en eller flera VBA-uttryck som tar beslut och beräknar med hjälp av argument som skickas till funktionen. Slutligen måste du ha ett uttryck som tilldelar ett värde till en variabel med samma namn som funktionen någonstans i funktionsproceduren. Det här värdet returneras till formeln som anropar funktionen.

Antalet VBA -nyckelord som du kan använda i anpassade funktioner är färre än antalet som du kan använda i makron. Anpassade funktioner får inte göra något annat än att returnera ett värde till en formel i ett kalkylblad eller ett uttryck som används i en annan VBA-makro eller -funktion. HTML-anpassade funktioner kan till exempel inte ändra storlek på fönster, redigera en formel i en cell eller ändra teckensnitt, färg eller mönsteralternativ för text i en cell. Om du inkluderar ”åtgärdskod” för den här typen i en funktionsprocedur returnerar funktionen #VÄRDEFEL!.

Den enda åtgärden en funktionsprocedur kan utföra (förutom att utföra beräkningar) är att visa en dialogruta. Du kan använda uttrycket IndataRuta uttryck i en anpassad funktion som ett sätt att få indata från användaren som kör funktionen. Du kan använda ett MsgBox-uttryck som ett sätt att skicka information till användaren. Du kan också använda anpassade dialogrutor eller Användarformulär, men det är överkurs för den här introduktionen.

Även enkla makron och anpassade funktioner kan vara svåra att läsa. Genom att lägga till förklarande text i form av kommentarer kan du göra så att det är enklare att förstå dem. Du kan lägga till kommentarer med hjälp av en apostrof före den förklarande texten. I följande exempel visas funktionen RABATT med kommentarer. Med kommentarer som dessa blir det enklare för dig eller andra att underhålla VBA-koden vart eftersom. Om du behöver göra en ändring i koden i framtiden blir det enklare att förstå det du gjorde ursprungligen.

Exempel på en VBA-funktion med kommentarer

En apostrof meddelar att Excel ska ignorera allt till höger på samma rad, så du kan skapa kommentarer antingen på raderna eller på höger sida av rader som innehåller VBA-kod. Du kan inleda ett längre kodblock med en kommentar som beskriver det övergripande syftet och sedan använda infogade kommentarer för att dokumentera enskilda uttryck.

Ett annat sätt att dokumentera makron och anpassade funktioner är att ge dem beskrivande namn. I stället för att exempelvis ge ett makro namnet Etiketter kan du ge det namnet Månadsetiketter och på så sätt beskriva mer utförligt vilket syfte makrot har. Det är särskilt användbart att använda beskrivande namn för makron och anpassade funktioner om du har skapat många procedurer, särskilt om du skapar procedurer som har liknande men inte identiska syften.

Hur du dokumenterar dina makron och anpassade funktioner har bara med din personliga smak att göra. Det som är viktigt är att hitta en metod för dokumentation och sedan använda den konsekvent.

Om du vill använda en anpassad funktion måste arbetsboken som innehåller modulen där du skapade funktionen vara öppen. Om arbetsboken inte är öppen får du felmeddelandet #NAMN? när du försöker använda funktionen. Om du refererar till funktionen i en annan arbetsbok måste du inleda funktionsnamnet med namnet på arbetsboken som funktionen finns i. Om du exempelvis skapar en funktion som kallas RABATT i en arbetsbok som kallas Personlig.xlsb och du anropar den funktionen från en annan arbetsbok måste du ange =personlig.xlsb!rabatt(), inte bara =rabatt().

Du kan spara några tangenttryckningar (och eventuella fel) genom att välja dina anpassade funktioner från dialogrutan Infoga funktion. Anpassade funktioner visas i den användardefinierade kategorin:

dialogrutan infoga funktion

Ett enklare sätt att göra så att dina anpassade funktioner är tillgängliga hela tiden är att lagra dem i en separat arbetsbok och sedan spara arbetsboken som ett tillägg. Du kan sedan göra tillägget tillgängligt när du kör Excel. Gör så här:

  1. Efter att du har skapat funktionen som du behöver klickar du på Arkiv > Spara som.

    I Excel 2007 klickar du på Microsoft Office-knappen och sedan på Spara som

  2. I dialogrutan Spara som öppnar du den nedrullningsbara listrutan Filformat och klickar på Excel-tillägg. Spara arbetsboken med ett namn du kommer ihåg, som MinaFunktioner, i mappen Tillägg. Dialogrutan Spara som föreslår den mappen, så att allt du behöver göra är att acceptera standardplatsen.

  3. När du har sparat arbetsboken klickar du på Arkiv > Excel-alternativ.

    I Excel 2007 klickar du på Microsoft Office-knappen och sedan på Excel-alternativ.

  4. Klicka på kategorin Tillägg i dialogrutan Excel-alternativ.

  5. Klicka på Excel-tillägg i den nedrullningsbara listrutan Hantera. Klicka sedan på Gå till-knappen.

  6. Markera kryssrutan bredvid det namn du använde när du sparade arbetsboken i dialogrutan Tillägg, så som visas nedan.

    dialogrutan tillägg

  1. Efter att du har skapat funktionen som du behöver klickar du på Arkiv > Spara som.

  2. I dialogrutan Spara som öppnar du den nedrullningsbara listrutan Filformat och klickar på Excel-tillägg. Spara arbetsboken med ett namn du kommer ihåg , som MinaFuntioner.

  3. När du har sparat arbetsboken klickar du på Verktyg > Excel-tillägg.

  4. I dialogrutan Tillägg hittar du ditt tillägg genom att klicka på knappen Bläddra. Klicka på Öppna och markera sedan kryssrutan bredvid ditt tillägg i rutan Tillgängliga tillägg.

När du har genomfört de här stegen blir anpassade funktioner tillgängliga varje gång du kör Excel. Om du vill lägga till i något funktionsbiblioteket återgår du till Visual Basic Editor. I projektutforskaren i Visual Basic Editor, under en VBA-projektrubrik, hittar du en modul med samma namn som din tilläggsfil. Tillägget har filnamnstillägget .xlam.

namngiven modul i vbe

Om du dubbelklickar på modulen i projektutforskaren visas din funktionskod i Visual Basic Editor. Lägg till en ny funktion genom att placera din insättningspunkt efter End Function-uttrycket som avslutar den sista funktionen i kodfönstret och börja sedan skriva. På det här sättet kan du skapa så många funktioner som du behöver och de är alltid tillgängliga i den användardefinierade kategorin i dialogrutan Infoga funktion.

Det här innehållet skapades ursprungligen av Mark Dodge och Craig Stinson som en del av boken Microsoft Office Excel 2007 Inside Out. Innehållet har sedan dess uppdaterats för att kunna tillämpas på nyare versioner av Excel.

Behöver du mer hjälp?

Du kan alltid fråga en expert i Excel Tech Community, få support i Answers-communityn eller föreslå en ny funktion eller förbättringar på Excel User Voice.

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×