Analysis ToolPaks VBA Fast Fourier Transform

[Dimitry]

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

 

[Dimitry]

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 (= 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 (= CONST)"
End Sub


Thanks again,
Dimitry

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

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

 

[Dimitry]

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

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

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 (= 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 (= CONST)"
End Sub


Thanks again,
Dimitry

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


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

 

[Bruce Sinclair]

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