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.

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 makes the code much more efficient because empty cells and formulas need not be included in the loop.

The CreateWorkRange function accepts two arguments:

■ Rng : A Range object that represents the range selected by the user.

■ TextOnly : A Boolean value. If True , the function returns only text cells. Otherwise, it returns all nonempty 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 End If End If End If

On Error Resume Next

Set Rng = Intersect(Rng, Rng.Parent.UsedRange) If TextOnly = True Then

Set CreateWorkRange = Rng. SpecialCells(xlConstants, xlTextValues) If Err <> 0 Then

Set CreateWorkRange = Nothing On Error GoTo 0 Exit Function End If

Else

Set CreateWorkRange = Rng.SpecialCells _ (xlConstants, xlTextValues + xlNumbers) If Err <> 0 Then

Set CreateWorkRange = Nothing On Error GoTo 0 Exit Function End If End If

End Function Note

The CreateWorkRange function makes heavy use of the SpecialCells property. To learn more about the SpecialCells property, try recording a macro while making various selections in Excel's Go To Special dialog box. You can display this dialog box by pressing F5 and then clicking the Special button in the Go To dialog box.

It's important to understand how the Go To Special dialog box works. Normally, it operates on the current range selection. For example, if an entire column is selected, the result is a subset of that column. But if a single cell is selected, it operates on the entire worksheet. Because of this, the CreateWorkRange function checks the number of cells in the range passed to it.

0 0

Post a comment