Join function (user types)

[Kanbei]

Hi. I have a variable array of a type i made:

(this is not waht i have... but it will do as an example)

--------------------
Type AllData
Name as string
LastName as String
Age as Integer
End Type

Dim Employee(3) as AllData
--------------------

Now: I want to join all the names with "-" as delimeter
I've tryed everything but can't make it, is it possible??

Thanks

[Merri]

Dim lngA As Long, strNames As String
 
strNames = Employee(0).Name
For lngA = 1 To UBound(Employee)
    strNames = strNames & "-" & Employee(lngA).Name
Next lngA

[space_monkey]

Dim i as long
Dim strNames as string
strNames = ""
For i = 0 to 3
     strNames = strNames & Employee(i).Name & " " Employee(i).LastName & "-"
Next i
strNames = Left(strNames, Len(strNames) - 1)

HTH

Edit: Merri's is a little Nicer. Just a little...not much really...lol

[Kanbei]

thanks. good idea....
but now suppose i want to join lot of stuff, so i need to make a function in a module.
Also i dont know how many big the array wil be.

So a parameter should be the array to join and then use LBound and UBound, but i cant declare a parameter as an Array and use the L/UBound....
how do i do that??

[szlamany]

And yet another way...

Dim i as long
Dim strNames as string
strNames = ""
strDelim = ""
For i = 0 to 3
     strNames = strNames & strDelim & Employee(i).Name & " " & Employee(i).LastName
     If i = 0 Then strDelim = "-"
Next i
' strNames = Left(strNames, Len(strNames) - 1) ** don't need this anymore

[Merri]

So a parameter should be the array to join and then use LBound and UBound, but i cant declare a parameter as an Array and use the L/UBound....
how do i do that??

Here is a function for the job:

Public Function EmployeeJoin(Employee As udtMyType, Separator As String) As String
    Dim strOut As String, lngA As Long
    ' check for existing items
    If (Not Employee) = True Then Exit Function
    ' insert the first item
    strOut = Employee(LBound(Employee)).Name
    ' insert the remainder
    For lngA = LBound(Employee) + 1 To UBound(Employee)
        strOut = strOut & Separator & Employee(lngA).Name
    Next lngA
    EmployeeJoin = strOut
End Function

For more information on checking if array is initialized, see the FAQ for How do I check if array has been initialized?

[Kanbei]

thanks, ill chekc all the ideas you posted and post again

And sorry space_monkey i started to write the 2nd post b4 you posted yours

[CVMichael]

This should be faster than the previous examples when joining lots of records.

Option Explicit
 
Private Type AllData
    Name As String
    LastName As String
    Age As Integer
End Type
 
Private Employee(3) As AllData
 
Private Function JoinAllData(Data() As AllData, ByVal Separator As String) As String
    Dim K As Long, StrOut As String, Pos As Long
    Dim LenName As Long, LenSep As Long
    
    LenSep = Len(Separator)
    StrOut = String(5000, 0)
    Pos = 1
    
    For K = LBound(Data) To UBound(Data)
        LenName = Len(Data(K).Name)
        
        If (Pos + LenSep + LenName) > Len(StrOut) Then
            StrOut = StrOut & String(5000, 0)
        End If
        
        Mid$(StrOut, Pos, LenSep) = Separator
        Pos = Pos + LenSep
        
        Mid$(StrOut, Pos, LenName) = Data(K).Name
        Pos = Pos + LenName
    Next K
    
    JoinAllData = Mid$(StrOut, LenSep + 1, Pos - LenSep - 1)
End Function
 
Private Sub Form_Load()
    Employee(0).Name = "faskdjfhsdkjfh"
    Employee(1).Name = "nbnbmnbmnbmnb"
    Employee(2).Name = "asdf"
    Employee(3).Name = "23423423"
    
    Debug.Print JoinAllData(Employee, "-")
End Sub