User Defined Array Function

[Pflugs]

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

 

[Jim Thomlinson]

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.

 

[Bernie Deitrick]

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]

Oh yeah. I forgot about that. Shoot.

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

Thanks,
Pflugs

 

[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