About This Code
Brief Description:
Concatenating two arrays/strings with Trim and Unique option
Contributor:
Andrew Jones
Last Modified:
17 Jun 2002
OpenNTF Disclaimer
All of the program code and information presented in the OpenNTF.org Code Bin are provided "as-is", and should be used at your own risk. OpenNTF.org make no express or implied warranty about anything in the Code Bin, and OpenNTF.org will not be responsible or liable for any damage caused by the use or misuse of anything from this site. OpenNTF.org makes no guarantees about anything. Please thoroughly test all of the knowledge and code you find here before you attempt to use them in your production environment.
Code / Description
This function is used to concatenate two arrays or strings and returns the concatenated values based on the filter options specified. The filter options are:
1. Trim the null entries
2. Unique entry
The filter option specified can be any one them or both. If none of the above action is specified, then the concatenated values will be returned as it is. The 'ConcatArray' is the main function which in turn calls 2 other functions 'TrimArray' and 'Unique'.
Usage / Example
A sample code to use this function:
Sub Click(Source As Button)
Const PARAM_NULL = ""
Const PARAM_TRIM = 1
Const PARAM_UNIQUE = 2
Dim Array1(3) As Variant
Dim Array2(1) As Variant
Dim array3 As Variant
Array1(0) = "List1"
Array1(1) = "List2"
Array1(2) = ""
Array1(3) = "List4"
Array2(0) = "List1"
Array2(1) = "List3"
Array3 = ConcatArray(Array1, Array2, PARAM_TRIM + PARAM_UNIQUE)
Forall item In Array3
Msgbox Item, 0 , "ConcatArray"
End Forall
Array3 = ConcatArray("Hello", Array2, PARAM_TRIM)
Forall item In Array3
Msgbox Item, 0 , "ConcatArray"
End Forall
Array3 = ConcatArray("Hello", "World", PARAM_TRIM)
Msgbox Array3, 0 , "ConcatArray"
End Sub
Code
Function ConcatArray ( vArray1 As Variant, vArray2 As Variant , Byval iOption As Integer) As Variant
' Author: C.Saravanan
' Purpose : This function is used to concatenate two arrays/strings. It has options to trim or unique the resultant value.
Const PARAM_NULL = ""
Const PARAM_TRIM = 1
Const PARAM_UNIQUE = 2
Dim vFinalList () As Variant
Dim iFListIdx As Integer
Dim strValues As String
ConcatArray = PARAM_NULL
strValues = ""
If Not Isarray(vArray1 ) And Not Isarray(vArray2 ) Then
ConcatArray = Cstr(vArray1) + Cstr(vArray2)
Else
If Not Isarray(vArray1) Then
Redim vFinalList(Ubound(vArray2)+1)
For iFListIdx = Lbound(vArray2) To Ubound(vArray2)
vFinalList(iFListIdx) = vArray2(iFListIdx)
Next
vFinalList(iFListIdx) = vArray1
Elseif Not Isarray(vArray2) Then
Redim vFinalList(Ubound(vArray1)+1)
For iFListIdx = Lbound(vArray1) To Ubound(vArray1)
vFinalList(iFListIdx) = vArray1(iFListIdx)
Next
vFinalList(iFListIdx) = vArray2
Else
iFListIdx = -1
Forall Item In vArray1
iFListIdx = iFListIdx + 1
Redim Preserve vFinalList(iFListIdx)
vFinalList(iFListIdx) = Cstr(Item)
End Forall
Forall Item In vArray2
iFListIdx = iFListIdx + 1
Redim Preserve vFinalList(iFListIdx)
vFinalList(iFListIdx) = Cstr(Item)
End Forall
End If
Select Case iOption
Case PARAM_UNIQUE: ' Unique
ConcatArray = Unique(vFinalList)
Case PARAM_TRIM: ' Trim
ConcatArray = TrimArray(vFinalList)
Case PARAM_UNIQUE + PARAM_TRIM: ' Unique + Trim
ConcatArray = TrimArray(Unique(vFinalList))
Case Else
ConcatArray = vFinalList
End Select
End If
End Function
Function TrimArray(vGivenList As Variant) As Variant
' Function written by C.Saravanan
' Purpose : Used to eliminate the duplicate values in an array. (No @Formula used)
' Advantage over @Formula fn - This function will handle all characters (including ; , . [all special chars] (etc))
Dim vFinalList () As Variant
Dim iFListIdx As Integer
Dim iFound As Integer
iFListIdx = -1
If Isarray(vGivenList) Then
Forall GItem In vGivenList
If GItem <> "" Then
iFListIdx = iFListIdx + 1
Redim Preserve vFinalList(iFListIdx)
vFinalList(Ubound(vFinalList)) = GItem
End If
End Forall
TrimArray = vFinalList
Else
TrimArray = vGivenList
End If
End Function
Function Unique(vGivenList As Variant) As Variant
' Function written by C.Saravanan
' Purpose : Used to eliminate the duplicate values in an array. (No @Formula used)
' Advantage over @Formula fn - This function handles all characters (including ; , . [all special chars] (etc))
Dim vFinalList () As Variant
Dim iFListIdx As Integer
Dim iFound As Integer
iFListIdx = -1
If Isarray(vGivenList) Then
Forall GItem In vGivenList
iFound = False
Forall FItem In vFinalList
If FItem = GItem Then
iFound = True
Exit Forall
End If
End Forall
If Not iFound Then
iFListIdx = iFListIdx + 1
Redim Preserve vFinalList(iFListIdx)
vFinalList(Ubound(vFinalList)) = GItem
End If
End Forall
Unique = vFinalList
Else
Unique = vGivenList
End If
End Function