slow refresh times in excel 2007 for workbooks with user-defined functions

[VAndre]

I have a couple of workbooks that use VBA functions. They have worked well in Excel2003 environments, but refresh time is very slow once I migrated to Excel2007. The speed is so slow that there is a visible series of blinks every time I refresh. It's really bad (I literally got a migraine the first day I spent a lot of time with one of these workbooks).

Has anyone seen some literature regarding what is going on here?

Keywords: Flashing Blinking slow refresh recalculation speed Excel 2007 flicker

 

[Norie]

Keywords : Post the code, and an explanation of what it's meant to do.

As far as I know a UDF should not be doing what you describe in any version of Excel.

 

[VAndre]

you guys sure are quick.
some of these items of code are simple calculations

examples are things like:

Function Factorial(n) As Double
    Dim rep As Double
    Dim i As Double

    rep = 1
    For i = 1 To n
        rep = rep * i
    Next i

    Factorial = rep

End Function

There are also a couple of simple command buttons. In all cases there are no screenupdating or such going on. also not selecting different worksheets.

Both of the workbooks I'm dealing with were pretty fast in 2003.

 

[Norie]

You do know there is an Excel worksheet function called FACT.

Screen updating etc shouldn't be an issue with a UDF because you probably shouldn't be using a UDF to alter values, format otters etc.

A UDF in Excel VBA is normally used to do some calculation(s) and return a value to a cell.

I think there might be something else going on, whatever that might be could be something to do with the version thing but then again it might not.

 

[VAndre]

yeah, I inherited this workbook and there are a lot of old functions like that in there... dozens of them.

It's a strange problem that a few people I know have seen