CODEWITHSUNDEEP
excelvba
Dumitru Daniel
Dumitru Daniel

Reputation: 543

combine two or more string arrays

I'm trying to find the fastest way to concatenate row values from about 7 columns, from huge files(800 000 rows).

I found an almost instant way of loading each column in separate arrays.

But when I use the code below, the processing is very slow, like 2-3 seconds for each 100 rows.

Maybe you guys can help me with a way to concatenate values from parallel arrays without a for.

The columns are not in the correct order.

For rowI = LBound(arrShInvoice) To UBound(arrShInvoice)
    dKey = arrShInvoice(rowI, 1)
    dItem = ""

    Call displayStatusBarEvents("Procesing " & dFName & vbNewLine & "row: ", rowI, UBound(arrShInvoice), 100, , True)
    For Each colI In arrTargetFields
        If dItem = "" Then
            dItem = dData(colI)(rowI, 1)
        Else
            dItem = dItem & delimiterI & dData(colI)(rowI, 1)
        End If
    Next
    dDescriptive.Add dKey, dItem
Next

I'm fairly advanced with VBA so hit my with your best ideas.

Upvotes: 1

Views: 508

Answers (1)

brettdj
brettdj

Reputation: 55672

Difficult as your post is general, and we haven't seen the full code (would be good to see it!). Will add this as answer given it doesn't suit a set of comments

You must read Optimize string handling in VB6

  1. When concatenating, join the longs strings once. dItem & (delimiterI & dData(colI)(rowI, 1)) is much superior to dItem & delimiterI & dData(colI)(rowI, 1) which uses Ditem twice
  2. VbNullString or Len(Ditem) = 0 is quicker than If dItem = ""
  3. Your IFtest should branch to the more likely outcome first to avoid going via Else. If dItem is not likely to be blank, change your IF flow
  4. If you must have a progress indicator, use the StatusBar every 10000 rows or so (using Mod) rather than call a separate function (which you are doing each line)

your new question

Short of using PowerShell you will have to combine your arrays with a For loop, ie

Dim Array1()
Dim Array2()
Dim lngRow As Long

Array1 = Array("a", "b", "c")
Array2 = Array(1, 2, 3)

For lngRow = 0 To UBound(Array1)
    Array1(lngRow) = Array1(lngRow) & Array2(lngRow)
Next

Upvotes: 1

Related Questions