Microsoft Office Tutorials and References
In Depth Information
Why Create Custom Functions?
Dim TempString As String
TempString = “”
For i = 1 To Len(txt)
Select Case ucase(Mid(txt, i, 1))
Case “A”, “E”, “I”, “O”, “U”
TempString = TempString & Mid(txt, i, 1)
RemoveVowels = TempString
In this version, I used a string variable ( TempString ) to store the vowel-less string as it’s being
constructed. Then, before the procedure ends, I assigned the contents of TempString to the
function’s name. This version also uses a Select Case construct rather than an If-Then construct.
Both versions of this function are available on the companion CD-ROM. The file is
named remove vowels.xlsm .
What custom worksheet functions can’t do
When you develop custom functions, it’s important to understand a key distinction between
functions that you call from other VBA procedures and functions that you use in worksheet
formulas. Function procedures used in worksheet formulas must be passive. For example, code
within a Function procedure can’t manipulate ranges or change things on the worksheet. An
example can help make this limitation clear.
You may be tempted to write a custom worksheet function that changes a cell’s formatting. For
example, it may be useful to have a formula that uses a custom function to change the color of
text in a cell based on the cell’s value. Try as you might, however, such a function is impossible
to write. No matter what you do, the function won’t change the worksheet. Remember, a
function simply returns a value. It can’t perform actions with objects.
That said, I should point out one notable exception. You can change the text in a cell comment by
using a custom VBA function. I’m not sure if this behavior is intentional, or if it’s a bug in Excel. In
any case, modifying a comment via a function seems to work reliably. Here’s the function:
Function ModifyComment(Cell As Range, Cmt As String)
Here’s an example of using this function in a formula. The formula replaces the comment in cell
A1 with new text. The function won’t work if cell A1 doesn’t have a comment.
=ModifyComment(A1,”Hey, I changed your comment”)