Newton Excel Bach, not (just) an Excel Blog

The comments from Bill Harvey in the previous post prompted me to have a closer look at the functions available in Excel for working with vectors.  Here is a complete list of them:

=SUMPRODUCT() (Equivalent to the vector dot product)

This is a strange omission, even by Microsoft standards, considering that they have gone to the trouble of programming several esoteric mathematical functions such as the Bessel Function, but they haven’t provided the most basic support for operations on vectors.  It’s easily fixed with some User Defined Functions (UDFs) of course, so that is what this post examines.  The VectorFunc.xlsb spreadsheet includes full open source code for all the functions.  Many of the UDFs in this spreadsheet return results as an array function.  Refer to Using Array Formulas if you are not familiar with their use.  In the following post I will have a closer look at the application of vector methods to calculating areas and other geometric section properties.

In the course of researching this post I came across the Softsurfer site, which contains detailed descriptions of coordinate geometry related algorithms, including c++ code.  Several of their vector related functions have been converted to VBA and are included in the download spreadsheet.

The first step was to write a routine to convert different input types (i.e. horizontal or vertical spreadsheet ranges, or 1D or 2D arrays of doubles or variants) into a uniform format for processing by the vector functions with minimum overhead.  That is what the code below does:

Function GetVect(Vect1 As Variant, ByRef Vect2() As Double) As Variant
Dim Num1 As Long, NumDims1 As Long
Dim i As Long, j As Long, Orient As Long

If TypeName(Vect1) = "Range" Then Vect1 = Vect1.Value2

Num1 = UBound(Vect1) - LBound(Vect1) + 1
NumDims1 = NumDims(Vect1)

If NumDims1 = 2 Then
 Orient = 1
If Num1 = 1 Then
Num1 = UBound(Vect1, 2) - LBound(Vect1, 2) + 1
 Orient = 2
 End If
 End If
ReDim Vect2(1 To Num1)
 j = 1
If NumDims1 = 1 Then

For i = LBound(Vect1) To UBound(Vect1)
Vect2(j) = Vect1(i)
j = j + 1
Next i
ElseIf NumDims1 = 2 Then
 If Orient = 1 Then
For i = LBound(Vect1) To UBound(Vect1)
Vect2(j) = Vect1(i, 1)
j = j + 1
Next i
 Else
For i = LBound(Vect1, 2) To UBound(Vect1, 2)
Vect2(j) = Vect1(1, i)
j = j + 1
Next i
 End If
 Else
GetVect = CVErr(xlErrNA)
 End If

GetVect = Num1

End Function
 

The steps in GetVect are:

• If the input vector (Vect1) is a range, convert it into a Variant array
• Find the size of the first dimension in Vect1
• Find the number of dimensions of Vect1 (calling the NumDims function)
• If Vect1 has 2 dimensions, check the orientation, assuming “vertical” if it has two or more rows, or “horizontal” if it has 1 row.
• If Vect1 is horizontal, find the number of columns
• Redimension the output array, Vect2, as a base 1, 1D array.
• Assign the values of Vect1 to Vect2
• Assign the number of values in Vect2 to the function return value

Note that the function returns the size of the array Vect2, but because Vect2 was passed as an empty double array “by reference”, its contents are available to the calling procedure.

The GetVect function is used in the Dot and Cross functions, which in turn call Dot2, Dot3, Cross2 or Cross3 as appropriate.  Note that if these functions are to be used direct from the spreadsheet they must be called through the Dot or Cross functions, which will peform the necessary conversion of the spreadsheet range into a 1D array of doubles.

In addition to Dot and Cross the spreadsheet contains the following vector related functions (optional function arguments are shown in italics):

• Length(Vect1, Vect2) returns the length of a vector from the origin if only Vect1 is specified, or the length between Vect1 and Vect2 if both are specified.
• isLeft(P1, P2, P3) tests if P3 is left or Right of the line through P1 and P2.  The magnitude of the return value is also twice the area of the triangle specified by P1, P2, P3.  Note that isLeft cannot be called as a UDF.
• Orientation2D_Triangle(P1, P2, P3) calls isLeft, and returns the same value.  It may be called as a UDF.
• Area2D_Triangle(P1, P2, P3) calls Orientation2D_Triangle, and divides the result by -2, returning the area of the triangle P1P2P3, with a positive area when the points are specified in a clockwise direction.
• Area2D(Pointrange) returns the area of a 2D polygon with any number of points.  The points are assumed to be arranged in a range of n rows x 2 columns (X and Y values), with the last point being the same as the first.
• Area3D(Pointrange) returns the area of a 3D polygon in a plane with any number of points.  The points are assumed to be arranged in a range of n rows x 3 columns (X, Y and Z values), with the last point being the same as the first.  If the points are not on a common plane it will return the area of the polygon projected onto the plane defined by the first three points.
• UnitNorm(Pointrange) returns the unit normal to a plane specified by the 3D points in Pointrange.  The points are assumed to be arranged in a range of n rows x 3 columns (X, Y and Z values)

Examples of the use of these functions are shown in the screenshots below.  Download VectorFunc.xlsb for more details and open source code.

Vector dot and cross products

3D cross product and length

Vector orientation, Area and Length