VBA Evaluate Formula value randomly only updates after worksheet/workbook re-calculated

[AmatuerNoob]

Hi,

For some reason the Evaluate formula within my VBA script is not always correctly updating/recalculating the correct value.

Please Login or Register  to view this content.

The formula is an array formula which looks to determine the relevant value across a range of filtered cells.

Each cycle, the range of cells is changed and the new compVal must be determined.

Unfortunately, sometimes the CompVal does not recalculate correctly and returns a value of 0. If I then recalculate the sheet via

Please Login or Register  to view this content.

and re-apply the evaluate formula in my VBA code, the CompVal will update to the correct value.

Due to the large amount of data in my worksheet and the number of cycles required, recalculating is adding hours/days to the total run-time of my macro.

Does any one know what might be causing the issue and how it could be fixed?

Any help would be greatly appreciated.

Thanks.

[bebo021999]

Without VBA, SUBTOTAL(OFFSET may help a lot with formula based option
But it is volatile formula, and cause the PC become slow, for large data.
With VBA, I believe there is no reason to use that complex formula combination.
If you are willing to get better VBA solution, try to upload a sample worksheet.

[AmatuerNoob]

Bebo,

Thanks for your suggestion.

I have had issues trying to determine the correct formula. The one I am currently using essentially works, so I don't want to change if it's not necessary.

Is the reason the formula doesn't update the value randomly due to its volatile nature?

Thanks.

[rorya]

Your formula is not in a worksheet so it's not volatile. (AFAIK SUBTOTAL is not volatile anyway)

The 0 value suggests your SUBTOTAL formula is returning an error - hard for us to say why that would be when there is no context provided.