Need help improving my VBA loop

Question

I have an Excel Worksheet consisting of two columns, one of which is filled with strings and the other is emtpy. I would like to use VBA to assign the value of the cells in the empty column based on the value of the adjacent string in the other column.

I have the following code:

Dim regexAdmin As Object 
Set regexAdmin = CreateObject("VBScript.RegExp") 
regexAdmin.IgnoreCase = True
regexAdmin.Pattern = "Admin" 

Dim i As Integer
For i = 1 To 10 'let's say there is 10 rows
    Dim j As Integer
    For j = 1 To 2
        If regexAdmin.test(Cells(i, j).Value) Then
            Cells(i, j + 1).Value = "Exploitation"
        End If
    Next j
Next i

The problem is that when using this loop for a big amount of data, it takes way too long to work and, most of the time, it simply crashes Excel.

Anyone knows a better way to this?

Hint: Don't read and write one cell at a time. Use array = Range().Value statements to pull all the values into memory with one operation. — John Alexiou
– John Alexiou, Commented Aug 7, 2014 at 14:59

RowanC · Accepted Answer · 2014-08-07 13:13:35Z

2

You have an unnecessary loop, where you test the just completed column (j) too. Dropping that should improve the speed by 10-50%

Dim regexAdmin As Object 
Set regexAdmin = CreateObject("VBScript.RegExp") 
regexAdmin.IgnoreCase = True
regexAdmin.Pattern = "Admin" 

Dim i As Integer
For i = 1 To 10 'let's say there is 10 rows
        If regexAdmin.test(Cells(i, 1).Value) Then
            Cells(i, 1).offset(0,1).Value = "Exploitation"
        End If
Next i

If the regex pattern really is simply "Admin", then you could also just use a worksheet formula for this, instead of writing a macro. The formula, which you'd place next to the text column (assuming your string/num col is A) would be:

=IF(NOT(ISERR(FIND("Admin",A1))),"Exploitation","")

In general, if it can be done with a formula, then you'd be better off doing it so. it's easier to maintain.

answered Aug 7, 2014 at 13:13

RowanC

1,65911 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

citizenkong Over a year ago

Also, even in VBA code the Regex is unnecessary as it's not a pattern, just a string literal. You could just as easily have If Cells(i, 1) = "Admin" Then....

citizenkong Over a year ago

And using unqualified Cells references is a crime!

Ravi Yenugu Over a year ago

Just curious in finding how'd you come up with 10-50% numbers!

RowanC Over a year ago

for every iteration through the outer loop, there was an unnecessary regex test on a cell which is either empty or contains 'Exploitation'. Assuming that the grabbing of the cell.value was the time intensive part, and that regex is the same speed when the cell is empty, there would be 1/2 as many checks to do, so it should take about 1/2 as long. But that's a best case scenario. Regex is probably quicker when there is no content to scan, so you probably wont realise the full benefit, so I hedged.

John Alexiou · Accepted Answer · 2014-08-07 15:10:09Z

Try this:

Before After

Public Sub ProcessUsers()

    Dim regexAdmin As Object
    Set regexAdmin = CreateObject("VBScript.RegExp")
    regexAdmin.IgnoreCase = True
    regexAdmin.Pattern = "Admin"

    Dim r As Range, N As Integer, i As Integer
    Set r = Range("A1") '1st row is headers
    N = CountRows(r) - 1 'Count data rows

    Dim inputs() As Variant, outputs() As Variant
    inputs = r.Offset(1, 0).Resize(N, 1) ' Get all rows and 1 columns
    ReDim outputs(1 To N, 1 To 1)

    For i = 1 To N
        If regexAdmin.test(inputs(i, 1)) Then
            outputs(i, 1) = "Exploitation"
        End If
    Next i

    'Output values
    r.Offset(1, 1).Resize(N, 1).Value = outputs
End Sub


Public Function CountRows(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
    End If
End Function

Collectives™ on Stack Overflow

Need help improving my VBA loop

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related