Using the function in a VBA procedure

In addition to using custom functions in worksheet formulas, you can also 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 some text from the user. Then the procedure uses the VBA built-in MsgBox function to display the user input after it's processed by the RemoveVowels function (see Figure 10-2). The original input appears as the caption in the message box.

Figure 10-2: Using a custom function in a VBA procedure.

Sub ZapTheVowels()

Dim Userlnput as String

Userlnput = InputBox("Enter some text:") MsgBox RemoveVowels(Userlnput) , , Userlnput End Sub

In the example shown in Figure 10-2, the string entered in response to the inputBox function was Excel Power Programming With VBA. The MsgBox function displays the text without vowels.

0 0

Post a comment