[Q] For, Next - How to Speed Up

  • Thread starter Thread starter John
  • Start date Start date
J

John

Looking for tips to speed up a loop that writes a value to a range of
cells given the value of related cells.

Details:
I have two ranges---both contain one column and same number of rows
(10). Each of the 3 cells is either TRUE or FALSE. The second range
has 3 cells. VBA writes a value to the second range for each cell
where TRUE in the first named range.

Code:

Dim i As Integer

For i = 1 To 3
If wsMenu.Range("rngChkRef" & i) Then
wsMenu.Range("vbChkRefStatus" & i) = "OK"
End if

Next i


Given:
A1 TRUE
A2 FALSE
A3 TRUE

Loop result:
B1 (vba writes OK)
B2
B3 (vba writes OK)

Is there any way to speed this up? Tried ScreenUpdating and
calculation but the loop writes are noticeable (ie, template contains
more than 3 cells). I'm thinking an array would be fast but not sure
how to write it. Any help is appreciated!
 
Looking for tips to speed up a loop that writes a value to a range of
cells given the value of related cells.

Details:
I have two ranges---both contain one column and same number of rows
(10). Each of the 3 cells is either TRUE or FALSE. The second range
has 3 cells. VBA writes a value to the second range for each cell
where TRUE in the first named range.

Code:

        Dim i As Integer

                    For i = 1 To 3
                        If wsMenu.Range("rngChkRef" & i) Then
                            wsMenu.Range("vbChkRefStatus" & i) = "OK"
                        End if

                    Next i

Given:
A1 TRUE
A2 FALSE
A3 TRUE

Loop result:
B1 (vba writes OK)
B2
B3 (vba writes OK)

Is there any way to speed this up? Tried ScreenUpdating and
calculation but the loop writes are noticeable (ie, template contains
more than 3 cells). I'm thinking an array would be fast but not sure
how to write it. Any help is appreciated!
Click to expand...

Why not just have spreadsheet formulas in the second range of cells?
VBA seems like overkill. But - assuming that you have good reasons for
wanting a VBA approach, I suspect that one of the problems is that you
are constanly computing named ranges. Perhaps you can do something
like

Sub test()
Dim R As Range, S As Range
Dim i As Long, n As Long

Set R = Range("A1:A3")
Set S = Range("B1:B3")

n = R.Cells.Count
For i = 1 To n
If R.Cells(i).Value Then S.Cells(i).Value = "OK"
Next i

End Sub

For larger ranges you can read the values of the first range into a
variant array, loop through that array while building up a second
array and tranfer the second array to the second range. This is
somewhat annoying in the case of 1-column ranges (since you somewhat
oddly need to use the worksheet transpose function) but would be much
easier if the two ranges are in adjacent columns (since then you could
get by with a single 2-dimensional array which encompasses both
ranges).
 
Couple of things to consider...

VB read/write from/to ranges is inherently slow.

Using an If...Then construct slows it down even more because VB has to
evaluate every iteration. Mind you, there's only 3 so this should be a
hardly negligable hit on performance.

Whenever you write to a range it works faster if you set...

Dim bEventsEnabled As Boolean, lCalcMode As Long
With Application
bEventsEnabled = .EnableEvents: lCalcMode = .Calculation
.ScreenUpdating = False
End With 'Application

'//do stuff

With Application
.EnableEvents = bEventsEnabled: .Calculation = lCalcMode
.ScreenUpdating = True
End With 'Application

As John suggests, it would be better to 'dump' the ranges into a 2D
array and work the values in memory, then 'dump' the array back into
the sheet.

VBA will NEVER be as fast or efficient as Excel's build-in functions.
That said, the following cell formula will update immediatelt when/as
the ref cell change...

Select B1:B3
Type: =IF($A1,"OK","")

...so if any cell in A1:A3 contains "TRUE" then its respective neighbor
in B1:B3 will display "OK", or return an empty string.
 
hi,

With Range("vbChkRefStatus")
.Value = wsMenu.Range("rngChkRef").Value
.Replace What:=True, Replacement:="OK", LookAt:=xlPart
.Replace What:=False, Replacement:="", LookAt:=xlPart
End With


--
isabelle



Le 2012-01-24 21:25, John a écrit :
 
Oops! I left something out. Geez.., I hate when that happens!!!
Couple of things to consider...

VB read/write from/to ranges is inherently slow.

Using an If...Then construct slows it down even more because VB has to
evaluate every iteration. Mind you, there's only 3 so this should be a hardly
negligable hit on performance.

Whenever you write to a range it works faster if you set...

Dim bEventsEnabled As Boolean, lCalcMode As Long
With Application
bEventsEnabled = .EnableEvents: lCalcMode = .Calculation
Click to expand...

.EnabeEvents = False: .Calculation = xlCalculationManual
 
This is butter. Thanks, Isabelle.


hi,

With Range("vbChkRefStatus")
     .Value = wsMenu.Range("rngChkRef").Value
     .Replace What:=True, Replacement:="OK", LookAt:=xlPart
     .Replace What:=False, Replacement:="", LookAt:=xlPart
End With

--
isabelle

Le 2012-01-24 21:25, John a écrit :










- Show quoted text -
Click to expand...
 
Back
Top