0

I have a macro that creates a very long string by looping through a 2D array which can sometimes contain 30,000 lines. It can take a half hour or more. Is there a way to speed this up. It pulls a spreadsheet into an array and then creates another array with that info. This part doesn't have any issues.

It slows down when looping through the second array which creates a single very long string that is used to create a text file.

Agingarray = XLSbk.Sheets("Aging_Report").Range("A4").CurrentRegion
ReDim FirstArray(1 To UBound(Agingarray) * UBound(Agingarray, 2) * 31, 1 To 8)
i = 1
For x = 2 To UBound(Agingarray)
    For y = 4 To LastCol
        If Agingarray(x, y) > 50 Then
        If Trim(Agingarray(x, 2)) = "" Then Exit For
            MI = ""
            If InStr(1, Agingarray(x, 2), ",") > 0 Then
                Namestr = Split(Agingarray(x, 2), ",")
                LastName = Trim(Namestr(0))
                Fname = Split(Trim(Namestr(1)), " ")
                If Trim(Namestr(1)) <> "" Then
                    NameFirst = Trim(Fname(0))
                    If UBound(Fname) = 1 Then
                        MI = Trim(Fname(1))
                    End If
                Else
                    NameFirst = ""
                End If
            Else
                LastName = Trim(Agingarray(x, 2))
                NameFirst = ""
            End If

            Monthend = Application.WorksheetFunction.EoMonth(Agingarray(1, y), 0)
            For j = Agingarray(1, y) To Monthend
                FirstArray(i, 1) = LastName
                FirstArray(i, 2) = NameFirst
                FirstArray(i, 3) = MI
                FirstArray(i, 4) = Agingarray(x, LastCol + 6)
                FirstArray(i, 5) = Format(Agingarray(x, LastCol + 8), "000000000")
                FirstArray(i, 6) = Agingarray(x, LastCol + 9)
                FirstArray(i, 7) = Agingarray(x, LastCol + 10)
                FirstArray(i, 8) = j
                i = i + 1
            Next j
        End If
    Next y
Next x

This is the part of the code that slows down.



For x = LBound(FirstArray) To UBound(FirstArray)
    If FirstArray(x, 1) = "" Then Exit For
    Body = Body & "HL*" & h & "*1*21*1~" & PrvLine & "HL*" & i & "*" & j & "*22*0~" & _
    "TRN*1*" & Format(Now, "hhmmss") & k & "*" & 9100000000# + k & "*00309417~" & _
    "NM1*IL*1*" & FirstArray(x, 1) & "*" & FirstArray(x, 2) & "*" & FirstArray(x, 3) & "***"
    If Trim(FirstArray(x, 4)) <> "" Then
        Body = Body & "MI*" & Trim(FirstArray(x, 4)) & "~"
        LineCount = LineCount + 8
    Else
        Body = Body & "*~REF*SY*" & Trim(Format(FirstArray(x, 5), "000000000")) & "~"
        LineCount = LineCount + 9
    End If
    Body = Body & "DMG*D8*" & Trim(Format(FirstArray(x, 6), "yyyymmdd")) & "*" & Trim(FirstArray(x, 7)) & "~" & _
    "DTP*291*D8*" & Format(FirstArray(x, 8), "yyyymmdd") & "~" & _
    "EQ*30~"
    h = h + 2
    i = i + 2
    j = j + 2
    k = k + 1
Next x
ezfrish
  • 11
  • 5
  • 1
    Voting to close as `cannot be reproduced` based on the info provided. Please include a little more structure around your situation, possibly indicating what happens before and after this snippit. Is there anything else within this snippit? Looks as though you're attempting to append to an email and ensuring that no other back and forth between, say, outlook/excel occurs would be key. – Cyril Jan 24 '20 at 17:47
  • 2
    This would be better posted in [codereview](https://codereview.stackexchange.com/). Though I suggest you to post it complete. – Ricardo Diaz Jan 24 '20 at 17:47
  • Building very long strings by repeated concatenation is an anti-pattern in many languages, which is why you find `StringBuilder` classes in those languages. VBA unfortunately doesn't have one, but you can roll your own, or use one someone else has already written. Previously - https://stackoverflow.com/questions/31787570/using-pre-made-stringbuilder-class and https://codereview.stackexchange.com/questions/67596/a-lightning-fast-stringbuilder – Tim Williams Jan 24 '20 at 17:54
  • For the sake of testing, have you looked at created a second array of the same bounds and instead of `body = body & blah`, write each new line to the second array and append that in the end? – Cyril Jan 24 '20 at 17:54
  • 1
    ...and @TimWilliams got to what i was alluding with expanding a single string (much more elegantly, with links); i feel like my thought was justified! lol – Cyril Jan 24 '20 at 17:56
  • @TimWilliams Thanks! I will take a look at those. – ezfrish Jan 24 '20 at 18:06
  • ..another approach which will also be faster is to write directly to the text file without buffering it in a string first. – Tim Williams Jan 24 '20 at 18:07
  • @TimWilliams While VBA while it doesn't have a string builder, because they weren't called anything special when VBA was written, it has all the primitives. See `Mid` statement and not the `Mid()` function - they do opposite of each other. –  Jan 24 '20 at 18:07
  • @Mark - I'm aware that you *can* build strings quickly in VBA (eg see the links I posted). Just pointing out that (a) just concatenating strings is slow (b) people already figured out how to do it quickly so the OP doesn't have to figure it out themselves. – Tim Williams Jan 24 '20 at 18:11
  • @TimWilliams Turns out the fastest route was this one "another approach which will also be faster is to write directly to the text file without buffering it in a string first. – Tim Williams " down from over half an hour to 9 seconds!! Thank you! – ezfrish Jan 26 '20 at 02:41

0 Answers0