VBA 出错退出调用函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23550468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA On Error Exit Calling Function
提问by mack
I have a simple excel function that connects to a db and retrieves some data and populates a worksheet. The function calls another function to make the db connection and return the query results. How do I exit the calling function if there is an error connecting to the database? This is what my function looks like that connects to the DB. If there is an error connecting to the DB, the messagebox is displayed but then processing resumes in the calling function and then I get an ugly "END or DEBUG" message box...which I am trying to avoid.
我有一个简单的 excel 函数，它连接到一个数据库并检索一些数据并填充一个工作表。该函数调用另一个函数来建立数据库连接并返回查询结果。如果连接到数据库时出现错误，如何退出调用函数？这就是我的函数连接到数据库的样子。如果连接到数据库时出现错误，则会显示消息框，但随后会在调用函数中恢复处理，然后我会收到一个丑陋的“END 或 DEBUG”消息框……这是我试图避免的。
Public Function QueryDB(sQuery As String)
On Error GoTo ErrorHandler
... Connect to database and get data
ErrorHandler:
... Display a messagebox telling the user there is an error
'Resume Next
Exit Function
End Function
回答by Ripster
Public Function QueryDB(sQuery As String)
On Error GoTo ErrorHandler
'... Connect to database and get data
' Exit function before the error handler so
' it doesn't get processed every run
Exit Function
ErrorHandler:
' ... Display a messagebox telling the user there is an error
MsgBox "Oops! An error occurred."
End Function
You may want to handle your errors in the calling sub though since errors "bubble up" to the caller. This will cause your macro to debug even though you have an error handler in QueryDB.
您可能希望在调用子程序中处理您的错误，因为错误会“冒泡”给调用者。即使您在 QueryDB 中有错误处理程序，这也会导致您的宏进行调试。
Here is an example of how you can handle errors in called functions
以下是如何处理被调用函数中的错误的示例
Sub Main()
On Error GoTo DBERROR
QueryDB ("Query String")
On Error GoTo 0
Exit Sub
DBERROR:
MsgBox "Oops! Error " & Err.Number & " occurred in " & Err.Source & ".", _
Title:="Error " & Err.Number
End Sub
Public Function QueryDB(sQuery As String)
Err.Raise 5000, "QueryDB", "Error connecting to DB"
End Function
回答by user3424922
Use "exit sub" command wherever necessary. as you did n't shared your subroutine/function, I cannot point out where it needs to be given. add "exit sub" according to your logic.
必要时使用“ exit sub”命令。由于您没有分享您的子程序/功能，我无法指出需要在哪里提供。根据您的逻辑添加“ exit sub”。
Regards,
问候，
Dominic
多米尼克
回答by deepspeed
It can be done using Global level variable as below:
可以使用全局级别变量来完成，如下所示：
Public dbError as Integer
Function ConnectToDb as Integer
On Error GoTo err_Connection
'Asume connection to DB failure
err_Connection:
MsgBox "Connection failed to database !! ", vbOKOnly + vbExclamation
dbError = -500 'Any number
Exit Function
End Function
Sub MainSub
'Call function
ConnectToDb
If dbError = -500 Then
Exit Sub
End If
End Sub
