如何将用户自定义函数（User Defined Function）与Excel公式集成？

你的问题在于Excel无法直接将自定义函数返回的文本当作工作表引用的一部分解析，必须用INDIRECT函数来将文本转换为有效的单元格引用。

修复方法 #

• 保留你原有的自定义函数（注意：如果当前是第一个工作表，ActiveSheet.Index - 1会报错，后面会给出优化版）：

Function PrevSheet()
    Application.Volatile
    PrevSheet = Worksheets(ActiveSheet.Index - 1).Name
End Function

• 在Excel单元格中使用以下公式替代=PrevSheet()!A1：

=INDIRECT(PrevSheet()&"!A1")

INDIRECT函数的作用是将拼接出来的文本（比如Sheet1!A1）转换为Excel能识别的单元格引用。

优化自定义函数（处理边界情况） #

如果当前工作表是第一个，原函数会返回错误，你可以添加判断逻辑，比如返回当前工作表名称或者提示信息：

Function PrevSheet()
    Application.Volatile
    Dim wsIndex As Integer
    wsIndex = ActiveSheet.Index
    ' 判断是否为第一个工作表
    If wsIndex > 1 Then
        PrevSheet = Worksheets(wsIndex - 1).Name
    Else
        ' 可以改为返回当前表名，或者自定义提示
        PrevSheet = ActiveSheet.Name
        ' 或者返回错误提示：PrevSheet = "无前置工作表"
    End If
End Function

注意事项 #

• Application.Volatile确保函数在工作表激活状态变化时重新计算，这部分是必要的，因为前置工作表会随当前激活表变化。
• 如果工作表名称包含空格或特殊字符，需要给名称加单引号，比如'Sheet Name'!A1，此时公式要改为：

=INDIRECT("'"&PrevSheet()&"'!A1")

这样能兼容所有合法的工作表名称。

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