Building Support for Vectors
Problem
You want to perform vector-based calculations , like vector cross and dot products, but can't find any vector functions in Excel.
Solution
Excel does not provide any built-in functions to perform vector calculations; however, you can add your own custom functions without too much effort.
Discussion
Since VBA allows you to write custom functions that can then be called directly from your Excel spreadsheet, you can easily add functions to perform vector calculations.
Figure 7-4 shows an example spreadsheet containing two vectors, u and v, whose x-, y-, and z-components are stored in separate cells.
Figure 7-4. Vector example
Cells D7 and E7 show the magnitudes of these vectors. These magnitudes are computed using a custom function called v_Mag. The syntax for v_Mag is =v_Mag(cell range), where cell range is a cell reference consisting of three cells containing the vector components, x, y, and z. Example 7-1 shows the VBA code for v_Mag. (See Recipe 2.2 for more information on writing VBA functions.)
Example 7-1. v_Mag
|
Public Function v_Mag(v As Range) As Double Dim x As Double Dim y As Double Dim z As Double x = v.Cells(1).Value y = v.Cells(2).Value z = v.Cells(3).Value v_Mag = Sqr(x ^ 2 + y ^ 2 + z ^ 2) End Function |
The only parameter for this function is a cell range, v, that represents the selected cell range containing the three vector components, x, y, and z. The first three lines of this function declare three local variables in which to store the vector components. You really don't need to use local variables in this example, but I did so to make the remainder of the code more readable.
The next three lines set the values of the local variables to the values contained in the input cell range. Since v is of type Range, you can access its Cells property to retrieve the value for each cell. Each cell in the range is referred to using a 1-based array index as shown.
Finally, the last line of this function computes the magnitude of the vector and returns the result as a Double type scalar value.
|
Computing the vector cross product is a little more involved than simply computing the magnitude of a vector. For the cross product, we need to return the three components of the resulting vector as an array in Excel. This means we need to write a VBA function that returns an array instead of a scalar. The resulting function will be an array function that must be entered in a spreadsheet using Ctrl-Shift-Enter just like Excel's built-in array functions.
In Figure 7-4, cells D10 through D12 contain an array formula called v_CrossProduct. This is a custom function I created to compute the vector cross product of two vectors passed in as cell ranges. If you take a look at the formula bar in Figure 7-4, you'll see how this function is used; specifically, the formula is {=v_CrossProduct(D3:D5, E3:E5)}, where the braces indicate the function is an array function.
To enter this function, you have to select a range consisting of three contiguous cells, either vertically or horizontally oriented. The function we'll write will detect how the input cells are oriented and will return the results accordingly.
|
Example 7-2 shows the VBA code for v_CrossProduct.
Example 7-2. v_CrossProduct
|
Public Function v_CrossProduct(u As Range, v As Range) ' Declare local variables: Dim ux As Double Dim uy As Double Dim uz As Double Dim vx As Double Dim vy As Double Dim vz As Double Dim ReturnArray(3) Dim DoTranspose As Boolean ' Determine whether or not the selected ' output range is a row or a column array: If Application.Caller.Rows.Count > 1 Then DoTranspose = True Else DoTranspose = False End If ' Get the vector components: ux = u.Cells(1).Value uy = u.Cells(2).Value uz = u.Cells(3).Value vx = v.Cells(1).Value vy = v.Cells(2).Value vz = v.Cells(3).Value ' Compute the cross product: ReturnArray(0) = (uy * vz - uz * vy) ReturnArray(1) = (uz * vx - ux * vz) ReturnArray(2) = (ux * vy - uy * vx) ' If the selected output range is a column of cells then transpose the result: If DoTranspose Then v_CrossProduct = Application.WorksheetFunction.Transpose(ReturnArray) Else v_CrossProduct = ReturnArray End If End Function |
v_CrossProduct takes two parameters. Both are cell ranges that must contain three contiguous cells, which themselves contain the x, y, and z components for each vector. The first six lines in this function declare six local variables to store the components for each input vector.
The second to last local variable, called ReturnArray, is a three-element array that is used to store the result of the vector cross product operation. The final local variable, DoTranspose, is a Boolean flag that is used to control whether or not the resulting vector is transposed upon output.
The next commented block of code checks to see whether the selected output range is a row vector or a column vector. If the selected output range is a column vector, then its row count will be greater than 1 and we'll have to transpose the resulting vector. Otherwise, the selected output range is a row vector and we don't have to transpose the result.
The next block of code stores the components of the input vectors in local variables. Then it computes the cross product with each component of the resulting vector stored in the three elements of the local array ReturnArray, which we declared to store the result.
The final step is to check the DoTranspose flag. If it is true, then ReturnArray must be transposed using the transpose worksheet function. If it is false, you can simply return the ReturnArray without further modification.
The techniques discussed here can be used to construct custom functions to perform all the usual vector operations.