Press Alt+F11 to open the Visual Basic Editor (in all Excel versions).

In Excel 2003 and previous versions, you can select Tools/Macro/Visual Basic Editor, and in the Excel 2010 and Excel 2007, you can click Visual Basic on the Developer tab.

Like other typical windows in Windows, the Visual Basic Editor has a title bar, a menu bar, and toolbars. On the left side, you can see two subwindows: the Project Explorer window and the Properties window. The large gray area is the workspace, which is still empty (see Figure 6-1).

You can see your Excel VBA projects in the Project Explorer (which is similar to the folder view in Windows Explorer). Each open Excel workbook has a folder with the name VBAProject followed by the name of the workbook in parentheses, such as Geometry.xlsm or Book1 if the workbook wasn’t saved yet. By default, the Microsoft Excel Objects subfolder contains the objects ThisWorkbook, Sheet1, Sheet2, and Sheet3. (The view of the Project Explorer in versions of Excel before Excel 2003 is slightly different.) The number and names of the subfolders vary depending on your project. In the next step, you will add a new subfolder named Modules that contains the object Module1.

The Properties window shows the properties and values of the selected VBA object. For the following practice, you don’t need to understand the functionality of the Project Explorer and the Properties window.

Figure 6-1. The Visual Basic Editor’s opening screen (Excel 2010).

Select Insert/Module to prepare the workspace for the command code (see Figure 6-2).

If you are familiar with VBA, you know that there are other methods by which you can enter VBA procedures. These methods, however, are not suitable for custom functions.

Figure 6-2. The Visual Basic Editor with a new but still empty module.

Select Insert/Procedure to open the dialog box shown in Figure 6-3, in which you can select the settings for the new function.

Figure 6-3. The Add Procedure dialog box with the default settings.

In the Add Procedure dialog box, enter AreaCircle in the Name field and select the Function option. You don’t need to change any of the other options (see Figure 6-4).

Figure 6-4. The Add Procedure dialog box with the information required for the function declaration.

Click OK to add the procedure. You will see two lines in the workspace, with the cursor in an empty line. The Public keyword allows the module to be used in other modules. This is not necessary in this case, but for simplicity leave it there.

Public Function AreaCircle()
|
End Function

The AreaCircle function has a single argument, the radius. You need to declare the argument for this function in the procedure header. First enter the name of the argument. You can choose any name, but it should be descriptive, because this name will be displayed in the Function Wizard when you use the function in your Excel workbook. The argument type should also be declared. In this case, use As Double, indicating that the data type of the argument is Double, which is the standard number format of Excel worksheets. (This format, which is more precisely a floating-point number with double precision, is saved in 8 bytes in the range –1.79769313486231x10³⁰⁸ to –4.94065645841247x10^–324 for negative values and 2.2250738585072x10^–308 to 1.79769313486232x10³⁰⁸ for positive values.) Various other data types are available, including Variant, which will be explained later in this chapter.

Public Function AreaCircle(Radius As Double)
|
End Function

It is also a good practice to assign a data type to the return value, which is the result of the function; otherwise, the Visual Basic Editor uses the Variant type by default. The Variant data type provides more flexible handling of data types but uses more memory and reduces the processing speed. Because our example uses only the standard Excel number format, enter As Double after the closing parenthesis.

Public Function AreaCircle(Radius As Double) As Double
|
End Function

Enter the actual procedure between these two lines; in other words, the executable code, as shown here.

Public Function AreaCircle(Radius As Double) As Double
AreaCircle = 3.14159265358979 * Radius ^ 2
End Function

The function calculates pi times the radius squared. Pi has been entered as a fixed constant. Press the Tab key to indent the code. This blocks and groups the lines of code and makes reading and understanding the procedure easier.

Enter a number value for the radius into a cell (for example, enter 2 in cell A1).

Calculate the area of a circle with radius 2. Type in the formula

=AreaCircle(A1)

in another cell.