Съдържание Макрос - Съвети на 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

Кратко обобщение на новите макро техники в по-новия макрос:

  • Рядко е необходимо да изберете лист
  • Вместо да преглежда всеки лист в работната книга, търсейки лист, наречен Съдържание, вторият макрос просто приема, че е там и проверява състоянието на променливата Err. Ако Err е нещо различно от 0, знаем, че листът не съществува и трябва да бъде добавен.
  • WST е обектна променлива и се определя като работен лист за съдържанието. По този начин, всяко позоваване на работни листове ("Съдържание"). може да бъде заменен с WST.
  • Конструкцията Клетки (ред, колона) е по-ефективна от клуба на Range ("A" & TOCRow). Тъй като Cells () очаква числови параметри, Range ("A" & TOCRow) се превръща в клетки (TOCRow, 1)
  • Квадратните скоби се използват като съкратен начин за препращане към обхват ("A1").

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