This function does basically the same thing that the AreaCircle() function described at the beginning of this chapter does, but the AreaCircle1() function will also show you how to use integrated worksheet functions.
The AreaCircle() function uses pi as a constant with the value 3.14159265358979 to calculate the area of a circle (by utilizing the maximum decimal places allowed in Excel). But you won’t always have pi with all decimal places handy, in which case you will have to use additional handbooks with tables of mathematical constants.
An experienced Excel user could argue that Excel includes a Pi() function that calculates the area of a circle with sufficient accuracy (and speed). The custom functions described in this chapter show that the Excel worksheet and the VBA development environment aren’t mutually incompatible. Numbers in worksheet cells can be passed to a VBA function, and the results returned by this function find their way back into the cell. Therefore, it is no surprise that worksheet functions can also be used in VBA.
Excel includes more than 300 functions. It makes sense to make use of these functions in custom functions to save time. The process for using built-in functions in a VBA project might seem complicated and obscure for the beginner, but it isn’t difficult. The complicated syntax is a consequence of object-oriented programming, which you don’t need to know about to use worksheet functions in a VBA project.
The syntax for the Pi() function in VBA is:
Application.WorksheetFunction.Pi
This function call approach prompts the Visual Basic Editor to use the built-in pi function of Excel. (Unlike the formulas in a worksheet, VBA functions don’t need the parentheses, because the term WorksheetFunction implies that the name is a function name.) A function call in the object-oriented manner is illustrated in the following simple example:
State.County.Town
Based on this statement, the town is located in the county, which is located in the state.
Now you can complete your enhanced AreaCircle() function. Instead of the constant for pi, you use the expression just shown with the Pi() function:
Public Function AreaCircle1(Radius As Double) As Double AreaCircle = Application.WorksheetFunction.Pi * Radius ^ 2 End Function
Admittedly, the example of the pi function is quite simple. Usually you have to consider two things:
You cannot use all built-in functions in VBA because VBA already includes functions that perform the same tasks.
Some built-in function names are identical in VBA but return different results because of their definition (one example is the log function).
The list boxes of the Visual Basic Editor help you enter functions. While entering code, you can select function names in the lists even if you don’t know the exact syntax (see Figure 6-6).
