Error Handler for Out of Memory

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DauntingGecko

Well-known Member
Joined
Feb 2, 2006
Messages
521
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' :)

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top