Chapter 26: VBA Custom Function Examples

In This Chapter

• Simple custom function examples

• A custom function to determine a cell's data type

• A custom function to make a single worksheet function act like multiple functions

• A custom function for generating random numbers and selecting cells at random

• Custom functions for calculating sales commissions

• Custom functions for manipulating text

• Custom functions for counting and summing cells

• Custom functions that deal with dates

• A custom function example for returning the last nonempty cell in a column or row

• Custom functions that work with multiple worksheets

• Advanced custom function techniques

This chapter is jam-packed with a wide variety of useful (or potentially useful) VBA custom worksheet functions. You can use many of the functions as they are written. You may need to modify other functions to meet your particular needs. For maximum speed and efficiency, these Function procedures declare all variables that are used.

Simple Functions

The functions in this section are relatively simple, but they can be very useful. Most of them are based on the fact that VBA can obtain useful information that's not normally available for use in a formula. For example, your VBA code can access a cell's HasFormula property to determine whether a cell contains a formula. Oddly, Excel does not have a built-in worksheet function that tells you this.

This book's website contains the workbook simple functions.xlsm ...

Get Excel 2013 Formulas now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.