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?

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?

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?

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

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?



--