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

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

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

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.

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.