VBA On Error Exit Calling Function

Question

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.

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

This was asked earlier today - stackoverflow.com/q/23544530/1048539 — enderland
– enderland, Commented May 8, 2014 at 21:56

Ripster · Accepted Answer · 2014-05-08 19:29:22Z

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.

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

deepspeed · Accepted Answer · 2016-12-12 06:53:21Z

1

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

edited Dec 12, 2016 at 6:53

answered Nov 28, 2016 at 13:22

deepspeed

112 bronze badges

Comments

user3424922 · Accepted Answer · 2014-05-08 19:15:46Z

0

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.

Regards,

Dominic

answered May 8, 2014 at 19:15

user3424922

321 silver badge5 bronze badges

Collectives™ on Stack Overflow

VBA On Error Exit Calling Function

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related