İçindekiler Makro - Excel İpuçları

Bu haftanın Excel sorusunu gönderen Matt'e teşekkürler:

Büyük ve büyüyen bir Excel çalışma kitabım var (çok sayıda sayfa). Yazdırırken sayfa numaralarını altbilgiye ekledim, ancak bir toplantıdayken gezinmek gittikçe zorlaşıyor. Excel çalışma sayfası adlarına dayalı bir içindekiler tablosu yazdırmanın bir yolu var mı, böylece ben ve personel hızla #xx sayfasına dönebilir mi?

Bu harika bir fikir. İlk basit öneri, çıktınızın altbilgilerine sayfa adını eklemektir. Sayfa Yapısı / Üstbilgi Altbilgisi iletişim kutusunda "Özel Altbilgi" yi tıkladığınızda, 7 simge vardır. En sağdaki simge, üç sekmeli bir dizin kartına benzer. Sağ bölüm: kutuya tıklamak ve bu simgeye basmak, sayfa adının her sayfaya yazdırılmasına neden olacaktır. Bu tek başına raporda gezinmeye yardımcı olabilir.

MrExcel, içindekiler tablosunu oluşturmak için bir makroya sahip olma fikrini seviyor. Temel sorun, Excel'in siz bir baskı önizlemesi yapana kadar çalışma sayfasında kaç sayfa yazdırıldığını hesaplamamasıdır. Bu nedenle, makro, kullanıcının bir Baskı Önizlemesi göreceğini bilmesini sağlar ve kapat düğmesine tıklayarak onu kapatmasını ister.

Makro, çalışma kitabındaki her sayfada döngü oluşturur. Mevcut durumunda, her çalışma sayfasının adından bilgi toplar. Ayrıca yorumlanmış diğer iki satırı da ekledim. Açıklamayı sol başlıktan veya A1 hücresindeki bir başlıktan almayı tercih ederseniz, bunlardan birini yapmak için örnek satırlar da vardır. Sadece kullanmak istediğinizin açıklamasını kaldırın.

Makro, yatay sayfa sonu sayısına (HPageBreaks.count) bir ekleyerek kaç sayfa olduğunu hesaplar. Dikey sayfa sonu sayısına bir ekler (VPageBreaks.Count). Bu çalışma sayfasındaki sayfa sayısını hesaplamak için bu iki sayıyı birbiriyle çarpar. Sadık okuyucuların bunu yapmanın daha iyi bir yolu varsa, lütfen bana bildirin. Sayfa sonlarını saymanın mevcut yöntemi şeytani bir şekilde yavaş. Bana kaç tane basılı sayfa olduğunu söyleyen bir özellik bulamadım, ancak Excel'in bir tane içereceğini düşünürdünüz.

Son numara, sayfa aralığına girmekti. Bir sayfa "3 - 4" sayfalarında olsaydı, Excel bunu tarih olarak kabul eder ve 4 Mart'a girer. Hücre biçimini "@" karakterli metne ayarlayarak, sayfalar düzgün şekilde girilir.

İşte 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

Aşağıda, birkaç yeni makro tekniği ile güncellenen eşdeğer bir makro bulunmaktadır.

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

Yeni makrodaki yeni makro tekniklerin kısa bir özeti:

  • Bir sayfa seçmek nadiren gereklidir
  • Çalışma kitabındaki her sayfada İçindekiler Tablosu adlı bir sayfayı aramak yerine, 2. makro onun orada olduğunu varsayar ve Err değişkeninin durumunu kontrol eder. Err, 0'dan farklı bir şey ise, sayfanın olmadığını ve eklenmesi gerektiğini biliyoruz.
  • WST bir nesne değişkenidir ve İçindekiler çalışma sayfası olarak tanımlanır. Bu nedenle, Çalışma Sayfalarına herhangi bir referans ("İçindekiler Tablosu"). WST ile değiştirilebilir.
  • Hücreler (satır, sütun) yapısı, Aralık kümesinden ("A" ve TOCRow) daha etkilidir. Cells () sayısal parametreler beklediğinden, Aralık ("A" & TOCRow) hücreler olur (TOCRow, 1)
  • Köşeli parantezler Aralıktan ("A1") bahsetmenin kısa bir yolu olarak kullanılmıştır.

Ilginç makaleler...