How to Use Wrapper Functions in Excel 2016 VBA - dummies

How to Use Wrapper Functions in Excel 2016 VBA

By John Walkenbach

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).

Using the ExtractElement function to return an element from a string.
Using the ExtractElement function to return an element from a string.

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:

=IF(C10>10000,SayIt(“Over budget”),”OK”)

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