VBA Calculation - taking forever
 

Hi

The VBA function could be replaces with a simple VLOOKUP, but I suspect that
the array formulas are the consumers here.

I work with datasets at least 10 times this big. These things can not be
loaded with lots of formulas, definitely not array formulas. So the first
thing to consider is: Will the data change ? If no to parts or whole, do the
calculation only once and, if done with formulas, replace the formulas with
result values once done. For example, you can loop 10000 rows of data with
VBA and hardcode Category, in a couple of seconds.

Try in a copy to remove all calls to the VBA functions. In another, remove
all array formulas. The speed of these will give a good indication on where
your performance problem is.
Also: If there's a pivot table in a flie like this, it will slow
calculations down. Remove it from the file.

HTH. Best wishes Harald


"Forgone" wrote in message
...
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