Inhoudsopgave Macro - Excel-tips

Met dank aan Matt die de Excel-vraag van deze week stuurde:

Ik heb een grote en groeiende Excel-werkmap (veel bladen). Ik heb paginanummers in de voettekst opgenomen tijdens het afdrukken, maar het wordt steeds moeilijker om te navigeren tijdens een vergadering. Is er een manier om een ​​inhoudsopgave af te drukken op basis van Excel-werkbladnamen, zodat ik en het personeel snel naar pagina #xx kunnen gaan?

Dit is een geweldig idee. De eerste eenvoudige suggestie is om de bladnaam in de voettekst van uw afdruk op te nemen. Als u op "Aangepaste voettekst" klikt in het dialoogvenster Pagina-instelling / koptekstvoettekst, zijn er 7 pictogrammen. Het meest rechtse pictogram ziet eruit als een indexkaart met drie tabbladen. Als u in het rechtergedeelte: -vak klikt en op dat pictogram drukt, wordt de bladnaam op elk blad afgedrukt. Dit alleen al kan helpen bij het navigeren door het rapport.

MrExcel houdt van het idee om een ​​macro te hebben om de inhoudsopgave te maken. Het grootste probleem is dat Excel pas berekent hoeveel afgedrukte pagina's er op een werkblad staan ​​als u een afdrukvoorbeeld maakt. De macro laat de gebruiker dus weten dat ze op het punt staan ​​een afdrukvoorbeeld te zien en vraagt ​​hen om het te sluiten met een klik op de knop Sluiten.

De macro loopt door elk blad in de werkmap. In de huidige staat verzamelt het informatie uit de naam van elk werkblad. Ik heb ook twee andere regels toegevoegd die zijn becommentarieerd. Als u de beschrijving liever uit de linkerkoptekst of uit een titel in cel A1 haalt, zijn er ook voorbeeldregels om een ​​van beide te doen. Verwijder gewoon het commentaar dat u wilt gebruiken.

De macro berekent hoeveel pagina's door er één toe te voegen aan het aantal horizontale pagina-einden (HPageBreaks.count). Het voegt een toe aan het aantal verticale pagina-einden (VPageBreaks.Count). Het vermenigvuldigt deze twee getallen met elkaar om het aantal pagina's op dat werkblad te berekenen. Als trouwe lezers een betere manier hebben om dit te doen, laat het me dan weten. De huidige methode om pagina-einden te tellen is duivels traag. Ik kon geen eigenschap vinden die me vertelt hoeveel afgedrukte pagina's er zijn, maar je zou denken dat Excel er een zou bevatten.

De laatste truc was het paginabereik binnen te gaan. Als een blad op pagina "3 - 4" stond, zou Excel dit als een datum behandelen en 4 maart invoeren. Door het celformaat in te stellen op tekst met het "@" -teken, worden de pagina's correct ingevoerd.

Hier is de macro:

Sub CreateTableOfContents() ' Copyright 1999.com ' Determine if there is already a Table of Contents TOCFound = False For Each s In Worksheets If s.Name = "Table of Contents" Then TOCFound = True Exit For End If Next s If Not TOCFound Then Sheets.Add Before:=Worksheets(1) ActiveSheet.Name = "Table of Contents" End If ' Set up the table of contents page TOCRow = 7 PageCount = 0 Sheets("Table of Contents").Select Range("A2").Value = "Table of Contents" Range("A6").CurrentRegion.Clear Range("A6").Value = "Subject" Range("A6").ColumnWidth = 36 Range("B6").Value = "Page(s)" Range("B6").ColumnWidth = 12 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Worksheets.Select Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." Msgbox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information For Each s In Worksheets s.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("Table of Contents").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 Next s End Sub

Hieronder staat een gelijkwaardige macro, bijgewerkt met verschillende nieuwe macrotechnieken.

Sub CreateTableOfContents() ' Copyright 2002.com ' Determine if there is already a Table of Contents ' Assume it is there, and if it is not, it will raise an error ' if the Err system variable is> 0, you know the sheet is not there Dim WST As Worksheet On Error Resume Next Set WST = Worksheets("Table of Contents") If Not Err = 0 Then ' The Table of contents doesn't exist. Add it Set WST = Worksheets.Add(Before:=Worksheets(1)) WST.Name = "TOC" End If On Error GoTo 0 ' Set up the table of contents page WST.(A2) = "Table of Contents" With WST.(A6) .CurrentRegion.Clear .Value = "Subject" End With WST.(B6) = "Page(s)" WST.Range("A1:B1").ColumnWidth = Array(36, 12) TOCRow = 7 PageCount = 0 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." MsgBox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information ' Loop through each sheet, collecting TOC information For Each S In Worksheets If S.Visible = -1 Then S.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("TOC").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 End If Next S End Sub

Een korte samenvatting van de nieuwe macrotechnieken in de nieuwere macro:

  • Het is zelden nodig om een ​​blad te selecteren
  • In plaats van door elk blad in de werkmap te gaan op zoek naar een blad met de naam Inhoudsopgave, gaat de tweede macro er gewoon van uit dat het er is en controleert de status van de variabele Err. Als Err iets anders is dan 0, weten we dat het blad niet bestaat en moet worden toegevoegd.
  • WST is een objectvariabele en wordt gedefinieerd als het werkblad Inhoudsopgave. Dus elke verwijzing naar werkbladen ("inhoudsopgave"). kan worden vervangen door WST.
  • De constructie Cellen (rij, kolom) is efficiënter dan de kluge van Bereik ("A" & TOCRow). Omdat Cells () numerieke parameters verwacht, wordt Bereik ("A" & TOCRow) cellen (TOCRow, 1)
  • De vierkante haken worden gebruikt als een verkorte manier om naar Range ("A1") te verwijzen.

Interessante artikelen...