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.
Pingback: Daily Download 25: Vector Functions | Newton Excel Bach, not (just) an Excel Blog
any chance of an example with unitnorm?
I’ll have to look into it!
The UnitNorm function is used in the Area3D function, and is included in the SoftSurfer module in my VBA code, which is code translated from C++ at the SoftSurfer.com site. Searching this site I found:
which includes the “UnitNorm” code inside their area3D_Polygon code, but they don’t have a function called UnitNorm. It looks like I have extracted the UnitNorm function from their code myself, and looking at it now I don’t think “UnitNorm” is the right name, since the length of the vector it returns is not 1.
It looks like the function returns a vector that is normal to a plane defined by the 3D coordinates in “pointrange”, but I’ll look into it further, and if necessary correct the terminology and post an example.
Pingback: The angle between two vectors, in 2D or 3D | Newton Excel Bach, not (just) an Excel Blog
Can you check the link to the spreadsheet? The zip file does not contain a spreadsheet.
Binh – it’s not actually a zip file, it’s an xlsb file. It’s possible that it has been renamed in the download. Try renaming the download file to vectorfunc.xlsb (without unzipping) and see if that works. If not, I have uploaded a version that is in a zip file at:
If you download that and unzip it it should work as long as your Excel will read 2007 version files.
Cross products apply to 3×3 matrices. The off-diagonal elements of an adjoint matrix are cross products. An adjoint matrix equals the product of the inverse matrix (minverse) and the determinant (mdeterm). Should work.
Can you give us more detail about what you are suggesting.
A tutorial on inverting 3 by 3 matrices with cross products
By Cedrick Collomb
Click to access A%20tutorial%20on%20inverting%203%20by%203%20matrices%20with%20cross%20products.pdf
Thanks Alfred, excellent article.
I also realised I haven’t linked to your blog, so I’ll fix that.
Happy to share.