Skapa anpassade funktioner i Excel

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.

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

1. Tryck på Alt+F11 för att öppna Visual Basic-Editor (på Mac trycker du på FN+ALT+F11) och klickar sedan på Infoga > modul. Ett nytt modulfönster visas till höger om 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
   

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å.

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 mindre än det tal 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 till ett uttryck som används i ett annat VBA-makro eller en annan funktion. 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 texten i en cell. Om du tar med "åtgärdskod" av den här typen i en funktionsprocedur returnerar funktionen #VALUE! #REF!.

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.

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:

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:

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.

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.