Indholdsfortegnelse Makro - Excel-tip

Tak til Matt, der sendte denne uges Excel-spørgsmål:

Jeg har en stor og voksende Excel-projektmappe (mange ark). Jeg har inkluderet sidetal i sidefoden under udskrivning, men det bliver sværere og sværere at navigere, når vi er i et møde. Er der en måde at udskrive en indholdsfortegnelse baseret på Excel-regnearknavne, så jeg og personalet hurtigt kan gå til side #xx?

Dette er en god idé. Det første enkle forslag er at inkludere arknavnet i bundfoden på din udskrift. Når du klikker på "Brugerdefineret sidefod" i dialogboksen Sideopsætning / sidehovedfod, er der 7 ikoner. Ikonet til højre ligner et indekskort med tre faner. Ved at klikke i højre sektion: boksen og trykke på ikonet får arknavnet til at udskrives på hvert ark. Dette alene kan hjælpe med at navigere gennem rapporten.

MrExcel kan lide ideen om at have en makro til at oprette indholdsfortegnelsen. Hovedproblemet er, at Excel ikke beregner, hvor mange udskrevne sider der er på et regneark, før du foretager en forhåndsvisning af udskrivningen. Så makroen giver brugeren besked om, at de er ved at se en udskrifteksempel og beder dem om at afvise den med et klik på luk-knappen.

Makroen løber gennem hvert ark i projektmappen. I den aktuelle tilstand indsamler den information fra navnet på hvert regneark. Jeg har også inkluderet to andre linjer, som kommenteres. Hvis du hellere vil få beskrivelsen fra venstre overskrift eller fra en titel i celle A1, er der eksempler på linjer, der også skal gøres. Fjern kun kommentar til den, du vil bruge.

Makroen beregner hvor mange sider ved at tilføje en til antallet af vandrette sideskift (HPageBreaks.count). Det tilføjer en til antallet af lodrette sideskift (VPageBreaks.Count). Det multiplicerer disse to tal sammen for at beregne antallet af sider på regnearket. Hvis nogen loyale læsere har en bedre måde at gøre dette på, så lad mig det vide. Den aktuelle metode til optælling af sideskift er djævelsk langsom. Jeg kunne ikke synes at finde en ejendom, der fortæller mig, hvor mange udskrevne sider der er, men du ville tro, at Excel ville indeholde en.

Det sidste trick var at komme ind i sideområdet. Hvis et ark var på siderne "3 - 4", behandlede Excel dette som en dato og indtastede 4. marts. Ved at indstille celleformatet til tekst med "@" tegnet, indgår siderne korrekt.

Her er makroen:

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

Nedenfor er en tilsvarende makro, opdateret med flere nye makroteknikker.

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

En kort oversigt over de nye makroteknikker i den nyere makro:

  • Det er sjældent nødvendigt at vælge et ark
  • I stedet for at løbe gennem hvert ark i projektmappen på udkig efter et ark, der hedder Indholdsfortegnelse, antager den anden makro simpelthen, at den er der og kontrollerer status for Err-variablen. Hvis Err er noget andet end 0, ved vi, at arket ikke findes og skal tilføjes.
  • WST er en objektvariabel og defineres som regneark over indholdsfortegnelse. Således enhver henvisning til regneark ("Indholdsfortegnelse"). kan erstattes med WST.
  • Celler (række, søjle) -konstruktion er mere effektiv end kluge of Range ("A" & TOCRow). Da celler () forventer numeriske parametre, bliver Område ("A" og TOCRow) celler (TOCRow, 1)
  • De firkantede parenteser bruges som en stenografisk måde at henvise til rækkevidde ("A1").

Interessante artikler...