Yes, it is very much so possible. I created a set of Text functions as the one below is just one of them that adds an item to a string variable. I created this function in Access. However, there are things I could do to make this more improved, but it's something you may want to take a look at.
Function fncAddItem(ByVal strItemList As String, ByVal item As String, ByVal Sep As String, Optional ByVal lngPos As Long, _
Optional ByVal lngOrder As Long, Optional ByVal SepCpr As Long = 0, Optional ByVal ItemCase As Long = 0, _
Optional ByVal TieBreaker As Long = 0) As String
'Either use lngPos or lngOrder for the sorting. If both are stated, order will take precedence.
'lngOrder has the possible valid values
'1 = Numeric Ascending
'2 = Numeric Descending
'3 = Alpha Ascending
'4 = Alpha Descending
'5 = Alpha, then Numeric Ascending
'6 = Alpha, then Numeric Descending
'7 = Numeric, then Alpha Ascending
'8 = Numeric, then Alpha Descending
'9 = First
'10 = Last
'11 = Random
Dim X As Long, L As Long, U As Long, M As Long, UI As String, LI As String, MI As String, LL As Long, ML As Long, UL As Long, IL As Long, XL As Long
Dim I As Long, MA As Long, IA As Long, LA As Long, UA As Long, YL As Long, UP As Long, LP As Long, IP As Long, TS As String
Dim IU As Long, MU As Long, LU As Long, UU As Long
On Error GoTo ErrHandle
Randomize
fncAddItem = strItemList
If SepCpr <> 1 Then
SepCpr = 0
End If
If ItemCase <> 1 Then
ItemCase = 0
End If
If TieBreaker <> 1 Then
TieBreaker = 0
End If
X = modTxtFncs.fncItemCount(strItemList, Sep, SepCpr)
Select Case lngOrder
Case 1 To 8
L = 1
U = X
LI = modTxtFncs.fncItem(strItemList, Sep, L, SepCpr)
UI = modTxtFncs.fncItem(strItemList, Sep, U, SepCpr)
LL = VBA.Strings.Len(LI)
UL = VBA.Strings.Len(UI)
IL = VBA.Strings.Len(item)
Do While L <= U
M = Int((L + U) / 2)
MI = modTxtFncs.fncItem(strItemList, Sep, M, SepCpr)
ML = VBA.Strings.Len(MI)
Select Case lngOrder
Case 1
If CLng(LI) > CLng(MI) Or CLng(UI) < CLng(MI) Then
GoTo ExitFnc 'List not properly sorted
ElseIf CLng(MI) > CLng(item) Then
U = M - 1
Else
L = M + 1
End If
Case 2
If CLng(LI) < CLng(MI) Or CLng(UI) > CLng(MI) Then
GoTo ExitFnc 'List not properly sorted
ElseIf CLng(MI) > CLng(item) Then
L = M + 1
Else
U = M - 1
End If
Case 3 To 4
'To be sorted based on how the ascii codes are done
'The only exception to this rule, all letters are to be sorted with upper case before lower case as a secondary sort.
'Example: AaBbCc...
'If items are not to be sorted based on case sensitivity, then all lower cases will be considered as upper cases for this purpose.
If item = "" Then
TS = item & Sep & strItemList
Else
If IL > ML Then
XL = IL
YL = ML
Else
XL = ML
YL = IL
End If
IP = 0
LP = 0
UP = 0
For I = 1 To XL Step 1
IA = fncCC(IL, I, ItemCase, item)
MA = fncCC(ML, I, ItemCase, MI)
UA = fncCC(UL, I, ItemCase, UI)
LA = fncCC(LL, I, ItemCase, LI)
IU = fncUC(IA)
MU = fncUC(MA)
LU = fncUC(LA)
UU = fncUC(UA)
If lngOrder = 3 Then
If LP > -1 Then
If LU > MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf LU < MU Then
LP = LP - 3
ElseIf LP = 0 And ItemCase = 1 Then
If LA < MA Then
LP = 1
ElseIf LA > MA Then
LP = 2
End If
End If
End If
If UP > -1 Then
If UU < MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf UU > MU Then
UP = UP - 3
ElseIf UP = 0 And ItemCase = 1 Then
If UA > MA Then
UP = 1
ElseIf UA < MA Then
UP = 2
End If
End If
End If
If IU < MU Then
IP = IP - 3
U = M - 1
Exit For
ElseIf IU > MU Then
IP = IP - 3
L = M - 1
Exit For
ElseIf IP = 0 And ItemCase = 1 Then
If IA < MA Then
IP = 1
ElseIf IA > MA Then
IP = 2
End If
End If
Else
If LP > -1 Then
If LU < MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf LU > MU Then
LP = LP - 3
ElseIf LP = 0 And ItemCase = 1 Then
If LA > MA Then
LP = 1
ElseIf LA < MA Then
LP = 2
End If
End If
End If
If UP > -1 Then
If UU > MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf UU < MU Then
UP = UP - 3
ElseIf UP = 0 And ItemCase = 1 Then
If UA < MA Then
UP = 1
ElseIf UA > MA Then
UP = 2
End If
End If
End If
If IU > MU Then
IP = IP - 3
U = M - 1
Exit For
ElseIf IU < MU Then
IP = IP - 3
L = M - 1
Exit For
ElseIf IP = 0 And ItemCase = 1 Then
If IA > MA Then
IP = 1
ElseIf IA < MA Then
IP = 2
End If
End If
End If
Next I
If UP = 2 Or LP = 2 Then
GoTo ExitFnc 'List not properly sorted
ElseIf L <= M And U >= M Then
If IP = 2 Then
L = M + 1
ElseIf IP = 1 Then
U = M - 1
ElseIf TieBreaker = 1 Then
U = M - 1
Else
L = M + 1
End If
End If
End If
Case 5
Case 6
Case 7
Case 8
End Select
Loop
TS = fncItemAdd(strItemList, item, Sep, L, SepCpr)
Case 9
TS = item & Sep & strItemList
Case 10
TS = strItemList & Sep & item
Case 11
lngPos = Int(Rnd * X + 1)
TS = fncItemAdd(strItemList, item, Sep, lngPos, SepCpr)
Case Else
If lngPos > 0 And lngPos < X + 2 Then
TS = fncItemAdd(strItemList, item, Sep, lngPos, SepCpr)
Else
GoTo ExitFnc
End If
End Select
fncAddItem = TS
ExitFnc:
Exit Function
ErrHandle:
Resume ExitFnc
End Function
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.
