Forum Discussion
VBA Optimization help for Large string manipulation related to memory management?
TLDR; The issue I need to solve. How do I optimize the code, so the runtime for each 250 is approximately the same? i.e, about say 15 secs? consistently from 0 - 35,000 conversations. and even if...
Hi, So, I'm not as convinced that it's an issue with the code.
I did change Instr to InstrB, cause I have no need for the positional value.
I ran the code, for small data sets, up to 2000, which is excellent in under 2mins.
I wrote a functional LapTime to update the Application status bar.
Up to 2000, each set of 250 conversations takes about 5 secs.
Running the same macro the fifth time, for no reason whatsoever,
Now causes up to 2000, each set of 250 conversations takes about 15 secs.
This is still fine.
After 2000 or 5000 conversations,
The lap time for each set of 250 conversations, suddenly increases to 31secs, then 54secs for the next set.
Then 1.21 mins for the next sequential set.
The issue I'm trying to solve is WHY?
WHY the consistency till a certain point? Why can't I have the same speed for all 30k conversations?
What is happening at the 2,000 or 5,000 mark that drastically changes the speed and runtime?
And how to mitigate this? or stop it?
I did change Instr to InstrB, cause I have no need for the positional value.
I ran the code, for small data sets, up to 2000, which is excellent in under 2mins.
I wrote a functional LapTime to update the Application status bar.
Up to 2000, each set of 250 conversations takes about 5 secs.
Running the same macro the fifth time, for no reason whatsoever,
Now causes up to 2000, each set of 250 conversations takes about 15 secs.
This is still fine.
After 2000 or 5000 conversations,
The lap time for each set of 250 conversations, suddenly increases to 31secs, then 54secs for the next set.
Then 1.21 mins for the next sequential set.
The issue I'm trying to solve is WHY?
WHY the consistency till a certain point? Why can't I have the same speed for all 30k conversations?
What is happening at the 2,000 or 5,000 mark that drastically changes the speed and runtime?
And how to mitigate this? or stop it?
One other thing to try is to avoid reading the value of cell many, many times.
At the top of the routine, add:
Dim cellValue as Variant
Then immediately after For Each cell In Search_Range
add:
cellValue = cell.Value2
Now everwhere in the loop, where you're retreiving the value of the cell variable, replace cell with cellValue.
For example, this line:
midText = Split(cell, "||")
should be changed to
midText = Split(cellValue, "||")
At the top of the routine, add:
Dim cellValue as Variant
Then immediately after For Each cell In Search_Range
add:
cellValue = cell.Value2
Now everwhere in the loop, where you're retreiving the value of the cell variable, replace cell with cellValue.
For example, this line:
midText = Split(cell, "||")
should be changed to
midText = Split(cellValue, "||")