Microsoft Office Tutorials and References
In Depth Information
UDF Examples That Solve Common Tasks
Remember that you can copy and paste a UDF just as you can a built-in formula
or function. You can also use the ill handle to copy the UDF down.
Function ExtractNumbers(strText As String)
‘Declare the necessary variables.
Dim i As Integer, strDbl As String
‘Loop through each character in the cell.
For i = 1 To Len(strText)
‘If the character is a digit, append it to the strDbl variable.
If IsNumeric(Mid(strText, i, 1)) Then
strDbl = strDbl & Mid(strText, i, 1)
End If
Next i
‘Each character in the cell has been evaluated, so you can define the
‘ExtractNumbers function by setting it equal to the strDbl variable.
‘The purpose of the CDbl function is to coerce the strDbl expression
‘into a numeric Double data type.
ExtractNumbers = CDbl(strDbl)
End Function
Function ExtractLetters(strText As String)
‘Declare the necessary variables.
Dim x As Integer, strTemp As String
‘Loop through each character in the cell.
For x = 1 To Len(strText)
‘If the character is not numeric, it must be a letter,
‘so append it to the strTemp variable.
If Not IsNumeric(Mid(strText, x, 1)) Then
strTemp = strTemp & Mid(strText, x, 1)
End If
Next x
‘Each character in the cell has been evaluated, so you can define the
‘ExtractLetters function by setting it equal to the strTemp variable.
ExtractLetters = strTemp
End Function
extract the address from a Hyperlink
Here is an example of how to return the actual
underlying address of a hyperlink. In Figure 16-3, hyperlinks
are in column A but the display text in those cells
describes the link’s destination. This UDF will return
the actual hyperlink address; the “mailto” portion of
the code deals with the possibility of a link being an
e-mail address.
Function Link(HyperlinkCell As Range)
Link = Replace(HyperlinkCell.Hyperlinks(1).Address, “mailto:”, “”)
End Function
UDF in B2 and copied down is:
=Lind(A2)
figurE 16-3
 
Search JabSto ::




Custom Search