Makron för alla dina arbetsböcker – Personal.xlsb

Makron för alla arbetsböcker

Dagens VBA-tips riktar sig till dig som redan skapat en del makron som du kanske vill använda i flera arbetsböcker. I exemplet markeras den aktiva cellen gul, men det kan givetvis bytas ut mot helt annan kod, beroende på vad du själv vill utföra med koden.

Var lagras makron för alla arbetsböcker?

Det är möjligt att spara makron så att de är tillgängliga överallt, men om vi gjort det så ligger de sparade på ett speciellt ställe, som kan vara lite bökigt att hitta till. Vi skall därför börja med att ta upp var makrona sparas så att du kan ta bort dem om du inte är nöjd med dem.

När vi skapar makron för alla arbetsböcker sparas dessa makron i praktiken i en fil som heter personal.xlsb och som ligger dold på hårddisken. Sökvägen är:

C:\Users\ditt_användarnamn\AppData\Roaming\Microsoft\Excel\XLSTART

I sökvägen ovan står ”ditt_användarnamn”. Det skall vara det användarnamn som du har på den aktuella datorn. Så länge du inte skapat och sparat makron för flera arbetsböcker så finner du inte filen.

Vill du ta bort personal.xlsb måste du alltså kunna leta dig fram till AppData i sökvägen, som är dold. Om du av någon anledning inte kan söka upp mappen AppData bör du avstå från att göra nedanstående exempel, eller i varje fall avstå från att spara det. Notera att resten av sökvägen (Roaming\Microsoft\Excel\XLSTART) kanske inte finns i sin helhet på din dator förrän du sparat personal.xlsb.

Ett första makro för alla arbetsböcker

Eftersom filen personal.xlsb inte finns från början måste vi skapa den. Det enklaste sättet är att stänga alla andra arbetsböcker och sedan öppna en helt ny arbetsbok.

I denna väljer vi menyfliken Utvecklare [Developer] och klickar där på Spela in kod [Record Macro] i menygruppen Kod [Code]. Vi får då se gränssnittet:

I detta gränssnitt finns en rullgardin under rubriken Lagra makrot i [Store macro in]. I denna rullgardin väljer vi Arbetsboken Egna makron [Personal Macro Workbook]. Notera att Excel minns ditt senaste val, så nästa gång vi vill spela in ett makro måste vi ändra tillbaka rullgardinen till valet Den här arbetsboken [This Workbook].

Namnet på makrot är ointressant. Vi trycker på OK och gör något på arket, t.ex. flyttar markering till en ny cell. Därefter klickar vi på Stoppa inspelning [Stop Recording] i menyfliken Utvecklare [Developer].

Om vi nu trycker på Alt+F11 och ser på projektfönstret så finner vi personal.xlsb:

Jag har öppnat mappen Moduler [Modules] så att jag ser den kod jag just spelade in.

Detta makro är nu tillgängligt i alla arbetsböcker som vi öppnar. Jag kan när som helst köra makrot, som innebär att G7 blir markerat i det ark som jag för tillfället har aktivt. Om du själv har skapat ett makro som du vill göra tillgängligt överallt så kan du lägga det i en modul i personal.xlsb.

Bevaka händelser

Makron kan vi köra på några olika sätt, t.ex. via menyn Utvecklare [Developer]. Vi kan där koppla dem till en genväg, t.ex. Ctrl+q. Vi kan också koppla dem till knappar och objekt (t.ex. textrutor) av olika slag.

Ett helt annat sätt att köra makron är att bevaka vissa händelser, t.ex. att ark öppnas eller att celler markeras.

Vi skall nu skapa en kod som reagerar på att en viss cell blir markerad. Vi skall helt enkelt gulmarkera en cell när vi klickar på den.

Koden för att göra detta i en enda arbetsbok ser vi nedan:

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    ' Tar bort färg från alla celler i arket
    sh.Cells.Interior.ColorIndex = 0
    ' Gör den aktuella markeringen gul, genom att blanda till färgen med
    ' hjälp av funktionen RGB, där vi blandar fullt rött och fullt grönt,
    ' vilket ger gul färg
    Target.Interior.Color = RGB(255, 255, 0)
End Sub

Koden skall ligga i ThisWorkbook i just den arbetsbok som vi vill använda den i, dvs den skall inte ligga i ThisWorkbook i personal.xlsb. Testa att koden fungerar men tag sedan bort den, för vi skall ju se till att koden blir tillgänglig för alla arbetsböcker.

Den första raden börjar med Workbook_SheetSelectionChange, vilket betyder att vi ber Excel bevaka SheetSelectionChange i den aktuella arbetsboken.

Workbook är ett fördefinierat namn som pekar på den arbetsbok i vilket koden ligger. Det finns inte ett motsvarande namn som pekar på alla arbetsböcker. För att kunna få koden att fungera måste vi därför definiera ett eget namn. Denna definition skall ligga i ThisWorkbook för personal.xlsb.

Innan vi kan använda oss av MinExcelApp måste vi tala om för Excel vilken applikationen är. Konkret gör vi det genom en metod som vi kallar StartaMinExcelApp och vi skapar samtidigt en metod StoppaMinExcelApp så att vi kan slå av och på funktionaliteten, vi kan lägga den under raden där vi definierade applikationen:

Option Explicit

Private WithEvents MinExcelApp As Application

Public Sub StartaMinExcelApp()
    Set MinExcelApp = ThisWorkbook.Application
End Sub

Public Sub StoppaMinExcelApp()
    Set MinExcelApp = Nothing
End Sub

De nya makrona inklusive makrot vi gjorde först går nu att nå via knappen Makron [Macros] i menygruppen Kod [Code] i menyfliken Utvecklare [Developer].

För att göra den markerade cellen gul, i den för tillfället öppna arbetsboken, måste vi nu skapa en händelsehanterare som lyssnar på händelsen SheetSelectionChange i hela vår applikation. Det är faktiskt bara en liten förändring som behövs i förhållande till koden ovan för bara en enda arbetsbok:

Private Sub MinExcelApp_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    ' Tar bort färg från alla celler i arket
    sh.Cells.Interior.ColorIndex = 0
    ' Gör den aktuella markeringen gul, genom att blanda till färgen med
    ' hjälp av funktionen RGB, där vi blandar fullt rött och fullt grönt,
    ' vilket ger gul färg
    Target.Interior.Color = RGB(255, 255, 0)
End Sub

Skillnaden finns på första raden. Där det förut stod Workbook står det nu MinExcelApp.

Om vi nu bara har stavat allt rätt så kan vi sätta igång det hela genom att starta vår app med metoden StartaMinExcelApp. Därefter är det bara att testa att klicka på olika celler i ett ark.

Öppnar vi nu en annan arbetsbok ser vi att koden fungerar även där. Det finns dock en liten miss och det är att när vi stoppar MinExcelApp så är fortfarande sist markerad cell gul. Samma sak gäller alla ark som vi klickat i.

För att komma till rätta med detta bör vi dels se till att när vi stänger av vår applikation så görs alla celler i det aktiva arket ofärgade. Vi ändrar därför avslutningsmetoden till:

Public Sub StoppaMinExcelApp()
    ActiveSheet.Cells.Interior.ColorIndex = 0
    Set MinExcelApp = Nothing
End Sub

Vi lägger även till en metod MinExcelApp_SheetDeactivate som gör att arket görs vitt när vi lämnar det. Vi kallar den metoden MinExcelApp_SheetDeactivate. Vi behöver även en metod som gör det aktiva arket vitt när vi lämnar en arbetsbok. Vi kallar den metoden MinExcelApp_Workbook­Deactivate. Den kompletta koden för lösningen visas nedan:

Option Explicit

Private WithEvents MinExcelApp As Application

Public Sub StartaMinExcelApp()
    Set MinExcelApp = ThisWorkbook.Application
End Sub

Public Sub StoppaMinExcelApp()
    ActiveSheet.Cells.Interior.ColorIndex = 0
    Set MinExcelApp = Nothing
End Sub

Private Sub MinExcelApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ' Tar bort färg från alla celler i arket
    Sh.Cells.Interior.ColorIndex = 0
    ' Gör den aktuella markeringen gul, genom att blanda till färgen med
    ' hjälp av funktionen RGB, där vi blandar fullt rött och fullt grönt,
    ' vilket ger gul färg
    Target.Interior.Color = RGB(255, 255, 0)
End Sub

Private Sub MinExcelApp_SheetDeactivate(ByVal Sh As Object)
    Sh.Cells.Interior.ColorIndex = 0
End Sub

Private Sub MinExcelApp_WorkbookDeactivate(ByVal Wb As Workbook)
    Debug.Print ActiveSheet.Name
    ' Gör det aktiva arket i den deaktiverade arbetsboken vitt
    Wb.ActiveSheet.Cells.Interior.ColorIndex = 0
End Sub

När vi sparar arbetsboken vi arbetar med så sparar vi inte personal.xlsb. Men när vi stänger den sista öppna arbetsboken så kommer vi få frågan om vi vill spara personal.xlsb. Om vi svarar jag på detta så kommer vi att spara personal.xlsb i mappen som nämndes först i detta tips. Om du inte har möjlighet att navigera dig fram till denna mapp bör du inte spara personal.xlsb.

Om vi sparar kommer makrona vi gjort att vara tillgängliga i alla arbetsböcker. Det enda vi behöver göra är att starta vår applikation med makrot StartaMinExcelApp.

Vi kan dock starta vår applikation automatiskt genom att i ThisWorkbook i personal.xlsb lägga till koden:

Private Sub Workbook_Open()
    Set MinExcelApp = ThisWorkbook.Application
End Sub

Grundinställning är nu att MinExcelApp startas när vi öppnar första arbetsboken. Metoderna för att starta och stoppa applikationen fungerar fortfarande.

Notera att vid en eventuell kodkrasch kan MinExcelApp stoppas och då måste den startas igen med hjälp av startmetoden.

OBS! Notera att all kod ska vara placerad i personal.xlsb.

Sammanfattning

• Det går utmärkt att skapa såväl makron som händelsehanterare i personal.xlsb som blir tillgängliga för alla arbetsböcker. Om du har en stor mängd arbetsböcker och alltid vill utföra vissa operationer, som passar som ett makro, så är detta en bra möjlighet.
• Makron i personal.xlsb följer inte med de vanliga Excel-filer som du skickar till andra.
• Alternativet kan vara att ha bra makron samlade i ett Word-dokument och klistra in dem i de arbetsböcker där man vill att de skall vara tillgängliga.