Many functions have optional arguments. However, many of these optional arguments shouldn’t be ignored. These optional arguments have a specific value, and different values need to be used only in special cases. Therefore, default values are assigned to these optional arguments, and you need to make sure that if you do not specify a value for the optional argument, the default provided is appropriate. For this reason, the AreaSect() example in this section demonstrates the following:
How to use default values for missing optional arguments
How to evaluate conditions by using If statements
For example, the financial PMT() function calculates annuities and has five arguments. The last two of these arguments are optional: the time value FV and the Type. These arguments are required to calculate an annuity. However, you don’t have to specify these arguments. Because a loan is usually repaid in full (FV = 0) and the interest payment is paid at the end of the corresponding payment period (Type = 0), these default values are predefined in the PMT() function. If you don’t specify one of these arguments, the calculation uses the default value.
You can use the process described in this section in your custom functions.
The syntax for functions with default values for optional arguments is:
Public Function DoItYourself(Always1, Always2, Optional Sometimes1, Optional Either_ Way = value)
This example includes two mandatory and two optional arguments. The default value value is assigned to the last optional argument. For this to work, you need to add only an equal sign and define the default value in the declaration for the optional Either_Way variable. The Either_Way argument in the formula will have either the value that is indicated when the function is defined in the worksheet or the default value specified in the function declaration.
This is illustrated in the example for AreaSect(Radius,Phi,ADim). This function is used to calculate the area of a segment of a circle based on the radius of the circle and the angle of the segment. In school, angles are usually measured in degrees. But in science and technology, radian measures are preferred. You want the AreaSect() function to be able to handle both measures. The optional ADim argument controls what angle measure is used. If you call the function without the ADim argument, the standard value is used in the function declaration, and the ADim argument used as a variable in the executable code has the value 1. In this case, the If statement is branched into the area calculation that uses degrees.
If ADim has any other value, the If statement initiates the calculation of the area with the angle Phi in radians. To do this, you have to enter a value other than 1 for the ADim argument in the worksheet formula.
Public Function AreaSect(Radius As Double, Phi As Double, Optional ADim = 1) If ADim = 1 Then AreaSect = 3.14159265358979 * Radius ^ 2 * Phi / 360 Else AreaSect = Radius ^ 2 * Phi / 2 End If End Function