20.5 Programmatically Adding Function and Argument Descriptions

In this section, we will implement a new function called EXTRACTELEMENT, which allows us to quickly extract a certain element based on an input value and a delimiter. If you are following along with the class file, we’ve already implemented this extract element function for you behind the scenes.

Function EXTRACTELEMENT(Txt As String, n As Integer, Separator As String) As String
     Dim AllElements As Variant
     AllElements = Split(Txt, Separator)
     EXTRACTELEMENT = AllElements(n - 1)
End Function

As you can see, there are three parameters: Txt, a string that will be parsed; n, which indicates which element to extract; and Separator, which is the delimiter we will use to split the string. Take a quick look at the function to see if you can understand how it identifies which element to hand back. You probably aren’t yet familiar with the Split function. This VBA function takes a string and splits it based on a delimiter. The result is an array of strings. Notice how since it is a 0-based array, we can simply return the text element that corresponds with n-1. Try out this function in the class worksheet to see how easy it is to extract different elements. What a useful function! This could make our text parsing much easier!

While that is a useful function, this section is about adding functions and argument descriptions. So, let’s get back to that.

In the class file, we’ve provided a custom sub procedure called DescribeFunction, which will set descriptions for the function parameters and the function itself and categorize the function in the My Functions group.

Sub DescribeFunction()   'Use once for a new function to describe its arguments
     Dim Desc(1 To 3) As String  'set up 3 spots in memory for description-array
     'change as needed for the number of arguments in your function
     Desc(1) = "The delimited text string"
     Desc(2) = "The number of the element to extract"
     Desc(3) = "The delimiter character"
     Application.MacroOptions _
          Macro:="EXTRACTELEMENT", _
          Category:="My Functions", _
          Description:="Returns the nth element of a string given a user defined separator.", _
          ArgumentDescriptions:=Desc
End Sub

The main command in this function is the Application.MacroOptions. Notice how we use that to set the Category, Description, and ArgumentDescriptions for the EXTRACTELEMENT function. When you have multiple arguments, you will pass a string array with the descriptions for each argument stored in order in that array.

To run this sub procedure so it adds this information to the EXTRACTELEMENT function, you can either type “Call DescribeFunction” in the Immediate window or press the Run button when your cursor is within the DescribeFunction in the code window. Once you do that, return to your worksheet and look at the Function Arguments dialog box to confirm the descriptions now appear.

Figure 20.9: EXTRACTELEMENT Function Arguments with descriptions.

We can also confirm that our function is now found under the My Functions custom category.

Previous Next