The screenshot below is from a spreadsheet that is used to calculate the square footage (area) and perimeter of a rectangular or square shape, such as a garden or a room. The purpose of this (example) spreadsheet is to answer questions such as

• How much turf do we need to buy for the garden?
• How much carpet do we need to buy?
• How much fencing is required around all 4 sides of the garden?

Excel doesn’t have any built in functions to perform these calculations so you’d have to manually enter formulas into columns D and E.

This is one area where I see panic setting in amongst Excel users because it requires them to know the “underlying calculation” (or back-of-an-envelope calculation as I call it). In other words, they’d need to know that to calculate the area of a square/rectangle, they multiply the width by the length and although that it obvious to many people, it’s also not obvious to many people!

In the screenshot above, E2 contains a function =Area(B2,C2)

User Defined Functions

The =AREA function does not exist in Excel. It is a User Defined Function (UDF), created using VBA code. To create a UDF:

• Press ALT+F11 to switch to the VBA Editor
• Select Insert > Module from the VBA Editor menu
• Type the following code into the blank module:

Function Area(vLength As Long, vWidth As Long)
    Area = vLength * vWidth
End Function

Once the function has been created, it can be used just like the built-in functions. However…

The file that contains the function can’t be saved as an XLSX file. It either needs to be an XLS file or a macro-enabled (XLSM) file (XLSM is only available in Excel 2007 or above (Windows) and Excel 2011 for Mac). If it is saved as an XLSX file, the function (which is really just a block of VBA code) is actually removed from the file!

If the file is an XLSM file, unless the file is stored in a Trusted Location, When the file is opened, macros must be enabled otherwise the function will return a value of #NAME?

The function can be used in files other that the one that it is stored in. However, the file that contains the function’s VBA code must be open otherwise the function will return a value of #NAME?

If the function is stored in a different file to the one that it is being used in, the filename must be included when entering the function, in other words =PerimeterArea.xlsm!Area(A1,A2)

Use An Add-In

A way to avoid the 4 issues listed above is to store the VBA code in an add-in file rather than a standard macro-enabled Excel file. To do this, if the VBA code is already written (which it probably will be):

• In the workbook containing the VBA code, press ALT+F11 to switch to the VBA Editor
• Find the function and select all the code, including the lines Function() and End Function.
• Copy the selected code
• Close the workbook
• Start a new Excel workbook
• Press ALT+F11 to switch to the VBA Editor
• Select Insert > Module from the VBA Editor menu
• Paste the code into the blank module
• Close the VBA Editor
• Save the Excel file as an Add-In. To do this, change the file type in the Save As Type drop down in the Save As dialog box (the drop down is labelled Format in Excel 2011 for Mac)
• Enter a name for the add-in file

Just a word about where to store the add-in file. An add-in file can be stored anywhere. On Windows, Excel will suggest a folder to store the add-in. Unless you have a compelling reason to change this, just accept the suggested folder. On the Mac, the Documents folder is a good place to store the add-in file.

To make the function available, the add-in must be enabled:

In Excel 2007 and above…

• Click the Office Button (File menu in Office 2010/2013)
• Click the Excel Options button (Options command in 2010/2013)
• Click the Add-Ins link on the left hand side
• From the Manage drop down at the bottom of the dialog box, select Excel Add-ins and click Go
• Place a tick in the checkbox next to the name of the add-in you created and click OK
• If the add-in is not listed, click browse to find the add-in file

In Excel 2003 and Excel 20011 (Mac)

• Select Tools > Addins
• You might need to browse to find the add-in file
• Once you’ve located it, place a tick in the checkbox next to the name of the add-in you created and click OK

Once the add-in is enabled:

• The function can be used without reference to the add-in file, i.e =AREA(A1,A2)
• Any file that contains the function in a cell can be saved as an XLSX (i.e. no need to save as a macro-enabled file
• There will be no need to enable macros when opening the XLSX file