Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Q] For, Next - How to Speed Up
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For, Next - How to Speed Up
On Jan 24, 9:25*pm, John wrote:
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! 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Q] For, Next - How to Speed Up
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Q] For, Next - How to Speed Up
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 .EnabeEvents = False: .Calculation = xlCalculationManual .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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Q] For, Next - How to Speed Up
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 : 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For, Next - How to Speed Up
This is butter. Thanks, Isabelle.
On Jan 24, 8:52*pm, isabelle wrote: 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 : 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!- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For, Next - How to Speed Up
glad to help, thanks for the feedback!
-- isabelle Le 2012-01-25 21:04, John a écrit : This is butter. Thanks, Isabelle. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed it up? | Excel Programming | |||
Speed | Excel Programming | |||
Speed this up for me, please | Excel Programming | |||
Is there any way to speed this up? | Excel Programming | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) |