Macro Questions: Returning multiple results, description of parameter syntax et al.

[Frederik Romanov]

<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?

3) Why does Application.Sqrt( @) not work, whereas Application.Power(
@, 0.5) does?

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

See the help for Functions
The arglist argument has the following syntax and parts:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [=
defaultvalue]

I would like very much to read the full description of this syntax.

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.


3) Why does Application.Sqrt( @) not work, whereas Application.Power(
@, 0.5) does?
-------------------------------------------
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>

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.

No, much simpler than this.

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

 

[Tom Ogilvy]

1) Nothing useful found in Excel online help for "Function" and all
its sub-categories

Information I gave was copied from the help file - not sure what you are
looking at. Option argument is well explained in the help and I gave you
the help example as well.

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

Public function MyFunction( arg as Range) as Variant
Dim results()
Redim results( 1 to arg.rows.count, 1 to arg.columns.count)
for i = 1 to arg.rows.count
for j = 1 to arg.columns.count
Results(i,j) = i*j
next
next
MyFunction = Results

Sub Tester1()
dim i as long, j as long, sStr as string, mvarr as variant
mvarr = MyFunction(Range("A1:B12")
for i = lbound(mvarr,1) to ubound(mvarr,1)
for j = ubound(mvarr,2) to ubound(mvarr,2)
sStr = sStr & mvarr(i,j) & ", "
next
debug.print sStr
sStr = ""
Next
End Sub


3) Why does Application.Sqrt( @) not work, whereas Application.Power(
@, 0.5) does?

All worksheet functions are not available in VBA, especially those that are
duplicated in VBA, such as sqrt

? sqr(500)
22.3606797749979

? 500^0.5
22.3606797749979

^ is an operator, there is no power function in VBA.



4) How would one specify multiple area ranges in an argument, and what
are these used for?

Range("A1,B9,G11:G31,N34:N56")

? Range("A1,B9,G11:G31,N34:N56").Address
$A$1,$B$9,$G$11:$G$31,$N$34:$N$56
? Range("A1,B9,G11:G31,N34:N56").Areas.Count
4

5) How can one measure computational effort, in terms of floating
point operations and computation time?

See Charles Williams site:
http://www.decisionmodels.com

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

A formula can only return values to the cell in which it is located. It
can't assign a value to another cell.

I don't really understand your example, but you should be able to use simple
cell addresses to reference other cells.