Microsoft Office Tutorials and References
In Depth Information
An Introductory VBA Function Example
For example, you can create a custom function that can significantly shorten your formulas.
Shorter formulas are more readable and easier to work with. However, it’s important to
understand that custom functions in your formulas are usually much slower than built-in functions. On
a fast system, though, the speed difference often goes unnoticed.
The process of creating a custom function is not difficult. In fact, many people (this author
included) enjoy creating custom functions. This topic provides you with the information that you
need to create your own functions. In this and subsequent chapters, you’ll find many custom
function examples that you can adapt for your own use.
When you create a custom function, capitalization doesn’t matter. For consistency with
Excel’s built-in functions, I use uppercase for all custom functions.
An Introductory VBA Function Example
Without further ado, I’ll show you a simple VBA Function procedure. This function, named
USER , does not accept any arguments. When used in a worksheet formula, this function simply
displays the user’s name in uppercase characters. To create this function, follow these steps:
1. Start with a new workbook.
This is not really necessary, but keep it simple for right now.
2. Press Alt+F11 to activate the VB Editor.
3. Click your workbook’s name in the Project window.
If the Project window is not visible, press Ctrl+R to display it.
4. Choose Insert
Module to add a VBA module to the project.
5. Type the following code in the code window:
Function USER()
‘ Returns the user’s name
USER = Application.UserName
USER = UCase(USER)
End Function
Figure 23-1 shows how the function looks in a code window.
To try out the USER function, activate Excel (press Alt+F11) and enter the following formula into
any cell in the workbook:
=USER()
 
Search JabSto ::




Custom Search