自定义函数错误:“表达式无法在计算列中使用" [英] Custom functions error: 'the expression cannot be used in a calculated column'

问题描述

在Access 2010中，我试图在计算列中使用自定义VBA函数.我得到表达式不能在计算列中使用".

In Access 2010 I am trying to use custom VBA functions in a calculated column. I get 'the expression cannot be used in a calculated column'.

这是我的步骤:

1. 启动Access 2010.
2. 创建一个新的数据库"DB".
3. 创建一个带有文本列"Column1"的表"Table1".在Column1中使用"hello"创建测试行.
4. 在创建"功能区上，单击右上角的模块"，这将启动VBA编辑器.
5. 在VBA编辑器的项目"窗口中，有两个项目"ACWZTOOL"和"DB".选择"DB"，然后选择插入"->模块".

6. 编写以下代码:

1. Launch Access 2010.
2. Create a new database "DB".
3. Create a table "Table1" with a text column "Column1". Create a test row with "hello" in Column1.
4. On "Create" ribbon, click "Module" at the upper-right corner, which launches VBA editor.
5. In VBA editor "Project" window, there are two projects "ACWZTOOL" and "DB". Choose "DB" and select "Insert" -> "Module".

6. Write the following code:

Public Function TestFunc() As String
    TestFunc = "test"
End Function

调试"->编译数据库"通过，保存"并关闭VBA编辑器.现在，"Module1"出现在左窗格的模块"选项卡中.

在表1中，创建一个计算列"Column2"，其表达式为"Len([Column1])"，该列可以正常工作，并且测试行中的值为5.

将表达式更改为"TestFunc()"，错误弹出.

我尝试了其他一些内置函数，似乎表达式生成器仅支持基本"函数.例如. "InStrRev()"也无法识别.

"Debug" -> "Compile DB" passed, "Save" and closed VBA editor. Now "Module1" appears in "Modules" tab in the left pane.

In Table1, create a calculated column "Column2", with expression "Len([Column1])", the column works properly with the value in test row being 5.

Change the expression to "TestFunc()", the error pops up.

I tried a few other built-in functions, it seems only the "basic" ones are supported in expression builder. E.g. "InStrRev()" is also not recognized.

推荐答案

根据在本指南中，用户定义的函数在计算所得的列表达式中是不允许的.相关报价在阅读"部分:

According to this guide, user-defined functions are not allowed in calculated column expressions. The relevant quote is in the "Read It" section:

请注意，计算所得的字段不能调用用户定义的函数， 仅内置功能.此外，您必须提供所有参数 对于您调用的方法，即使参数是可选的.

Be aware that calculated fields cannot call user-defined functions, only built-in functions. In addition, you must supply all parameters for methods that you call, even if the parameters are optional.

这篇关于自定义函数错误:“表达式无法在计算列中使用"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！