how can VBA assist with 'releasing' memory?

[Marijke]

Hello all,

My team and I have an inventory report that has a variable length. (currently up to 50,000 lines or so.) Number of columns is fixed.

In order to present it to our customer, we run a large amount of vlookups and such to pull data in from other Excel reports.
Once that is completed, we run an number if/then statements for correcting some of the data.
Our reports are growing in size (general increase in number of items on inventory) and as s result, my VBA is getting slower by the day.

I know either of the 2 (vlookups and if/then statements) are unfortunately causing quite some issues on memory usage.
Without having to disclose the entire VBA routine, are there general steps I can take to release my memory or speed things up a bit?

- All scripts we are using start with Application.ScreenUpdating = False
- I have tried Saving my file in between, in addition to saving at the end, but that actually did nothing to the memory usage and/or the time the VBA is running.

I have read something about VBA Arrays as well as VBA Dim, but are they helpful, and how do I use them?
And perhaps there is a method that can replace the if/then statements?

any help is highly appreciated!
Marijke

[torachan]

This could take many posts and a multitude of speculative suggestions.
Without the code it is impossible to give concise reasons for its deteriorating performance.
torachan.

[Marc L]

Hi,

avoid using useless Object variables (see With statement) and if you use some,
set them to Nothing once there are no more necessary …

[Marc L]

I have read something about VBA Arrays as well as VBA Dim, but are they helpful, and how do I use them?

Use them as per the logic necessary …

Among so many samples from this forum :

Macro to Extract Matching Values into Columns

VBA required to delimit cells with some rules applied on it

[davsth]

Without code it is stumbling in the dark

usually referencing another sheet in the same workbook is quicker than an external workbook

Are the vlookups static once they have been calcualted? if yes change them to values

applicatrion.calculation may also be worth looking at, so you only calculate when you need to

[CK76]

Since you have Office 365... you can use something other than VBA, more suited to efficient use of memory (vertipaq engine).

Replace VBA with using PowerQuery (ETL tool) and PowerPivot (reporting & analysis tool) to generate your report.

However, as others have stated, without your code. It's bit hard to tell what the bottle neck is in your code.