CODEWITHSUNDEEP
excelvba
Scott Severance
Scott Severance

Reputation: 953

Why is this VBA function slow?

I have a rather large Excel file with a lot of manually-entered data as well as a lot of calculated columns. One set of columns holds rack numbers, which are formatted as text and are three-digit numbers. A cell may contain zero or more rack numbers. When multiple rack numbers exist in a cell, they are delimited by /. Because I often fat-finger the rack numbers and enter four-digit numbers by mistake, I set up a conditional formatting rule to color cells with bad rack numbers red.

Here's my rule:

=HAS_BAD_RACK_NO(L1834)

which is applied to the range =$L$1834:$S$1981. The exact range changes from day to day, but it's always approximately the same size. Here's the VBA function that drives it:

Public Function HAS_BAD_RACK_NO(ref As Range) As Boolean
    Dim re As New RegExp
    Dim cell As Range
    Dim found As Boolean
    found = False
    re.Pattern = "[0-9]{4,}"
    For Each cell In ref.Cells
        If re.Test(cell.value) Then
            HAS_BAD_RACK_NO = True
            found = True
            Exit For
        End If
    Next cell
    If Not found Then
        HAS_BAD_RACK_NO = False
    End If
End Function

The problem is that this conditional formatting rule greatly slows Excel down whenever I enter data in one of the cells it covers. I estimate that it takes between 1 and 10 seconds after I leave the cell for Excel to start responding again and accept keyboard input. I can't figure out why it's so slow, because it's never examining more than one cell. There's only a single-cell range.

Admittedly, this is a complex spreadsheet with several custom functions and quite a lot of conditional formatting rules, together with a large number of charts. However, through testing I've established that this slowdown only occurs when I edit a cell in the range of this particular conditional formatting rule. If I edit the range to exclude a cell I'm editing, the performance problems disappear.

This function is really simple; why is it so slow?

Upvotes: 0

Views: 211

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

In my testing this function will execute about 30x faster - uses a static regexp instead of creating a new one each time it runs:

Public Function HAS_BAD_RACK_NO(ref As Range) As Boolean

    Static re As RegExp    '<< use a static object

    'only create if needed
    If re is nothing then
        Set re = New RegExp
        re.Pattern = "[0-9]{4,}"
    end if

    if ref.countlarge > 1 then
        HAS_BAD_RACK_NO = True 'don't allow multiple-cell inputs
    else
        HAS_BAD_RACK_NO = re.Test(ref.value)
    end if
End Function

Testing sub:

Sub Driver()
    Dim t
    t = Timer
    'L34:S181 contains the data which the UDF processes
    With ActiveSheet.Range("L34:S181")
        .Value = .Value
    End With
    Debug.Print Timer - t
End Sub

~1.3 sec for the original version, vs ~0.04 sec for the one using a static regexp.

Upvotes: 3

Related Questions