Custom function error: "expression cannot be used in a computed column"

Question

In Access 2010, I try to use custom VBA functions in a computed column. I get an expression that cannot be used in a calculated column.

Here are my steps:

• Launch Access 2010.
• Create a new DB database.
• Create the table "Table1" with the text column "Column 1". Create a test line with "hello" in column 1.
• In the "Create" ribbon, click "Module" in the upper right corner, which launches the VBA editor.
• There are two projects in the VBA project editor window: "ACWZTOOL" and "DB". Select "DB" and select "Insert" → "Module".

• Write the following code:

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

• "Debugging" → "Compilation of the database" passed, "Save" and closed the VBA editor. Now "Module1" appears on the "Modules" tab in the left pane.
• In table 1, create the calculated column "Column2" with the expression "Len ([Column1])", the column works correctly with the value in test row 5.
• Change the expression to "TestFunc ()", an error will appear.
• I tried several other built-in functions, it seems that only the "basic" ones are supported in the expression constructor. For instance. "InStrRev ()" is also not recognized.

Answer 1

According to this guide , user-defined functions are not allowed in computed column expressions. The corresponding quote is in the "Reading" section:

Remember that computed fields cannot call user-defined functions, only built-in functions. In addition, you must provide all the parameters for the methods you call, even if the parameters are optional.