VBA | Individual Iterations faster than Loop Statement

[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?

[Patrick Molloy]

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?
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

[Butaambala]

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

[Howard Kaikow]

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.

--
http://www.standards.com/; See Howard Kaikow's web site.
"Butaambala" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Jake]

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?
>


--