## Modules

For example, to create a module containing new mathematical functions, you can use in-cell formulas:

In Excel, open a workbook and choose Tools → Macro → Visual Basic Editor to start programming.

In the Visual Basic Editor, choose Insert → Module. Visual Basic adds a new module to the Project window and displays the new, empty module in an Edit window.

Select Name in the module’s Properties window and type

`Math`

to rename the module.Add the following code by typing in the module’s Edit window:

' Math module. Public Function Inverse(x As Double) As Double If x = 0 Then Inverse = 0 Else Inverse = 1 / x End Function Public Function CubeRoot(x As Double) As Double If x < 0 Then CubeRoot = 0 Else CubeRoot = x ^ (1 / 3) End Function

To use these new functions from Excel, include them in a formula as shown in Figure 5-1.

Figure 5-1. Use modules to create user-defined functions

To use these functions from Visual Basic, include them in an expression as shown here:

Sub TestMathFunctions( ) Dim result As Double, value As Double, str As String value = 42result = Inverse(value)str = "The inverse of " & value & " is " & resultresult = CubeRoot(value)str = str & " and the cube root is " & result MsgBox str, , "Test Math Functions" End Sub

Here, `result = Inverse(value)`

calculates the inverse and assigns that number to `result`

. Alternately, I could write that as `Math.Inverse(value)`

; including ...

Get *Programming Excel with VBA and .NET* now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.