错误集但未在VBA表达式服务中使用InStr函数引发 [英] Error set but not raised with InStr function in the VBA Expression Service

问题描述

您好

在复杂的插入查询中，我收到错误5："无效的过程调用或参数"。但是，查询结果是正确的。我无法解释这一点，但经过多次反复试验后，我终于找到了"罪魁祸首"。是InStr函数。如果使用
，则使用两个参数，例如

In a complex insert query I got an error 5: "Invalid procedure call or argument". However, the result of the query was correct. I could not explain this, but after much trial and error I finally found that the "culprit" is the InStr function. If it is used with two arguments, as in

   Instr（SomeField，'x'）

   Instr(SomeField, 'x')

它将将错误Err对象设置为上述错误，但
它不会引发即使错误陷阱选项设置为"中断所有错误"，也会出现错误。代码继续执行！ 由于InStr函数的结果是正确的，因此没有真正的危害。

it will set the error Err object to the above error, but it will not raise the error even if the Error Trapping option is set to "Break on all errors". The code continues to execute! No real harm is done since the result of the InStr function is correct.

分辨率是使用所有四个参数，如

The resolution is to use all four arguments, as in

   Instr（1，SomeField，'x'，0）

   Instr(1, SomeField, 'x', 0)

您可以使用下面的代码对此进行测试（为了简洁，我使用Eval而不是查询或记录集来调用VBA表达式服务） 。  我在Access 2012 32bit中发现了此问题。我还没有在其他版本中测试它。

You can test this with the code below (for shortness I use Eval instead of a query or recordset to invoke the VBA Expression Service).  I have found this issue in Access 2012 32bit. I have not tested it in other versions.

有没有人知道其他这样的情况？还有更多具有可选参数的函数。

Does anyone know about other such situations? There are more functions with optional parameters.

MatthiasKläy

Matthias Kläy

KläyComputingAG

Kläy Computing AG

Public Sub HiddenError()

Dim lngPosOfX As Long

' Normal case in VBA
On Error GoTo 0
lngPosOfX = InStr("aaxa", "x")
Debug.Print "Error " & Err.Number & " - " & Err.Description & " - PosOfX = " & lngPosOfX
' Result: Error 0 - - PosOfX = 3

' Using the VBA Expression Service
' The Result of the InStr function is correct, the code continues, the error is not raised!
On Error GoTo 0
lngPosOfX = Eval("InStr('aaxa', 'x')")
Debug.Print "Error " & Err.Number & " - " & Err.Description & " - PosOfX = " & lngPosOfX
' Result: Error 5 - Invalid procedure call or argument - PosOfX = 3

' The resolution is to use all four parameters of the Instr function
On Error GoTo 0
lngPosOfX = Eval("InStr(1, 'aaxa', 'x', 0)")
Debug.Print "Error " & Err.Number & " - " & Err.Description & " - PosOfX = " & lngPosOfX
' Result: Error 0 - - PosOfX = 3

End Sub

推荐答案

我运行了您的示例程序在Windows 7 Home Premium SP1（64位）上的Access 2010 SP1（32位）中。结果是

I ran your sample procedure in Access 2010 SP1 (32-bit) on Windows 7 Home Premium SP1 (64-bit). The result was

错误0 -   - PosOfX = 3

错误0 -   - PosOfX = 3

错误0 -   - PosOfX = 3

Error 0 -  - PosOfX = 3
Error 0 -  - PosOfX = 3
Error 0 -  - PosOfX = 3

I.e。这三个人都没有提出错误。

I.e. none of the three raised an error.

这篇关于错误集但未在VBA表达式服务中使用InStr函数引发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！