Microsoft Office Tutorials and References
In Depth Information
VBA Techniques
Determining the number of printed pages
If you need to determine the number of printed pages for a worksheet printout, you can use
Excel’s Print Preview feature and view the page count displayed at the bottom of the screen. The
VBA procedure that follows calculates the number of printed pages for the active sheet by
counting the number of horizontal and vertical page breaks:
Sub PageCount()
MsgBox (ActiveSheet.HPageBreaks.Count + 1) * _
(ActiveSheet.VPageBreaks.Count + 1) & “ pages”
End Sub
The following VBA procedure loops through all worksheets in the active workbook and displays
the total number of printed pages, as shown in Figure 11-13:
Figure 11-13: Using VBA to count the number of printed pages in a workbook.
Sub ShowPageCount()
Dim PageCount As Integer
Dim sht As Worksheet
PageCount = 0
For Each sht In Worksheets
PageCount = PageCount + (sht.HPageBreaks.Count + 1) * _
(sht.VPageBreaks.Count + 1)
Next sht
MsgBox “Total printed pages = “ & PageCount
End Sub
A workbook that contains this example is on the companion CD-ROM in a file named
page count.xlsm .
Displaying the date and time
If you understand the serial number system that Excel uses to store dates and times, you won’t
have any problems using dates and times in your VBA procedures.
Search JabSto ::

Custom Search