Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA | Individual Iterations faster than Loop Statement
I have a procedure that loops through 14 data sets, evaluating the
variables for inclusion in a model. I find that when I LOOP through the 14 iterations, the performance seems "bogged-down", and often results in 'not enough memory' errors and the like. Code sample: i = 1 Do while i <= 14 ...CODE.... ...CODE.... ...CODE.... i = i +1 Loop Executing each iteration manually, however, results in more efficient processing, no errors, and accurate results. The problem, of course, is that I'd like to LOOP so I can start the procedure, go to sleep, and awake with accurate results. Instead I am a slave to the computer to affect one key-stroke every 30 mins. This evidence begs the question: How does the computer differentiate the two approaches? How can I programmatically replicate the difference? Any thoughts? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA | Individual Iterations faster than Loop Statement
did you try:-
For i = 1 to 14 Next since yo know there's 14 loops, this would seem more efficient anyway. "Butaambala" wrote: I have a procedure that loops through 14 data sets, evaluating the variables for inclusion in a model. I find that when I LOOP through the 14 iterations, the performance seems "bogged-down", and often results in 'not enough memory' errors and the like. Code sample: i = 1 Do while i <= 14 ...CODE.... ...CODE.... ...CODE.... i = i +1 Loop Executing each iteration manually, however, results in more efficient processing, no errors, and accurate results. The problem, of course, is that I'd like to LOOP so I can start the procedure, go to sleep, and awake with accurate results. Instead I am a slave to the computer to affect one key-stroke every 30 mins. This evidence begs the question: How does the computer differentiate the two approaches? How can I programmatically replicate the difference? Any thoughts? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA | Individual Iterations faster than Loop Statement
Are you creating variables which are not being released at the end? It may
be best to show the code in more detail. -- HTH RP (remove nothere from the email address if mailing direct) "Butaambala" wrote in message oups.com... I have a procedure that loops through 14 data sets, evaluating the variables for inclusion in a model. I find that when I LOOP through the 14 iterations, the performance seems "bogged-down", and often results in 'not enough memory' errors and the like. Code sample: i = 1 Do while i <= 14 ...CODE.... ...CODE.... ...CODE.... i = i +1 Loop Executing each iteration manually, however, results in more efficient processing, no errors, and accurate results. The problem, of course, is that I'd like to LOOP so I can start the procedure, go to sleep, and awake with accurate results. Instead I am a slave to the computer to affect one key-stroke every 30 mins. This evidence begs the question: How does the computer differentiate the two approaches? How can I programmatically replicate the difference? Any thoughts? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA | Individual Iterations faster than Loop Statement
I am probably creating variables which are not being released (because
I've made no effort to release them), but I think there is something more fundamental here. What is the difference between me putting a break at the "LOOP" line, hitting F5 to run the procedure, then hitting F5 again at each break point .VS. no break point and hitting F5 once?? Does Excel/VBA somehow "reset itself" at each break? It is almost like the procedure gets bogged down under its own weight, that it can only release at a break point. Here is some of the code: Sub mcrCycleYears() Dim rngYears As Range Dim cellYears As Range Dim ws As Worksheet Dim adrStart As Integer Dim adrEnd As Integer Dim c As Integer Application.ScreenUpdating = False Sheets("input").Select dteEnd = Range("C2").Value adrEnd = Range("D2").Value Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Set rngYears = Selection c = 0 For Each cellYears In rngYears Windows("TOOL_20050502_TAA_VariableSelection.xls") .Activate Sheets("data").Activate Cells.ClearContents Sheets("input").Select cellYears.Select dteStart = Selection.Value adrStart = Selection.Offset(0, 1).Value mcrVariableEvaluation_LOOP adrStart, adrEnd Sheets("data_ZeroMC").Select Cells.ClearContents Sheets("data").Select Cells.Select Selection.Copy Sheets("data_ZeroMC").Select Range("A1").Select Selection.PasteSpecial xlValues LoopArray 0.999, 0.998, 0.99, 0.95, 0.9, 0.8, 0.7, 0.6, 0.5 Sheets("CorrelationMatrix").Select Range("A2:B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("data_Correlations").Select Cells(2, (4 * c) + 2).Select Selection.PasteSpecial xlValues 'Selection.PasteSpecial xlFormats Windows("20050504_data_ZeroMC.xls").Activate Sheets.Add ActiveSheet.Name = "data_" & adrStart Set ws = ActiveSheet Windows("TOOL_20050502_TAA_VariableSelection.xls") .Activate Sheets("data_ZeroMC").Select Cells.Select Selection.Copy Windows("20050504_data_ZeroMC.xls").Activate ws.Select Range("A1").Select Selection.PasteSpecial xlValues ActiveWorkbook.Save c = c + 1 Next cellYears Application.ScreenUpdating = True End Sub Public Function LoopArray(ParamArray rng() As Variant) Dim i As Integer For i = 0 To UBound(rng()) SetCriteria (rng(i)) mcrVariableEvaluation Next End Function Sub mcrVariableEvaluation_LOOP(adrStart As Integer, adrEnd As Integer) Dim rng As Range Dim cell As Range Dim ws As Worksheet Dim wb As Workbook Dim wsName As String Dim i As Integer i = 1 Sheets("data").Select Cells.ClearContents Do While i <= 14 wsName = "data_" & i Sheets("data_ZeroMC").Select Cells.ClearContents Windows("20050504_data_AllTrans.xls").Activate Sheets(wsName).Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Windows("TOOL_20050502_TAA_VariableSelection.xls") .Activate Sheets("data_ZeroMC").Select Range("A1").Select Selection.PasteSpecial xlValues Windows("20050504_data_AllTrans.xls").Activate Sheets(wsName).Select Range("A" & adrEnd, "A" & adrStart).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Windows("TOOL_20050502_TAA_VariableSelection.xls") .Activate Sheets("data_ZeroMC").Select Range("A2").Select Selection.PasteSpecial xlValues Application.DisplayAlerts = False Sheets("CorrelationMatrix").Select Cells.Clear Range("A1").Select Sheets("data_ZeroMC").Activate Range("C1").Select Selection.End(xlToRight).End(xlDown).Activate adrRowEnd = ActiveCell.Address Sheets("CorrelationMatrix").Select Application.Run "ATPVBAEN.XLA!Mcorrel", Range("data_ZeroMC!$C$1:data_ZeroMC!" & adrRowEnd), ActiveSheet.Range("$A$1"), "C", True mcrElimPerfMC Sheets("CorrelationMatrix").Select Cells.Clear LoopArray 0.999, 0.998, 0.99, 0.95, 0.9, 0.8, 0.7, 0.6, 0.5 Sheets("data_ZeroMC").Select If i = 1 Then Range("A1").Select Else Range("D1").Select End If If Selection.Offset(0, 1).Value = "" Then Range(Selection, Selection.End(xlDown)).Select Else Range(Selection, Selection.End(xlDown).End(xlToRight)).Select End If Selection.Copy Sheets("data").Select Range("A1").Select If i = 1 Then Else Selection.End(xlToRight).Offset(0, 1).Select End If Selection.PasteSpecial xlValues i = i + 1 ActiveWorkbook.Save Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA | Individual Iterations faster than Loop Statement
Have you tried inserting a Doevents statement inside the loop ?
Jake Moskowitz Jacob.Moskowitz at db dot com 212-250-4636 Butaambala wrote: I have a procedure that loops through 14 data sets, evaluating the variables for inclusion in a model. I find that when I LOOP through the 14 iterations, the performance seems "bogged-down", and often results in 'not enough memory' errors and the like. Code sample: i = 1 Do while i <= 14 ...CODE.... ...CODE.... ...CODE.... i = i +1 Loop Executing each iteration manually, however, results in more efficient processing, no errors, and accurate results. The problem, of course, is that I'd like to LOOP so I can start the procedure, go to sleep, and awake with accurate results. Instead I am a slave to the computer to affect one key-stroke every 30 mins. This evidence begs the question: How does the computer differentiate the two approaches? How can I programmatically replicate the difference? Any thoughts? -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
for loop with if statement | Excel Worksheet Functions | |||
Why can the number of iterations repeat many times as a loop?? | Excel Discussion (Misc queries) | |||
If statement - Loop? | Excel Discussion (Misc queries) | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Faster For-Next Loop? | Excel Programming |