Cross Products with Excel and VBA
Microsoft Excel does not have the built-in worksheet functions to calculate the cross product of two vectors. Therefore, some techniques are required to do the calculation — either by setting up your own formula in a worksheet or by creating a custom VBA function. The article will walk you through both techniques.
Contents
Math Recap – Cross Products with 3D Components of Vectors
Let’s begin with a quick recap of the basics of the math operation for the multiplication of two vectors in a three-dimensional space.
We have two vectors a and b, where i, j, k are standard basis vectors. (a_{1}, a_{2} and a_{3} are vector components of a, and b_{1}, b_{2}, b_{3} are vector components of b.)
(Note: some people may be more used to reading a_{x}, a_{y} and a_{z} instead, which represent the vector components along the xyz axes. In this article, let’s sick with the 1-2-3 convention as it’ll make working in Excel easier.)
The product of vectors a and b can be presented in matrix form as below (also known as a “format determinant”):
Next, the matrix can be expanded with Sarrus’s Rule into:
Lastly, we further expand it with cofactor expansion:
Here, ((a2b3 – a3b2) , (a3b1 – a1b3) , (a1b2 – a2b1)) is actually the end point of the cross product vector.
In order to remember this sequence for calculating the 3 components for such an end point, you can memorize the mnemonics “xyzzy” or “12332”, which means:
- The first group (x) is 23 minus 32
- The 2nd group (y) is adding 1 to each number: 31 minus 13
- The 3rd group (z) is adding 1 to each number in the 2nd group: 12 minus 21
We are going to use this sequence shortcut during the development our VBA custom function a later section of this article.
Direction of resultant vector To find the direction of the resultant vector, you can use the right-hand rule. |
Another case of cross product is with the scalers (magnitudes) of vectors a and b, and the sine of the angle between a and b.
Because the calculation of this is very straightforward, this will not be covered in this article. We’ll concentrate of the calculation with vector components.
Download examples
First, download a sample workbook with the examples in this article.
(Please enable macros when using the file)
Vector Cross Product in Excel
We can set up our worksheet to calculate cross product with Excel formulas. Because Excel does not come with built-in functions for calculating cross products, we have to setup the calculations in the worksheet carefully.
Excel setup for scenario 1 – with 3D components of vectors
See in the picture below the Excel formula in cells C7, D7 and E7 are set up:
The result looks like this:
So, we have the answer of: a x b = -3i – 6j -3k
Cross Product with VBA
To avoid the complexity with setting up the formula in Excel every time you need to calculate the cross product, you can create your own VBA custom function.
The macro “CrossProduct
” below uses the mnemonics of “12332” (or “xyzzy”) with a For-Next loop to calculate the three vector components of the cross product.
The array “a” in the function stores the mnemonics in 3 groups of 4 items: “2332”, “3113” and “1221”, which are used to construct:
- a_{2}b_{3} – a_{3}b_{2}
- a_{3}b_{1} – a_{1}b_{3}
- a_{1}b_{2} – a_{2}b_{1}
Function CrossProduct(v1 As Variant, v2 As Variant) As Variant Dim i As Integer Dim x As Integer Dim a Dim answer(1 to 3) 'define a array with 3 slots from 1 to 3 'this array contains the sequence of vector components 'with 3 groups of 4 index numbers a = Array(2, 3, 3, 2, 3, 1, 1, 3, 1, 2, 2, 1) For i = 1 to 3 x = (i - 1) * 4 answer(i) = _ v1(a(0 + x)) * v2(a(1 + x)) - v1(a(2 + x)) * v2(a(3 + x)) Next CrossProduct = answer End Function
Because the function returns an array (instead of a single value), when applying it in the worksheet, we have to apply it as a Array Function. See in the picture below, range C7:E7.
- First, select range C7:E7
- Type =CrossProduct(C4:E4,C5:E5)
- Finish by pressing CTRL + SHIFT + ENTER.
(Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}
. Do not type these yourself. They will disappear when you edit the formula.)
The macro can be enhanced to give you an option to output the answer in either horizontal or vertical orientation.
In the enhanced VBA function below, an optional argument “orientation” has been added, with a default value of 1. A “Select Case” statement has also been added, so that:
- When orientation = 1, return answer in horizontal orientation
- When orientation = 2, return answer in vertical orientation
Function CrossProduct(v1 As Variant, v2 As Variant, _ Optional orientation As Integer = 1) As Variant Dim i As Integer Dim x As Integer Dim a Dim answer(1 to 3) 'define a array with 3 slots from 1 to 3 'this array contains the sequence of vector components 'with 3 groups of 4 index numbers a = Array(2, 3, 3, 2, 3, 1, 1, 3, 1, 2, 2, 1) For i = 1 to 3 x = (i - 1) * 4 answer(i) = _ v1(a(0 + x)) * v2(a(1 + x)) - v1(a(2 + x)) * v2(a(3 + x)) Next 'to control the orientation of output Select Case orientation Case 1 'default, horizontal CrossProduct = answer Case 2 'vertical CrossProduct = Application.Transpose(answer) End Select End Function
We can now apply the VBA function to return the answer in vertical orientation. In the screenshot below, we can see the value of 2 has been assigned to the last argument in the function.
Conclusion
In this article, we have discussed the calculation of the cross product of two vectors. The challenge actually also applies to other cases of matrix calculations, for instance, adjoint matrix, cofactor matrix, determinant, matrix multiplication, etc.
While Excel provides no built-in features for working with such type of mathematics, these can be tackled with formulas as well as VBA custom functions (together with array function input techniques).