Obsah Makro - tipy v programe Excel

Ďakujeme Mattovi, ktorý poslal tento týždeň otázku programu Excel:

Mám veľký a rastúci zošit programu Excel (veľa listov). Počas tlače som do päty zahrnul čísla stránok, navigácia je však čoraz ťažšia, keď sme na schôdzke. Existuje spôsob, ako vytlačiť obsah založený na názvoch pracovných hárkov programu Excel, aby som sa ja aj zamestnanci mohli rýchlo obrátiť na stránku #xx?

Toto je skvelý nápad. Prvým jednoduchým návrhom je zahrnúť názov hárku do päty výtlačku. Keď kliknete na „Prispôsobená päta“ v dialógovom okne Nastavenie stránky / Päta záhlavia, zobrazí sa 7 ikon. Ikona úplne vpravo vyzerá ako indexová karta s tromi kartami. Kliknutím na políčko v pravej časti: a klepnutím na túto ikonu spôsobíte, že sa názov hárku vytlačí na každý hárok. Toto samotné môže pomôcť pri prechádzaní prehľadom.

MrExcel má rád myšlienku mať makro na vytvorenie obsahu. Hlavným problémom je, že program Excel nepočíta, koľko vytlačených stránok je v pracovnom hárku, kým nevytvoríte ukážku tlače. Makro teda dáva používateľovi vedieť, že sa chystá zobraziť ukážku pred tlačou, a požiada ho, aby ju kliknutím na tlačidlo zatvorenia zavrel.

Makro prechádza každým listom v zošite. V súčasnom stave zhromažďuje informácie z názvu každého pracovného hárka. Zaradil som tiež ďalšie dva riadky, ktoré sú komentované. Ak chcete radšej získať popis z ľavej hlavičky alebo z nadpisu v bunke A1, môžete urobiť ukážkové riadky. Stačí odkomentovať ten, ktorý chcete použiť.

Makro vypočítava, koľko stránok pridaním jednej k počtu vodorovných zalomení strán (HPageBreaks.count). Pridáva jeden k počtu zvislých zlomov stránok (VPageBreaks.Count). Násobením týchto dvoch čísel sa vypočíta počet strán v tomto hárku. Ak majú verní čitatelia lepší spôsob, ako to urobiť, dajte mi vedieť. Súčasný spôsob počítania zlomov stránok je diabolsky pomalý. Nezdalo sa mi, že by som našiel nehnuteľnosť, ktorá mi hovorí, koľko tlačených stránok je, ale mysleli by ste si, že Excel jednu bude obsahovať.

Posledným trikom bolo zadanie rozsahu stránok. Keby sa hárok nachádzal na stránkach „3 - 4“, Excel by to považoval za dátum a zadal by 4. marca. Nastavením formátu bunky na text so znakom „@“ sa stránky zadajú správne.

Toto 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

Nižšie je uvedené ekvivalentné makro aktualizované o niekoľko nových techník makra.

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

Stručné zhrnutie nových techník makra v novšom makre:

  • Málokedy je potrebné vybrať list
  • Namiesto opakovania jednotlivých hárkov v zošite a hľadania hárku s názvom Obsah druhé makro jednoducho predpokladá, že tam je, a skontroluje stav premennej Err. Ak je Err niečo iné ako 0, vieme, že hárok neexistuje a je potrebné ho pridať.
  • WST je premenná objektu a je definovaná ako pracovný hárok s obsahom. Teda akýkoľvek odkaz na pracovné listy („obsah“). možno nahradiť WST.
  • Konštrukcia Bunky (riadok, stĺpec) je efektívnejšia ako skupina Range („A“ & TOCRow). Pretože Cells () očakáva číselné parametre, Range ("A" & TOCRow) sa stane bunkami (TOCRow, 1)
  • Hranaté zátvorky sa používajú ako skratkový spôsob označovania rozsahu („A1“).

Zaujímavé články...