Microsoft Office Tutorials and References
In Depth Information
=LEN(USER())
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 the worksheet.
You may be tempted to try to write a custom worksheet function that changes the formatting of a cell. For ex-
ample, 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 suc-
ceeds. 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 ob-
jects.
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.
If you don't like the fact that your name is in uppercase, edit the procedure as follows:
Function USER()
‘ Returns the user's name
USER = Application.UserName
End Function
After editing the function, reactivate Excel and press F9 to recalculate. Any cell that uses the USER function
displays a different result.
About Function Procedures
In this section, I discuss some of the technical details that apply to Function procedures. These are general
guidelines for declaring functions, naming functions, using custom functions in formulas, and using arguments
in custom functions.
Declaring a function
The official syntax for declaring a function is as follows:
[Public | Private][Static] Function name ([arglist]) [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function
The following list describes the elements in a Function procedure declaration:
Search JabSto ::




Custom Search