Excel 中自定义函数的限制

写这篇文章的起因是因为我在Excel 2003 中用VBA 写了个自定义的函数。

Function Test() As Integer
On Error GoTo Test_error
Range("A1:B10") = "MyString"
Test = 1
Test_exit:
    Exit Function
Test_error:
    MsgBox Err.Description
    Test = 0
    GoTo Test_exit
End Function

然后我在工作表中任选了一个单元格，在里面输入“=Test() ”，就收到错误信息“Application-defined or object-defined error”，并且A1:B10 这块单元格也没有改成我设置的值。语法没有任何错误，不明白为什么会报错。

在寻找答案的过程中，我发现同样的代码放在例程(Sub)里就没有问题。

Sub Test()
Range("A1:B10") = "MyString"
End Sub

更妖的是，如果我不是在工作表单元格的公式里调用这个函数，而是直接在编辑器里面跑相关代码，也是没有问题的。A1:B10 这块单元格都变成了代码里设置的值。

经过艰苦的搜索，发现微软有篇技术文章是讲这个问题的。Description of limitations of custom functions in Excel

问题的关键是，在工作表单元格的公式中调用函数时，用户定义函数不能执行改变 Excel 环境的操作。

所谓不能改变 Excel 环境，表示函数不能执行以下任何操作：

•	在电子表格中插入、删除单元格或设置单元格格式。
•	更改其它单元格中的值。
•	在工作簿中移动、重命名、删除或添加工作表。
•	更改任何环境选项，例如计算模式或屏幕视图。
•	向工作簿中添加名称。
•	设置属性或执行大多数方法。

除了上面设置单元格的值的代码放在自定义函数里会无效外，类似 Range("A1:B10").ClearFormats， Range("A1:B10").ClearContents 的语句放在函数里也是无效的。

除了用户自定义的函数，Microsoft Excel 中自带的函数也不能更改环境。函数可以读取单元格的值进行计算，并在输入它们的单元格中返回某个值或文本。要改变环境就要使用 Visual Basic 子例程来完成。