VBA for Cross Products in Excel

Question

It seems that for some reason Excel programmers chose to omit any vector cross-product functionality.

Furthermore, looking online, there isn't really much demand for it. Although excel is a powerful tool to do linear algebra.

I need a VBA script to do Vector Cross Products. And the only one I could find was from here:

EDIT:

1. To add a VBA script, press Alt + F11
2. Under Project, right click on VBAProject and Insert->Module
3. Save, click No to save as a Macro-Enabled Workbook
4. Save as an .xlsm file

https://www.excelbanter.com/excel-worksheet-functions/209233-how-do-you-use-visual-basic-find-cross-product-two-vectors.html

--------------------
Function vCP(v1 As Variant, v2 As Variant) As Variant
vCP = Array(v1(2) * v2(3) - v1(3) * v2(2), _
v1(3) * v2(1) - v1(1) * v2(3), _
v1(1) * v2(2) - v1(2) * v2(1))
End Function
--------------------

Using it is simple,

1. select 3 horizontal adjacent cells, type in formula
2. =vCP(
3. Select vector A (in A x B) which is in either 3 consecutive horizontal or vertical cells
4. type ,
5. Select vector B, which is either 3 consecutive horizontal of vertical cells
6. type )
7. Press Ctrl+Shift+Enter

I did a couple tests on it, and it works, but it outputs a horizontal vector, not vertical, the way it's preferred for Linear Algebra.

Does anyone know how to change this script so the 3D vectors can be output vertically instead?

Is there a better way to get a cross product in Excel?

Thanks, -D

Answer 1

Use Application.Transpose:

Function vCP(v1 As Variant, v2 As Variant) As Variant

vCP = Application.Transpose(Array(v1(2) * v2(3) - v1(3) * v2(2), _
v1(3) * v2(1) - v1(1) * v2(3), _
v1(1) * v2(2) - v1(2) * v2(1)))
End Function