Can you improve the performance on my user defined function?

[Law]

Hi All

I have 5 sheets. 4 of the sheets contain forecast data and 1 of them
contains Actual data.

The forecast sheets have the following formula under the required
columns at the bottom of the data range for calculating the
corresponding actual total when the forecast data is autofiltered:

'=SUM((SpecialLookUp(SubRangeSingleCol(QT_SAM_FCT4_1, 3), ACT!
QT_SAM_ACT_1, 3, 6)))

Essentially what I am doing here is retrieving the filtered row IDs in
the Forecast sheet and then looking up these rowIds in the Actual data
sheet and then summing these req. values of the Actual data in the
Forecast sheet.

The problem occurs when I filter the Actual data it takes ages for
Excel to re-calculate these lookup formulas in the Forecast sheets.

Any suggestion as to how to make the calculation quicker would be more
than welcome.




Function SubRangeSingleCol(QueryRange As Range, Col As Integer) As
Range

For Each cl In QueryRange.Columns(Col).Cells

If (cl.EntireRow.Hidden = False) Then
If SubRangeSingleCol Is Nothing Then
Set SubRangeSingleCol = cl
Else
Set SubRangeSingleCol = Union(SubRangeSingleCol, cl)
End If
End If

Next cl


End Function

Function SpecialLookUp(IDInputRange As Range, LookUpRange As Range,
IDCol As Integer, ResultCol As Integer) As Range

For Each cl In IDInputRange.Cells
For Each cl1 In LookUpRange.Columns(IDCol).Cells
If SpecialLookUp Is Nothing Then
If cl1.Value = cl.Value Then
Set SpecialLookUp = cl1.Offset(0, ResultCol)
End If
Else
If cl1.Value = cl.Value Then
Set SpecialLookUp = Union(SpecialLookUp,
cl1.Offset(0, ResultCol - IDCol))
End If
End If
Next cl1

Next cl

End Function


Regards

Lars

 

[Peter T]

Hi Lars,

It's difficult to follow quite what you are doing, following comments based
merely on a glance of your functions.

The most obvious possibility that would make your functions slow is multiple
Union's, these become exponentially slower with many non contiguous areas,
if that's what your filtered range gives. Your first function, I think,
could be done in a single line -

On error resume next
Set SubRangeSingleCol = _
QueryRange.Columns(Col).SpecialCells(xlCellTypeVisible)

Not sure what's going overall with your second function and cell formula.
But it might be quicker to do the Sum in the function rather than returning
a range to the cell, to avoid the union.

It's worth declaring your variables, eg Dim cl as Range. Also use a
temporary range variable, when done assign to the function, eg -
Dim rngOutput as
' build rngOutput
Set SpecialLookUp = rng

If you care to explain with references (names, address's etc) and simple
data samples that can easily be recreated without seeing what you have,
others may well be able to suggest more ideas and perhaps a different
approach.

Regards,
Peter T

 

[Law]

Hi Lars,

It's difficult to follow quite what you are doing, following comments based
merely on a glance of your functions.

The most obvious possibility that would make your functions slow is multiple
Union's, these become exponentially slower with many non contiguous areas,
if that's what your filtered range gives. Your first function, I think,
could be done in a single line -

On error resume next
Set SubRangeSingleCol = _
QueryRange.Columns(Col).SpecialCells(xlCellTypeVisible)

Not sure what's going overall with your second function and cell formula.
But it might be quicker to do the Sum in the function rather than returning
a range to the cell, to avoid the union.

It's worth declaring your variables, eg Dim cl as Range. Also use a
temporary range variable, when done assign to the function, eg -
Dim rngOutput as
' build rngOutput
Set SpecialLookUp = rng

If you care to explain with references (names, address's etc) and simple
data samples that can easily be recreated without seeing what you have,
others may well be able to suggest more ideas and perhaps a different
approach.

Regards,
Peter T





















- Show quoted text -

Thanks Peter

Using a temp variable in the function made things a lot quicker.

Cheers

L