Microsoft Office Tutorials and References
In Depth Information
An Introductory VBA Function Example
Figure 23-1: A simple VBA function displayed in a code window.
What custom worksheet functions can’t do
As you develop custom worksheet functions, you should understand a key point. A Function
procedure used in a worksheet formula must be passive: In other words, it can’t change things in
You may be tempted to try to write a custom worksheet function that changes the formatting of
a cell. For example, you may want to edit the USER function (presented in this section) so that
the name displays in a different color. Try as you might, a function such as this is impossible to
write — everybody tries this, and no one succeeds. No matter what you do, the function always
returns an error because the code attempts to change something on the worksheet. Remember
that a function can return only a value. It can’t perform actions with objects.
None of Excel’s built-in functions are able to change a worksheet, so it makes sense that custom
VBA functions cannot change a worksheet.