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.