Revised - Easy FFT plotting

[electricpete]

Note - I have red-flagged the previous thread on this subject since the excel file posted there cause an error for Corus and also caused an error for me in Excel 2003. This new thread posts a revised verision which works for me in Excel 2003.

Attached is a spreadsheet which uses the Alglib FFT algorithm.

I have combined it with dynamic named formulas (similar to the ChartFmla spreadsheet posted in this forum) so that you don't need to enter any data or ranges... just enter the time waveform formula, number of points, and time spacing between samples and you get handy time waveform plot and spectrum plot. Does not require any tool-boxes or add-ins. Not limited to powers of 2 or below 4096... however it seems to give me errors above 10,000 points.


=====================================
(2B)+(2B)' ?

 

[kacarrol]

Sorry to say but I'm getting the Automation Error with this version of the spreadsheet. I'm running Excel 2007.

 

[magoo2]

Ditto here. Excel 2007.

 

[IDS]

I get a message about invalid references when I start up, but it seems to work anyway. (Excel 2010)

I'll have a good look later.

Pete - what was the problem with the original version?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

The file in the first thread was creaetd in excel 2000 and ran fine in my excel 2000. Then later I tried it in excel 2003 and it gave an error "automation error - catostrophic failure".

So I rebuit the file within excel 2003 and attached it to the first post in this thread... it runs fine in excel 2003 and excel 2000, but apparently not in excel 2007. I tried it on an excell 2007 computer and go the same error as before "automation error - catostrophic failure".

I was able to get into vba while the error is displayed. The line that is highlighted is the function header:

Function myfft(invar As Variant, Optional window As String = "none") As Double()

The function code is as follows (it calls some subroutines which I have not included

Function myfft(invar As Variant, Optional window As String = "none") As Double()

    ' Accepts time samples invar and returns spectral magnitudes using Alglib's FFT routine
    ' Requires Alglib FFT, FTBAS, AND AP modules


    ' INPUT: invar can be either a single-column range or a nx1 array of type double

    ' If input is range, convert it to nx1 array of type double
    If TypeName(invar) = "Range" Then invar = invar.Value2

    If UBound(invar, 2) <> 1 Then MsgBox ("Error - need one-colum input")


    Dim Arm() As Double        ' A - same as input, except type "Real Matrix" ... i.e. nx1 array of type double  (2D)
    Arm = va2rm(invar)        ' store invar in Arm

    Dim Avec() As Double        ' Avec - same as A, except a vector of length n.l (1D... as required by Alglib's FFT routine
    Avec = rm2vec(Arm)        ' store Arm into Avec

    Dim N As Long        ' Number of points in input
    N = UBound(Avec)

    Dim ctr As Long
    Dim windowscale As Double

    Select Case window
        Case Is = "none"
          windowscale = 2 / N
        Case Is = "hanning"
          windowscale = 4 / N ' Not sure about this one
          For ctr = 1 To N
          'fnhanning(t)=(0.5 - 0.5*cos(2*pi*t/T2)
            Avec(ctr) = Avec(ctr) * (0.5 - 0.5 * Cos(2 * Application.WorksheetFunction.pi() * ctr / (N)))
            
          Next ctr
          
        Case Else
            MsgBox "error in window type"
    End Select

    ' F will hold the output complex vector (1D array of complex) from the Alglib FFT routine
    Dim F() As Complex        ' Will return the output vector of the FFT routine
    Call FFTR1D(ShiftIndexDoub(Avec, -1), N, F)

    ' f1 = same as F, with indeces shifted up by 1
    Dim f1() As Complex
    f1 = ShiftIndexComp(F, 1)

    ' f2 = same as f1, except will be stored as a nx1 array of type complex
    Dim f2() As Complex
    f2 = veccol2cm(f1)

    ' mags will hold the magnitudes
    Dim mags() As Double
    mags = cm_abs2rm(f2)

    ' Sale as required so that a sinusoid of peak magnitude 1 will show up as a 1 on the FFT output
    mags = rm_MulR(mags, windowscale)        ' SCALING
    ' mags(1, 1) = mags(1, 1) / 2        ' undo scaling of dc component in order to capture true dc component (even though it creates discontinuity in spectrum of heavily damped sinusoid)
    ' Scaling of dc component is tricky. Leave it scaled similar to others -> will give smooth spectrum

    ' output will hold the 1st-half portion of the magnitudes that are to be returned from the function
    Dim output() As Double
    output = rm_getsm(1, 1, Int(UBound(mags, 1) / 2), 1, mags)

    myfft = output
End Function

=====================================
(2B)+(2B)' ?

 

[electricpete]

Actually I have a suspicion the problem is not with the function but the way the function is linked to the named formula's and graphs. The reason is that an early version of my first spreadsheet worked in both 2000 and 2003 and only stopped working when I took out the data and replaced it with the named formula's (in 2000). Whatever problem that was was fixed when I took the older version of spreadsheet and rebuilt the named formula's in excel 2003.

Maybe I need to try to rebuild the spreadsheet again in excel 2007....

=====================================
(2B)+(2B)' ?

 

[IDS]

Just a thought. Could using "window" as a variable name be causing problems?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Yes, I just reached that conclusion. Changing "window" to "mywindow" eliminated crash on startup, but spreadsheet still seems not to work.

Attached is a file that will load in excel 2007 but seems not to work in 2007

=====================================
(2B)+(2B)' ?

 

[electricpete]

ok, the above attached file works, except gives a message about invalid reference.

If you delete the block of formula's in B11:B20, then I think it works without error. What's wrong with those formula's?

=====================================
(2B)+(2B)' ?

 

[electricpete]

Hmmmm. I undid the delete of those formulas and now everything works. Attached seems like it will work in 2007

=====================================
(2B)+(2B)' ?

 

[electricpete]

Well the 2007 file crashes in excel 2000. Haven't tried it in 2003.

Therefore:
use the file posted 24 Aug 10 21:39 for excel 2007.
use the file posted 24 Aug 10 11:43 for excel 2000, 2003.

=====================================
(2B)+(2B)' ?

 

[IDS]

Pete - I had a look through it and couldn't see anything obvious that would upset earlier versions of Excel. One thing you might try is making the Evaluate routine a function in VBA, rather than using the formula in the name. It's possible the dynamic names have some limits that might cause memory problems.

Also if you declare pi as a constant at the top of the module it will save a lot of worksheetfunction calls, which should speed things up a bit (especially in 2007).

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

If you declare pi as a constant, you will get a conflict with portions of Alglib which also declare pi as a constant.

=====================================
(2B)+(2B)' ?

 

[IDS]

It seems he has a PiNumber constant declared as private, which never seems to be used, and a Public Pi function. I'm not sure why he does that, but there is no reason why you shouldn't use the Pi function.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Yes, I certainly could improve on my use of pi although if it works...

There is a far more aggregious time waster.... the FFT routine gets called a number of times. I also have the 3 cells that give a specific harmonic in cells B18:B20. I think that calls the FFT routine 3 more times (!).

It is still pretty darned fast. Recalculation time is barely noticeable. What is noticeable and somewhat annoying to me is that it takes about 5 seconds to save (and I like to save multiple revisions).


=====================================
(2B)+(2B)' ?

 

[IDS]

If you are using 2007 try saving as an xlsb. It should be much faster and smaller than an xls.

I agree that the pi question is likely to be a side issue.

I think it might be worth hard coding one of the functions and getting rid of the Evaluate name as a trial though.

Also maybe limiting the number of points in the graph.

Anyway, time I was off to bed here

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/