Microsoft Office Tutorials and References
In Depth Information
Function Examples
To enter an array formula, you must press Ctrl+Shift+Enter (and don’t enter the curly
braces).
The lower bound of an array, created using the Array function, is determined by the
lower bound specified with the Option Base statement at the top of the module. If
there is no Option Base statement, the default lower bound is 0 .
A workbook that demonstrates the MonthNames function is available on the companion
CD-ROM. The file is named month names.xslm .
A function that returns an error value
In some cases, you might want your custom function to return a particular error value. Consider
the RemoveVowels function, which I presented earlier in this chapter:
Function RemoveVowels(Txt) As String
‘ Removes all vowels from the Txt argument
Dim i As Long
RemoveVowels = “”
For i = 1 To Len(Txt)
If Not UCase(Mid(Txt, i, 1)) Like “[AEIOU]” Then
RemoveVowels = RemoveVowels & Mid(Txt, i, 1)
End If
Next i
End Function
When used in a worksheet formula, this function removes the vowels from its single-cell
argument. If the argument is a numeric value, this function returns the value as a string. You may
prefer that the function returns an error value (#N/A), rather than the numeric value converted
to a string.
You may be tempted simply to assign a string that looks like an Excel formula error value. For
example:
RemoveVowels = “#N/A”
Although the string looks like an error value, other formulas that may reference it don’t treat it as
such. To return a real error value from a function, use the VBA CVErr function, which converts
an error number to a real error.
 
Search JabSto ::




Custom Search