Fast Fourier Transform (FFT) in Excel 2007 is 10 to 30 times slower than in
Excel 2003. The only differences is in Analysis ToolPaks -
ATPVBAEN.XLAM!Fourier in Excel 2007 and ATPVBAEN.XLA!Fourier in Excel 2003. I
have a doubt about compression in *.xlam format. It seems like these files
are unwrapped every time when a macro calls them. I have to calculate more
than 12800 FFTs in one workbook. Each one takes 12 seconds. The macro with
FFT should work more than two days. Any suggestions how to accelerate FFT in
Excel 2007?
Thanks

Thanks Dana!

It was a helpful hint. But the 2007 remains slower. The problem is that
there are live formulas in any open Workbook. The cell formating leads to
additional delay. You can see this in the next example. If you open more
Workbooks with formulas the time can reach more than 90 seconds (in my case).

Sub Demo()
Const FFT As String = "ATPVBAEN.XLAM!Fourier"

'// Set up - CHANGING VALUES in col.A
' FFT over col B

Range("A1:A4096").Formula = "=Rand()"
Range("A1:A4096").Select
Selection.Copy
Range("B1:B4096").Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
' "Rand()" in A1:A4096 are still alive
'time for calc is not in the test intervals

For I = 1 To 4

'// Timing Test 1
Tme1 = Timer 'Start time 1

Run FFT, [B1:B4096], Cells(1, (I + 6)), False, False
Cells((I), 3) = I
Cells((I), 4) = (Timer - Tme1)
Next I
Cells((I), 3) = "[A]=Rand()"
Cells((I), 4) = " Time_1,s"
Cells((I + 1), 3) = "FFT ([B]= CONST)"
Range("K1:K4096").Value = " "

'// Set up - CONSTANTS in col.A
' FFT over col B

Range("A1:A4096").Select
Selection.Copy
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
' <A1:A4096> are constants

For J = 1 To 4

'// Timing Test 2
Tme2 = Timer 'Start time 2

Run FFT, [B1:B4096], Cells(1, (J + 11)), False, False
Cells((J), 5) = J
Cells((J), 6) = (Timer - Tme2)

Next J
Cells((J), 5) = "[A]=Const"
Cells((J), 6) = " Time_2,s"
Cells((J + 1), 5) = "FFT ([B]= CONST)"
End Sub


Thanks again,
Dimitry


"Dana DeLouis" wrote:

> > Excel 2003. The only differences is in Analysis ToolPaks -
>
> > ATPVBAEN.XLAM!Fourier in Excel 2007 and ATPVBAEN.XLA!Fourier in Excel
> 2003. I
>
> Hi. Actually, 2003 should be much slower because Microsoft left in
> Debug.Print statements in the Analysis ToolPak that caused the programs to
> run very, very, slow.
> They refused to fix this for some unknown reason. Having said that, my
> times in Excel 2007 are around 4.3 seconds vs your 12 seconds. (for size
> 4096)
> You are right, this is still very slow! I don't have a reason for it
> being so slow.
> You may want to consider your own vba Fourier Program. The advantages
> are that you can keep the Real & Imaginary values in two separate arrays.
> When Calling FFT, you do not have to waste time joining each into a
> string, and them placing them on a worksheet. It is much faster this way.
>
> I get 4.3 seconds with Windows Vista and Excel 2007.
>
> Sub Demo()
> Const FFT As String = "ATPVBAEN.XLAM!Fourier"
> Const Forward As Boolean = False
> Const Inverse As Boolean = True
> Const NoLabels As Boolean = False
> Const HasLabels As Boolean = True
>
> Dim Tme As Double
> Dim Rng As Range
>
> '// Set up
> [A:C].Clear
> Set Rng = [A1].Resize(2 ^ 12)
>
> With Rng
> .Formula = "=Rand()"
> .Copy
> .PasteSpecial xlPasteValues
> Application.CutCopyMode = False
> End With
>
> '// Timing Test
> Tme = Timer 'Start time
> Run FFT, Rng, [C1], Forward, NoLabels
> MsgBox Timer - Tme
> End Sub
>
>
> --
> HTH :>)
> Dana DeLouis
>
>
> "Dimitry" <(E-Mail Removed)> wrote in message
> news:4098182F-D7EA-4FF6-A3E7-(E-Mail Removed)...
> > Fast Fourier Transform (FFT) in Excel 2007 is 10 to 30 times slower than
> in
> > Excel 2003. The only differences is in Analysis ToolPaks -
> > ATPVBAEN.XLAM!Fourier in Excel 2007 and ATPVBAEN.XLA!Fourier in Excel
> 2003. I
> > have a doubt about compression in *.xlam format. It seems like these
> files
> > are unwrapped every time when a macro calls them. I have to calculate
> more
> > than 12800 FFTs in one workbook. Each one takes 12 seconds. The macro
> with
> > FFT should work more than two days. Any suggestions how to accelerate
> FFT in
> > Excel 2007?
> > Thanks
> >
>

Thanks Dana,

The problem is that the formulas in any open Workbook together with the
Workbook with FFT lead to unavoidable FFT delay. This is a Microsoft problem.
Probably I should write my own VBA FFT routine.

Regards
Dimitry

"Dana DeLouis" wrote:

> > the time can reach more than 90 seconds (in my case).
>
> Hi. Yes, I have terrible times also. I don't know why.
>
> Just for a comparison:
> On my vista machine, my custom vba FFT program that works at full precision (28 digits) takes 0.2 seconds for size 4096.
> Therefore, a standard double precision routine should be much faster.
> (Also, a custom FFT program will not have an arbitrary limit of 2^12)
>
> --
> Dana DeLouis
>
>
> "Dimitry" <(E-Mail Removed)> wrote in message news:02F7CEDB-B6C7-4B0B-B9DB-(E-Mail Removed)...
> > Thanks Dana!
> >
> > It was a helpful hint. But the 2007 remains slower. The problem is that
> > there are live formulas in any open Workbook. The cell formating leads to
> > additional delay. You can see this in the next example. If you open more
> > Workbooks with formulas the time can reach more than 90 seconds (in my case).
> >
> > Sub Demo()
> > Const FFT As String = "ATPVBAEN.XLAM!Fourier"
> >
> > '// Set up - CHANGING VALUES in col.A
> > ' FFT over col B
> >
> > Range("A1:A4096").Formula = "=Rand()"
> > Range("A1:A4096").Select
> > Selection.Copy
> > Range("B1:B4096").Select
> > Selection.PasteSpecial xlPasteValues
> > Application.CutCopyMode = False
> > ' "Rand()" in A1:A4096 are still alive
> > 'time for calc is not in the test intervals
> >
> > For I = 1 To 4
> >
> > '// Timing Test 1
> > Tme1 = Timer 'Start time 1
> >
> > Run FFT, [B1:B4096], Cells(1, (I + 6)), False, False
> > Cells((I), 3) = I
> > Cells((I), 4) = (Timer - Tme1)
> > Next I
> > Cells((I), 3) = "[A]=Rand()"
> > Cells((I), 4) = " Time_1,s"
> > Cells((I + 1), 3) = "FFT ([B]= CONST)"
> > Range("K1:K4096").Value = " "
> >
> > '// Set up - CONSTANTS in col.A
> > ' FFT over col B
> >
> > Range("A1:A4096").Select
> > Selection.Copy
> > Selection.PasteSpecial xlPasteValues
> > Application.CutCopyMode = False
> > ' <A1:A4096> are constants
> >
> > For J = 1 To 4
> >
> > '// Timing Test 2
> > Tme2 = Timer 'Start time 2
> >
> > Run FFT, [B1:B4096], Cells(1, (J + 11)), False, False
> > Cells((J), 5) = J
> > Cells((J), 6) = (Timer - Tme2)
> >
> > Next J
> > Cells((J), 5) = "[A]=Const"
> > Cells((J), 6) = " Time_2,s"
> > Cells((J + 1), 5) = "FFT ([B]= CONST)"
> > End Sub
> >
> >
> > Thanks again,
> > Dimitry
> >
> >
> > "Dana DeLouis" wrote:
> >
> >> > Excel 2003. The only differences is in Analysis ToolPaks -
> >>
> >> > ATPVBAEN.XLAM!Fourier in Excel 2007 and ATPVBAEN.XLA!Fourier in Excel
> >> 2003. I
> >>
> >> Hi. Actually, 2003 should be much slower because Microsoft left in
> >> Debug.Print statements in the Analysis ToolPak that caused the programs to
> >> run very, very, slow.
> >> They refused to fix this for some unknown reason. Having said that, my
> >> times in Excel 2007 are around 4.3 seconds vs your 12 seconds. (for size
> >> 4096)
> >> You are right, this is still very slow! I don't have a reason for it
> >> being so slow.
> >> You may want to consider your own vba Fourier Program. The advantages
> >> are that you can keep the Real & Imaginary values in two separate arrays.
> >> When Calling FFT, you do not have to waste time joining each into a
> >> string, and them placing them on a worksheet. It is much faster this way.
> >>
> >> I get 4.3 seconds with Windows Vista and Excel 2007.
> >>
> >> Sub Demo()
> >> Const FFT As String = "ATPVBAEN.XLAM!Fourier"
> >> Const Forward As Boolean = False
> >> Const Inverse As Boolean = True
> >> Const NoLabels As Boolean = False
> >> Const HasLabels As Boolean = True
> >>
> >> Dim Tme As Double
> >> Dim Rng As Range
> >>
> >> '// Set up
> >> [A:C].Clear
> >> Set Rng = [A1].Resize(2 ^ 12)
> >>
> >> With Rng
> >> .Formula = "=Rand()"
> >> .Copy
> >> .PasteSpecial xlPasteValues
> >> Application.CutCopyMode = False
> >> End With
> >>
> >> '// Timing Test
> >> Tme = Timer 'Start time
> >> Run FFT, Rng, [C1], Forward, NoLabels
> >> MsgBox Timer - Tme
> >> End Sub
> >>
> >>
> >> --
> >> HTH :>)
> >> Dana DeLouis
> >>
> >>
> >> "Dimitry" <(E-Mail Removed)> wrote in message
> >> news:4098182F-D7EA-4FF6-A3E7-(E-Mail Removed)...
> >> > Fast Fourier Transform (FFT) in Excel 2007 is 10 to 30 times slower than
> >> in
> >> > Excel 2003. The only differences is in Analysis ToolPaks -
> >> > ATPVBAEN.XLAM!Fourier in Excel 2007 and ATPVBAEN.XLA!Fourier in Excel
> >> 2003. I
> >> > have a doubt about compression in *.xlam format. It seems like these
> >> files
> >> > are unwrapped every time when a macro calls them. I have to calculate
> >> more
> >> > than 12800 FFTs in one workbook. Each one takes 12 seconds. The macro
> >> with
> >> > FFT should work more than two days. Any suggestions how to accelerate
> >> FFT in
> >> > Excel 2007?
> >> > Thanks
> >> >
> >>

In article <(E-Mail Removed)>, "Dana DeLouis" <(E-Mail Removed)> wrote:
>SGkuICBJIGRvbid0IGtub3cgd2h5IGl0J3Mgc28gc2xvdy4gICBJIGhhdmUgc29tZSBvbGQgbm90
>ZXMgb24gRXhjZWwncyBmb3VyaWVyIHRpbWluZyBzb21ld2hlcmUuICBJJ2xsIGxvb2sgZm9yIHRo
>ZW0uDQooSXQgd2FzIGZvciBtdWx0aXBseWluZyAyMDAwKyBkaWdpdCBudW1iZXJzKQ0KDQpBbnl3
>YXksIEkgbG9va2VkIGF0IHNvbWUgb3RoZXIgRm91cmllciBjb2RlIEkgaGFkIGFu
(snip many more lines)

Not sure how you are posting or with what ... but as you can see, it looks
entirely incomprehensible in the news group.
Suggest you post as text only in future.

Thanks