xlf | GetTextInArray function


GetTextInArray - about the function


Description: search for a text string in a VBA array. Returns a Boolean True / False

Syntax: GetTextInArray(InArr, InString)

Arguments: InArr (required): the target VBA array, InString (required): the search string


Is text string in array? A function to search for a text string in an array. Function name - GetTextInArray - see code 1.


GetTextInArray - the VBA code



Code 1: Function procedure GetTextInArray loop through a VBA array to find string 
Function GetTextInArray(InArr() As String, InString As String) As Boolean
Dim i As Integer
    For i = LBound(InArr, 1) To UBound(InArr, 1)
        If InArr(i) = InString Then
            GetTextInArray = True
            Exit For
        End If
    Next i
End Function

The code 1 For ... Next loop searches a one dimensional array, or the first dimension of a multidimensional array - line 3 - Note, the syntax for LBound is LBound(arrayname [,dimension])


In code 2, a four element string array is passed ByRef to the function, with the Debug.Print method in lines 10 to 18 sending results to the immediate window (see figure 1).



Code 2: Sub procedure TestGetTextInArray send one dimensional array and search string to called function 
Private Sub TestGetTextInArray()
Dim i As Integer
Dim Arr(1 To 4) As String
'' Populate array
    Arr(1) = "aa"
    Arr(2) = "bb"
    Arr(3) = "cc"
    Arr(4) = "dd"
'' Output to immediate window
    Debug.Print vbNewLine & "==============================="
    Debug.Print "Time: " & Format(Time, "hh:mm:ss am/pm")
    Debug.Print vbNewLine & "The array ->"
        For i = LBound(Arr) To UBound(Arr)
            Debug.Print "Arr(" & i & "): " & Arr(i)
        Next i
    Debug.Print vbNewLine & "Is bb in array?: " & GetTextInArray(Arr, "bb")
    Debug.Print "Is bbb in array?: " & GetTextInArray(Arr, "bbb")
    Debug.Print "Is bbb NOT in array?: " & Not GetTextInArray(Arr, "bbb")
End Sub

Run the private procedure from the VBE. Use F5 or Run > Run Sub on the menu.


xlf-find-text-in-array-immediate-w
Fig 1: Immediate window with output from TestGetTextInArray