仅单台PC出现Excel宏报错？OneDrive上传及宏运行故障排查求助

我正在修改存储于OneDrive中的带宏Excel文件的条件格式，打开文件时弹出报错（错误提示见下方），通过VBA工具无法定位问题。同事使用该文件时，宏运行及OneDrive上传均无异常。此前我添加了随当前周移动的竖线（该功能可正常运行），但之后文件无法上传，运行宏也会报错，恳请协助排查问题。

错误提示（中文翻译）：运行时错误'9'：下标越界

触发报错的VBA代码 #

Private Sub Workbook_Open()
  Dim WS_Count As Integer
  Dim I, acolumn As Integer
  Dim aRange, acell As Range
  Dim wsName As String
   

  'Set WS_Count equal to the number of worksheets in the active workbook
   WS_Count = ActiveWorkbook.Worksheets.Count

   For I = 1 To WS_Count
     
     wbName = ActiveWorkbook.Worksheets(I).Name
     Worksheets(wbName).Activate
     Set aRange = Range("G9:BF9")
        
     If Range("A1").Value = 1 Then
       acolumn = 7
    
       'Finds the column where to position the line
       For Each acell In aRange
    
         If (acell.Value < Date) Then
             acolumn = acell.Column
         Else
           Exit For
         End If
        Next acell
    '  MsgBox acolumn
        ActiveSheet.Shapes("Line").Left = ActiveSheet.Cells(9, acolumn + 1).Left
      End If
      
    Next I
   wbName = ActiveWorkbook.Worksheets(1).Name
   Worksheets(wbName).Activate
End Sub

排查方向 #

• 形状对象缺失：代码直接调用ActiveSheet.Shapes("Line")，若某张工作表中没有名为"Line"的形状，会直接触发下标越界错误。检查你修改过的工作表，确认是否误删或重命名了该形状。
• 工作表引用不严谨：代码依赖Activate切换工作表后使用ActiveSheet引用，OneDrive同步时可能导致文件状态异常，引发引用错误。建议直接通过工作表对象操作，避免激活，示例调整：

  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
      Set aRange = ws.Range("G9:BF9")
      If ws.Range("A1").Value = 1 Then
          acolumn = 7
          For Each acell In aRange
              If acell.Value < Date Then
                  acolumn = acell.Column
              Else
                  Exit For
              End If
          Next acell
          ' 先判断形状是否存在
          On Error Resume Next
          Dim lineShape As Shape
          Set lineShape = ws.Shapes("Line")
          On Error GoTo 0
          If Not lineShape Is Nothing Then
              lineShape.Left = ws.Cells(9, acolumn + 1).Left
          End If
      End If
  Next ws
  

• OneDrive同步冲突：本地文件可能和云端存在同步延迟或版本冲突，先将文件下载到本地修改测试，确认正常后再重新上传覆盖云端文件。
• 变量未声明隐患：代码中wbName未声明（仅声明了wsName），隐式声明可能导致意外错误，建议在代码开头添加Option Explicit强制变量声明。

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