Sadržaj Makronaredba - Excel savjeti

Hvala Mattu koji je poslao ovotjedno Excel pitanje:

Imam veliku i sve veću Excel radnu knjigu (puno listova). Brojeve stranica uključio sam u podnožje tijekom ispisa, međutim sve je teže i teže se snalaziti kad smo na sastanku. Postoji li način za ispis sadržaja na temelju naziva Excel-ovog radnog lista, tako da ja i osoblje možemo brzo otvoriti stranicu #xx?

Ovo je sjajna ideja. Prvi jednostavan prijedlog je da se naziv lista uključi u podnožja vašeg ispisa. Kada kliknete "Prilagođeno podnožje" u dijaloškom okviru Postavljanje stranice / Podnožje zaglavlja, pojavit će se 7 ikona. Ikona s desne strane izgleda poput indeksne kartice s tri kartice. Klikom na odjeljak Desno: i pritiskom na tu ikonu prikazat će se naziv lista na svakom listu. Samo ovo može pomoći u kretanju kroz izvješće.

MrExcelu se sviđa ideja da imate makronaredbu koja će stvoriti sadržaj. Glavni je problem što Excel ne izračuna koliko je ispisanih stranica na radnom listu dok ne napravite pregled ispisa. Dakle, makronaredba daje korisniku do znanja da će uskoro vidjeti pregled ispisa i traži da je odbaci klikom na gumb zatvori.

Makronaredba petlja kroz svaki list u radnoj knjizi. U trenutnom stanju prikuplja podatke iz naziva svakog radnog lista. Uključio sam i dvije druge linije koje su komentirane. Ako biste radije dobili opis iz lijevog zaglavlja ili iz naslova u ćeliji A1, postoje i retci s uzorcima koji će to učiniti. Samo komentirajte onu koju želite koristiti.

Makronaredba izračunava broj stranica dodavanjem jedne broju vodoravnih prijeloma stranica (HPageBreaks.count). Dodaje jedan broju vertikalnih prijeloma stranica (VPageBreaks.Count). Množi ova dva broja zajedno da bi izračunao broj stranica na tom radnom listu. Ako neki vjerni čitatelji imaju bolji način za to, javite mi. Trenutna metoda brojanja prijeloma stranica vraški je spora. Čini mi se da nisam mogao pronaći svojstvo koje mi govori koliko ima ispisanih stranica, ali pomislili biste da bi Excel uključio jednu.

Posljednji trik bio je unos raspona stranica. Da je list na stranicama "3 - 4", Excel bi to tretirao kao datum i ušao 4. ožujka. Postavljanjem formata ćelije na tekst sa znakom "@", stranice ulaze pravilno.

Evo makronaredbe:

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

Ispod je ekvivalentna makronaredba, ažurirana s nekoliko novih makro tehnika.

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

Kratki sažetak novih makro tehnika u novijoj makronaredbi:

  • Rijetko je potrebno odabrati list
  • Umjesto da pregledava svaki list u radnoj knjizi tražeći list pod nazivom Sadržaj, 2. makronaredba jednostavno pretpostavlja da je tu i provjerava status varijable Err. Ako je Err nešto drugo osim 0, znamo da list ne postoji i da ga treba dodati.
  • WST je objektna varijabla i definira se kao radni list Sadržaja. Dakle, bilo koja referenca na radne listove ("Sadržaj"). može se zamijeniti WST-om.
  • Konstrukcija Ćelije (redak, stupac) učinkovitija je od klase Range ("A" & TOCRow). Budući da Cells () očekuje numeričke parametre, Range ("A" & TOCRow) postaje ćelije (TOCRow, 1)
  • Uglate zagrade koriste se kao skraćeni način upućivanja na domet ("A1").

Zanimljivi članci...