Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
The HelpButton_Click procedure in the UserForm1 code module
This procedure is executed when the Help button is clicked. It simply displays the Help file (which
is a standard compiled HTML help file).
Making the Text Tools utility efficient
The procedures in the Text Tools utility work by looping through a range of cells. It makes no
sense to loop through cells that will not be changed — for example, empty cells and cells that
contain a formula. Therefore, I added code to improve the efficiency of the cell processing.
The ApplyButton_Click procedure calls a Function procedure named CreateWorkRange .
This function creates and returns a Range object that consists of all non-empty and nonformula
cells in the user’s selected range. For example, assume that column A contains text in the range
A1:A12. If the user selects the entire column, the CreateWorkRange function would convert that
complete column range into a subset that consists of only the non-empty cells (that is, the range
A:A would be converted to A1:A12). This conversion makes the code much more efficient because
empty cells and formulas need not be included in the loop.
The CreateWorkRange function accepts two arguments:
h Rng : A Range object that represents the range selected by the user.
h TextOnly : A Boolean value. If True , the function returns only text cells. Otherwise, it
returns all non-empty cells.
Private Function CreateWorkRange(Rng, TextOnly)
‘ Creates and returns a Range object
Set CreateWorkRange = Nothing
‘ Single cell, has a formula
If Rng.Count = 1 And Rng.HasFormula Then
Set CreateWorkRange = Nothing
Exit Function
End If
‘ Single cell, or single merged cell
If Rng.Count = 1 Or Rng.MergeCells = True Then
If TextOnly Then
If Not IsNumeric(Rng(1).Value) Then
Set CreateWorkRange = Rng
Exit Function
Else
Set CreateWorkRange = Nothing
Exit Function
End If
Else
If Not IsEmpty(Rng(1)) Then
Set CreateWorkRange = Rng
Exit Function
 
Search JabSto ::




Custom Search