The year is 2015 and we still have to use WinWrap Basic when it comes to Transformations (KTM’s Project Builder or KTA’s Transformation Designer). As it is unlikely that this will change very soon, why not make the best of it? Here are some of the more useful functions for arrays that did not make it into WWB-COM. Oh yes, and they work with variants, so strings, integers, doubles – whatever you need.
Pushing and Popping
Push and Pop are the most common methods and essential when working with arrays. Push adds a new item to the very end of the array, while pop removes the last item (returning it at the same time)
' pushes a new item to the end of an array Public Sub Array_Push(arr() As Variant, item As Variant) ReDim Preserve arr(UBound(arr) + 1) arr(UBound(arr)) = item End Sub ' pops (removes) the last item of an array, returning that item Public Function Array_Pop(arr() As Variant) Dim item As Variant item = arr(UBound(arr)) ReDim Preserve arr(UBound(arr) - 1) Return item End Function
Contains and Count
The contains method tells you if a given item already is present in an array, and the Count method gives you the number of occurrences of said item.
' returns true if an array already contains the given item Public Function Array_Contains(arr() As Variant, itemToSearchFor As Variant) As Boolean Dim i As Long For i = 0 To UBound(arr) If arr(i) = itemToSearchFor Then Array_Contains = True Exit Function End If Next End Function ' returns the # of occurrences of an item within an array Public Function Array_Count(arr() As Variant, itemToSearchFor As Variant) As Long Dim i As Long, count As Long count = 0 For i = 0 To UBound(arr) If arr(i) = itemToSearchFor Then count += 1 End If Next Return count End Function
Sort and Distinct
Sorting allows you to rearrange an array. The sample below uses BubbleSort, feel free to change it to a more efficient method such as QuickSort when needed. Then, there is the Distinct method that eliminates all duplicate entries from an array.
' sorts an array using bubblesort, returning the sorted array Public Sub Array_BubbleSort(ByRef arr() As Variant) Dim n As Long, newn As Variant, i As Long, tmp As Variant n = UBound(arr) Do newn = 1 For i = 0 To n - 1 If arr(i) > arr(i+1) Then 'swap tmp = arr(i+1) arr(i+1) = arr(i) arr(i) = tmp newn = i+1 End If Next n = newn Loop While (n > 1) End Sub ' removes all duplicate entries from an array Public Sub Array_Distinct(arr() As Variant) Dim x() As Variant Dim distinctCount As Long, i As Long distinctCount = 0 ' worst case: no duplicates - so both arrays are of the same size ReDim x(0) ' the first item will always be added x(0) = arr(i) distinctCount += 1 For i = 1 To UBound(arr) If Not Array_Contains(x, arr(i)) Then ' item is not present - add it Array_Push(x, arr(i)) ' at the same time, we increase the distinctCount distinctCount += 1 End If Next ' finally, reduce the size of the array and return it ReDim Preserve x(distinctCount - 1) arr = x End Sub
The following sample script shows you how to make use of the methods shown above.
' we need to work with a dynamic array variable all times (do not set the bounds when initializing it) Dim x() As Variant ' dynamically changing the bounds to three items (0..2) ReDim x(2) ' set items old-school x(0) = "banana" x(1) = "apple" x(2) = "pear" ' more fancy: pushing new items to the end of the array ' the array will automatically grow in size! Array_Push(x, "peach") Array_Push(x, "grape") Array_Push(x, "mango") Array_Push(x, "mango") Array_Push(x, "mango") Array_Push(x, "salmon") ' now, remove the last item Debug.Print Array_Pop(x) & " removed from the array!" ' even fancier: sort the array with bubble sort (works also on string-variants!) Array_BubbleSort(x) ' misc functions ' check if the array constains an item Debug.Print Array_Contains(x, "banana") ' --> TRUE Debug.Print Array_Contains(x, "plantain") ' --> FALSE ' count the occurrences of a single item Debug.Print Array_Count(x, "mango") ' --> 3 ' reduce the array to distinct items Array_Distinct(x)
Here’s how the different steps look like:
There are some other methods not covered yet, such as slicing and reversing – but I think you get the idea of how to implement them easily.