Excel VBA实现同步滚动遇运行时错误求助，排序对比功能正常

问题描述 #

我正在编写VBA脚本实现以下流程：对两个工作表按名称排序→对比内容标记差异单元格→按最新日期重新排序→并排显示并启用同步滚动。但在启用同步滚动时遇到报错：

• 尝试Application.CommandBars.ExecuteMso ("WindowSideBySideSynchronousScrolling")，报错运行时错误'5'：无效的过程调用或参数
• 尝试ActiveWindow.SyncedScrolling = True，报错运行时错误'438'：对象不支持该属性或方法

注意到手动操作需先开启「并排查看」才能激活同步滚动按钮，因此尝试先启用并排查看，但使用ActiveWindow.ViewSideBySideWith = Windows(2)或Application.CommandBars.ExecuteMso ("ViewSideBySide")仍未解决问题。除同步滚动外，其余代码功能正常，完整代码见下方。

原完整代码 #

Sub SortCompareSortByDateAndViewSideBySide()
    ' Sort sheet "New" by column 1 A-Z
    With Sheets("New")
        .Range("A1").CurrentRegion.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
    End With
    
    ' Sort sheet "New (2)" by column 1 A-Z
    With Sheets("New (2)")
        .Range("A1").CurrentRegion.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
    End With
    
    ' Compare column 4 of sheet "New" and "New (2)"
    Dim wsNew As Worksheet
    Dim wsNew2 As Worksheet
    Dim lastRowNew As Long
    Dim lastRowNew2 As Long
    Dim i As Long
    
    Set wsNew = Sheets("New")
    Set wsNew2 = Sheets("New (2)")
    
    lastRowNew = wsNew.Cells(wsNew.Rows.Count, 4).End(xlUp).Row
    lastRowNew2 = wsNew2.Cells(wsNew2.Rows.Count, 4).End(xlUp).Row
    
    ' Ensure both sheets have the same number of rows in column 4 for comparison
    If lastRowNew <> lastRowNew2 Then
        MsgBox "The number of rows in column 4 of both sheets is not the same."
        Exit Sub
    End If
    
    For i = 2 To lastRowNew ' Assuming there is a header row
        If wsNew.Cells(i, 4).Value = wsNew2.Cells(i, 4).Value Then
            wsNew.Rows(i).Interior.Color = RGB(0, 255, 0) ' Green
            wsNew2.Rows(i).Interior.Color = RGB(0, 255, 0) ' Green
        Else
            wsNew.Rows(i).Interior.Color = RGB(255, 165, 0) ' Orange
            wsNew2.Rows(i).Interior.Color = RGB(255, 165, 0) ' Orange
        End If
    Next i
    
    ' Sort sheet "New" by column 5 (Date) newest to oldest
    With wsNew
        .Range("A1").CurrentRegion.Sort Key1:=.Range("E1"), Order1:=xlDescending, Header:=xlYes
    End With
    
    ' Sort sheet "New (2)" by column 5 (Date) newest to oldest
    With wsNew2
        .Range("A1").CurrentRegion.Sort Key1:=.Range("E1"), Order1:=xlDescending, Header:=xlYes
    End With
    
    ' Change the window view to split side by side vertically
    If Windows.Count = 1 Then
        ActiveWindow.NewWindow
    End If
    
    ' Arrange windows vertically
    Windows.Arrange ArrangeStyle:=xlVertical
    
    ' Ensure sheet "New" is open in the first window and sheet "New (2)" is open in the second window
    Windows(1).Activate
    Sheets("New").Activate
    Windows(2).Activate
    Sheets("New (2)").Activate
    
    ' Enable view side by side and synchronized scrolling
    Application.CommandBars.ExecuteMso ("ViewSideBySide")
    Application.CommandBars.ExecuteMso ("WindowViewSideBySideSynchronousScrolling")
End Sub

解决方案 #

问题根源在于两个细节：

1. ExecuteMso命令ID错误：同步滚动的正确命令ID是WindowSideBySideSynchronousScrolling，而非WindowViewSideBySideSynchronousScrolling。
2. 并排查看的状态冲突：原代码中Windows.Arrange ArrangeStyle:=xlVertical会破坏并排查看的绑定状态，导致同步滚动命令失效；同时直接调用ViewSideBySide未明确绑定窗口，容易出现状态异常。

修改后的关键代码段 #

替换原代码中从' Arrange windows vertically到末尾的部分为以下代码：

' Change the window view to split side by side vertically
If Windows.Count = 1 Then
    ActiveWindow.NewWindow
End If

' Ensure sheet "New" is open in the first window and sheet "New (2)" is open in the second window
Windows(1).Activate
Sheets("New").Activate
Windows(2).Activate
Sheets("New (2)").Activate

' 绑定两个窗口为并排查看状态
If Not Windows(1).ViewSideBySideWith Is Nothing Then
    Windows(1).ViewSideBySideWith = Nothing
End If
Windows(1).ViewSideBySideWith Windows(2)

' 启用同步滚动
Application.CommandBars.ExecuteMso "WindowSideBySideSynchronousScrolling"

核心说明 #

• 移除Windows.Arrange ArrangeStyle:=xlVertical：该命令会将窗口平铺，覆盖并排查看的特殊关联状态，导致同步滚动无法生效。
• 明确绑定并排窗口：通过Windows(1).ViewSideBySideWith Windows(2)指定要并排的两个窗口，避免状态混乱。
• 修正ExecuteMso命令ID：使用正确的WindowSideBySideSynchronousScrolling，且调用时无需包裹括号（标准VBA写法）。

内容的提问来源于stack exchange，提问作者John Ouzts