Petua Kandungan Makro - Excel Petua

Terima kasih kepada Matt yang menghantar soalan Excel minggu ini:

Saya mempunyai buku kerja Excel yang besar dan berkembang (banyak helaian). Saya telah memasukkan nombor halaman di footer semasa mencetak, namun semakin sukar untuk dinavigasi ketika kita berada dalam mesyuarat. Adakah terdapat cara untuk mencetak jadual kandungan berdasarkan nama lembaran kerja Excel sehingga saya dan kakitangan dapat dengan cepat beralih ke halaman #xx?

Ini adalah idea yang bagus. Cadangan mudah pertama adalah memasukkan nama helaian di bahagian bawah cetakan anda. Ketika anda mengklik "Custom Footer" dalam dialog Page Setup / Header Footer, ada 7 ikon. Ikon paling kanan kelihatan seperti kad indeks dengan tiga tab. Mengklik di bahagian Kanan: kotak dan menekan ikon itu akan menyebabkan nama helaian dicetak pada setiap helaian. Ini sahaja dapat membantu menavigasi laporan.

MrExcel menyukai idea mempunyai makro untuk membuat senarai isi. Masalah utama ialah Excel tidak mengira berapa halaman bercetak pada lembaran kerja sehingga anda melakukan pratonton cetak. Oleh itu, makro tersebut membolehkan pengguna mengetahui bahawa mereka akan melihat Pratonton Cetakan dan meminta mereka untuk menolaknya dengan satu klik butang tutup.

Makro mengulangi setiap helaian dalam buku kerja. Dalam keadaan sekarang, ia mengumpulkan maklumat dari nama setiap lembaran kerja. Saya juga memasukkan dua baris lain yang dikomentari. Sekiranya anda lebih suka mendapatkan keterangan dari header kiri atau dari tajuk di sel A1, ada garis contoh untuk melakukan salah satu dari itu juga. Kerahkan sahaja yang anda mahu gunakan.

Makro mengira berapa halaman dengan menambahkan satu ke jumlah jeda halaman mendatar (HPageBreaks.count). Ia menambah satu jumlah jeda halaman menegak (VPageBreaks.Count). Ia mengalikan dua nombor ini untuk mengira bilangan halaman pada lembaran kerja tersebut. Sekiranya ada pembaca setia mempunyai cara yang lebih baik untuk melakukan ini, beritahu saya. Kaedah semasa menghitung jeda halaman perlahan. Saya tidak dapat mencari harta tanah yang memberitahu saya berapa banyak halaman bercetak yang ada, tetapi anda fikir Excel akan menyertakan satu.

Silap mata terakhir adalah memasuki julat halaman. Sekiranya helaian terdapat di halaman "3 - 4", Excel akan menganggap ini sebagai tarikh dan memasuki 4 Mac. Dengan menetapkan format sel ke teks dengan watak "@", halaman masuk dengan betul.

Inilah 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

Di bawah ini adalah makro yang setara, dikemas kini dengan beberapa teknik makro baru.

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

Ringkasan ringkas teknik makro baru dalam makro baru:

  • Jarang sekali memilih helaian
  • Daripada melengkung setiap helaian dalam buku kerja mencari helaian yang disebut Jadual Kandungan, makro ke-2 hanya menganggapnya ada dan memeriksa status pemboleh ubah Err. Sekiranya Err adalah perkara lain selain 0, kami tahu helaian itu tidak ada dan perlu ditambah.
  • WST adalah pemboleh ubah objek dan ditakrifkan sebagai lembaran kerja Jadual Kandungan. Oleh itu, apa-apa rujukan ke Lembaran Kerja ("Jadual Kandungan"). boleh diganti dengan WST.
  • Pembinaan Sel (baris, lajur) lebih efisien daripada kluge of Range ("A" & TOCRow). Kerana Sel () mengharapkan parameter numerik, Range ("A" & TOCRow) menjadi sel (TOCRow, 1)
  • Tanda kurung persegi digunakan sebagai cara ringkas untuk merujuk kepada Range ("A1").

Artikel menarik...