Any VBA tricks for Array Formula burdens?

[Intermedius]

Hello,

I have an array formula being inserted into an Excel Data table column via VBA statement, which then processes 100's & sometimes 1000's of lines that are taking too long for My laptop to process in a feasible amount of time.

I have chosen VBA because it appears moderately faster to clear the data and existing array formula, then add the new data 1st, rather than just keeping the array formula column filled, and use table properties to remove all other data and replace with new data.


Sub ClearTableData()

'Clear Table Data

With Sheets("Sheet1").ListObjects("Data_Table")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With

I then append new data to the existing, but cleared data table.

After the new data is appended, i re-add the Array formula:

Sub AddArrayFormula()

'Add Array Formula


With ActiveSheet.ListObjects("Data_Table").ListColumns("ArrayColumn").DataBodyRange
.ClearContents
.Cells(1).FormulaArray = "=Array Formula"
End With

It could just be in my head that it works faster, as i have not timed the process and My data sets have changed overtime. In either case, the processing time is not ideal to achieve the result needed.

 

[Smitty]

What's the array formula doing? Is it possible you could replace that with a Power Query routine?

Otherwise, the only other thing I see you could do it set ScreenUpdating to False, and Calculation to Manual, then turn them back on at the end.

 

[Fazza]

Suggest you explain the data set up & provide some sample inputs & results. Likely there are efficient alternative solutions. (Without array formulas.)

How long does calculation take with array formulas?

 

[Intermedius]

What's the array formula doing? Is it possible you could replace that with a Power Query routine?

Otherwise, the only other thing I see you could do it set ScreenUpdating to False, and Calculation to Manual, then turn them back on at the end.

Suggest you explain the data set up & provide some sample inputs & results. Likely there are efficient alternative solutions. (Without array formulas.)

How long does calculation take with array formulas?

Hello Smitty and Fazza,

Depending on the volume of data, a data refresh can take 5-20 minutes.

The Array formula is an index/match checking each row in the data table and pulling in corresponding data from a column on another sheet in that workbook. {=IFERROR(INDEX(Calculation!A:A,MATCH($N8,IF(Calculation!B:B=[@Material],Calculation!A:A))),0)}

This thread has some background on the actual formula https://www.mrexcel.com/forum/excel-questions/1060309-two-way-lookup-exact-approximate-match.html

I have also found this resource regarding time of calculation methods similar to the one i am using.

https://exceloffthegrid.com/optimize-formula-calculation-speed-part-7-multi-criteria-lookups/