Kazalo vsebine Makro - nasveti za Excel

Hvala Mattu, ki je ta teden poslal vprašanje o Excelu:

Imam velik in rastoč Excelov delovni zvezek (veliko listov). Med tiskanjem sem v nogo vključil številke strani, vendar je vse težje in težje krmariti, ko smo na sestanku. Ali obstaja način, da natisnete kazalo na podlagi imen Excelovih delovnih listov, da se lahko jaz in osebje hitro obrnemo na stran #xx?

To je dobra ideja. Prvi preprost predlog je, da ime lista vključite v noge izpisa. Ko v pogovornem oknu Nastavitev strani / Noga glave kliknete "Noga po meri", je 7 ikon. Najbolj desna ikona je videti kot kazalo s tremi zavihki. S klikom na polje Desno: in s pritiskom na to ikono se bo ime lista natisnilo na vsak list. Že samo to lahko pomaga pri krmarjenju po poročilu.

MrExcelu je všeč ideja, da imamo makro za ustvarjanje kazala. Glavna težava je, da Excel ne izračuna, koliko natisnjenih strani je na delovnem listu, dokler ne naredite predogleda tiskanja. Makro torej uporabniku sporoči, da si bo kmalu ogledal predogled tiskanja, in ga prosi, da ga s klikom na gumb zapre zavrne.

Makro se vrti skozi vsak list v delovnem zvezku. V trenutnem stanju zbira informacije iz imena vsakega delovnega lista. Vključil sem tudi dve drugi vrstici, ki sta komentirani. Če bi raje dobili opis iz leve glave ali naslova v celici A1, obstajajo vzorčne vrstice, ki jih je treba narediti. Prekomentirajte tistega, ki ga želite uporabiti.

Makro izračuna število strani, tako da enemu doda število vodoravnih prelomov strani (HPageBreaks.count). Število navpičnih prelomov strani (VPageBreaks.Count) doda eno. Ti dve številki pomnoži skupaj, da izračuna število strani na tem delovnem listu. Če imajo zvesti bralci boljši način za to, mi sporočite. Trenutni način štetja prelomov strani je hudičevo počasen. Zdi se mi, da nisem našel lastnosti, ki bi mi povedala, koliko natisnjenih strani je, vendar bi si mislili, da bi jo Excel vključil.

Zadnji trik je bil vnos obsega strani. Če bi bil list na straneh "3 - 4", bi Excel to obravnaval kot datum in vpisal 4. marec. Če nastavite format celice na besedilo z znakom "@", strani vnesejo pravilno.

Tu je makro:

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

Spodaj je enakovreden makro, posodobljen z več novimi tehnikami makro.

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

Kratek povzetek novih makro tehnik v novejšem makro:

  • Redko je treba izbrati list
  • Namesto da bi prelistaval vsak list v delovnem zvezku in iskal list, imenovan kazalo, drugi makro preprosto domneva, da je tam, in preveri stanje spremenljivke Err. Če je Err kaj drugega kot 0, vemo, da list ne obstaja in ga je treba dodati.
  • WST je predmetna spremenljivka in je opredeljena kot delovni list Kazalo. Torej, kakršno koli sklicevanje na delovne liste ("Kazalo"). lahko nadomestite z WST.
  • Konstrukcija Celice (vrstica, stolpec) je učinkovitejša od klage Range ("A" & TOCRow). Ker Cells () pričakuje številske parametre, Range ("A" & TOCRow) postane celice (TOCRow, 1)
  • Oglati oklepaji se uporabljajo kot okrajšava za sklicevanje na obseg ("A1").

Zanimive Članki...