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 `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, ...

Start Free Trial

No credit card required