You want to perform vector-based calculations , like vector cross and dot products, but can't find any vector functions in Excel.
Excel does not provide any built-in functions to perform vector calculations; however, you can add your own custom functions without too much effort.
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
cell range is a cell reference consisting of three cells containing the vector components,
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, ...