Microsoft Office Tutorials and References
In Depth Information
EXTRACTELEMENT = AllElements(n - 1)
This function returns an element from a delimited text string and uses three arguments. For example, the follow-
ing formula returns the string fghi (the third element in the string, which uses a dash to separate the elements):
=EXTRACTELEMENT(“ab-cde-fghi-jkl”, 3, “-”)
Following is a VBA Sub procedure that adds argument descriptions, which appear in the Function Arguments
Dim desc(1 To 3) As String
desc(1) = “The delimited text string”
desc(2) = “The number of the element to extract”
desc(3) = “The delimiter character”
Application.MacroOptions Macro:=”EXTRACTELEMENT”, ArgumentDe-
The argument descriptions are stored in an array, and that array is used as the ArgumentDescriptions argument
for the MacroOptions method. You need to run this procedure only one time. After doing so, the argument de-
scriptions are stored in the workbook.
Testing and Debugging Your Functions
Naturally, testing and debugging your custom function is an important step that you must take to ensure that it
carries out the calculation that you intend. This section describes some debugging techniques that you may find
If you're new to programming, the information in this section will make a lot more sense
after you're familiar with the material in Chapter 25.
VBA code that you write can contain three general types of errors:
• Syntax errors: An error in writing the statement — for example, a misspelled keyword, a missing operator,
or mismatched parentheses. The VB Editor lets you know about syntax errors by displaying a pop-up error
box. You can't use the function until you correct all syntax errors.
• Runtime errors: Errors that occur as the function executes. For example, attempting to perform a mathem-
atical operation on a string variable generates a runtime error. Unless you spot it beforehand, you won't be
aware of a runtime error until it occurs.
• Logical errors: Code that runs but simply returns the wrong result.