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
Sample Usage
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.