Collection.Add doesn't create new item but adds to item1 (1 Viewer)

[Guus2005]

I have a lot of concatenations to do so i used a class to do that more efficiently.
clsBuildString

It uses a collection to store every string and then joins everything together. Which should be faster than x = a & b & c.

When i debug the code i notice that new items are not created and all added strings are concatenated to the first one.
In the end that is what i wanted but i find it odd that the code doesn't do what it is supposed to do.

But perhaps i am doing something wrong?

The clsBuidString code is from the internet.

I have attached an example database with the code.

To test the functionality.

Public Sub CollectionTest()
'Exporteren van de data.
    Dim strLine As New clsBuildString
  
    strLine.Add "1000S0000"
    strLine.Add "999"
    strLine.Add "FOO"
    strLine.Add "!!!"
    strLine.Add "BAR"
  
    Debug.Print strLine.Text

End Sub

The class clsBuildString

Private mcolStrings As Collection
Private mstrDelim As String

Private Sub Class_Initialize()
    Set mcolStrings = New Collection
    Delim = ""
End Sub

Private Sub Class_Terminate()
    Set mcolStrings = Nothing
End Sub

Public Property Get Delim() As String
    Delim = mstrDelim
End Property

Public Property Let Delim(ByVal strNewValue As String)
    mstrDelim = strNewValue
End Property

Public Property Get Text() As String
    Dim lngNext As Long
    Dim strStrings() As String
    Dim varItem As Variant
    Dim strText As String
  
    Select Case mcolStrings.Count
        Case 0
            Text = vbNullString
        Case 1
            Text = mcolStrings(1)
        Case Else
            ReDim strStrings(1 To mcolStrings.Count)
            For Each varItem In mcolStrings
                lngNext = lngNext + 1
                strStrings(lngNext) = varItem
            Next
            Set mcolStrings = Nothing
            Set mcolStrings = New Collection
            strText = Join(strStrings, mstrDelim)
            mcolStrings.Add strText
            Text = strText
    End Select
End Property

Public Property Let Text(ByVal strNewValue As String)
    Set mcolStrings = Nothing
    Set mcolStrings = New Collection
    mcolStrings.Add strNewValue
End Property

Public Function Add(ByVal strNewValue As String)
    mcolStrings.Add strNewValue
End Function

 

[arnelgp]

Which should be faster than x = a & b & c.

there is a String Builder class i found here:
VBA-StringBuilder/StringBuilder.cls at master · retailcoder/VBA-StringBuilder · GitHub

unfortunately, it is very slow compared to direct Concatenation (&).

 

[ebs17]

Public Sub CollectionTest()
'Exporteren van de data.
    Dim strLine As New clsBuildString
   
    strLine.Add "1000S0000"
    strLine.Add "999"
    strLine.Add "FOO"
    strLine.Add "!!!"
    strLine.Add "BAR"
    strLine.Add "999"
    strLine.Add "999"
   
    Debug.Print strLine.Text   ' => 1000S0000999FOO!!!BAR999999
End Sub

Your class is not doing anything efficient. It is simply code overhead here in actual use.
I don't need a collection for a join operation on array values.

A collection would be efficient if you wanted to avoid duplicates and check if a new value already exists. But this is visibly not done here.

 

[ebs17]

I have a lot of concatenations to do

One might ask the question:
What exactly do you REALLY want to do?

What have been the efficiency problems so far? Which initial structures, which processes?

 

[Guus2005]

i have to concatenate 200K records into a textfile for further processing.
I took 4 hours to process by doing x = a & b & c.
I know that there i a much faster way to do this by using clsBuildString.
But instead of creating new items in the collection, each add command concatenates to item1. Which imo is not correct.

I want ot speed things up to one hour or less.

[edit]
And, no, writing to the textfile is not the problem because that's not even in the picture.
I write to a table which will be exported later to the said file
[/edit]

 

[ebs17]

i have to concatenate 200K records into a textfile for further processing

If you are moving in a database, the concrete starting situation (schema of the database) and the exact target would be interesting.
Mostly simple query solutions with a function that compiles the desired content are sufficient.
Three such functions (already somewhat performance optimized) I have shown here (in German):

SQL ist leicht (22) - Zusammenfassen von Datensätzen - MS-Office-Forum

(Der Beitrag soll u.a. auch Informationen aus dem leider nicht mehr verfügbaren DBWiki bereitstellen und eine einfache Nachschlagemöglichkeit sein.) Zielrichtung ist es, Inhalte von mehreren Datensätzen aus einem Feld oder mehreren Feldern zu einem kompakten Einzelinhalt zusammenzufassen...

www.ms-office-forum.net

A query can be exported directly to a text file.

I want ot speed things up to one hour or less.

Less than a minute would be more of a serious goal, but I don't know the overall task.

 

[Guus2005]

Thanks Eberhard,

Declaring space for the variables to concatenate in is very nice!
I had such a class once but i lost it. I think i will try that solution.
I don't speak German but i do speak VBA so that's no problem.

And on-topic:
Collection.Add should add an item to the collection and not add the string to item1.

 

[Guus2005]

Tried to register on that site to get the Demo_Concat.zip sample database but that didn't work. Bummer.

 

[ebs17]

Collection.Add should add an item to the collection

This is exactly what happens.
But when you call the text property, quite a lot happens. The individual items are loaded into an array, concatenated by join, the collection is terminated and recreated, the assembled string is entered into the new collection.

All in all: A lot of effort for nothing. Individual contents could also be written into an array and then the join applied. But then it doesn't look so spectacular anymore.

I can also upload the file directly.

 

[Guus2005]

Yes, you are right. I should have seen that. Must be getting old...
Thanks for the demo_concat.zip file!