Skapa en automatisk Innehållsförteckning i Excel med VBA
Att skapa en automatisk innehållsförteckning i Excel med VBA gör dina arbetsböcker tydliga och professionella
Använd innehållsförteckningar i dina arbetsböcker för att skapa struktur och tydlighet
Att jobba med stora Excel-ark med många flikar kan vara utmanande. Därför kan en innehållsförteckning på det första bladet vara användbar. Innehållsförteckningen kan ha namnen på alla flikar. Dessutom kan den ge en kort beskrivning av varje fliks innehåll. Den kan även innehålla kommentarer om vem som gjort vad.
Stora arbetsböcker kan vara problematiska. Det blir lätt rörigt att hålla koll på allt innehåll. Kanske har du flera liknande flikar. Det kan då bli svårt att minnas vad du gjorde i vilken bok. Men med kommenterad innehållsförteckning blir allt betydligt mer överskådlig.
Dessutom, om flera personer dessutom ska samarbeta inom samma arbetsbok, förstärks behovet av en genomtänkt struktur med en tydlig innehållsförteckning.
Skapa en innehållsföreckning manuellt
Att skapa en innehållsförteckning manuellt är visserligen inte komplicerat, men kan ändå vara tidskrävande. Du behöver kopiera varje fliks namn och lägga in dem i en gemensam lista. Helst vill man också att det ser snyggt ut. Därför krävs det lite arbete med formateringen.
Skapa en innehållsföreckningmed VBA
Ett alternativt tillvägagångssätt är att använda VBA för att automatisera processen.
Att skrive en egen VBA-kod från grunden kan ta lite tid, särskilt om du är ny på VBA. Lyckligtvis finns det många kodexempel på nätet som du kan kopiera och anpassa. Det är också så jag brukar göra. AI-botar som ChatGPT gör det ännu enklare.
Nedan har du den VBA-kod jag själv använder. Den är sammansatt av koddelar jag hittat på nätet och har en layout och ett format som passar mig bra. Bilden nere till höger visar hur innehållsförteckningen kommer att se ut. Den har först en kolumn som numrerar varje bra från 1 och stigande. Till höger om numreringen ligger namnen på alla bladflikar i den ordning de ligger i arbetsboken. Varje bladfliksnamn är också en hyperlänk som man kan klicka på för att gå direkt till den fliken. Vanligen lägger jag sedan kommentarer i en tredje kolumn där jag t.ex. ger en kort beskrivning av vad som finns i just det bladet, eller någon annan kommentar jag anser behövas.
Om du kör koden skapas en innehållsförteckning automatiskt som ser ut som på bilden här bredvid. Koden skapar en numrerad lista med namn på alla bladflikar i arbetsboken och med hyperlänkar till varje blad. Det innebär att du kan klicka på bladnamnet för att direkt hoppa till det arbetsbladet
Du kan lägga koden i den arbetsbok du vill ha en innehållsförteckning för. Eller i ”Egna” arbetsboken (för personliga makron). Lägger du den i ”Egna”, är den alltid tillgänglig i Excel. Jag har en knapp i Snabbåtkomst-verktygsfältet. Ett klick på den skapar min innehållsförteckning.
Jag planerar att göra videoklipp som illustrerar hur detta fungerar på min Youtube-kanal, Andersexcel. Fram till dess kommer jag enbart att publicera VBA-koden här. En steg-för-steg-instruktion om hur koden kan användas följer efter koden.
Koden innehåller förklarande text (meningar som börjar med apostrof). De förklarar vad koddelarna gör. Dessa meningar påverkar inte koden. De gör den bara lättare att förstå. De kan också hjälpa dig om du vill ändra koden.
VBA-kod som skapar en innehållsförteckning med hyperlänkar till alla arbetsblad i en arbetsbok i Excel
Kopiera alla kod från och med ”Sub” till och med ”End sub” och följ därefter steg-för-steg instruktionen som följer:
Sub Innehållsförteckning()
'Lägger till en innehållsförteckning i början av arbetsboken för att skapa tydlighet och enkelt navigera till valfri flik
Dim blad As Worksheet
Dim Innehåll_blad As Worksheet
Dim område As Variant
Dim Alfa As Long
Dim bladNamn1 As String, bladNamn2 As String
Dim InnehållNamn As String
'Förbättrar kodens prestanda (stänger av skärmuppdatering medan makrot kör, samt inaktiverar ondödiga varningar)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Kollar om det redan finns en bladflik som heter Innehåll och om det gör det frågar om den skall ersättas
InnehållNamn = "Innehåll"
On Error Resume Next
Worksheets("Innehåll").Activate
On Error GoTo 0
If ActiveSheet.Name = InnehållNamn Then
Svar = MsgBox("Ett blad med namnet " & InnehållNamn & _
" Finns redan, vill du ersätta det?", vbYesNo)
'Valdes Nej?
If Svar <> vbYes Then GoTo ExitSub
Worksheets(InnehållNamn).Delete
End If
'Skapar en ny bladflik i början av arbetsboken
Worksheets.Add Before:=Worksheets(1)
'Det aktiva bladet tilldelas variabeln Innehåll_blad
Set Innehåll_blad = ActiveSheet
'Formaterar bladet "Innehåll"
With Innehåll_blad
.Name = InnehållNamn
.Range("B1") = "Innehållsförteckning"
.Range("B1").Font.Bold = True
End With
'Skapar en lista med alla bladnamn, bladet "Innehåll" undantaget
ReDim område(1 To Worksheets.Count - 1)
For Each blad In ActiveWorkbook.Worksheets
If blad.Name <> InnehållNamn Then
område(Alfa + 1) = blad.Name
Alfa = Alfa + 1
End If
Next blad
'Skapar en tabell med innehållsförteckning
For Alfa = LBound(område) To UBound(område)
Set blad = Worksheets(område(Alfa))
blad.Activate
With Innehåll_blad
.Hyperlinks.Add .Cells(Alfa + 2, 3), "", _
SubAddress:="'" & blad.Name & "'!A1", _
TextToDisplay:=blad.Name
.Cells(Alfa + 2, 2).Value = Alfa
End With
Next Alfa
Innehåll_blad.Activate
Innehåll_blad.Columns(3).EntireColumn.AutoFit
'Fixar formatet
Columns("A:B").ColumnWidth = 3.9
Range("B1").Font.Size = 18
Range("B1:F1").Borders(xlEdgeBottom).Weight = xlThin
With Range("B3:B" & Alfa + 1)
.Borders(xlInsideHorizontal).Color = RGB(255, 255, 255)
.Borders(xlInsideHorizontal).Weight = xlMedium
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(0, 117, 71)
End With
'Zoomar in och gör rutnätet osynligt
ActiveWindow.DisplayGridlines = False
ActiveWindow.Zoom = 130
ExitSub:
'Återställer skärmuppdatering
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Steg för steg hur du använder denna kod
Observera att detta visar hur du använder koden i en specifik arbetsbok. Vill du ha koden ”globalt” tillgänglig, dvs alltid när du öppnar Excel, så behöver du lägga in den i arbetsboken Egna (jag visar detta i filmen).
- Spara en kopia av din arbetsbok. Man kan inte ångra en VBA-kod man kört. Men om man sparat en kopia av sin arbetsbok kan så kan man alltid gå tillbaka till den. Jag brukar även alltid klicka på ”spara” innan jag kör ett oprövat makro. Om det blir fel kan du stänga utan att spara. När du öppnar arbetsboken igen är allt som det var innan du körde koden.
- Kopiera koden ovan
- Öppna din Excel-arbetsbok.
- Tryck på Alt + F11 för att öppna VBA-editorn.
- Klicka på Infoga i menyn och välj sedan Modul. Detta kommer att skapa en ny modul.
- Klistra in koden i det nyöppnade fönstret (i modulen).
- Tryck Ctrl + S för att spara och sedan Alt + Q för att stänga VBA-editorn.
- Gå tillbaka till din Excel-arbetsbok och tryck Alt + F8
- Välj Innehållsförteckning och tryck på Kör/Run.
Viktigt att veta när du stänger/sparar din arbetsbok med VBA-kod
När du stänger en arbetsbok som du har ett makro i så kommer du troligen få ett meddelande som säger att det inte går att spara en arbetsbok som innehåller VBA (dvs makron) som en makrofri arbetsbok. Detta är en skyddsmekanism inbyggd i Excel för att minska risken för att en arbetsbok innehåller någon form av skadlig kod. Vill du behålla din VBA-kod i din arbetsbok så måste du därför spara den som en ”Makroaktiverad arbetsbok”. Om du väljer ”Ja” här så sparas din arbetsbok som en vanlig (makrofri) arbetsbok utan ditt makro. Vill du behålla makrot i din arbetsbok så väljer du ”Nej” och därefter ”Spara som” och i listan över möjliga filformat så väljer du xlsm som innebär just att det blir en makroaktiverad arbetsbok.
En sak att tänka på är att om du planerar att dela din arbetsbok med andra användare så kan det vara smart att inte spara din arbetsbok som en makroaktiverad arbetsbok. När man öppnar en arbetsbok som innehåller makron får man nämligen alltid en varning om att arbetsboken innehåller ett makro och för den som inte vet vad detta betyder så kan det skapa onödig osäkerhet.
Nu tar vi ett steg bakåt för att ge en liten kort introduktion till VBA och makron:
En kort introduktion till VBA och Makron
Visual Basic for Applications (VBA) är ett programmeringsspråk utvecklat av Microsoft. Med VBA kan man till exempel automatisera processer som upprepas ofta eller skapa egna tillämpningsprogram. VBA-koden som jag visade ovan är ett typexempel på fördelarna med VBA. Att skapa en snygg innehållsförteckning i Excel men namn på alla bladflikar och med hyperlänkar kan naturligtvis göras helt manuellt, men det tar tid. I stället kan man ha en VBA-kod som gör detta med ett knapptryck.
Vad är VBA?
VBA, som står för Visual Basic for Applications, integrerar sig i alla Microsoft Office-produkter, inklusive Excel. Detta programmeringsspråk driver händelser, vilket innebär att användarhändelser som musklick eller knapptryckningar aktiverar koden (t.ex. när du klickar på en knapp, aktiveras programmet ”Innehållsförteckning”). Ett VBA-program kallas också för makro. Dvs ett makro är ett program skrivet med VBA-kod.
Förstå Makron
Makron är således bitar av VBA-kod som utför olika typer av uppgifter. De kan vara så enkla som att formatera en rad celler eller så komplexa som att utföra flera steg för att bearbeta och analysera data.
Makron kan skapas på två sätt, antingen genom att spela in ett antal åtgärder som man gör eller genom att skriva egen VBA-kod.
Skapa Makron med Inspelningsfunktionen
För enklare uppgifter kan du skapa makron genom att använda en inspelningsfunktion, som sparar allt du gör som VBA-instruktioner. Denna funktion registrerar exakt vad du gör och översätter det till VBA-kod. När du spelat in ett makro kan du sedan upprepa det du gjort genom att köra ditt makro igen. Inspelningsfunktionen är mycket användbar för att skapa enkla makron, som till exempel cellformatering i Excel.
Skriva egna VBA-koder
För svårare uppgifter kan du behöva skriva egen VBA-kod. Du kan inte spela in ett makro som utför en loop, till exempel. Koden jag visade tidigare, som skapar en innehållsförteckning, är en sådan kod.
Att skriva egen VBA-kod kan verka skrämmande om du är ny. Men det finns mycket hjälp att få. När du kan VBA-grunderna, kan du skriva kod som automatiserar nästan allt i Excel. AI-botar som ChatGPT kan skriva koden åt dig. Men det hjälper att ha grundläggande VBA-kunskap. För mer avancerade program kan det vara nödvändigt att manuellt justera koden.
Jag planerar att göra filmer om hur ChatGTP kan användas för att skriva VBA. Om du inte redan prenumererar på min youtubekanal (kostar ingenting men hjälper mig att växa) kan du klicka här [PRENUMERERA PÅ ANDERSEXCEL] så missar du inte när nästa film kommer.
Användningsområden för VBA och Makron
VBA och makron kan användas för att automatisera många olika typer av uppgifter i Excel. Detta kan inkludera saker som att formatera celler, bearbeta data, skapa diagram och mycket mer. Genom att automatisera dessa uppgifter kan du spara tid och undvika möjligheten för mänskliga fel.
Sammanfattningsvis
VBA och makron är kraftfulla verktyg för att automatisera uppgifter och effektivisera ditt Excel-arbete. Med makroinspelning eller egen VBA-kod kan du blir ditt arbetare enklare.
När jag körde koden så fick jag följande felmeddelande:
Kompileringsfel: Syntaxfel
På följande markering: ’ Skapa ett nytt kalkylblad i början
Hej
Märkligt fel… Raden med ”Skapa ett nytt kalkylblad i början” är inte en kodrad utan bara information (den har en apostrof i början).
”Kompileringsfel: Syntaxfel” är ett generiskt felmeddelande som kan vara resultatet av många olika kodproblem i VBA. Felmeddelandet kan till exempel utlösas av ett felaktigt format på ett uttryck, en ofullständig instruktion, eller att det saknas något element som VBA förväntar sig att hitta.
Så den enda anledningen till att den ger ett fel är om det blivit något fel med apostrofen.
När du öppnar VBA-editorn så skall alla meningar med apostrof vara grönfärgade. Är de inte det så är det som är felet.
Dvs det har blivit något läsfel på koden när den kopierats och klistrats in.
Några steg för att felsöka kan vara detta.
1. Stäng ner Excel helt och hållet. Starta om och testa igen (ibland så räcker det).
2. Kopiera och klistra in koden en gång till. Om möjligt med en annan webbläsare.
3. Om felet fortfarande. Testa att skriva in en apostrof före meningen. (det kan vara något problem med teckensnitt etc).
mvh
Anders