XL Fourier function?

[N1GHTEYES]

I'd like to be able to return an FFT as an array function.
I'd also like to use the fourier functionality in the ATPVBAEN.XLA add-in. Unfortunately, The only two Fourier-related items in there seem to be Fourier and FourierQ - both of which are subs and both of which take ranges as arguments. They also seem to want actual ranges - not simply a dummy variable declared as a range. What's more, they seem to insist on changing the supplied output range - so I know I can't use that in a function, since XL doesn't like that kind of thing.

I could call the FFT from another library but that means I'd also have to ensure that users had the library available and properly referenced too - which is a bit of a pain, or I could use my own FFT hand-coded version, but that is a bit slow.

So, does anyone know of a way round this problem? Is there a way of calling a (undocumented?) function version of Fourier - or a sub which uses a variant or array rather then a range? Even a pointer to some decent help for ATPVBAEN.XLA would be useful.

 

[PHV]

http://support.microsoft.com/?id=143466

http://support.microsoft.com/?id=128185

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[macropod]

Hi N1GHTEYES,

When you call Excel's Fourier Analysis Add-in, you can specify both the input and output ranges:

Application.Run "ATPVBAEN.XLA!Fourier", ActiveSheet.Range("A1:A128"), ActiveSheet.Range("I1:I128"), False, False

where:
A1:A100 = Data Records (Input)
I1:I100 = Imaginary FFT data (Output)

I realise this doesn't overcome the issue you have with being unable to use a dummy variable declared as a range, but it does allow you to choose the output range.

Cheers

 

[N1GHTEYES]

Thanks guys, but unfortunately neither of the above helps too much. I had the help files - they just don't tell me anything that helps solve the problem. I can run an FFT from ATPVBAEN.XLA as a macro - that's not a problem. The problem is I want to be able to run it as part of a function, which returns the FFT of the source data, and which can be implemented as an array formula. One of the advantages of doing so is that it changes "live" as the data changes, and another being that I'm trying to put together a set of useful user functions for some of my colleagues who use XL, but are not as familiar with it.

I was hoping someone might know of some undocumented calls to FFT routines or some clever way of persuading the ATPVBAEN routine to accept a dummy range, rather than itself insisting on writing to a sheet (which can't be done inside an XL function).

Oh well, I guess I'll just have to use my long-hand version.
BTW if that is of any use to anyone, reply to this thread and I'll post it.