MS Excel VBA:每个打印页面周围的边框 [英] MS Excel VBA: Border Around Each Printed Page

问题描述

我在下面获得了VBA代码，以在excel(MS Excel 2013)工作表的打印页面周围添加边框，但是得到的错误代码如下:运行时错误'1004':应用程序定义的错误或对象定义的错误".调试突出显示了块引号中的行.我该如何解决?

I got the VBA code below to add borders around the printed pages of an excel (MS Excel 2013) worksheet but I got error codes as follows: "Runtime Error '1004': Application-defined or object-defined error". The debug highlighted the lines in block quotes. How do I resolve this please?

Sub Create_Borders_Around_Pages()
    Dim rngBorder As Range
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim lngHPBreak As Long
    Dim lngVPBreak As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim rngAC As Range

    With ActiveSheet
        Set rngAC = ActiveCell
        lngLastRow = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Row
        lngLastCol = .UsedRange.Cells(1, .UsedRange.Columns.Count).Offset(1, 0).Column
        .Cells(lngLastRow + 1, 1).Activate

        lngRow = 1
        For lngVPBreak = 1 To .VPageBreaks.Count
            lngCol = 1
            For lngHPBreak = 1 To .HPageBreaks.Count
                Set rngBorder = .Range(.Cells(lngRow, lngCol), _
                .Cells(.HPageBreaks(lngHPBreak).Location.Row - 1, .VPageBreaks(lngVPBreak).Location.Column - 1))
                rngBorder.BorderAround xlContinuous, xlThick
                lngRow = .HPageBreaks(lngHPBreak).Location.Row
            Next

            Set rngBorder = .Range(.Cells(lngRow, lngCol), .Cells(lngLastRow, .VPageBreaks(lngVPBreak).Location.Column - 1))
            rngBorder.BorderAround xlContinuous, xlThick

            lngCol = .VPageBreaks(lngVPBreak).Location.Column
        Next
        lngRow = 1
        For lngHPBreak = 1 To .HPageBreaks.Count

设置rngBorder = .Range(.Cells(lngRow，lngCol)，_.Cells(.HPageBreaks(lngHPBreak).Location.Row-1，lngLastCol))

Set rngBorder = .Range(.Cells(lngRow, lngCol), _ .Cells(.HPageBreaks(lngHPBreak).Location.Row - 1, lngLastCol))

            rngBorder.BorderAround xlContinuous, xlThick
            lngRow = .HPageBreaks(lngHPBreak).Location.Row
        Next
        Set rngBorder = .Range(.Cells(lngRow, lngCol), .Cells(lngLastRow, lngLastCol))
        rngBorder.BorderAround xlContinuous, xlThick
        rngAC.Activate
    End With
End Sub

推荐答案

除非您的工作表中没有 VPageBreaks ，否则代码才能正常工作.

Code works fine unless there are no VPageBreaks in your sheet.

仅在 For lngVPBreak = 1 To .VPageBreaks.Count 循环中设置变量 lngCol ，因此，如果没有 vPageBreaks 它没有被设置.

The variable lngCol is only being set within the For lngVPBreak = 1 To .VPageBreaks.Count loop, so if there are no vPageBreaks it doesn't get set.

因此，您的 Set rngBorder 行在语句的 .Cells(lngRow，lngCol)部分中的列值为0时失败.

As a result your Set rngBorder line is failing with a column value of 0 in the .Cells(lngRow, lngCol) part of the statement.

建议您在此循环之外某个地方设置 lngCol = 1 吗?

Suggest you set lngCol = 1 outside of this loop somewhere?

这篇关于MS Excel VBA:每个打印页面周围的边框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！