O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

7.11. 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.

Vector example

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required