How to Use Wrapper Functions in Excel 2016 VBA
Here, you will find some relatively simple custom Excel VBA worksheet functions that are also very useful. These functions are called wrapper functions because they consist of code that’s wrapped around intrinsic VBA elements. In other words, they allow you to use VBA functions in worksheet formulas.
Function User() ‘ Returns the name of the current user User = Application.UserName End Function
This function, in essence, lets your formulas access the UserName property of the Application object.
The NumberFormat function
This function simply displays the number format for a cell. It can be useful if you need to ensure that a group of cells all have the same number format.
Function NumberFormat(Cell) ‘ Returns the cell’s number format NumberFormat = Cell(1).NumberFormat End Function
Notice the use of Cell (1)? If a multicell range is used as an argument, only the first cell is used.
You can easily write similar functions that return a cell’s text color, background color, font, and so on.
The ExtractElement function
This wrapper function returns a substring from a text string that contains multiple elements, separated by a separator character. For example, this formula returns cow, which is the third element in a string that uses a space as a separator. The arguments, of course, could be cell references.
=ExtractElement(“dog horse cow cat”, 3, “ “)
Here’s the code, which is a wrapper for VBA’s Split function:
Function ExtractElement(Txt, n, Sep) ‘ Returns the nth element of a text string, where the ‘ elements are separated by a specified separator character ExtractElement = Split(Application.Trim(Txt), Sep)(n - 1) End Function
The image below shows the ExtractElement function used in worksheet formulas. Column A contains the text string, Column B contains the element number to be extracted, and Column C contains the delimiter (cells that appear to be blank contain a space character).
The SayIt function
This simple function is a wrapper for the Speak method of the Application.Speech object. It uses a synthesized voice to “speak” the argument.
Function SayIt(txt) ‘ Speaks the argument Application.Speech.Speak txt, True End Function
Here’s an example:
The formula checks cell C10. If the value is greater than 10,000, the function speaks the text: “Over budget.” If the value is less than 10,000, the function displays the text OK (and doesn’t say anything).
Use sparingly. If you use this function more than one time, it can be very confusing. Also, remember that this function is evaluated each time the worksheet is calculated, so the voice may get very annoying if you’re making many changes. This function is probably more suited for amusement purposes.
The IsLike function
VBA’s Like operator is a very flexible way to compare text strings. Check it out in the VBA Help system. This function brings that power to your worksheet formulas:
Function IsLike(text, pattern) ‘ Returns true if the first argument is like the second IsLike = text Like pattern End Function