Microsoft Office Tutorials and References
In Depth Information
Why Create Custom Functions?
Figure 10-1: Using a custom function in a worksheet formula.
Using the function in a VBA procedure
In addition to using custom functions in worksheet formulas, you can use them in other VBA
procedures. The following VBA procedure, which is defined in the same module as the custom
RemoveVowels function, first displays an input box to solicit text from the user. Then the procedure
uses the VBA built-in MsgBox function to display the user input after the RemoveVowels function
processes it (see Figure 10-2). The original input appears as the caption in the message box.
Dim UserInput as String
UserInput = InputBox(“Enter some text:”)
MsgBox RemoveVowels(UserInput), vbInformation, UserInput
In the example shown in Figure 10-2, the string entered in response to the InputBox function
was Excel 2010 Power Programming With VBA . The MsgBox function displays the text
Figure 10-2: Using a custom function in a VBA procedure.