Analysis ToolPaks VBA Fast Fourier Transform
 

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

Answer: Analysis ToolPaks VBA Fast Fourier Transform
 

Hi there!
I understand that you're having trouble with the Fast Fourier Transform (FFT) in Excel 2007 being slower than in Excel 2003. It sounds like you're concerned that the difference in file format (XLA vs XLAM) might be causing the slowdown.

First, let me explain a bit about the file formats. XLA files are Excel add-ins that are saved in binary format, while XLAM files are add-ins that are saved in XML format. The main difference is that XLAM files can contain VBA code, while XLA files cannot. However, both file formats should be loaded into memory when Excel starts up, so there shouldn't be any significant difference in performance between the two.

That being said, there are a few things you can try to speed up your FFT calculations:

1. Use the latest version of Excel: If you're not already using the latest version of Excel, upgrading to a newer version may help improve performance.
2. Optimize your code: Make sure your VBA code is as efficient as possible. This means minimizing the number of calculations and avoiding unnecessary loops and iterations.
3. Use a different FFT algorithm: Excel's built-in FFT algorithm may not be the fastest one available. There are many third-party FFT algorithms that you can use instead. One popular option is the FFTW library, which has a VBA wrapper that you can use in Excel.
4. Use parallel processing: If you have a multi-core processor, you can use parallel processing to speed up your FFT calculations. This involves splitting the calculations into multiple threads that can run simultaneously on different cores.

I hope these suggestions help you speed up your FFT calculations in Excel 2007.

Analysis ToolPaks VBA Fast Fourier Transform
 

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" wrote in message ...

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

Analysis ToolPaks VBA Fast Fourier Transform
 

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" wrote in message
...
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

Analysis ToolPaks VBA Fast Fourier Transform
 

Hi. I don't know why it's so slow. I have some old notes on Excel's fourier timing somewhere. I'll look for them.
(It was for multiplying 2000+ digit numbers)

Anyway, I looked at some other Fourier code I had and was reminded of the following... :(


'// Set up - CONSTANTS in col.A


With [A1:A4096]
.Formula = "=Rand()"
.Value = .Value
End With

--
Dana DeLouis


"Dimitry" wrote in message ...

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" wrote in message
...

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

Analysis ToolPaks VBA Fast Fourier Transform
 

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" wrote in message ...

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" wrote in message
...

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

Analysis ToolPaks VBA Fast Fourier Transform
 

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" wrote in message ...
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" wrote in message
...
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

Analysis ToolPaks VBA Fast Fourier Transform
 

In article , "Dana DeLouis" wrote:
SGkuICBJIGRvbid0IGtub3cgd2h5IGl0J3Mgc28gc2xvdy4gI CBJIGhhdmUgc29tZSBvbGQgbm90
ZXMgb24gRXhjZWwncyBmb3VyaWVyIHRpbWluZyBzb21ld2hlc mUuICBJJ2xsIGxvb2sgZm9yIHRo
ZW0uDQooSXQgd2FzIGZvciBtdWx0aXBseWluZyAyMDAwKyBka WdpdCBudW1iZXJzKQ0KDQpBbnl3
YXksIEkgbG9va2VkIGF0IHNvbWUgb3RoZXIgRm91cmllciBjb 2RlIEkgaGFkIGFu
(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 :)