Зміст Макрос - Поради Excel

Зміст

Дякую Метту, який надіслав запитання Excel цього тижня:

У мене є велика і зростаюча книжка Excel (багато аркушів). Під час друку я включив номери сторінок у нижній колонтитул, однак орієнтуватися все важче і важче, коли ми знаходимось на зустрічі. Чи є спосіб надрукувати зміст на основі імен робочого аркуша Excel, щоб я та співробітники могли швидко перейти на сторінку #xx?

Це чудова ідея. Перша проста пропозиція - включити назву листа в колонтитули роздруківки. Коли ви натискаєте "Спеціальний нижній колонтитул" у діалоговому вікні Налаштування сторінки / Верхній колонтитул, з'являється 7 піктограм. Самий правий значок виглядає як покажчик із трьома вкладками. Клацнувши в правому розділі: поле та натиснувши цю піктограму, назва аркуша надрукується на кожному аркуші. Одне лише це може допомогти в навігації по звіту.

MrExcel подобається ідея мати макрос для створення змісту. Основна проблема полягає в тому, що Excel не обчислює, скільки друкованих сторінок знаходиться на аркуші, поки ви не зробите попередній перегляд друку. Отже, макрос повідомляє користувачеві, що він збирається побачити попередній перегляд друку, і просить його відхилити натисканням кнопки закриття.

Макрос перебирає кожен аркуш книги. У поточному стані він збирає інформацію з назви кожного аркуша. Я також включив ще два рядки, які коментуються. Якщо ви бажаєте отримати опис з лівого заголовка або з заголовка в комірці А1, є також зразки рядків, щоб зробити будь-який із них. Просто коментуйте ту, яку ви хочете використовувати.

Макрос обчислює кількість сторінок, додаючи одну до кількості горизонтальних розривів сторінок (HPageBreaks.count). Він додає одиницю до числа вертикальних розривів сторінок (VPageBreaks.Count). Він множить ці два числа разом, щоб обчислити кількість сторінок на цьому аркуші. Якщо у будь-якого відданого читача є кращий спосіб зробити це, будь ласка, повідомте мене. Нинішній метод підрахунку розривів сторінки диявольськи повільний. Я не міг знайти властивість, яка повідомляє мені, скільки друкованих сторінок є, але ви думаєте, що Excel містив би таку.

Останнім фокусом було введення діапазону сторінок. Якби аркуш був на сторінках "3 - 4", Excel вважав би це датою і вводив 4 березня. Встановивши формат комірки текстом із символом "@", сторінки входять належним чином.

Ось макрос:

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

Нижче наведено еквівалентний макрос, оновлений кількома новими методами макросу.

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

Короткий підсумок нових методів макросу в новіших макросах:

  • Рідко потрібно вибрати аркуш
  • Замість того, щоб перебирати кожен аркуш у книзі, шукаючи аркуш із назвою Зміст, 2-й макрос просто припускає, що він там є, і перевіряє статус змінної Err. Якщо Err - це щось інше, ніж 0, ми знаємо, що аркуш не існує, і його потрібно додати.
  • WST є об'єктною змінною і визначається як аркуш Змісту. Таким чином, будь-яке посилання на робочі аркуші ("Зміст"). можна замінити на WST.
  • Конструкція "Клітини" (рядок, стовпець) є більш ефективною, ніж клубок Range ("A" & TOCRow). Оскільки Cells () очікує числових параметрів, Range ("A" & TOCRow) стає клітинками (TOCRow, 1)
  • Квадратні дужки використовуються як скорочений спосіб посилання на діапазон ("А1").

Цікаві статті...