如何在VBA中使用索引而非硬编码名称引用外部Excel工作簿的工作表？

首先得明确：Excel的公式本身不支持直接用索引引用外部工作表，必须使用工作表的名称。所以我们的思路是：在VBA里先通过索引获取目标工作表的名称，再把这个名称拼接到FormulaR1C1的字符串中，这样就能彻底避免硬编码工作表名了。

核心解决步骤 #

1. 获取外部工作簿对象：确保目标工作簿已经打开（如果没打开，也可以用Workbooks.Open方法打开），然后定义变量引用它。
2. 通过索引获取工作表名称：用Sheets(索引)拿到目标工作表，提取它的Name属性。
3. 拼接公式字符串：把获取到的工作表名称插入到公式模板中，注意处理特殊字符（比如工作表名含空格时，公式里需要用单引号包裹）。

具体代码实现 #

第一步：定义变量并获取工作表名称 #

先在代码开头加上这段，一次性获取目标工作表名称，之后所有公式都可以复用这个变量：

' 声明变量
Dim prevMonthWB As Workbook
Dim prevSheetName As String

' 引用已打开的外部工作簿（注意文件名里的两个单引号是转义原文件名的单引号）
Set prevMonthWB = Workbooks("Previous Month''s Public Numbers.xls")

' 通过索引获取工作表名称，这里用1表示第一个工作表，你可以改成实际需要的索引
prevSheetName = prevMonthWB.Sheets(1).Name

第二步：替换硬编码的公式 #

把你原来的硬编码公式，替换成用变量拼接的版本。比如原来的：

ActiveCell.FormulaR1C1 = _ 
"='[Previous Month''s Public Numbers.xls]Previous Month'!R4C4+RC[-4]"

改成：

ActiveCell.FormulaR1C1 = _
"='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R4C4+RC[-4]"

第三步：优化重复代码（避免Select/Activate） #

你的代码里大量使用Select和Activate，这不仅效率低，还容易因为选中区域变化导致错误。我们可以直接对Range对象操作，批量处理重复任务。比如原来的H4、H5、H8-H11等单元格的公式设置，可以改成这样：

With Sheets("Month Raw Data")
    ' 设置H4和H5的公式
    .Range("H4").FormulaR1C1 = _
        "='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R4C8+RC[-4]"
    .Range("H5").FormulaR1C1 = _
        "='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R5C8+RC[-4]"
    
    ' 批量设置H8:H11的公式（利用行号对应关系）
    Dim rng As Range
    For Each rng In .Range("H8:H11")
        rng.FormulaR1C1 = _
            "='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R" & rng.Row & "C8+RC[-4]"
    Next rng
    
    ' 批量粘贴值（H4:H5）
    .Range("H4:H5").Copy
    .Range("H4:H5").PasteSpecial Paste:=xlPasteValues
    
    ' 设置H14、H15、H17的公式
    .Range("H14").FormulaR1C1 = _
        "='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R14C8+RC[-4]"
    .Range("H15").FormulaR1C1 = _
        "='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R15C8+RC[-4]"
    .Range("H17").FormulaR1C1 = _
        "='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R17C8+RC[-4]"
    
    ' 复制格式
    .Range("H8").Copy
    .Range("H4:H5").PasteSpecial Paste:=xlPasteFormats
End With

' 处理"Month % Change"工作表的内容
With Sheets("Month % Change")
    .Range("I29").FormulaR1C1 = _
        "='[Previous Month''s Public Numbers.xls]" & prevSheetName & "'!R[-26]C[8]+RC[-5]"
    .Range("I29").AutoFill Destination:=.Range("I29:I50")
    .Range("I29:I50").Copy
End With

' 清除剪贴板模式
Application.CutCopyMode = False

为什么你之前的写法失败？ #

你尝试的'(1)!是Excel公式不支持的语法——公式只能识别工作表的名称字符串，不能直接用索引。必须通过VBA先把索引转换成名称，再拼到公式里才行。

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