VBA | Individual Iterations faster than Loop Statement

  • Thread starter Thread starter Butaambala
  • Start date Start date
B

Butaambala

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?
 
did you try:-
For i = 1 to 14

Next

since yo know there's 14 loops, this would seem more efficient anyway.
 
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)
 
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
 
You need to recode the loop so that you minimize the retrieval of objects
For example, you are re-opening each .xls file each time you do a .activate.

Open the files outside of the loop, assigning each to a workbook object, the
activate the object within the loop. By itself, I would guess this would
show a large improvement.

Next remove redundant uses of objects within the Loop using

With

End With

statements.

Etc.
 
Have you tried inserting a Doevents statement inside the loop ?

Jake Moskowitz

Jacob.Moskowitz at db dot com
212-250-4636


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?
Click to expand...


--
 
Back
Top