Loop dilemma, different approach same result-CodePudding

I was trying to trigger worksheet event by changing content of a cell. I know my code has the wrong approach (I should used redim,...), but result is the same. Is it OK to solve problems as you like or is there the right way to do things in programming?

'I condition
For Each cell In rng
    If cond1 = "" Then
        arr(i) = cell.Row
        i = i   1
    Else
        If cell.Offset(0, 4).Value = cond1 Then
            arr(i) = cell.Row
            i = i   1
        End If
    End If
Next

'II condtion
For a = LBound(arr) To UBound(arr)
    If cond2 = "" Then
        GoTo X
    Else
        If arr(a) <> "" Then
            If wsDB.Cells(arr(a), 5).Value <> cond2 Then
                arr(a) = ""
            End If
        End If
    End If
Next a
X:

b = 9
For i = LBound(arr) To UBound(arr)
    If arr(i) <> "" Then
        If b > 150 Then
            Exit Sub
        Else
            ws.Cells(b, 3).Value = wsDB.Cells(arr(i), 3).Value
            b = b   1
        End If
    End If
Next i

End Sub

CodePudding user response：

"Is it OK to solve problems as you like or is there the right way to do things in programming?"

There is usually an idiomatic way of coding something, a way which is the simplest and perhaps the fastest. In Python, for example, lists can be built using list comprehensions, which is simple and fast. Each language has an idiomatic approach.

An important thing to note about Excel / VBA is that they are not integrated. They are actually two separate things linked over a very slow COM interface. If there are too many range accesses it will slow everything down. Some things aren't too slow, because the number of individual accesses is very low:

range("myrange") = 0                  ' One access does the whole range
nrows = range("myrange").rows.count   '  Again only one access

Other things are much more expensive. Working through rows and columns is very expensive if done normally, but using variants is much cheaper.

Option Explicit

Public Sub DoWork()
    Dim ThisTime As Variant
    Dim myrange As Variant
    Dim Row, Col As Integer

    ThisTime = Timer
    For Row = 1 To 100
       For Col = 1 To 100
          Range("myrange").Cells(Row, Col) = 1
       Next Col
    Next Row
    MsgBox Timer - ThisTime            ' 2.023 seconds

    ThisTime = Timer
    myrange = Range("myrange")
    For Row = 1 To 100
         For Col = 1 To 100
            myrange(Row, Col) = 1
         Next Col
    Next Row
    Range("myrange") = myrange
    MsgBox Timer - ThisTime          ' 0.03125 seconds, 65 times faster

End Sub

So which version is more idiomatic?