Multi-select Combobox with comma separation between selections - making sure no comma at end

I am using the below VBA code to put comma separation between combobox (lstHomeroom) multi-selections. However, my code is giving me a comma at the end of the list. How can I amend my code to remove the comma at the end?

For example: Don't Want: selection1,selection2,selection3, Want: selection1,selection2,selection3

Dim x As Long
Dim y As Long
Dim Homeroomselected As String

For y = 2 To x

        'Update Homeroom
        For Z = 0 To lstHomeroom.ListCount - 1
        
            If lstHomeroom.Selected(Z) = True Then
                Homeroomselected = Homeroomselected & lstHomeroom.List(Z) & ","
            End If
            
            Next Z
            Sheets("Input").Cells(y, 4).Value = Homeroomselected

Next y

Solution

It is a common task to concatenate an unknown number of strings, including a separator character (in your case a comma). If you have an array of values, you can use the VBA function join for that, but in your case this will not work because you don't have an array and you want to add only the selected items.

Basically, there are two ways to solve this issue.

The first is what you currently do in your code, always append the separator character, and when you're done, remove the last separator. This is what @Tom suggested in the comments:

Homeroomselected  = Left(Homeroomselected, Len(Homeroomselected) - 1)

This has a small problem: When nothing was selected, you will get a runtime error as the Len of the string is 0, and -1 is not a valid parameter to the Left function. To be sure, you will need to check for that:

If Homeroomselected <> "" Then
    Homeroomselected  = Left(Homeroomselected, Len(Homeroomselected) - 1)
End If

And, if you change the separator to let's say ", ", you need to remember that now you need to remove 2 characters.

What I also don't like so much is that the string in invalid until the very last moment. You have to ensure that the last character is always removed. Consider you have a loop to create the string and decide for whatever reason you have enough data and want to exit (for example the maximum length or maximum number of elements was reached). You need to ensure that the statement to remove the last character is always executed, so you can't simply issue an Exit Function statement.

The second method is to check if we need the comma at all. As we cannot tell in advance if another element is to be added later, reverse the logic: Before you write an element, check if there was already an element. If yes, add the comma to the existing element.

If lstHomeroom.Selected(Z) = True Then
    If Homeroomselected <> "" Then
        Homeroomselected = Homeroomselected & ","
    End If
    Homeroomselected = Homeroomselected & lstHomeroom.List(Z)
End If

Which can be written in short using the IIf-function:

Homeroomselected = Homeroomselected & IIf(Homeroomselected <> "", ",", "") & lstHomeroom.List(Z)

Or, you could create a small routine:

Sub JoinString(ByRef result As String, element As String, Optional Separator As String = ",")
    result = result & IIf(result <> "", Separator, "") & element
End Sub

and use it like that:

If lstHomeroom.Selected(Z) Then
    JoinString Homeroomselected, lstHomeroom.List(Z), ","
End If