Useful Array Functions for VBA, Part 6

It has been nearly a year since my last post about functions adapted from PHP for use in VBA. Sorry! :roll: In this article we'll look at some functions for moving values around inside arrays.

Shifting Arrays

The array_shift function removes an element from the beginning of an array, "shifting" it one position to the right. This is a simple process, requiring a loop in VBA.

Function Array_Shift(arr As Variant) As Variant
' http://www.php.net/manual/en/function.array-shift.php

  Dim tempArray As Variant
  Dim i As Long

  tempArray = arr

  ' shift elements one position up
  ' by skipping the first element of the source array
  For i = LBound(tempArray) To UBound(tempArray) - 1
    tempArray(i) = tempArray(i + 1)
  Next i

  ' remove last element
  ' which is now empty
  ReDim Preserve tempArray(LBound(tempArray) To UBound(tempArray) - 1)

  Array_Shift = tempArray

End Function

Reverse the Elements of an Array

To reverse the elements of an array, we just need to loop through it backwards (in a manner of speaking).

Function Array_Reverse(arr As Variant) As Variant
' http://www.php.net/manual/en/function.array-reverse.php

  Dim tempArray As Variant
  Dim i As Long

  ' make copy of array the same size as source
  ReDim tempArray(LBound(arr) To UBound(arr))

  For i = LBound(tempArray) To UBound(tempArray)
    tempArray(i) = arr(UBound(tempArray) - i)
  Next i

  Array_Reverse = tempArray

End Function

Note that we could have looped backwards through either the source or target array.

Searching Arrays

array_search searches an array and returns the key associated with the value being sought. For our purposes, we will simply return the index position of the found element. Which is basically the array key anyway, since VBA doesn't do associative arrays (except for Dictionary objects).

Function Array_Search(arr As Variant, valueToFind As Variant, _
    Optional offset As Long) As Long
' http://www.php.net/manual/en/function.array-search.php
  Dim i As Long
  Dim joinedArray As String
  Dim combinedString As Variant
  Dim leftPosition As String

  For i = (LBound(arr) + offset) To UBound(arr)
    If StrComp(arr(i), valueToFind) = 0 Then
      Array_Search = i + 1
      Exit For
    End If
  Next i

End Function

If you knew the relative position of the item you were looking for, you could specify an offset to make the function run a bit faster. Also note that a string comparison is done.

Replacing values in arrays

Inspired by array_replace, with a twist. For simplicity, this function takes an array and replaces it with a given value.

Function Array_Replace(array1 As Variant, valueToReplace As Variant, _
    valueToFind As Variant) As Variant
' http://www.php.net/manual/en/function.array-replace.php

  Dim tempArray As Variant
  Dim i As Long
  Dim valuePosition As Long

  tempArray = array1

  Do Until IsInArray(tempArray, valueToReplace) = False
    valuePosition = _
      Array_Search(tempArray, valueToReplace, valuePosition) - 1
    tempArray(valuePosition) = valueToFind
  Loop

  Array_Replace = tempArray
End Function

All found values are replaced. We could have also used a combination of Join and Replace to do the same thing (I'll post that function at some point in the future).

Array_Replace requires the below function:

Function IsInArray(arr As Variant, valueToFind As Variant, _
    Optional offset As Long) As Boolean
' http://www.jpsoftwaretech.com/checking-arrays-exact-matches/

  Dim i As Long

  For i = (LBound(arr) + offset) To UBound(arr)
    If StrComp(arr(i), valueToFind) = 0 Then
      IsInArray = True
      Exit For
    End If
  Next i

End Function

In the next post we'll review functions for randomly sorting arrays.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
Random Data Generator