Turinys Macro - Excel patarimai

Ačiū Matui, kuris atsiuntė šios savaitės „Excel“ klausimą:

Turiu didelę ir vis didėjančią „Excel“ darbaknygę (daug lapų). Spausdindamas įtraukiau puslapių numerius į poraštę, tačiau vis sunkiau naršyti, kai esame susitikime. Ar yra būdas atspausdinti turinio lentelę pagal „Excel“ darbalapių pavadinimus, kad aš ir darbuotojai galėtume greitai pereiti į #xx puslapį?

Tai puiki idėja. Pirmasis paprastas pasiūlymas yra įtraukti lapo pavadinimą į savo spaudinio poraštes. Spustelėjus „Pasirinktinė poraštė“ dialogo lange Puslapio nustatymas / Antraštės poraštė, yra 7 piktogramos. Dešiniausia dešinioji piktograma atrodo kaip rodyklės kortelė su trim skirtukais. Spustelėjus langelį „Dešinė skiltis“ ir paspaudus šią piktogramą, lapo pavadinimas bus spausdinamas ant kiekvieno lapo. Vien tai gali padėti naršyti ataskaitą.

MrExceliui patinka idėja turėti makrokomandą, kad būtų galima sukurti turinį. Pagrindinė problema yra ta, kad „Excel“ neapskaičiuoja, kiek atspausdintų puslapių yra darbalapyje, kol neatliksite spausdinimo peržiūros. Taigi, makrokomanda praneša vartotojui, kad jie ketina pamatyti spausdinimo peržiūrą, ir paprašo jo atsisakyti spustelėjus mygtuką Uždaryti.

Makrokomanda perrašo kiekvieną darbaknygės lapą. Esant dabartinei būsenai, jis renka informaciją iš kiekvieno darbalapio pavadinimo. Aš taip pat įtraukiau dvi kitas eilutes, kurios yra komentuojamos. Jei norėtumėte gauti aprašymą iš kairės antraštės arba iš pavadinimo langelyje A1, taip pat galite atlikti eilučių pavyzdžius. Tiesiog nekomentuokite to, kurį norite naudoti.

Makrokomanda apskaičiuoja, kiek puslapių pridedant vieną prie horizontalių puslapių pertraukų skaičiaus (HPageBreaks.count). Jis prideda vieną prie vertikalių puslapių pertraukų skaičiaus (VPageBreaks.Count). Padauginus šiuos du skaičius, apskaičiuojamas tame darbalapyje esančių puslapių skaičius. Jei kas nors ištikimas skaitytojas turi geresnį būdą tai padaryti, praneškite man. Dabartinis puslapių lūžių skaičiavimo metodas yra velniškai lėtas. Atrodė, kad negaliu rasti savybės, nurodančios, kiek yra atspausdintų puslapių, bet jūs manote, kad „Excel“ būtų vienas.

Paskutinis triukas buvo įeiti į puslapių diapazoną. Jei lapas buvo puslapiuose „3–4“, „Excel“ tai traktuos kaip datą ir įrašys kovo 4 d. Nustačius langelio formatą į tekstą su simboliu „@“, puslapiai įvedami tinkamai.

Štai makrokomanda:

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

Žemiau yra lygiavertė makrokomanda, atnaujinta keletu naujų makrokomandų.

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

Trumpa naujų makrokomandų santrauka naujesnėje makrokomandoje:

  • Retai reikia pasirinkti lapą
  • Užuot kilnojusi kiekvieną darbaknygės lapą ieškodama lapo pavadinimu Turinys, 2-oji makrokomanda tiesiog daro prielaidą, kad jis yra, ir tikrina kintamojo „Err“ būseną. Jei klaida yra kas kita nei 0, žinome, kad lapo nėra ir jį reikia pridėti.
  • WST yra objekto kintamasis ir apibrėžiamas kaip turinio lentelės darbalapis. Taigi bet kokia nuoroda į darbalapius („Turinys“). galima pakeisti WST.
  • Ląstelių (eilutės, stulpelio) konstrukcija yra efektyvesnė už Range („A“ ir TOCRow) kluge. Kadangi „Cells“ () tikisi skaitinių parametrų, diapazonas („A“ ir „TOCRow“) tampa langeliais (TOCRow, 1)
  • Laužtiniai skliaustai naudojami kaip trumpas būdas nurodyti diapazoną ("A1").

Įdomios straipsniai...