CODEWITHSUNDEEP
regexvbaperformanceexcel
Imanuel
Imanuel

Reputation: 3667

Can a Regex-Replace be run on a range instead of looping through the cells in Excel?

I need to do many Regex replacements (~ 100 currently, but the list will grow) on a range of cells (varies, but up to 4 or 5 digit cell count).
Currently, my working draft is to loop through all cells repeatedly for each pattern, but obviously that's many loops.

Ideally, I'd call something like (pseudocode):

Sheet.Range("A1:G1000").RegexReplace(pattern, replacement)

However, the nearest thing is Range.Replace which only mentions "The string you want Microsoft Excel to search for".

The list of Regex.Replace overloads does not mention anything related to cells or ranges.

So, since Range.RegexReplace seems to be out - is there a more efficient way to replace many patterns in many cells than to loop through each pattern, row and column?

Upvotes: 1

Views: 1157

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

Don't iterate cells. Whether you're writing VBA, C#, or VB.NET, if you're working against Range objects in nested loops you're doing the single slowest thing you could possibly do with the Excel object model.

Work against an array instead - you need a function like this in your toolbox:

Public Function ToArray(ByVal target As Range) As Variant
    Select Case True
        Case target.Count = 1
            'singe cell
            ToArray = Array(target.Value)
        Case target.Rows.Count = 1
            'horizontal 1D range
            ToArray = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(target.Value))
        Case target.columns.Count = 1
            'vertical 1D range
            ToArray = Application.WorksheetFunction.Transpose(target.Value)
        Case Else
            '2D array: let Excel to the conversion itself
            ToArray = target.Value
    End Select
End Function

Now you iterate an in-memory array of values (with For loops) and for each value you iterate a number of Regex.Replace calls - cache and reuse the Regex objects as much as possible, so you're not re-creating the same objects over and over and over for thousands of values.

Once you've traversed the entire array, dump it into the worksheet (resize and transpose as needed), and voilà - you've instantly rewritten thousands of cells in a single operation.

Upvotes: 3

Related Questions