批量向多工作表单元格插入远程工作表名称的VBA问题

问题根源 #

• 公式字符串语法错误：你将VBA变量直接嵌入字符串常量中，Excel无法解析变量值，只会将其当作普通文本；同时原代码的引号配对混乱，生成的公式无效，导致循环异常卡顿。
• 冗余的工作表激活操作：频繁激活、选择工作表会拖慢宏的执行速度，甚至触发Excel的界面刷新逻辑引发异常。

修正后的代码 #

Option Explicit
Sub insertRowsSheetsWorkbooks()

    ' 禁用Excel属性提升执行效率
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    ' 声明变量
    Dim ws As Worksheet, wb As Workbook
    Dim startWorkbook As String, startSheet As String
       
    ' 保存初始工作簿和工作表
    startWorkbook = ThisWorkbook.Name
    startSheet = ThisWorkbook.ActiveSheet.Name
                        
    ' 遍历所有打开的工作簿
    For Each wb In Application.Workbooks
        ' 遍历当前工作簿的所有工作表
        For Each ws In wb.Sheets
            ' 处理含空格/特殊字符的工作表名，用单引号包裹
            Dim formattedSheetName As String
            formattedSheetName = ws.Name
            If InStr(formattedSheetName, " ") > 0 Or InStr(formattedSheetName, "!") > 0 Then
                formattedSheetName = Chr(39) & formattedSheetName & Chr(39)
            End If
            
            ' 正确拼接外部引用公式，使用Formula属性确保Excel识别为公式
            ws.Range("I7").Formula = "='Z:\...\[Another_workbook.xlsx]" & formattedSheetName & "'!$D$87"
        Next ws
    Next wb
    
    ' 恢复初始工作簿和工作表
    Workbooks(startWorkbook).Worksheets(startSheet).Activate
    Range("A1").Select
        
    ' 恢复Excel默认属性
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

关键修改说明 #

• 变量正确嵌入公式：用&连接字符串和ws.Name变量，让每个工作表的公式引用对应自身的表名。
• 特殊表名处理：如果工作表名包含空格、感叹号等特殊字符，必须用单引号包裹，否则公式会报错。
• 使用Formula属性：设置公式时用Formula而非Value，确保Excel将内容识别为公式而非普通文本。
• 移除激活/选择操作：直接通过工作表对象操作单元格，避免不必要的界面交互，提升效率并减少异常。

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