Excel ran out of resources while attempting to calculate one or more formulas

[All2Cheesy]

Hi all,

Apologies for the long post. I maintain a relatively large workbook (5.91mb) that's used by the sales team within the company I work in. The company has expanded, and as such, I've had to add in calculations to the workbook, and ever since, the sales team have been receiving the following error message:

Excel ran out of resources while attempting to calculate one or more formulas

I've done a bit of troubleshooting to try and resolve this error, including:
* Cleaning up the data and macros within the workbook
* Rebuilding the workbook from the ground up
* Consolidating and removing pages
* Set excel to manually use less processes
* Removed all conditional formatting
* Removed the newly added section (While the above changes were applied)

However, none of this has resolved the issue.
I've restored the previous version of the workbook (Which is roughly 1.5mb larger than the new workbook after the above changes) and the issue does not occur.

I'm at a loss here, are there any other ways to get around this issue?

Unfortunately, this workbook contains sensitive information and cannot be posted.

 

[hiker95]

Unfortunately, this workbook contains sensitive information and cannot be posted.

All2Cheesy,

Can we see all of your macro code?

When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]

 

[All2Cheesy]

That, I can do.

There's a lot of macros, so I've saved them into a blank excel doc. You'll be able to see them once you access VBA.

For you reference, the macros which were added after the update which caused the error message to appear are:
Sub CustomerQuote_Townsville() (Located in module: Sub_CustQuote_Branch)
Sub Summary_Townsville() (Located in module: Sub_Summary_Branch

I'm not sure how much help this will be without the accompanying workbook, but I appreciate the effort anyway. Thanks.

https://www.dropbox.com/s/vvgx0ratof16uat/Macros.xlsm?dl=0

 

[hiker95]

All2Cheesy,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.


And, can you then post all of your macro code in your next reply?

Then post your original VBA code, below, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]

 

[All2Cheesy]

Would it help to save it as a xlsb extension? I have 63 subs, and 2 functions, so I'd rather avoid posting it all here at once.

The workbook I posted is blank, and just hosts my macros. I don't see how posting a blank workbook without macros would be helpful?

 

[hiker95]

I maintain a relatively large workbook (5.91mb)

Unfortunately, this workbook contains sensitive information and cannot be posted.

All2Cheesy,

Based on the above two quotes, I will not be able to help you.


Please see Rule #12 at the next link on how to continue with your thread.

https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[StephenCrump]

I have 63 subs, and 2 functions, so I'd rather avoid posting it all here at once.

I agree, way too much code to post!

The two Subs you identified (below) look pretty innocuous, and are virtually identical to many others, so they are unlikely to be the problem.

If you can't post your workbook (or a de-sensitised version?) we're not going to be able to replicate the problem.

So perhaps you can narrow it down ...

Which Sub is causing the problem? Can you put some break points into your code (and any called Subs and Functions) to identify exactly where Excel runs out of resources.

Just by the by, there's a few ways you can reduce your code and make it more efficient. The easiest is not to use Select, e.g.

Range("E6:K6,E8:K8").ClearContents
'rather than
Range("E6:K6,E8:K8").Select
Selection.ClearContents

Also by the by, you have sprinkled through your code:

On Error Resume Next

Occasionally it may be appropriate to wrap a bit of code like this:

On Error Resume Next
'Code snippet where an error may occur
On Error GoTo 0

e.g. where you test for the error to determine which of two paths you take subsequently.

But using On Error Resume Next the way you have is potentially dangerous. It means that your code may error anywhere and produce unexpected results with no indication to the user that there is any problem.

Sub CustomerQuote_Townsville()
    
'Disable additional features
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    
'Show Newcastle
    Range("A5:M1098").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("BC3:BD5"), Unique:=False
    
'Enable additional feautres
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
    End With
End Sub
Sub Summary_Townsville()
' Townsville Macro
    
'Disable additional features
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    
'Show Townsville
    Range("A16:R531").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("M2:M3"), Unique:=False
    
'Enable additional feautres
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
    End With
End Sub

 

[All2Cheesy]

Thanks Stephen.

I've spent some time re-building every aspect of the workbook and testing it along each step, and I believe I've found the root of the issue.

It looks like it was due to a small check I put in place to determine whether certain lines get an additional charged placed on them. It was so small, I've been completely overlooking it this whole time.

Removing that check speeds up the workbook, however, I can't confirm whether the issue is resolved yet as the issue only happens on other users PCs. I'll keep you posted.

 

[All2Cheesy]

Error appears to be resolved.

It wasn't quite what I thought.

I was running an Index-Match statement that referred to entire columns instead of the columns I needed. The code was hidden away pretty well, but a bit of a rookie error on my part. Thanks for all the assistance guys.

 

[StephenCrump]

Great, I'm glad you found the problem.

The code was hidden away pretty well ...

Yes, if it was code, rather than Excel formula, it wasn't in the blank workbook you posted .