I've tried to do my homework, reading Walkenbach and searching the net for
answers before posting this. I'd appreciate any advice on improving this.

I'm trying to help someone do a little project handling arrays.

First:

I found an example implementing the Cross Product function in
the Microsoft techbase.

Option Base 1
'based on http://support.microsoft.com/default...b;en-us;121820
'Usage: select C1:C3, type =C(A1:A3,B1:B3) and then <ctrl<shift<enter
Function Cross(Vec1 As Object, Vec2 As Object) As Variant
Dim TempArray(3, 1)
TempArray(1, 1) = Vec1.Cells(2, 1).Value * Vec2.Cells(3, 1).Value - Vec1.Cells(3, 1).Value * Vec2.Cells(2, 1).Value
TempArray(2, 1) = Vec1.Cells(3, 1).Value * Vec2.Cells(1, 1).Value - Vec1.Cells(1, 1).Value * Vec2.Cells(3, 1).Value
TempArray(3, 1) = Vec1.Cells(1, 1).Value * Vec2.Cells(2, 1).Value - Vec1.Cells(2, 1).Value * Vec2.Cells(1, 1).Value
Cross = TempArray
End Function

The Microsoft example uses .Value everywhere but no other examples
I've seen use this

'Well, that seems to work, so try a dot or inner product of two vectors
Function Dot(Vec1 As Object, Vec2 As Object) As Double
' Dot = Vec1.Cells(1, 1).Value * Vec2.Cells(1, 1).Value + Vec1.Cells(2, 1).Value * Vec2.Cells(2, 1).Value + Vec1.Cells(3, 1).Value * Vec2.Cells(3, 1).Value
Dot = Vec1.Cells(1, 1) * Vec2.Cells(1, 1) + Vec1.Cells(2, 1) * Vec2.Cells(2, 1) + Vec1.Cells(3, 1) * Vec2.Cells(3, 1)
End Function

'And try to find the Norm or length of a vector
Function Norm(Vec1 As Object) As Double
' Norm = Sqr(Vec1.Cells(1, 1).Value * Vec1.Cells(1, 1).Value + Vec1.Cells(2, 1).Value * Vec1.Cells(2, 1).Value + Vec1.Cells(3, 1).Value * Vec1.Cells(3, 1).Value)
Norm = Sqr(Vec1.Cells(1, 1) * Vec1.Cells(1, 1) + Vec1.Cells(2, 1) * Vec1.Cells(2, 1) + Vec1.Cells(3, 1) * Vec1.Cells(3, 1))
End Function

I've tested it with and without .Value and they seem to give the same
result. Is there some good explanation about when this is needed and
when it isn't? And are there any other simplifications I could make
to these definitions?

Next:

What I really need to do is use these functions within each other at
times and I can't figure out why that isn't working.

For example, if I
select C1:C3 and then type =Cross(A1:A3,B1,B3)<ctrl<shift<enter
it works. But if I
select C1:C3 and then type =Cross(Cross(A1:A3,B1:B3),B1:B3))<ctrl<shift<ent er
it doesn't give the cross product of the cross and B1:B3.

I thought Cross was returning an array that could then be used to
fill a range of cells OR be used just like an array of cells to
be given to a function, like cross, that accepts arrays of cells.

Thanks

value is the default property for a range, so it *usually* doesn't matter if
you leave it out. However, it is good practise to include it.

Can't help with the second part.

Tim



"Don Taylor" wrote in message
...
I've tried to do my homework, reading Walkenbach and searching the net for
answers before posting this. I'd appreciate any advice on improving this.

I'm trying to help someone do a little project handling arrays.

First:

I found an example implementing the Cross Product function in
the Microsoft techbase.

Option Base 1
'based on http://support.microsoft.com/default...b;en-us;121820
'Usage: select C1:C3, type =C(A1:A3,B1:B3) and then <ctrl<shift<enter
Function Cross(Vec1 As Object, Vec2 As Object) As Variant
Dim TempArray(3, 1)
TempArray(1, 1) = Vec1.Cells(2, 1).Value * Vec2.Cells(3, 1).Value -
Vec1.Cells(3, 1).Value * Vec2.Cells(2, 1).Value
TempArray(2, 1) = Vec1.Cells(3, 1).Value * Vec2.Cells(1, 1).Value -
Vec1.Cells(1, 1).Value * Vec2.Cells(3, 1).Value
TempArray(3, 1) = Vec1.Cells(1, 1).Value * Vec2.Cells(2, 1).Value -
Vec1.Cells(2, 1).Value * Vec2.Cells(1, 1).Value
Cross = TempArray
End Function

The Microsoft example uses .Value everywhere but no other examples
I've seen use this

'Well, that seems to work, so try a dot or inner product of two vectors
Function Dot(Vec1 As Object, Vec2 As Object) As Double
' Dot = Vec1.Cells(1, 1).Value * Vec2.Cells(1, 1).Value + Vec1.Cells(2,
1).Value * Vec2.Cells(2, 1).Value + Vec1.Cells(3, 1).Value * Vec2.Cells(3,
1).Value
Dot = Vec1.Cells(1, 1) * Vec2.Cells(1, 1) + Vec1.Cells(2, 1) *
Vec2.Cells(2, 1) + Vec1.Cells(3, 1) * Vec2.Cells(3, 1)
End Function

'And try to find the Norm or length of a vector
Function Norm(Vec1 As Object) As Double
' Norm = Sqr(Vec1.Cells(1, 1).Value * Vec1.Cells(1, 1).Value +
Vec1.Cells(2, 1).Value * Vec1.Cells(2, 1).Value + Vec1.Cells(3, 1).Value *
Vec1.Cells(3, 1).Value)
Norm = Sqr(Vec1.Cells(1, 1) * Vec1.Cells(1, 1) + Vec1.Cells(2, 1) *
Vec1.Cells(2, 1) + Vec1.Cells(3, 1) * Vec1.Cells(3, 1))
End Function

I've tested it with and without .Value and they seem to give the same
result. Is there some good explanation about when this is needed and
when it isn't? And are there any other simplifications I could make
to these definitions?

Next:

What I really need to do is use these functions within each other at
times and I can't figure out why that isn't working.

For example, if I
select C1:C3 and then type =Cross(A1:A3,B1,B3)<ctrl<shift<enter
it works. But if I
select C1:C3 and then type
=Cross(Cross(A1:A3,B1:B3),B1:B3))<ctrl<shift<ent er
it doesn't give the cross product of the cross and B1:B3.

I thought Cross was returning an array that could then be used to
fill a range of cells OR be used just like an array of cells to
be given to a function, like cross, that accepts arrays of cells.

Thanks

Hi Don,

What I really need to do is use these functions within each other at
times and I can't figure out why that isn't working.

For example, if I
select C1:C3 and then type =Cross(A1:A3,B1,B3)<ctrl<shift<enter
it works. But if I
select C1:C3 and then type =Cross(Cross(A1:A3,B1:B3),B1:B3))<ctrl<shift<ent er
it doesn't give the cross product of the cross and B1:B3.

I thought Cross was returning an array that could then be used to
fill a range of cells OR be used just like an array of cells to
be given to a function, like cross, that accepts arrays of cells.

The problem is that the code sample you're working from is only designed to handle
ranges as arguments, not general arrays, and only 3x1 arrays as well. To write the
formula properly, you need code to do the following:

1. Check the type of the first input variable
2. If it's an array, make sure it's a 2D array, converting it if not.
3. If it's a range, check it's only one area and more than one cell
(and maybe that it's a specific size).
4. If the range is an OK size and shape, read the values into an array (using
vArr1 = TheRange.Value)
5. Repeat 1-4 for the second parameter
6. You now have two 2D arrays, so you have to check they're the same size and
contain numbers (not text).
7. Finally loop through the arrays using LBound() and UBound() to get their
dimensions, to calculate the result.


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk