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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,204
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top