I would like to write a function that calculates the cross product of two 3D
vectors (that is, x1,y1,z1 and x2,y2,z2) The output of a cross product is
also a vector (that is, x3,y3,z3).

The math is simple enough, but I would like to export each variable to a
separate cell. That is, I select three linear cells in a row, type
"=CROSS(A1:A3)", hit CSE, and the value appears in each cell.

Could someone please advise on how to distribute the results to each cell?
The code to calculate x3,y3, z3 is below:

x3 = y1 * z2 - z1 * y2
y3 = z1 * x2 - x1 * z2
z3 = x1 * y2 - y1 * x2

The call line should be:

Function Cross(x1, y1, z1, x2, y2, z2) as Double

The inputs should not be set to a type since the user should be allowed to
enter either ranges or numbers.

Thanks,
Pflugs

A UDF can only modify the value of the cell that it is in. It can not modify
the values of other cells, nor can it manipulate the formats of any cells
inlcuding the one that it is in. If not called as a UDF from within a sheet
then it can manipulate other cells including the value and the format... But
NOT as a UDF.
--
HTH...

Jim Thomlinson


"Pflugs" wrote:

I would like to write a function that calculates the cross product of two 3D
vectors (that is, x1,y1,z1 and x2,y2,z2) The output of a cross product is
also a vector (that is, x3,y3,z3).

The math is simple enough, but I would like to export each variable to a
separate cell. That is, I select three linear cells in a row, type
"=CROSS(A1:A3)", hit CSE, and the value appears in each cell.

Could someone please advise on how to distribute the results to each cell?
The code to calculate x3,y3, z3 is below:

x3 = y1 * z2 - z1 * y2
y3 = z1 * x2 - x1 * z2
z3 = x1 * y2 - y1 * x2

The call line should be:

Function Cross(x1, y1, z1, x2, y2, z2) as Double

The inputs should not be set to a type since the user should be allowed to
enter either ranges or numbers.

Thanks,
Pflugs

Pflugs,

Function Cross( _
x1 As Double, _
y1 As Double, _
z1 As Double, _
x2 As Double, _
y2 As Double, _
z2 As Double) As Variant
Dim myArray(1 To 3) As Double
myArray(1) = y1 * z2 - z1 * y2
myArray(2) = z1 * x2 - x1 * z2
myArray(3) = x1 * y2 - y1 * x2
If Application.Caller.Rows.Count = 1 Then
Cross = myArray
Else
Cross = Application.Transpose(myArray)
End If

End Function

Called like

=Cross(1,2,3,4,5,6)

entered using Ctrl-Shift-Enter after selecting 3 cells.

HTH,
Bernie
MS Excel MVP


"Pflugs" wrote in message
...
I would like to write a function that calculates the cross product of two
3D
vectors (that is, x1,y1,z1 and x2,y2,z2) The output of a cross product is
also a vector (that is, x3,y3,z3).

The math is simple enough, but I would like to export each variable to a
separate cell. That is, I select three linear cells in a row, type
"=CROSS(A1:A3)", hit CSE, and the value appears in each cell.

Could someone please advise on how to distribute the results to each cell?
The code to calculate x3,y3, z3 is below:

x3 = y1 * z2 - z1 * y2
y3 = z1 * x2 - x1 * z2
z3 = x1 * y2 - y1 * x2

The call line should be:

Function Cross(x1, y1, z1, x2, y2, z2) as Double

The inputs should not be set to a type since the user should be allowed to
enter either ranges or numbers.

Thanks,
Pflugs

Oh yeah. I forgot about that. Shoot.

Well, that's another drawback to UDFs, then.

Thanks,
Pflugs

"Jim Thomlinson" wrote:

A UDF can only modify the value of the cell that it is in. It can not modify
the values of other cells, nor can it manipulate the formats of any cells
inlcuding the one that it is in. If not called as a UDF from within a sheet
then it can manipulate other cells including the value and the format... But
NOT as a UDF.
--
HTH...

Jim Thomlinson


"Pflugs" wrote:

I would like to write a function that calculates the cross product of two 3D
vectors (that is, x1,y1,z1 and x2,y2,z2) The output of a cross product is
also a vector (that is, x3,y3,z3).

The math is simple enough, but I would like to export each variable to a
separate cell. That is, I select three linear cells in a row, type
"=CROSS(A1:A3)", hit CSE, and the value appears in each cell.

Could someone please advise on how to distribute the results to each cell?
The code to calculate x3,y3, z3 is below:

x3 = y1 * z2 - z1 * y2
y3 = z1 * x2 - x1 * z2
z3 = x1 * y2 - y1 * x2

The call line should be:

Function Cross(x1, y1, z1, x2, y2, z2) as Double

The inputs should not be set to a type since the user should be allowed to
enter either ranges or numbers.

Thanks,
Pflugs

Bernie,

Thank you so unbelievably much. The ability to program UDFs as UDAFs opens
an lot of doors for me. I will be using them to create multiple vector
functions in 3D.

What awesome syntax. Thanks again for your help.
Pflugs

"Bernie Deitrick" wrote:

Pflugs,

Function Cross( _
x1 As Double, _
y1 As Double, _
z1 As Double, _
x2 As Double, _
y2 As Double, _
z2 As Double) As Variant
Dim myArray(1 To 3) As Double
myArray(1) = y1 * z2 - z1 * y2
myArray(2) = z1 * x2 - x1 * z2
myArray(3) = x1 * y2 - y1 * x2
If Application.Caller.Rows.Count = 1 Then
Cross = myArray
Else
Cross = Application.Transpose(myArray)
End If

End Function

Called like

=Cross(1,2,3,4,5,6)

entered using Ctrl-Shift-Enter after selecting 3 cells.

HTH,
Bernie
MS Excel MVP


"Pflugs" wrote in message
...
I would like to write a function that calculates the cross product of two
3D
vectors (that is, x1,y1,z1 and x2,y2,z2) The output of a cross product is
also a vector (that is, x3,y3,z3).

The math is simple enough, but I would like to export each variable to a
separate cell. That is, I select three linear cells in a row, type
"=CROSS(A1:A3)", hit CSE, and the value appears in each cell.

Could someone please advise on how to distribute the results to each cell?
The code to calculate x3,y3, z3 is below:

x3 = y1 * z2 - z1 * y2
y3 = z1 * x2 - x1 * z2
z3 = x1 * y2 - y1 * x2

The call line should be:

Function Cross(x1, y1, z1, x2, y2, z2) as Double

The inputs should not be set to a type since the user should be allowed to
enter either ranges or numbers.

Thanks,
Pflugs

Hi. I added Bernie's excellent code to my library, but modified it slightly
so that it can be called from vba as well.

Function Cross( _
x1 As Double, _
y1 As Double, _
z1 As Double, _
x2 As Double, _
y2 As Double, _
z2 As Double) As Variant

Cross = Array(y1*z2-z1*y2,z1*x2-x1*z2,x1*y2-y1*x2)

If TypeName(Application.Caller) = "Range" Then
If Application.Caller.Rows.Count 1 Then _
Cross = WorksheetFunction.Transpose(Cross)
End If

End Function

Sub TestIt()
Dim v
v = Cross(1, 2, 3, 4, 5, 6)
End Sub

--
Dana DeLouis

"Pflugs" wrote in message
...
Bernie,

Thank you so unbelievably much. The ability to program UDFs as UDAFs
opens
an lot of doors for me. I will be using them to create multiple vector
functions in 3D.

What awesome syntax. Thanks again for your help.
Pflugs

"Bernie Deitrick" wrote:

Pflugs,

Function Cross( _
x1 As Double, _
y1 As Double, _
z1 As Double, _
x2 As Double, _
y2 As Double, _
z2 As Double) As Variant
Dim myArray(1 To 3) As Double
myArray(1) = y1 * z2 - z1 * y2
myArray(2) = z1 * x2 - x1 * z2
myArray(3) = x1 * y2 - y1 * x2
If Application.Caller.Rows.Count = 1 Then
Cross = myArray
Else
Cross = Application.Transpose(myArray)
End If

End Function

Called like

=Cross(1,2,3,4,5,6)

entered using Ctrl-Shift-Enter after selecting 3 cells.

HTH,
Bernie
MS Excel MVP


"Pflugs" wrote in message
...
I would like to write a function that calculates the cross product of
two
3D
vectors (that is, x1,y1,z1 and x2,y2,z2) The output of a cross product
is
also a vector (that is, x3,y3,z3).

The math is simple enough, but I would like to export each variable to
a
separate cell. That is, I select three linear cells in a row, type
"=CROSS(A1:A3)", hit CSE, and the value appears in each cell.

Could someone please advise on how to distribute the results to each
cell?
The code to calculate x3,y3, z3 is below:

x3 = y1 * z2 - z1 * y2
y3 = z1 * x2 - x1 * z2
z3 = x1 * y2 - y1 * x2

The call line should be:

Function Cross(x1, y1, z1, x2, y2, z2) as Double

The inputs should not be set to a type since the user should be allowed
to
enter either ranges or numbers.

Thanks,
Pflugs

Here's another general idea:

Function Cross(ParamArray v()) As Variant
'// 6 inputs only..
If UBound(v) + 1 < 6 Then
Cross = CVErr(xlErrRef)
Exit Function
End If

Cross = Array( _
v(1)*v(5)-v(2)*v(4), _
v(2)*v(3)-v(0)* v(5), _
v(0)*v(4)-v(1)*v(3))

If TypeName(Application.Caller) = "Range" Then
If Application.Caller.Rows.Count 1 Then
Cross = WorksheetFunction.Transpose(Cross)
End If
End If
End Function

--
Dana DeLouis

<snip