VBA Performance / Optimization & Out of Memory Error

[reberryjr]

I was tasked with creating a scorecard for some of the Associates in my company. As of late, I’ve been receiving an “Out of Memory” error. The error is periodic, sometimes occurring when I’m importing data and other times it just appears. My laptop only has 16 GB of RAM which may be the issue. However, I would like to make this tool more efficient where possible. I know this frustrates most of you, and understandably so, but I am extremely limited on code that I can share and unable to upload anything.

Here’s a little info about the tool:

• 13 worksheets
• 59 subs in total
  • 35 subs open data source files: copying certain data elements and pasting them into the parent workbook (New Data tab)
  • 3 subs format data
  • 1 appends New Data to the Running Data tab (these are separated so that when data needs to be sanitized, I’m only doing it against the new data and not all data)
  • The remaining subs are either form controls, twist the data into different views or print the views to PDF.

Where I think the issue is occurring, is the number of formulas in the workbook as a whole:

• 2 worksheets have over 1100 formulas.
• 3 worksheets have over 700 formulas.
• 1 worksheet has 2880 formulas.
• 1 worksheet has 3840 formulas.

The primary issue with the formulas is that for each of these worksheets, the business owners want to either select an Associate from a drop-down box and see updated figures OR they want to update a date range and see updated figures. In thinking through this:

• MS Access might be better, but my company has “outlawed” MS Access.
• I don’t know if this might help, but I supposed I could update the tool to let the business owner select the desired Associate and click an “Update Data” button that would add the requisite formulas, then paste them as values. This would, in theory, get rid of the over 8000 live formulas
• I don’t know if/how Power Query, Power Pivot or any other MS Excel tool might help, as I’ve never worked with them before.

Given the information provided above, thoughts on a solution other than adding more RAM (I’m not certain that would help either).

 

[Candyman8019]

What types of formulas are in the worksheets? Are you using a bunch of 'offset' functions for example? Those volatile functions are memory hogs. There are some tips on this page for making workbooks easier on memory...maybe something there to help you.

Clean up an Excel workbook so that it uses less memory - Microsoft 365 Apps

Identifies areas in Excel workbooks that use lots of memory and describes how you can make your workbook files work more efficiently.

learn.microsoft.com

 

[reberryjr]

What types of formulas are in the worksheets? Are you using a bunch of 'offset' functions for example? Those volatile functions are memory hogs. There are some tips on this page for making workbooks easier on memory...maybe something there to help you.

Clean up an Excel workbook so that it uses less memory - Microsoft 365 Apps

Identifies areas in Excel workbooks that use lots of memory and describes how you can make your workbook files work more efficiently.

learn.microsoft.com

They're mostly VLOOKUPS, COUNTIFS, SUMIFS....stuff like that. Nothing overly complex per se. There are a few that have 6 or 7 formulas nested. I'll check out that link. Thanks!

 

[reberryjr]

They're mostly VLOOKUPS, COUNTIFS, SUMIFS....stuff like that. Nothing overly complex per se. There are a few that have 6 or 7 formulas nested. I'll check out that link. Thanks!

@Candyman8019 I read through that article and unfortunately (or perhaps fortunately), none of the topics covered impact this tool.

 

[Candyman8019]

with 16GB of ram it's surprising that you're getting out of memory errors. How large is the workbook? Have you checked your task manager to see what is consuming your resources as you open the workbook? You mentioned there are 35 subs that open data sources and import data...I would make sure that each of those close the connection when they're complete...and don't have too many connections open concurrently.

 

[reberryjr]

with 16GB of ram it's surprising that you're getting out of memory errors. How large is the workbook? Have you checked your task manager to see what is consuming your resources as you open the workbook? You mentioned there are 35 subs that open data sources and import data...I would make sure that each of those close the connection when they're complete...and don't have too many connections open concurrently.

MS Teams is using ~620 MB, Edge ~350 MB, Excel ~240 MB and Outlook ~225 MB. Those are the top 4 consumers. All in, 63% of my memory is in use. I'm not sure I know what you mean when you say "close the connection". The import process finds a source file, opens it, grabs what it needs and closes the source file.

 

[Candyman8019]

I was thinking you may have been using 'data connections' rather than open a file, copy what you need and closing the file.

Next, I would try running the subs as you would in a typical scenario and see which one may be causing the memory issue...and maybe run that test a couple of times to see if you're getting consistent results. If we can pinpoint which sub is causing the error, we're closer to sorting it out.

 

[reberryjr]

I was thinking you may have been using 'data connections' rather than open a file, copy what you need and closing the file.

Next, I would try running the subs as you would in a typical scenario and see which one may be causing the memory issue...and maybe run that test a couple of times to see if you're getting consistent results. If we can pinpoint which sub is causing the error, we're closer to sorting it out.

I thought about using Data Connections, but I've never really used it much. Should I just be watching for a spike in Memory usage on the Task Manager?

 

[Candyman8019]

Yeah, I'd watch for spikes and make note of which sub is running at the time

 

[Candyman8019]

I also read a post recently where the user was doing just as you are and the workaround was to make the file save after every couple of data imports.