Using Worksheet Functions in VBA

Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel's worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function.

VBA makes Excel's worksheet functions available through the WorksheetFunction object, which is contained in the Application object. (Remember, the Application object is Excel.) Therefore, any statement that uses a worksheet function must use the Application. WorksheetFunction qualifier. In other words, you must precede the function name with Application.WorksheetFunction (with a dot separating the two). The following is an example:

Total = Application.WorksheetFunction.Sum(Range("A1:A12"))

You can omit the Application part of the expression because it's assumed. You can also omit the WorksheetFunction part of the expression; VBA will determine that you want to use an Excel worksheet function. But if you do so, then you must include the Application part. In other words, these three expressions all work exactly the same:

Total = Application.WorksheetFunction.Sum(Range(''A1:A12")) Total = WorksheetFunction.Sum(Range("A1:A12")) Total = Application.Sum(Range("A1:A12"))

My personal preference is to use the WorksheetFunction part just to make it perfectly clear that the code is using an Excel function.

0 0

Post a comment