Question: Passing a range to a macro and using this argument

Question: Passing a range to a macro and using this argument

Post by Frederik Roman » Wed, 09 Jul 2003 20:43:20



Excel-97 (SR-2)

I would like to write a macro (CrossProduct) to achieve the following

        [G15:Fourier Coefficient] = CrossProduct( A20:A2067, G20:G2067) /G14

presumably to look something like

public function CrossProduct( range1, range2)
 dim iCos, result
 result = 0
 for iCos = 0: Len( range1)-1
  result += range1[ iCos] *range2[ iCos]
 end for
 CrossProduct = result
End Function

BTW, I am doing something equivalent to this (well not precisely
correct since I have not corrected with the Sqrt( N/(N-1))^2 factor
cleaning up after STDEV introduces), but it is slow and tedious:

        [G15:Fourier Coefficient] = Correl( A20:A2067, G20:G2067)
                               * Stdev(A20:A2067) *Stdev(G20:G2067)
/G14

but I need the method for accessing a cell range for plenty of other
applications.

I have no idea how to find a thread to this topic in the online help,
hence the post.

TIA,
Fred.

 
 
 

Question: Passing a range to a macro and using this argument

Post by Tom Ogilv » Wed, 09 Jul 2003 23:01:26


public function CrossProduct( range1 as range, range2 as range)

CrossProduct = range1.Address & " - " & range2.Address
End Function

Usage
=CrossProduct( A20:A2067, G20:G2067)

len(range1)  will produce an error - it is meaningless with a multicell
range.

range1.count  instead.

Function Crossproduct(range1 as Range, range2 as Range)
Dim iCos as Long, result as double
Crossproduct = "invalid inputs"
if range1.Areas.Count = 1 and range2.Areas.count = 1 then
 If range1.count = range2.count then
  for   iCos = 1 to range1.count
    result = result + range1( iCos).Value * range2(iCos).Value
   Next
 End if
End if
CrossProduct = result
End Function

Note that the build in SUMPRODUCT performs this function.

Regards,
Tom Ogilvy


Quote:> Excel-97 (SR-2)

> I would like to write a macro (CrossProduct) to achieve the following

> [G15:Fourier Coefficient] = CrossProduct( A20:A2067, G20:G2067) /G14

> presumably to look something like

> public function CrossProduct( range1, range2)
>  dim iCos, result
>  result = 0
>  for iCos = 0: Len( range1)-1
>   result += range1[ iCos] *range2[ iCos]
>  end for
>  CrossProduct = result
> End Function

> BTW, I am doing something equivalent to this (well not precisely
> correct since I have not corrected with the Sqrt( N/(N-1))^2 factor
> cleaning up after STDEV introduces), but it is slow and tedious:

> [G15:Fourier Coefficient] = Correl( A20:A2067, G20:G2067)
>                                * Stdev(A20:A2067) *Stdev(G20:G2067)
> /G14

> but I need the method for accessing a cell range for plenty of other
> applications.

> I have no idea how to find a thread to this topic in the online help,
> hence the post.

> TIA,
> Fred.


 
 
 

1. Question: Macro overloading, passing variable number of arguments

Excel-97 (SR-2)

In general I would like to write macros that can be overloaded, for
example Hyperlink() has an optional displaytext field.

        HyperLink( Filename)
        HyperLink( Filename, DisplayText)

Please could someone post a short, useful macro that does such a
thing, rather than give a description of how to do so.

I have searched the online help for "overloading" and "arguments" but
nothing useful is shown there.

TIA,
Fred.

2. Login script problems with 9x clients

3. pass a conditional expression as macro argument?

4. Use PPC 2002 SDK?

5. Array name passed to macro as argument?

6. Garage Sale: Part 1

7. v8.2 macro question - passing the contents of a data step variabl e to a macro

8. v8.2 macro question - passing the contents of a data step var iable to a macro

9. Simple question: ranges as VB function arguments?

10. General Question on passing Arguments in .NET Console Application

11. Lotus 4.01 : Using a preselected range in a macro.

12. user input to macro to be used as part of range specifier