Programming Excel with VBA and .NET

Modules

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

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

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

3. Select Name in the module’s Properties window and type Math to rename the module.

4. 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 = 42
        result = Inverse(value)
        str = "The inverse of " & value & " is " & result
        result = 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 ...