Error Handler for Out of Memory

[largeselection]

Hello,

Is there a way I can spring a message and exit a routine if Excel receives an out of memory error?

I have a routine which does a lot of calculation and importing data via ODBC and on occasion I will get ODBC error System Resources Exceeded- The fix is pretty easy, I just close Excel and re-open the file and refresh the connection. So I'd like to spring a message that instructs the user to do that only if they get the system resources exceeded error.

Any ideas?

Thanks!

 

[DauntingGecko]

Put an error handler at the start of your code so it knows what to do when it comes across an error.

Once it reaches the error handler, it will then check the error number and will show the message.

To check error numbers go to the Visual Basic help and search for 'Trappable Errors'

Option Explicit
Private Sub exampleCode()
On Error GoTo err

    ' your normal code goes here

Exit Sub

err:
    If err.Number = 7 Then 'check error number
        Select Case MsgBox("The following error has occured:" & vbCr & vbCr & _
                err.Number & " " & err.Description & "." & vbCr & vbCr & _
                "It is advised to close Excel and try again. Do you want to do this?", _
                vbYesNo, "Error")
                
                Case vbYes: Application.Quit
                
                End Select
    End If

End Sub