VBA Calculation - taking forever

[Forgone]

I have a workbook with a series of formulas to prepare a financial
report.
There is a large volume of data in these workbooks but it is taking a
very long period of time to calculate and would like some assistance
to try and reduce the amount of calculation it does in order to speed
up the process.

So far it has taken over 10 minutes to do the calculations.

On the raw data, there is a UDF which determines what category the
transaction falls into: this is.....

------

Public Function CoA_Class_Lookup(Account As Variant) As String

Select Case Account

Case 100102 To 399901
CoA_Class_Lookup = "Expense"
Case 400102 To 551101
CoA_Class_Lookup = "Revenue"
Case 551102
CoA_Class_Lookup = "Transfer"
Case 551103 To 599907
CoA_Class_Lookup = "Revenue"
Case Else
CoA_Class_Lookup = "ERROR"
End Select

End Function

------

There is a large volume of array formulas in the cells which the
formula is:

=SUM((CCE='Grants Balance'!$A12)*(Period='Grants Balance'!H$2)*
(category='Grants Balance'!I$3)*(period.activity))*-1

------

In the raw data there is a column that has the formula
=CoA_Class_Lookup() where the range is named "category"

------

There is currently 11,000 rows of data in the raw data workbook and
any suggestions to improve the speed it takes to calculate all 11,000
rows would be greatly appreciated.

My suspision is that it is the "Select Case" function that is causing
the grief.
Is there any suggestions

 

[Forgone]

Hi Herald,

Thanks for this information, it is interesting to read.
I have just set the "Case" function formula as values only and removed
the formulas but yeah.... the array formulas appear to be killing it.

I have been wondering if it maybe better to pass the array formula
into a UDF

=SUM((CCE='Grants Balance'!$A12)*(Period='Grants Balance'!H$2)*
(category='Grants Balance'!I$3)*(period.activity))*-1

EG: =SUMGRANTS(Criteria1,Criteria2,Criteria3)

or........ would it better to use an MS Access data table and have
SQL.

The plan for this would be that once it got to the end of the
financial year, the end balance would move into the next worksheet and
so forth.