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.