VBA中End(xlUp).Row取值异常及工作表激活问题排查

问题现象 #

在循环逻辑中依次调用function_1、check_sheet_exist、function_3三个函数，其中：

• 循环开头会激活Workbooks(main).Worksheets(mask_1)
• function_1操作正常，不会修改活动工作簿/工作表
• check_sheet_exist中会切换到main工作簿遍历secondary工作簿的工作表，最后再激活main的mask1或mask2工作表
• 问题出在function_3：无论先激活Workbooks(secondary).Sheets(name_sheet)再用Range，还是直接写全路径Workbooks(secondary).Sheets(name_sheet).Range("AQ100").End(xlUp).Row，返回的行号都是main工作簿mask1工作表中的19，而非预期的secondary工作簿对应工作表中的21。

关键代码背景 #

主循环代码 #

For i = 1 To Collection_1.Count
    For j = 1 To 5  
        Workbooks(main).Worksheets(mask_1).Activate
        If Collection_1(i).Length(j) = 5 Then
            Call function_1(name_sheet)
            Call check_sheet_exist(name_sheet)
            Call function_3(name_sheet)
        End If
    Next j
Next i

check_sheet_exist函数代码 #

Windows(main).Activate
For Each check_sheet In Workbooks(secondary).Sheets
    ' 部分业务代码
Next check_sheet
Workbooks(main).Sheets(mask1).Activate ' 根据参数可能激活mask2

function_3问题代码 #

' 写法1：激活后引用Range
Workbooks(secondary).Sheets(name_sheet).Activate
MsgBox Range("AQ100").End(xlUp).Row

' 写法2：全路径引用
MsgBox Workbooks(secondary).Sheets(name_sheet).Range("AQ100").End(xlUp).Row

secondary工作簿创建代码（错误根源） #

Dim secondary As New Workbook ' 函数外部定义

Sub example ()
    Set new_file = Workbooks.Add
    With new_file
        .SaveAs Filename:=secondary ' 错误：Filename需要字符串路径，而非Workbook对象
    End With
    secondary = ActiveWorkbook.Name ' 错误：将Workbook类型变量赋值为字符串，类型冲突
End Sub

核心原因 #

1. 变量类型混乱：一开始将secondary定义为Workbook对象，但后续又把它赋值为工作簿名称的字符串，VBA的隐式类型转换导致后续Workbooks(secondary)的引用逻辑混乱，实际指向的并非目标secondary工作簿，而是错误地指向了main工作簿。
2. SaveAs参数错误：SaveAs的Filename参数要求传入字符串格式的文件路径，而代码中传入了Workbook对象secondary，这本身就是非法操作，会导致工作簿保存逻辑异常，进一步加剧后续引用错误。

解决方法 #

步骤1：修正secondary变量的定义与赋值 #

将secondary定义为字符串类型，用于存储目标工作簿的名称（或完整路径），确保类型一致：

Dim secondary As String ' 改为字符串类型，存储工作簿名称/路径

Sub example ()
    Dim new_file As Workbook
    Set new_file = Workbooks.Add
    ' 定义保存路径，比如指定桌面路径+文件名
    Dim savePath As String
    savePath = Environ("USERPROFILE") & "\Desktop\secondary_workbook.xlsx"
    With new_file
        .SaveAs Filename:=savePath ' 传入正确的字符串路径
    End With
    secondary = new_file.Name ' 直接从new_file对象获取名称，避免依赖ActiveWorkbook
End Sub

步骤2：使用对象变量直接引用工作簿（推荐） #

避免通过名称字符串引用工作簿，直接用Workbook对象变量引用，彻底消除混淆：

' 全局定义Workbook对象
Dim wbSecondary As Workbook

Sub example ()
    Set wbSecondary = Workbooks.Add
    Dim savePath As String
    savePath = Environ("USERPROFILE") & "\Desktop\secondary_workbook.xlsx"
    wbSecondary.SaveAs Filename:=savePath
End Sub

' 后续函数中直接使用对象引用
' 比如check_sheet_exist中
For Each check_sheet In wbSecondary.Sheets
    ' 业务代码
Next check_sheet

' function_3中直接用对象引用
MsgBox wbSecondary.Sheets(name_sheet).Range("AQ100").End(xlUp).Row

步骤3：彻底避免依赖ActiveWorkbook/ActiveSheet #

所有Range、Sheet的操作都通过完整的对象路径引用，不要依赖激活操作，这是VBA编写稳定代码的最佳实践：

' 错误写法：依赖激活
Workbooks(secondary).Sheets(name_sheet).Activate
MsgBox Range("AQ100").End(xlUp).Row

' 正确写法：直接通过对象引用
MsgBox wbSecondary.Sheets(name_sheet).Range("AQ100").End(xlUp).Row

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