Tip

Concatenating two arrays/strings with Trim and Unique option

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'.

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


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

This was first published in August 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.