Pointers needed for speeding up nested loop macro in VBA

Question

I need help speeding up my straightforward, 8-variable, nested loop macro. Each loop still takes ~1 second and there are a few hundred thousand loops to completion so it takes 3 to 4 days to complete! I’ve really done as much streamlining as I can from my reading here and experimenting but have now hit a wall.

There are essentially 3 segments to my macro. 1) Assigning values to the variables on each loop, 2) plugging those values into my excel model to perform the calculations and 3) pasting the results from each iteration onto a new row each time. I’ve timed each segment and the time required for each segment is (approximately) 0.4s, 0.4s and 0.2s, respectively.

I'm mostly confused with segment 1) as it takes 0.4s just to assign values to the variables on each loop. I’m accepting that segment 2)’s slowness is the result of my excel spreadsheet being ~5,000 lines in size (and probably my bad excel programming) and 3) has already been restructured (thanks to you guys on this forum!) and is MUCH faster than before.

Here’s the code:

Sub VariableIteration2()
Dim a As Double
Dim b As Double
Dim c As Double
Dim d As Double
Dim w As Double
Dim x As Double
Dim y As Double
Dim z As Double
Dim Row As Long
Dim Results As Variant
Dim T1S As Double
Dim T1E As Double
Dim T2S As Double
Dim T2E As Double
Dim T3S As Double
Dim T3E As Double
Dim T4S As Double
Dim T4E As Double
Dim V1S As Double
Dim V1E As Double
Dim V2S As Double
Dim V2E As Double
Dim V3S As Double
Dim V3E As Double
Dim V4S As Double
Dim V4E As Double
Dim ST1 As Double
Dim ST2 As Double
Dim ST3 As Double
Dim ST4 As Double
Dim SV1 As Double
Dim SV2 As Double
Dim SV3 As Double
Dim SV4 As Double
Dim Startime As Single
Row = 18
T1S = Range("S3").Value
T1E = Range("S4").Value
T2S = Range("t3").Value
T2E = Range("t4").Value
T3S = Range("u3").Value
T3E = Range("u4").Value
T4S = Range("v3").Value
T4E = Range("v4").Value
V1S = Range("s6").Value
V1E = Range("s7").Value
V2S = Range("t6").Value
V2E = Range("t7").Value
V3S = Range("u6").Value
V3E = Range("u7").Value
V4S = Range("v6").Value
V4E = Range("v7").Value
ST1 = Range("s5").Value
ST2 = Range("t5").Value
ST3 = Range("u5").Value
ST4 = Range("v5").Value
SV1 = Range("s8").Value
SV2 = Range("t8").Value
SV3 = Range("u8").Value
SV4 = Range("v8").Value

‘SEGMENT_1_ASSIGNING_VALUES   
Startime = Timer
For a = V1S To V1E Step SV1
    For w = T1S To T1E Step ST1
        For b = V2S To V2E Step SV2
            For x = T2S To T2E Step ST2
                For c = V3S To V3E Step SV3
                    For y = T3S To T3E Step ST3
                        For d = V4S To V4E Step SV4
                            For z = T4S To T4E Step ST4
                            Range("dy20") = Timer - Startime
‘SEGMENT_2_PASTE_VARIABLES_INTO_CALCULATIONS
                             Startime = Timer
                                Range("s19").Value = w
                                Range("s20").Value = a
                                Range("t19").Value = x
                                Range("t20").Value = b
                                Range("u19").Value = y
                                Range("u20").Value = c
                                Range("v19").Value = z
                                Range("v20").Value = d
                                Range("s23:v24").Value = Range("s19:v20").Value
                             Range("dy21") = Timer - Startime
‘SEGMENT_3_RECORD_RESULTS
                             Startime = Timer
                                    Row = Row + 1
                                    Results = Range("g15:ax15")
                                    Range(Cells(Row, 131), Cells(Row, 131 + UBound(Results, 2) - 1)) = Results
                             Range("dy22") = Timer - Startime
                            Next z
                        Next d
                    Next y
                Next c
            Next x
        Next b
    Next w
Next a

End Sub

Thanks so much for helping.

Answer 1

Remember that each time you move/assign a value to a cell on a worksheet, Excel stops and recalculates the whole sheet again. @Joe's link to turn off this auto-calculation while you move your data will give you some speed boost. But...

Your loops would speed up tremendously if you perform all your calculations in the VBA routine instead of copying them to your worksheet and relying on your formulas to perform the calculations. Also, using memory-based arrays to hold your results can minimize how much your routine slows down for this copy. (Chip Pearson has a very good explanation of this.)

So my recommendations:

1. Use the loop variables (w, a, x, ...) to perform your calculations within your VBA routine, i.e. move the formulas into your code.
2. Store the results in a memory array Dim results(1 to 44) as Double
3. Copy that results array directly to the next area of your results range (g15:ax15).
4. Or better yet, append this result array to another memory array Dim allResults(1 to 1000, 1 to 44) as Double -- (hint: you can't do this in a single statement, you'll have to loop to copy from the results array to the next slot in the allResults array). Then block copy the allResults array to a Range on your worksheet.

You should get a large speed boost from these techniques.

Answer 2

When setting properties or calling methods, each is a function call in the CPU. That means stack setup overhead. Function calls are slower than inline code. Use loops rather than functions in VBA for the same reason.

For a start don't specify all those properties over and over again. Unless you change them they don't change.

With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchByte = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = False
    .MatchFuzzy = False

    For loop to go through each word pair
        .Text = SrcText
        .Replacement.Text = DestText
        .Find.Execute Replace:=wdReplaceAll
    Next

End With

Minimise Dots

So if you are interested in performance minimise dots (each dot is a lookup), especially in loops.

There are two ways. One is to set objects to the lowest object if you are going to access more than once.

eg (slower)

set xlapp = CreateObject("Excel.Application")
msgbox xlapp.worksheets(0).name 

(faster because you omitt a dot every time you use the object)

set xlapp = CreateObject("Excel.Application")
set wsheet = xlapp.worksheets(0)
msgbox wsheet.name

The second way is with. You can only have one with active at a time.

This skips 100 lookups.

with wsheet
For x = 1 to 100
 msgbox .name
Next
end with

String Concatination

And don't join strings one character at a time. With strings either concat small strings then concat the now larger strings. You can use the MID statement (not mid function) which is like a string builder.

Reading Properties

Don't reread properties that don't change especially if out of process or late bound. Put them into a variable. Reading variables is quick compared to an object(s) lookup (which is also a function call or at least two if late bound) and then a function call.

Variables

Constants and Literals are pretty much the same once compiled.

Const x = 5
msgbox x

is the same as

msgbox 5

Literals are inserted direct in code. String and object variables have managers, incuring overhead. Avoid creating variables for no reason. This is an example of a pointless and slow variable.

x = "This is a string"
msgbox x

compared to

const x = "This is a string"
msgbox x

or

msgbox "This is a string"

Object Types

Two concepts here - in or out of process and early or late binding.

exefiles are connected to out of process. All calls are marshalled over RPC (a networking protocol). Dllfiles are in process and function calls are made direct with a jump.

Early binding is set x = objecttype. Functions are looked up when you write the program. On execution the program is hard coded to jump to address stored in the vtable for that function.

Late binding is set x = createobject("objecttype"). Each function call goes like this. "Hi object do you have a print command". "Yes", it replies, "command number 3". "Hi object can you please do command number 3". "Sure, here's the result".

From Visual Basic Concepts (part of Help)

You can make your Visual Basic applications run faster by optimizing the way Visual Basic resolves object references. The speed with which Visual Basic handles object references can be affected by:

Whether or not the ActiveX component has been implemented as an in-process server or an out-of-process server.

Whether an object reference is early-bound or late-bound. In general, if a component has been implemented as part of an executable file (.exe file), it is an out-of-process server and runs in its own process. If it has been implemented as a dynamic-link library, it is an in-process server and runs in the same process as the client application.

Applications that use in-process servers usually run faster than those that use out-of-process servers because the application doesn't have to cross process boundaries to use an object's properties, methods, and events. For more information about in-process and out-of-process servers, see "In-Process and Out-of-Process Servers."

Object references are early-bound if they use object variables declared as variables of a specific class. Object references are late-bound if they use object variables declared as variables of the generic Object class. Object references that use early-bound variables usually run faster than those that use late-bound variables.

Excel Specific

See this link from a Microsoft person. This is excel specific rather than VBA. Autocalc and other calc options/screenupdating etc.

http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/

.

Answer 3

Screen updating and auto calculations can bring performance down.

This is explained in details here.