VBA Array Contains: How to Check for a Value in Excel

A VBA array contains multiple values stored under one variable name, with each value reached by an index or position. VBA does not provide a built-in Contains method for arrays, so to check whether a value is present, you normally loop through the array and compare its elements.

What a VBA array contains

An array is a variable that holds a sequence of related values. Instead of declaring separate variables such as color1, color2, and color3, you can store those values in one array called colors. Each element has an index, also called a subscript, that identifies its position.

Array elements can contain numbers, text, dates, Boolean values, or other supported VBA data. The declared data type determines what values an array can hold. A String array holds text, a Long array holds whole numbers, and a Variant array can hold values of different types. A Variant array can also contain objects, but object references need to be assigned and compared differently from ordinary values.

An array is not the same thing as a worksheet range. It exists in VBA memory while code runs; a range is a set of cells in a workbook. Code can copy data between them, which is useful when processing many cells efficiently.

How to declare and create an array

VBA supports fixed-size arrays, dynamic arrays, and arrays created as Variant values. A fixed-size array has a known number of positions when declared. A dynamic array can be sized later with ReDim. For example, these declarations show the main patterns:

Option Explicit

Sub DeclareArrays()
    Dim scores(1 To 5) As Long
    Dim names() As String
    Dim mixedValues As Variant

    ' Allocate five positions in the dynamic string array.
    ReDim names(1 To 3)

    ' Create a Variant array containing three values.
    mixedValues = VBA.Array("red", "green", "blue")
End Sub

The first array has five Long elements, indexed from 1 through 5. The second starts as a dynamic array with no allocated elements; ReDim gives it three positions. The third variable receives an array created by VBA.Array. Because it is a Variant array, its elements can hold different kinds of values, although keeping a collection of related values to one type is often easier to maintain.

With a dynamic array, ReDim Preserve can resize the array while retaining its existing values. In a multidimensional array, VBA allows the preserved dimension to change only in the last dimension. Plan the dimensions before using this feature, especially if the array is large or represents rows and columns of worksheet data.

Indexes, bounds, and array positions

Array indexes are not always zero-based. The lower and upper bounds depend on how the array was declared and, in some cases, on the module’s Option Base setting. An explicit declaration such as Dim items(1 To 10) As String makes the bounds clear. For an array whose bounds are not known in advance, use LBound and UBound rather than assuming the first index is zero or one.

For example, a loop can visit every index in a one-dimensional array like this:

Sub ShowArrayItems()
    Dim items As Variant
    Dim index As Long

    ' VBA.Array makes the lower bound explicit in this example.
    items = VBA.Array("oak", "maple", "pine")

    For index = LBound(items) To UBound(items)
        Debug.Print items(index)
    Next index
End Sub

VBA.Array creates an array with a zero lower bound, regardless of Option Base. The unqualified Array function can follow the module’s Option Base setting. Other array-producing functions can have their own rules: for example, Split returns a zero-based array. Checking the actual bounds prevents “Subscript out of range” errors when code assumes the wrong starting index.

How to check whether an array contains a value

For a simple membership test, loop over the elements and stop as soon as one matches. The following function is for a populated, one-dimensional array of ordinary string values. It compares text without regard to letter case, so “Maple” and “maple” count as a match.

Public Function ArrayContainsText(ByVal values As Variant, _
                                  ByVal sought As String) As Boolean
    Dim item As Variant

    ' This function expects a populated, one-dimensional string array.
    If Not IsArray(values) Then Exit Function

    For Each item In values
        If StrComp(CStr(item), sought, vbTextCompare) = 0 Then
            ArrayContainsText = True
            Exit Function
        End If
    Next item
End Function

Use the function from another procedure by passing an array and the value to find. The example prints True in the Immediate window because “MAPLE” matches “maple” under a text comparison.

Sub TestArrayContainsText()
    Dim trees As Variant
    Dim wasFound As Boolean

    ' Save a copy of the workbook before running macros.
    trees = VBA.Array("oak", "maple", "pine")
    wasFound = ArrayContainsText(trees, "MAPLE")

    Debug.Print wasFound
End Sub

To add these procedures in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the code there. On Mac, Option+F11 may require the Fn key depending on the keyboard. Excel for the web does not run VBA macros. Save a copy of the workbook before running a macro: changes made by VBA cannot be undone with Ctrl+Z (or Command+Z on Mac).

Choosing the right kind of comparison

“Contains” can mean different things. The function above checks for an entire matching element, not whether a shorter string appears inside a longer one. If an array element is "maple tree", searching for "maple" will not match it with StrComp. For a partial text search, use a function such as InStr inside the loop and decide whether the comparison should be case-sensitive.

Case-sensitive text: use StrComp with vbBinaryCompare.
Case-insensitive text: use StrComp with vbTextCompare, as in the example.
Numbers: compare numeric elements with the sought number, taking care to use compatible numeric types.
Partial text: use InStr to look for a substring within each element.

For numeric arrays, a direct equality comparison inside a loop is usually sufficient. For specialized values such as Null, error values, objects, or arrays nested inside Variants, decide explicitly what a match should mean and handle those types separately. The sample string function converts each element with CStr, so it is intended for normal string values, not error values or Null.

Common array problems and how to avoid them

A frequent cause of failure is checking bounds on an array that has not been allocated. IsArray can tell you that a variable is an array, but it does not guarantee that a dynamic array currently has usable elements. Calling LBound or UBound on an uninitialized dynamic array can raise an error. Initialize the array before searching, or add deliberate error handling for the empty or uninitialized case.

Another common mistake is treating a two-dimensional array like a one-dimensional list. A worksheet-shaped array may have row and column indexes, so a search may need nested loops or a clearly defined traversal. Also check whether the array contains cell values or objects: a range object and the values read from that range are not interchangeable.

Finally, remember that ordinary VBA comparisons can behave differently when values have different types. A text value such as "12" and the number 12 may be coerced during comparison. If the distinction matters, check the values’ types before comparing rather than relying on implicit conversion.

Arrays compared with collections and dictionaries

Arrays are a good fit when you know the set of values you need to process, want indexed access, or are moving a block of worksheet values into memory. They are compact and convenient for loops, but they do not have a built-in membership method. Searching an unsorted array generally means examining elements until a match is found or the array ends.

A VBA Collection is useful when you want to add or remove items more flexibly and retrieve them by key or position. A Scripting.Dictionary is often a better choice when you need repeated membership checks or key-to-value lookup. It offers an Exists method for checking keys, unlike an array. A dictionary does add setup and deployment considerations, including how the scripting library is referenced in different environments. Choose based on whether you need ordered positions, flexible items, or fast repeated key lookups—not simply because one structure sounds more advanced.

Frequently Asked Questions

Does VBA have an Array.Contains function?
No. VBA arrays do not have a built-in Contains method. Loop through the elements, or use a collection or dictionary when its built-in lookup behavior better suits the task.

How do I check whether an array contains a number?
Loop through the array and compare each numeric element with the number you are searching for. Use compatible numeric types and decide whether values such as 5 and 5.0 should count as equal in your application.

How do I check an array without regard to text case?
Compare strings with StrComp and the vbTextCompare option. Use vbBinaryCompare when capitalization should make a difference.

How can I find the bounds of a VBA array?
Use LBound(arrayName) for the lowest index and UBound(arrayName) for the highest. Do not assume that every array starts at zero, and guard against uninitialized dynamic arrays.

Can an array contain different data types?
Yes, when its elements are held in a Variant array. A typed array, such as a Long() or String() array, is intended for values of its declared type. Using one consistent type generally makes comparisons clearer.

Choosing a practical approach

For a small, populated array and a one-off check, a loop is direct, readable, and easy to adapt. Make the comparison rule explicit—whole value or partial text, case-sensitive or case-insensitive—and use the array’s real bounds when working by index. If the data is empty, uninitialized, multidimensional, or contains unusual Variant values, account for that case rather than assuming a basic string loop will cover it. For frequent lookups across a growing set of keys, consider a dictionary; for worksheet data, first decide whether you need the cell range itself or an in-memory array of its values.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.