<Excel-97 (SR-2)>

Thanks a lot to all for the very useful replies to my previous posts,

especially Tom.

So I have made use of a lot of the replies, now I am afraid I am

stumped on the finish line with my current macro. As is my usual wont,

I have found a work-around, but I prefer to do things properly. So I

would very much appreciate the usual high standard of replies.

Folks, this is a long post (these items are all connected to some

degree, but more than that, I suspect one person (hopefully Tom) will

respond to all, so we can keep the posting bandwidth down a bit).

*** PLEASE *** do not copy the entire transcript of this post into

replies, this is such a waste of bandwidth.

I don't mean to treat this as a free excel support service, and try my

best to capitalize on the very useful posted results by means of

a) Online help in Excel

b) My out of date version 4 hardcopy of Excel Function reference

c) The odd macro I have downloaded from google or Ozgrid ,

however these are limited in usefulness - hence this post.

Immediately following is the short list of questions, I will elaborate

on them below with some positive feedback on how I have used previous

replies.

1) Nothing useful found in Excel online help for "Function" and all

its sub-categories

2) How can one return multiple results from a Macro?

4) How would one specify multiple area ranges in an argument, and what

are these used for?

5) How can one measure computational effort, in terms of floating

point operations and computation time?

6) I will return to "Cell formula or macro to write result of one cell

to another cell"

1) Nothing useful found in Excel online help for "Function" and all

its sub-categories

-------------------------------------------

Tom, I could not find anything useful in response to you suggestion

I would like very much to read the full description of this syntax.Quote:> See the help for Functions

> The arglist argument has the following syntax and parts:

> [Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [=

> defaultvalue]

I have looked in all the subitems in "Functions" and see nothing like

your description there.

2) How can one return multiple results from a Macro?

-------------------------------------------

This is what I have implemented :

Public Function DFT(harmonic, data As Range, ByRef results As Range,

Optional period = 1)

....

results(1).Value = dftCos

results(2).Value = dftSin

results(3).Value = DFT

results(4).Value = harmonic / period

VBA spits the dummy on "results(1).Value = "

So how do I get four results out of this routine ?

Currently I am doing this very wastefully, copying the whoe routine

into variants to get out one result at a time.

-------------------------------------------

Why does the first line not work but the second does?

DFT = Application.Sqrt( dftCos *dftCos +dftSin *dftSin)

DFT = Application.Power(dftCos *dftCos +dftSin *dftSin, 0.5)

I am concerned that other functions won't work for which there is no

easy workaround. There is no concept of including libraries as their

is for .h files in C/C++ or ??? files in html, is there?

4) How would one specify multiple area ranges in an argument, and what

are these used for?

-------------------------------------------

I like the error handling suggestions, so I implemented

If (data.Areas.Count <> 1) Then

DFT = "ERROR{DFT}: Can only have one linear data range for Arg:2"

GoTo errorHandler

End If

If (data.Count < 1) Then

DFT = "ERROR{DFT}: Arg:2 needs at least one element"

GoTo errorHandler

End If

So I thought I would test this out, and invoked

[H16] = dft_Cos( 1, G31:G542 h1:h2, e5:h5, 1)

Excel just gave up and committed hara-kiri on this.

So how does one specify multiple ranges and what are they used for?

5) How can one measure computational effort, in terms of floating

point operations and computation time?

-------------------------------------------

I like to think I have written an efficient DFT routine (for my

application I reckon it is faster than most FFTs). How can one

measure the computation required and the execution time within excel?

Previously I have tried inserting now() commands all over the

worksheet to hope that I would catch two at different points in the

execution sequence, but they all return the same time.

6) I will return to "Cell formula or macro to write result of one cell

to another cell"

-------------------------------------------

TOM>

No, much simpler than this.Quote:> Hard to understand what you want, but if you want A15 to tell the function

> where to get the value for the argument

> [A15] holds the string "B30:B40" (no quotes)

> =Sum(Indirect(A15))

> would sum the values found in B30:B40.

> Perhaps that is what you are referring to.

This is not far off from a live example I have, the simple example is

easier to follow:

[A2] = 1 [B2] = 5 [C2] = 6

[A3] = sqrt( max( 0, b2*b2-4*a2*c2))

[A4] = -b2/2 +a3 [B4] = -b2/2 -a3

Follow, so we have the A4 and B4 giving us the real part of the roots

of the quadratic with parameters in A2:C2.

The roots for these parameters ar -2 and -3.

Now suppose we want to see if it can find the roots e=Exp(1) and pi()

from a quadratic. I don't want to manually calculate the quadratic

parameters, or even calculate them in another cell and paste them into

B2 and C2, as I want to see where the parameters came from. I would

like to have

[A1] = Exp(1) [B1] = Pi()

and then the crucial

[C1] = Assign( A2, 1)

[D1] = Assign( B2, -a1 -b1)

[E1] = Assign( C2, a1 *b1)

So that A2, B2 and C2 receive the values calculated elsewhere.

Then later on I could either manually put values back into A2:C2, or

use further assign statements elsewhere in the sheet, to put insert

different values for which I want the quadratic solution.

I know this raises precedence issues, but if Excel is implemented in a

sequential language, there may be some hope of some way of doing this.

Muchas Gratias,

Fred