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.
- This example was developed in Excel 2013 Pro 64 bit.
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]