User Defined Array Function

G

Guest

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
 
G

Guest

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.
 
B

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
 
G

Guest

Oh yeah. I forgot about that. Shoot.

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

Thanks,
Pflugs
 
G

Guest

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
 
D

Dana DeLouis

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
 
D

Dana DeLouis

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top