User-defined Functions

In Excel you can write 2 different types of macros, although they are often confused and used interchangeably.  The first is a sub routine (denoted by the use of "Sub" before the name of the routine).  The second is a function (denoted, logically, by the use of "Function" before the name of the function).  The difference between the two is quite important.

• A sub routine can change the value and/or format of a cell, but can only be invoked by an event occurring (e.g. the workbook being saved, or the value of a cell changing) or a user command (such as clicking on a button to run the sub routine).

• A function cannot change the value or format of a cell in the workbook, but they can be invoked directly from a cell.

The reason  for this difference is simple: Excel must keep track of which cells are dependent on others and which are independent; if you were able to call a sub routine from a cell and then change other cell values from the macro, Excel would no longer be able to track dependencies.

You might think that sub routines sound more flexible and therefore more widely used (and you would probably be right), however user-defined functions also have their, well, functions (I couldn't help myself)!  A classic example of this is a function that calculates an area based on dimensions (such as radius, diameter, length, height, etc.).  Functions can be much more complicated though.  For example, I recently wrote a function that would determine the type of element being considered (cylinder, rectangle, etc.), use that information to read in a formula from a separate worksheet (where all of the possible formulas were listed - a different one for each element type), and then calculate and output the second moment of area for that element.  To start with though, we'll write a quick function that just calculates the area of a circle or of a rectangle.

User-Defined Function: Area of a Rectangle or Circle

To begin, open a new workbook.  Add the following data to your worksheet, in 4 different columns:

Width	Height	Diameter	Area
10	50
5	12
6	13
		6
		10
		0.75

Now add a new Module to the workbook in the Visual Basic Editor (Alt+F11) under "Insert - Module".  The function must be written in a module, not in one of the Excel Objects (a sheet or the workbook).  Choose a name for your function - I'm going to use "Area".  A function requires you to specify the input when you declare the function.  In this case the input we require are the variables used to calculate the area of a circle or of a rectangle (width, height, and diameter).  Declare the function using the following line of code:

Function Area(dblWidth As Double, dblHeight As Double,
    dblDiameter As Double) As Double

Excel will automatically add End Function when you press enter after typing the line above.  The line above tells Excel that all of the inputs and the output are all double variable types.  Although not necessary, it's always a good idea to specify the variable type.  Now our function needs to work out if the equation for the area of a rectangle or for the area of a circle should be used.  We can do this by looking at the dblDiameter input - if this is empty, then we use the formula for a rectangle, if it's not empty (it has a value) then we'll use the formula for a circle.  To do this, add the code below between the Function declaration line above and the End Function line:

If dblDiameter = "" Then

    Area = dblWidth * dblHeight

Else

    Area = 2 * WorksheetFunction.Pi * (dblDiameter / 2) ^ 2

End If

Your final code should look like this:

Note that the underscores (_) are used for a break in the code - normally each line of code is a new statement, but using an underscore allows you to continue a statement onto a 2nd line of code.

Return to the workbook now; all that is left to do is to call the function for each row.  In the first cell under the "Area" header, type "=Area(B3,C3,D3)".  Note that the cell values B3, C3 and D3 should correspond to the width, height, and diameter entries for that row - these are the three cells that I have used, but since you may have used different cells make sure that you specify the correct ones.  This tells Excel to call the Area function, with the three correct inputs.  If your function is written correctly, the cell will now display the area of the rectangle/circle!  Simply fill down the rows to the last entry in the table to determine the area for all of the entries.

Worksheet Function

One of the most powerful features of programming with VBA in Excel is the ability to use functions that are built into Excel.  For example, say you're writing a macro that calculates the area of a circle from the radius or diameter (the formula is of course Area = 2 * pi * r^2).  Instead of typing out the constant pi to a certain number of decimal places, you can use the Pi() function built into Excel.  Normally if you were computing this formula in an Excel cell, you would just use Pi(); you can call the same function in VBA using the following line of code:

Application.WorksheetFunction.Pi

If you have a macro that uses this function, it would look something like this (using variables dblArea for the area of a circle, and sngRadius for the radius of the circle):

dblArea = 2 * Application.WorksheetFunction.Pi * sngRadius ^ 2

While this may not seem very useful for Pi (we can easily approximate the constant to 3.14159), it becomes invaluable when using other Excel functions - for example you might use Excel's Linear Regression function to output the slope of a best fit line, or VLookup to get the value of a cell in a range.  In all cases, you call up the function using Application.WorksheetFunction.Function - Excel helpfully suggests all possible functions when you type the period between WorksheetFunction and Function, so you can then just choose the function you need from the list of suggestions.

We will use this technique in the next tutorial post to learn how to write a user-defined function - also a type of macro, but different to a sub procedure, which is what we have looked at in the past.

Excel Screen Updating

It can sometimes be useful to stop Excel from updating the screen (or window) while a macro is running. This isn't commonly used when your macro is short and quick to run, but longer/slower macros can employ this as it speeds up the runtime of the macro. This is because Excel no longer has to refresh the screen every time the macro uses a Scroll, Activate, Select, etc. command or whenever formulas need to be re-calculated.

Switching off the screen updating is extremely simple to do. It's best to do it at the beginning of the macro, but you can also switch it on and off repeatedly as many times as you like in a macro (if there is something specific you want to have updated while the macro runs). To switch screen updating off add the following line to your code:

Application.ScreenUpdating = False

And to turn screen updating back on, use this line:

Application.ScreenUpdating = True

As mentioned above, you would usually put these lines at the beginning and end of your macro respectively; something like this:

Sub Macro()
    Application.ScreenUpdating = False

    'The rest of your code

    Application.ScreenUpdating = True
End Sub

And don't worry - when the macro ends and the focus switches back to Excel, screen updating is automatically enabled again, so while it's always a good idea to remember to enable screen updating again at the end of your code, it's not completely necessary!