Περιεχόμενα Macro - Excel Συμβουλές

Ευχαριστώ τον Matt που έστειλε την ερώτηση Excel αυτής της εβδομάδας:

Έχω ένα μεγάλο και αναπτυσσόμενο βιβλίο εργασίας του Excel (πολλά φύλλα). Έχω συμπεριλάβει αριθμούς σελίδων στο υποσέλιδο κατά την εκτύπωση, ωστόσο γίνεται όλο και πιο δύσκολο να πλοηγηθείτε όταν είμαστε σε μια συνάντηση. Υπάρχει τρόπος να εκτυπώσετε έναν πίνακα περιεχομένων με βάση τα ονόματα των φύλλων εργασίας του Excel, έτσι ώστε εγώ και το προσωπικό να μπορέσουμε να γυρίσουμε γρήγορα στη σελίδα #xx;

Είναι θαυμάσια ιδέα. Η πρώτη απλή πρόταση είναι να συμπεριλάβετε το όνομα του φύλλου στα υποσέλιδα της εκτύπωσης. Όταν κάνετε κλικ στο "Custom Footer" στο διάλογο Page Setup / Header Footer, υπάρχουν 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

Μια σύντομη περίληψη των νέων τεχνικών μακροεντολών στη νεότερη μακροεντολή:

  • Είναι σπάνια απαραίτητο να επιλέξετε ένα φύλλο
  • Αντί να περιβάλλει κάθε φύλλο στο βιβλίο εργασίας που αναζητά ένα φύλλο που ονομάζεται Πίνακας περιεχομένων, η 2η μακροεντολή απλά υποθέτει ότι είναι εκεί και ελέγχει την κατάσταση της μεταβλητής Err. Εάν το Err είναι κάτι διαφορετικό από το 0, γνωρίζουμε ότι το φύλλο δεν υπάρχει και πρέπει να προστεθεί.
  • Το WST είναι μια μεταβλητή αντικειμένου και ορίζεται ως το φύλλο εργασίας του πίνακα περιεχομένων. Έτσι, οποιαδήποτε αναφορά σε φύλλα εργασίας ("Πίνακας περιεχομένων"). μπορεί να αντικατασταθεί με WST.
  • Η κατασκευή Cells (σειρά, στήλη) είναι πιο αποτελεσματική από το kluge του Range ("A" & TOCRow). Επειδή τα κελιά () αναμένουν αριθμητικές παραμέτρους, το εύρος ("A" & TOCRow) γίνεται κελιά (TOCRow, 1)
  • Οι αγκύλες τετράγωνου χρησιμοποιούνται ως ένας σύντομος τρόπος αναφοράς στο εύρος ("A1").

ενδιαφέροντα άρθρα...