VBA Offset within Loop - taking forever to run

Question

I'm brand new to programming, and I figured VBA is a good place for me to start since I do a lot of work in Excel.

I created a macro that takes an integer from an input box (I've been using 2, 3 and 4 to test) and it creates a set of a 4-tier hierarchy of that number; e.g. entering "2" would produce

1.0.0.0
1.0.0.1
1.0.0.2
1.0.1.0
1.0.1.1
1.0.1.2 etc.

I got the macro to work as intended, but it takes forever to run. I think it's the offsets within the loops that are slowing it down. Does anyone have any suggestions to speed this up? Any general feedback is welcome as well.

Sub Tiers()

'Input Box
Dim Square As Integer
Square = InputBox("Enter Number of Tiers")
Range("f5").Select
Selection.Value = 0
 With Application
    .ScreenUpdating = False
End With

'Rows down
Dim g As Integer
Dim h As Integer
Dim i As Integer
Dim j As Integer

'Start For loops
For g = 1 To Square
    For h = 0 To Square
        For i = 0 To Square
            For j = 0 To Square

                'calculate offsets and place values of loop variables
                Dim step As Long
                step = ((g - 1) * (Square + 1) ^ 3 - 1 + (h * (Square + 1) ^ 2) + Square * i + i + j + 1)
                Selection.Offset(step, 0).Value = j
                Selection.Offset(step, -1).Value = i
                Selection.Offset(step, -2).Value = h
                Selection.Offset(step, -3).Value = g


            Next j
        Next i
    Next h
Next g

With Application
    .ScreenUpdating = True
End With

End Sub

Thanks

Welcome to the forums. What is step = ((g - 1) * (Square + 1) ^ 3 - 1 + (h * (Square + 1) ^ 2) + Square * i + i + j + 1)? Also looping and writing to sheets like this will be too slow. Write to an array and then write the array to worksheet — Siddharth Rout
– Siddharth Rout, Commented Oct 2, 2018 at 16:35
The "step" is the number of rows down that the offset goes to then place each variable. Each output (1 0 0 0, 1 0 0 1) is placed in an individual row across four columns (one per digit). And thank you, I will research arrays and figure out how to use those. — dmihel
– dmihel, Commented Oct 2, 2018 at 16:38
Declare/allocate a 2D variant array, populate the 2D array, dump the array onto the worksheet. You'll find that the less you read/write from/to the worksheet, the faster your code gets. Selection isn't supposed to change during the execution of the loop - it should be captured into a Range reference at the start of the procedure, and never invoked ever again. Repeatedly taking the Selection, accessing the Offset member of the Range interface through IDispatch late-bound calls (because Selection is Object, not Range), is what's killing it. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 2, 2018 at 16:44
Also note, Step, as in For i = 9 To 0 Step -1, is a keyword, or should be treated as one: avoid using language keywords as identifiers, even if the compiler lets you. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 2, 2018 at 16:45

Siddharth Rout · Accepted Answer · 2018-10-02 16:52:13Z

6

Further to my comment below your post, looping and writing to sheets like this will be too slow. Write to an array and then write the array to worksheet. This ran in a blink of an eye.

Is this what you are trying?

Sub Sample()
    Dim TempArray() As Long
    Dim n As Long
    Dim g As Long, h As Long, i As Long, j As Long
    Dim reponse As Variant

    '~~> Accept only numbers
    reponse = Application.InputBox(Prompt:="Enter Number of Tiers", Type:=1)

    If reponse <> False Then
        For g = 1 To reponse
            For h = 0 To reponse
                For i = 0 To reponse
                    For j = 0 To reponse
                        n = n + 1
                    Next j
                Next i
            Next h
        Next g

        ReDim Preserve TempArray(1 To n, 1 To 4)
        n = 1

        For g = 1 To reponse
            For h = 0 To reponse
                For i = 0 To reponse
                    For j = 0 To reponse
                        TempArray(n, 1) = g
                        TempArray(n, 2) = h
                        TempArray(n, 3) = i
                        TempArray(n, 4) = j
                        n = n + 1
                    Next j
                Next i
            Next h
        Next g

        '~~> Replace this with the relevant sheet
        Sheet1.Range("A1").Resize(UBound(TempArray), 4).Value = TempArray
    End If
End Sub

Screenshot:

answered Oct 2, 2018 at 16:52

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Davesexcel Over a year ago

Yes it ran very fast, it looks like 31 is the max you can enter before running out of room

Siddharth Rout Over a year ago

yes. It took 3 seconds for me for 31 and reached A1015808. For 33 it gave an error as it needs 1297032 rows.

dmihel Over a year ago

Amazing... this is exactly it, thank you! So, it looks like you have one set of loops to create the size of the array and then the second set to place the values in the array? Is it possible to do both at the same time?

Siddharth Rout Over a year ago

@dmihel: You are welcome :) I would recommend reading up on arrays. See this LINK

chris neilsen Over a year ago

You don't need the first loop. Calculate n from responce. Untested, I think its n = ( response +1)^3 × responce

Mathieu Guindon · Accepted Answer · 2018-10-02 16:47:35Z

1

The step calculation seems superfluous:

step = ((g - 1) * (Square + 1) ^ 3 - 1 + (h * (Square + 1) ^ 2) + Square * i + i + j + 1)

Try the following:

Sub Tiers()

'Input Box
Dim Square As Long
Square = InputBox("Enter Number of Tiers")
With Application
    .ScreenUpdating = False
End With

'Rows down
Dim g As Long
Dim h As Long
Dim i As Long
Dim j As Long
Dim step As Long

step = 1

For g = 1 To Square
    For h = 0 To Square
        For i = 0 To Square
            For j = 0 To Square
                Range("F5").Offset(step, 0).Value = j
                Range("F5").Offset(step, -1).Value = i
                Range("F5").Offset(step, -2).Value = h
                Range("F5").Offset(step, -3).Value = g
                step = step + 1
            Next j
        Next i
    Next h
Next g

With Application
    .ScreenUpdating = True
End With

End Sub

edited Oct 2, 2018 at 16:47

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Oct 2, 2018 at 16:37

cybernetic.nomad

6,5763 gold badges20 silver badges36 bronze badges

1 Comment

dmihel Over a year ago

Ha... You are correct. Kind of funny to see that formula rendered superfluous by "x = x+1" I definitely should have thought of that, thanks.

Collectives™ on Stack Overflow

VBA Offset within Loop - taking forever to run

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related