Add to string array alphabetically
I'm looking for a way to add a string (from a cell) to a string array in alphabetical order.
For example:
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 whole 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
source to share
You can use the class System.Collections.SortedList
from the library .NET
if you like. Then there is 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
Printing
apple
banana
cherry
orange
If you want to concatenate the values into a string, just concatenate them as you pass through the values, or you can pass them into an array and use them 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, """, """) & """}"
source to share
It is not clear where the declaration or assignment vStrings
and 'cherry' are, but here is a helper function call that adds an array and returns a delimited list (one text value) in Sheet1 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 added options to specify the delimiter character (default as semi-colony) and changed the sort order.
source to share