CODEWITHSUNDEEP
arraysvbaexcelsorting
Rachkay
Rachkay

Reputation: 45

Adding to a string array alphabetically

I'm looking for a way to add a string (from a cell) to a string array alphabetically.

For instance:

string array = {"apple", "banana", "orange"}

add "cherry":

string array = {"apple", "banana", "cherry", "orange"}

Hence if I do sheets(1).range("A1").value = new string array, the entire array will be in one cell.

I found a function online that sorts selected cells alphabetically, but not sure if it helps in my instance. 

Function Alphabetize(vStrings As Variant, separator As String) As String

    Dim v As Variant, vSorted As Variant
    Dim i As Long, j As Long, n As Long
    Dim bDone As Boolean

    For Each v In vStrings
        n = n + 1
    Next
    ReDim vSorted(1 To n)
    ReDim pos(1 To n)
    For Each v In vStrings
         i = i + 1
         vSorted(i) = v
    Next
    For j = 2 To n
         bDone = True
         For i = 2 To n
             If vSorted(i) < vSorted(i - 1) Then
                 v = vSorted(i - 1)
                 vSorted(i - 1) = vSorted(i)
                 vSorted(i) = v
                 bDone = False
             End If
         Next
         If bDone Then Exit For
    Next
    For i = 1 To n
         If vSorted(i) <> "" Then
             If i = 1 Then
                 Alphabetize = separator & vSorted(i)
             Else
                 If vSorted(i) <> vSorted(i - 1) Then Alphabetize = Alphabetize & separator & vSorted(i)
             End If
         End If
    Next
    Alphabetize = Mid$(Alphabetize, 2)
End Function

Upvotes: 3

Views: 175

Answers (2)

user4039065
user4039065

Reputation:

It isn't clear where the declaration or assignment of vStrings and 'cherry' are but here is a sub calling the function that appends the array and returns a delimited list (single text value) to Sheet1's A1.

Sub main()
    Dim string_array As Variant, new_string As String

    string_array = Array("apple", "banana", "orange")
    new_string = "cherry"

    Sheets(1).Range("A1").Value = add_and_alphabetize(string_array, new_string, sDELIM:=Chr(44))

End Sub

Function add_and_alphabetize(vSTR As Variant, sSTR As String, _
         Optional sDELIM As String = ";", Optional bDESC As Boolean = False)
    Dim i As Long, j As Long, vTMP As Variant

    If CBool(Len(sSTR)) Then
        ReDim Preserve vSTR(LBound(vSTR) To UBound(vSTR) + 1)
        vSTR(UBound(vSTR)) = sSTR
    End If

    For i = LBound(vSTR) To UBound(vSTR) - 1
        For j = i To UBound(vSTR)
            If (vSTR(i) < vSTR(j) And bDESC) Or (vSTR(i) > vSTR(j) And Not bDESC) Then
                vTMP = vSTR(j)
                vSTR(j) = vSTR(i)
                vSTR(i) = vTMP
            End If
        Next j
    Next i

    add_and_alphabetize = Join(vSTR, sDELIM)
End Function

I've added options to specify the delimiter character (defaulted as a semi-colon) and change the order of the sort.

Upvotes: 0

Bond
Bond

Reputation: 16311

You can use the System.Collections.SortedList class from the .NET library, if you want. Then there's no need to worry about sorting.

Dim objList As Object
Set objList = CreateObject("System.Collections.SortedList")
objList.Add "apple",  ""
objList.Add "banana", ""
objList.Add "orange", ""
objList.Add "cherry", ""

Dim i As Long
For i = 0 To objList.Count - 1
    Debug.Print objList.GetKey(i)
Next

Prints:

apple
banana
cherry
orange

If you want to combine the values into a string, just concatenate them as you loop through the values or you can transfer to an array and use Join to create the string:

ReDim a(objList.Count - 1) As String
Dim i As Long

For i = 0 To objList.Count - 1
    a(i) = objList.GetKey(i)
Next

' Combine strings into the format: {"string1", "string2", "stringN"}
Sheet1.Range("A1").Value = "{""" & Join(a, """, """) & """}"

Upvotes: 3

Related Questions