Visual Basic Topic

Question

My intended title fo this post: VB6 (VBA) Loop Code Optimization (Sorry for any troubles)

So I have been using this code in my VB6 (VBA) program:

Public Sub Form_Load()
    Dim Output As String
    For i = 1 To 990
        For i2 = 1 To 990
            Output = Output & i * i2
        Next
    Next
End Sub

And am having trouble with the for loops being extremely slow, and takes a few hours to execute in extreme cases. Any tips to optimize my code?

This will always result in the same answer so why are you calculating it each time? Just work it out once and hard code the answer (Output wil be something like 8*10^8 I think) Whoops just noticed you''re using & so this will be a very long string not a number . So it's doing a long to string conversion everytime through the loop - no wonder it's taking a while - even more reason to hard code the string — Harassed Dad, Dec 24 '18 at 10:15

score 0 · Answer 1 · answered Dec 24 '18 at 10:49

I can hardly guess what's that for, but an intermediate step with arrays speeds it up

Public Sub Form_Load2()
    Const n As Long = 500, n2 As Long = 200

    Dim Output As String
    Dim i As Long, i2 As Long

    ReDim vals(1 To n2) As String
    For i = 1 To n
        For i2 = 1 To n2
            vals(i2) = i * i2
        Next
        Output = Output & Join(vals, "")
    Next
    Debug.Print Output
End Sub

With i and i2 limited, respectively, to 500 and 200 it is almost instantaneous

it's up to you to try larger number, but I'll advise you to keep little steps..

Aleksandr M. · Answer 2 · 2018-12-24T11:55:31.910

This will be a bit faster then the solution provided by @DisplayName

Public Sub Form_Load()
Const I_MAX     As Long = 990
Const I2_MAX    As Long = 990

Dim Output  As String
Dim i       As Long
Dim i2      As Long
Dim lCount  As Long
Dim arr     As Variant

lCount = 0
ReDim arr(I_MAX * I2_MAX)

For i = 1 To I_MAX
    For i2 = 1 To I2_MAX
        arr(lCount) = i * i2
        lCount = lCount + 1
    Next
Next

Output = Join(arr, vbNullString)
Debug.Print Output
End Sub

yowyow · Answer 3 · 2018-12-24T21:02:46.790

Your problem is that you are shuffling millions of megabytes of memory. At the halfway point your code is copying 5.7 MBytes from one memory location to another each time through the inner loop. A total off 5,700 Gigabytes over the entire program.

The rules are join small strings together then join the smaller strings at the end of the concatenation. To make the smaller strings no larger than say 4000 bytes then join those files at the end.

Modern languages have a string builder object, VBA has one too it's just not called that.

In VBA there is the Mid function and the Mid statement. They do opposite things. So you create your final sting first. Then using the Mid statement pop your substrings into the first string. This avoids all the 100s of megabytes of shuffling. Use Rtrim to lop off any excess spaces at the end. If you Dim the Output to 6 million spaces then it will fit in your example, and use about 12 MBytes compared to thousands of Gigabytes.

Here is a small example.

Private Sub Form_Load()
OutPut = Space(1500)

For x = 1 To 500
    Offset = Offset + Len(CStr(x))
    Mid(OutPut, Offset) = CStr(x)
Next
MsgBox OutPut
End Sub

See Pointers needed for speeding up nested loop macro in VBA

Visual Basic Topic

3 Answers3