VBA实用小程序：获取工作表每页的地址

有时，我们需要知道组成工作表的所有页面的地址。如下图1所示的工作表，由6页组成。

图1

下面的程序列出每页的地址：

代码语言：javascript复制

Sub MainCode()
   PageAddress True
End Sub
Sub PageAddress(colorcode As Boolean)
    Dim c As Integer
    Dim v As Integer
    Dim h As Integer
    Dim cln As Integer
    Dim rw As Integer
    Dim hgth As Integer
    Dim wth As Integer
    Dim i As Integer
    Dim s As String
    Dim pag()
    Dim ws As Worksheet
    Set ws =ActiveSheet
    c = 1
    s =""
   ActiveWindow.View = xlPageBreakPreview
    ws.PageSetup.PrintArea= ""
   ws.PageSetup.PrintArea = ws.UsedRange.Address
    ReDim Preserve pag(1 To (ws.VPageBreaks.Count   1) * (ws.HPageBreaks.Count   1))
    For v = 0 To ws.VPageBreaks.Count
        For h= 0 To ws.HPageBreaks.Count
            If v = ws.VPageBreaks.Count Then
               wth = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
           Else
               wth = ws.VPageBreaks(v   1).Location.Column - 1
           End If
           If h = ws.HPageBreaks.Count Then
               hgth = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
           Else
               hgth = ws.HPageBreaks(h   1).Location.Row - 1
           End If
           If v = 0 Then
               cln = 1
           Else
                cln = ws.VPageBreaks(v).Location.Column
           End If
           If h = 0 Then
               rw = 1
           Else
               rw = ws.HPageBreaks(h).Location.Row
           End If
           Set pag(c) = ws.Range(ws.Cells(rw, cln).Address & ":"& ws.Cells(hgth, wth).Address)
            s= s & pag(c).Address & vbLf
           If colorcode Then pag(c).Interior.Color = RGB(CInt(250 * Rnd), CInt(250* Rnd), CInt(250 * Rnd))
            c= c   1
        Next h
    Next v
    MsgBox s
End Sub

示例运行代码后的结果如下图2所示。

图2

注：本程序整理自www.mrexcel.com，供大家学习参考。

com 程序 工作

0 人点赞