Excel VBA 如何冻结窗格而不进行选择

Question

我在Excel中有一段VBA脚本可以冻结Excel工作表的窗格，但我想知道是否可以在不首先选择范围的情况下实现。以下是我的当前代码，它可以冻结1-7行，但使用了Range.Select：

ActiveSheet.Range("A8").Select
ActiveWindow.FreezePanes = True

有什么建议吗？

- ChrisB

Answer 1

使用“查看”菜单下的“冻结窗格”功能，将顶部行冻结。点击.FreezePanes查看效果。

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

因此，无论ActiveCell property是什么，修改.SplitColumn和/或.SplitRow属性应该就可以了。

- user4039065

---

做得不错！对于其他人，请注意SplitColumn / SplitRow值代表拆分上方/左侧的最后一个单元格。因此，要冻结第1行到第7行并将它们与第8行及以后分开，代码应如下所示：With ActiveWindow .SplitColumn = 0 .SplitRow = 7 End With ActiveWindow.FreezePanes = True - ChrisB

我认为在 With ActiveWindow 块的第一行使用类似 If .FreezePanes Then .FreezePanes = False 的代码可能是适当的。 - user4039065

如果您通过Application.Windows("[window name]")访问Window对象，可以避免使用ActiveWindow。其中[window name]是工作簿名称。请注意不要改变原来的意思，使内容更加通俗易懂，不提供解释或其他信息。 - Loophole

@Loophole索引Application.Windows()的工作簿名称是错误的，会导致Run-time error '9' Subscript out of range错误，请参见https://stackoverflow.com/a/47177498/1026。 - Nickolay

代码会导致错误，当您尝试冻结并且您已经激活了一个远离您意图冻结的单元格时。我打算冻结第一列和若干行，行被正确地冻结，但是对于列，第一个可见列被冻结，这是任意的。如果我手动取消冻结，则保持为分割屏幕。我尝试在代码之前添加激活，但没有效果（可能是因为我关闭了屏幕更新以加快宏速度）...然后我尝试了z32a7ul发布的解决方案，那个解决方案起作用了。我想区别在于添加滚动。 - blablubbb

Answer 2

冻结窗格是有很多易错点的。我会添加自己的答案，这样下次我就能在这里找到它，而不必重新发明。

Public Sub FreezePanesAt(rngDataTopLeft As Range)
    Dim wndCurrent As Window
    
    For Each wndCurrent In rngDataTopLeft.Worksheet.Parent.Windows
        With wndCurrent
            .FreezePanes = False
            If Not ((rngDataTopLeft.Row = 1) And (rngDataTopLeft.Column = 1)) Then
                .ScrollRow = 1
                .ScrollColumn = 1
                .SplitRow = rngDataTopLeft.Row - 1
                .SplitColumn = rngDataTopLeft.Column - 1
                .FreezePanes = True
            End If
        End With
    Next
End Sub

使用示例：

FreezePanesAt ThisWorkbook.Worksheets("Sheet1").Range("B3")
FreezePanesAt ThisWorkbook.Names("Header").RefersToRange

输入参数为右下角窗格的左上角单元格；我认为这是最常见的用例：你知道要分割的范围，不关心它在哪个工作簿/工作表/窗口中

如果输入参数位于第一行/第一个单元格，但不是A1，则只会有两个窗格；然而，A1是一个特殊情况，Excel会将窗口分割在当前视图的中心，但我阻止了这种情况，因为我无法想象任何情况下需要这样做

它迭代遍历附加到工作簿/工作表的所有窗口；索引到Application.Windows（Windows(Thisworkbook.Name)）不会导致错误，如果您有更多窗口指向同一工作簿（名称将是"MyWorkbook:1"），或者Excel尝试（通常失败）在崩溃后修复工作簿（名称将是"MyWorkbook [Repaired]"）

它考虑到窗格可能已经被冻结，用户/另一个宏可能已经滚动到工作簿中的某个位置，并且窗口中的左上角单元格不是A1

- z32a7ul

Answer 3

我发现之前的答案只在循环浏览标签时对某些工作表有效。我发现以下代码适用于我循环遍历的每个标签（目标是单个工作簿），无论哪个工作簿是活动工作簿。

简而言之：

With Application.Windows(DataWKB.Name) 
    Application.Goto ws.Cells(4, 5)
    .SplitColumn = 4
    .SplitRow = 3
    .FreezePanes = True
End With

以下是我Sub中的代码（请注意，我在这个子程序中还进行了很多格式化处理，我尝试去掉它们并只留下需要的代码）：

Sub Format_Final_Report()
Dim DataWKB As Workbook
Set DataWKB = Workbooks("Report.xlsx")
Dim ws As Worksheet

Dim tabCNT As Long
Dim tabName As String
tabCNT = DataWKB.Sheets.Count

For i = 1 To tabCNT
    Set ws = DataWKB.Worksheets(i)
    tabName = ws.Name


    With Application.Windows(DataWKB.Name)
        Application.Goto ws.Cells(4, 5)
        .SplitColumn = 4
        .SplitRow = 3
        .FreezePanes = True
    End With

Next i

End Sub

希望这篇文章能够帮助未来的读者节省一些研究时间。

- Jon Dee

Answer 4

我需要能够正确地冻结窗格（尤其是在创建新窗口时），而不会丢失活动单元格或混乱可见范围。经过多次尝试，我想我有一个可靠的解决方案：

Sub FreezePanes(nbLignes As Integer, nbColonnes As Integer, Optional ByVal feuille As Worksheet)
    If feuille Is Nothing Then Set feuille = ActiveSheet Else feuille.Activate
    Error GoTo erreur
    With ActiveWindow
        If .View = xlNormalView Then
            If .FreezePanes Then .FreezePanes = False
            If .Split Then .Split = False

            .SplitColumn = nbColonnes
            .SplitRow = nbLignes

            If .Panes.Count = 4 Then 'rows and columns frozen
                .Panes(1).ScrollRow = 1
                .Panes(1).ScrollColumn = 1
                .Panes(2).ScrollRow = 1 'top right pane
                .Panes(3).ScrollColumn = 1 'bottom left pane
            ElseIf nbLignes > 0 Then .Panes(1).ScrollRow = 1
            ElseIf nbColonnes > 0 Then .Panes(1).ScrollColumn = 1
            Else: GoTo erreur
            End If

            .FreezePanes = True
        End If
    End With
    Exit Sub
erreur:
    Debug.print "Erreur en exécutant le sub 'FreezePanes " & nbLignes & ", " & nbColonnes & ", '" & feuille.Name & "' : code #" & Err.Number & Err.Description
End Sub

- CETAB

Answer 5

我知道这已经过时了，但我发现这个小提示可能会有用......正如ChrisB所说，SplitColumn / SplitRow值表示分割线上方/左侧的最后一个单元格，但是当前可见窗口的。因此，如果您恰好拥有像这样的代码：

Application.Goto Worksheets(2).Range("A101"), True
With ActiveWindow
 .SplitColumn = 0
 .SplitRow = 10
 .FreezePanes = True
End With

分割线将在第110行和111行之间，而不是10行和11行。

为了澄清并添加更多信息进行编辑：
我的意思是这些值是左上角单元格的偏移量，而不是单元格的地址。因此，ChrisB在主要答案下的12月4日18:34的评论仅在第1行在ActiveWindow中可见时才有效。

对此还有几点需要注意：

1. 使用Application.goto并不一定会将你要去的单元格放在左上角
2. 当使用.goto时，放在左上角的单元格可能取决于Excel窗口的大小、当前缩放级别等（因此相当任意）
3. 可以将分割线放置在你看不到它们甚至无法在可见窗口中滚动（如果.FreezePanes = true）。例如：

Application.Goto Worksheets(1).Range("A1"), True  
With ActiveWindow  
 .SplitColumn = 100  
 .SplitRow = 100  
 .FreezePanes = True  
End With  

CETAB可能在他们的回答中处理这个问题。

- TaitK

---

似乎没有意义。即使参考的窗口未处于活动状态，它似乎也能正常工作。 - Tuntable

Answer 6

如果您可见的窗口不包括单元格A1，则ActiveWindow.ScrollRow = 1和ActivWindow.ScrollColumn = 1对于FreezePanes是必须的。

如果您选择行4或单元格A4来冻结1:3行，并且单元格A3不可见，则FreezePanes函数将在可见窗口中心冻结窗口。

同样，如果选择了B4单元格并且列A不可见，则只有1:3行会被冻结（列A不会被冻结）。 同样，如果1:3行不可见，则只有A列会被冻结。 如果列A和1:3行都不可见，则FreezePanes函数将在可见窗口中心冻结窗口。

- DrJohn

Answer 7

问题在于拆分后，如果用户取消冻结窗格，则窗格仍然会被拆分。(我找不到一种方法可以在保持窗格冻结的同时关闭拆分)

这可能太显而易见/简单了，但是如果当前选择仅仅被保存，然后在之后重新选择呢？

Sub FreezeTopRow()
    
    'First save the current selection to go back to it later
    Dim rngOriginalSelection As Range
    Set rngOriginalSelection = Selection
    
    'Change selection to A2 to make .FreezePanes work
    ActiveSheet.Range("A2").Select
    ActiveWindow.FreezePanes = True

    'Change selection back to original
    rngOriginalSelection.Select

End Sub

- SAL

---

是的，这种技巧是最可靠的，副作用最少。 （请参见我上面对user4039065帖子的评论。）但在选择单元格A2之前，我还会设置ActiveWindow.FreezePanes = False，以防止冻结窗格已经设置为其他状态，这将防止此代码产生所需的效果。 - pstraton

另外要注意：不要使用 Range("1:1").Select 或 Rows(1).Select 这样的代码，虽然很诱人，但实际上会创建一个四窗格冻结窗格状态（似乎没有任何用处，可能是 Excel 的一个旧 bug）。相反，应该使用 Range("A2").Select。 - pstraton

Answer 8

这是我使用的...

Public Sub FreezeTopRowPane(ByRef MyWs As Excel.Worksheet, _
                            Optional ByVal AfterRowNr As Integer = 1)

Dim SavedWS As Worksheet
Dim SavedUpdating As Boolean

SavedUpdating = Application.ScreenUpdating      'save current screen updating mode

Set SavedWS = ActiveSheet                       'save current active sheet

Application.ScreenUpdating = False              'turn off screen updating
MyWs.Activate                                   'activate worksheet for panes freezing
ActiveWindow.FreezePanes = False                'turn off freeze panes in case 
With ActiveWindow
    .SplitColumn = 0                            'set no column to split
    .SplitRow = AfterRowNr                      'set the row to split, default = row 1
End With
ActiveWindow.FreezePanes = True                 'trigger the new pane freezing

SavedWS.Activate                                'restore previous (saved) ws as active

Application.ScreenUpdating = SavedUpdating      'restore previous (saved) updating mode

End Sub

- Keith

Answer 9

我使用 .Select 和 .Activate 进行了 Freezing 的时间测试。以下是代码：

Dim numLoops As Long
Dim StartTime, LoopTime As Long
numLoops = 1000


Debug.Print ("Timing test of numloops:" & numLoops)

StartTime = Timer

For I = 0 To numLoops
        targetSheet.Activate
    With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
        .SplitColumn = 2
        .SplitRow = 1
        .FreezePanes = True
    End With

Next I

LoopTime = Timer
Debug.Print ("Total time of activate method:" & Format((LoopTime - StartTime) / 86400, "hh:mm:ss"))
StartTime = Timer

For I = 0 To numLoops
        targetSheet.Select
        Application.Range("C2").Select
        Application.ActiveWindow.FreezePanes = True
Next I

LoopTime = Timer
Debug.Print ("Total time of select method:" & Format((LoopTime - StartTime) / 86400, "hh:mm:ss"))

这是结果。

Timing test of numloops:1000 
Total time of activate method:00:00:39 
Total time of select method:00:00:01

正如您所看到的，.Select 更快。

- john k

---

为了更准确地比较，应该在不同的工作表上冻结窗格，而不是在同一工作表上反复执行此操作。任何实际使用冻结窗格多次的情况都涉及到不同的工作表。（我能想到一个实际的例子：为不同用户生成大量报告。）此外，您的 Select 方法没有先取消冻结窗格，因此，即使在工作表的不同位置已经冻结了窗格，它也不会做任何事情。我怀疑之所以时间如此之快，是因为循环2-1000没有完成任何操作，但我很好奇是否真的如此。 - ChrisB

Answer 10

我正在使用“不选中代码的Excel VBA冻结窗格”。

我唯一做出的更改是添加了一个额外的参数，以传递一个对象到TheApp作为“应用程序”的一部分，因为在我的测试环境中未定义“应用程序”。

Public Sub FreezeTopRowPane(ByRef MyWs As Excel.Worksheet, _
            ByRef TheApp As Object, _
            Optional ByVal AfterRowNr As Integer = 1)

- Edward Z.