Макрос содержания - Советы по Excel

Спасибо Мэтту, приславшему на этой неделе вопрос по Excel:

У меня есть большая и постоянно растущая книга Excel (много листов). Я включил номера страниц в нижний колонтитул во время печати, однако становится все труднее ориентироваться на собрании. Есть ли способ распечатать оглавление на основе имен листов Excel, чтобы я и сотрудники могли быстро перейти к странице №xx?

Это отличная идея. Первое простое предложение - включить название листа в нижний колонтитул распечатки. Когда вы нажимаете «Пользовательский нижний колонтитул» в диалоговом окне «Параметры страницы / нижний колонтитул», появляется 7 значков. Крайний правый значок выглядит как учетная карточка с тремя вкладками. Если щелкнуть поле «Правый раздел» и нажать этот значок, имя листа будет печататься на каждом листе. Одно это может помочь при навигации по отчету.

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

Макрос проходит по каждому листу в книге. В текущем состоянии он собирает информацию из имени каждого рабочего листа. Я также включил две другие строки, которые закомментированы. Если вы предпочитаете получать описание из левого заголовка или из заголовка в ячейке A1, есть образцы строк для выполнения любого из них. Просто раскомментируйте тот, который хотите использовать.

Макрос вычисляет количество страниц, добавляя единицу к количеству горизонтальных разрывов страниц (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

Краткое описание новых методов макросов в новом макросе:

  • Подбирать лист бывает необходимо редко
  • Вместо того, чтобы перебирать каждый лист в книге в поисках листа с названием Table of Contents, второй макрос просто предполагает, что он есть, и проверяет состояние переменной Err. Если Err отличен от 0, мы знаем, что лист не существует и его нужно добавить.
  • WST - это объектная переменная, которая определяется как рабочий лист Table of Contents. Таким образом, любая ссылка на Рабочие листы («Оглавление»). можно заменить на WST.
  • Конструкция Cells (row, column) более эффективна, чем набор Range («A» и TOCRow). Поскольку Cells () ожидает числовые параметры, Range ("A" & TOCRow) становится ячейками (TOCRow, 1).
  • Квадратные скобки используются как сокращенное обозначение диапазона («A1»).

Интересные статьи...