21.4 If...Then Statments

You have been hired to write a function that contains trade secrets for a specialized industry. You are asked to write a function called “MaxMin,” which computes for an arbitrary amount of input variables: the minimum (MI) and the maximum (MA) of these variables. The function MaxMin should return the following.

If MI is 4, then the calculation would create a “divide by zero” error. Thus, if MI is 4, return the message Division by zero!

So, you will have an input parameter that represents a range of cells. You will return either the calculation (a number) or the error message (a string), so I suggest using a variant as the return type.

Hint: Consider using the min and max worksheet functions to calculate MI and MA from the input range.

Use your function with the following data.

Here is one implementation that would solve the problem:

Show Code

There are several ways this function could be implemented. Notice how we use the WorksheetFunction.Min and WorksheetFunction.Max, which allows us to quickly get the minimum and maximum in one line of code. Beyond that, we just need a simple If...Then statement to handle when MI-4 <> 0. We could simplify this statement to MI <> 4, but the current implementation closely matches the requirements.