As Tim Zych says, Excel ignores screenupdating and manual calculation
changes inside a UDF.

You can speed up calculation a lot by changing calculation to Manual rather
than automatic and initiating calculation from VBA rather than Excel. This
bypasses a long-standing bug which refreshes the VBE title bar twice each
time a formula containing a UDF is calculated (so several thousand times in
your case). Application.screenUpdating does not affect the VBE so will not
bypass this problem.

(See http://www.decisionmodels.com/calcsecretsj.htm )

add this to the workbook open sub so that it traps F9

Application.OnKey "{F9}", "CalcBook"

then add this sub to the module containing your UDFs

Public Sub CalcBook()
Application.Calculate
End Sub

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"ker_01" wrote in message
...
I mostly work in 2003, but I'm working on a project to help out a co-worker
who uses 2007. I have two problems (I'll post them separately to keep the
discussion threads clean)

I've written a custom function (below) which is being used to reconcile
data
across
two worksheets in the same workbook. The function is used in several
thousand
cells. There is a lot of data being processed, and it takes several
minutes
to update the workbook.

This takes far longer than I would have expected, and I see a couple of
problems with my approach (I just don't know how to fix them).

(1) If there was a way to turn off recalculation and screenupdating at the
beginning of the first (of thousands of) cell update(s) and turn it all on
back at the very end, I think that would speed it up....but the formula is
separate in those thousands of cells, which I believe calls the function
separately for each cell- and I don't know how to tell when the first call
starts and the last one finishes, as opposed to any random one in between.

(2) I have to adjust for case sensitivity and remove the tail end of an
email address. Truth is, I should only have to do that once for the whole
list... but again, I'd need to know when the function was first triggered,
and not do it each time the function was called within a single
recalculation

I'd welcome any advice on how to better design my function, including
anything else that might speed this up.

Many thanks,
Keith


Code:
Function PullOverData(SentToAddress As Range, BrainSharkAddress As Range,
BrainSharkData As Range)

Dim Col2 As Variant
Dim Col3 As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col1 = SentToAddress
Col2 = BrainSharkAddress.Value
Col3 = BrainSharkData.Value

If (InStr(Col1, "@")) = 0 Then
SourceEmailValue = LCase(Col1)
Else
SourceEmailValue = LCase(Left(Col1, InStr(Col1, "@") - 1))
End If

For i = LBound(Col2) To UBound(Col2)
If Col2(i, 1) = "" Then Exit For
AtFound = InStr(Col2(i, 1), "@")
If AtFound = 0 Then
Col2(i, 1) = LCase(Col2(i, 1))
Else
Col2(i, 1) = LCase(Left(Col2(i, 1), AtFound - 1))
End If

'UseRow = Application.Match(SourceEmailValue, Col2, False)
If Col2(i, 1) = SourceEmailValue Then
PullOverData = Col3(i, 1)
Exit Function
Else
PullOverData = ""
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Function