Quicker Calculation Time

  • Thread starter Thread starter jeffcravener
  • Start date Start date
J

jeffcravener

I have a sheet thath as multiple LOOKUP functions being used and
couple custom functions being used.

The calculations are extremely slow...is there a way to speed them up
 
... I did some tests...and it seems to be my functions that are causin
the increase in calculation time...

here is the one that appears to increase the time a great deal:

Code
-------------------

Function MONTHSUM(Month As Integer, Year As Integer, LookupRange As Range, ValueRange As Range) As Variant

Dim ArrayOfDates() As String
Dim i, ArrayOfValues() As String
Dim rngCell As Range

ReDim ArrayOfValues(100000)
ReDim ArrayOfDates(100000)

i = 0

For Each rngCell In LookupRange
If rngCell.value <> "" Then
ArrayOfDates(i) = rngCell.value
End If
i = i + 1
Next rngCell

i = 0

For Each rngCell In ValueRange
If rngCell.value <> "" Then
ArrayOfValues(i) = rngCell.value
End If
i = i + 1
Next rngCell

MONTHSUM = ""
For i = 0 To UBound(ArrayOfDates)
If (Format(ArrayOfDates(i), "M") = Month) And (Format(ArrayOfDates(i), "YYYY") = Year) And (ArrayOfValues(i) <> "") Then
MONTHSUM = CDbl(MONTHSUM + ArrayOfValues(i))
End If
Next

End Function

-------------------


I created a report that holds call statistcs for a single skill(queue
on a single tab...just testing it with one has a long calculatio
time....now, when in operation, the number of skill tabs can be up t
100....so you can imagine the time it would take.

Any way to speed up the above function
 
Back
Top