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
Mathto 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 = 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
result = Inverse(value) calculates the inverse and assigns that number to
result. Alternately, I could write that as
Math.Inverse(value); including ...