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.

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. (a1, a2 and a3 are vector components of a, and b1, b2, b3 are vector components of b.)

Vector a and vector b

(Note: some people may be more used to reading ax, ay and az 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”):

Product of vectors a and b

Next, the matrix can be expanded with Sarrus’s Rule into:

Expanded matrix

Lastly, we further expand it with cofactor expansion:

Matrix expanded with cofactor exapnsion

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:

  1. The first group (x) is  23 minus 32
  2. The 2nd group (y) is adding 1 to each number: 31 minus 13
  3. 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.

scalers and vectors for cross product

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

Vector for excel setup

See in the picture below the Excel formula in cells C7, D7 and E7 are set up:

Example 1 Excel setup

The result looks like this:

Cross product results in Ecel

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:

  1. a2b3 – a3b2
  2. a3b1 – a1b3
  3. a1b2 – a2b1
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.
VBA cross product function being used as input in Excel

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

Setting the orientation of the cross product via the custom vba function parameter

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

Leave a Reply

Your email address will not be published. Required fields are marked *