VBA引用动态名称工作表单元格到公式时出现#NAME?错误求助

首先，你的问题出在VBA变量没有正确插入到公式字符串中——你在公式里直接写了CellAddress，Excel会把它当成一个单元格名称或者命名区域，而不是你VBA代码里存储的Sheet1!$B$1这个值，这就是为什么会出现#NAME?错误。

修正后的代码 #

Sub GetCellAddress()
    Dim Cell As Range
    Dim CellAddress As String
    
    Set Cell = ActiveWorkbook.Worksheets(1).Cells(1, 2)
    ' 处理工作表名称带空格的情况，自动添加单引号避免报错
    If InStr(Cell.Parent.Name, " ") > 0 Then
        CellAddress = "'" & Cell.Parent.Name & "'!" & Cell.Address(External:=False)
    Else
        CellAddress = Cell.Parent.Name & "!" & Cell.Address(External:=False)
    End If
    
    With ActiveSheet
        ' 把VBA变量CellAddress的实际值拼接进公式字符串，而非直接写变量名
        .Range("B3").Formula = _
            "=CONCATENATE(""Balance Sheet"","" - "",MID(" & CellAddress & ",FIND("" "", " & CellAddress & ",FIND("",""," & CellAddress & ")+2)+1,256))"
    End With
End Sub

关键改动说明 #

• 变量值的正确拼接：原来的公式里MID(CellAddress,...)是把CellAddress作为文本传递给Excel，现在改成MID(" & CellAddress & ",...)，VBA会把变量里的实际单元格引用（比如Sheet1!$B$1）插入到公式中，Excel就能正确识别这个引用了。
• 兼容带空格的工作表名称：如果工作表名称包含空格，直接写Sheet Name!$B$1会触发Excel语法错误，所以我们增加了判断逻辑，自动给带空格的表名加上单引号，变成'Sheet Name'!$B$1，让代码更健壮。

额外优化建议 #

如果你的公式核心逻辑是提取第一个工作表的名称并拼接文本，其实可以不用通过单元格地址间接提取——直接在VBA里拿到工作表名称再拼进公式，会让公式更简洁易读：

Sub GetCellAddress()
    Dim wsName As String
    wsName = ActiveWorkbook.Worksheets(1).Name
    
    ' 处理带空格的表名
    If InStr(wsName, " ") > 0 Then
        wsName = "'" & wsName & "'"
    End If
    
    ActiveSheet.Range("B3").Formula = _
        "=CONCATENATE(""Balance Sheet"","" - "", """ & wsName & """)"
End Sub

最终生成的公式会是=CONCATENATE("Balance Sheet"," - ","Sheet1")（或带单引号的版本），和你原逻辑效果一致，但代码和公式都更直观。

内容的提问来源于stack exchange，提问作者Martin Hewitt Tayler