To check whether a VBA array contains a value, loop through its elements and compare each one with the value you are looking for. For a one-dimensional array, a small Boolean function makes the check reusable; for a range or a simple lookup, VBA’s Application.Match can be a shorter alternative.
A reusable loop for a one-dimensional array
VBA does not provide a general-purpose Contains method for ordinary arrays. A loop is explicit, works with arrays created in VBA, and lets you decide how comparisons should behave. This function expects a one-dimensional array of comparable values:
Public Function ArrayContains(ByVal target As Variant, ByRef values As Variant) As Boolean
Dim i As Long
Dim firstIndex As Long
Dim lastIndex As Long
' Return False if values is not an array or has no usable bounds.
If Not IsArray(values) Then Exit Function
On Error GoTo NoElements
firstIndex = LBound(values, 1)
lastIndex = UBound(values, 1)
On Error GoTo 0
' Stop as soon as a matching element is found.
For i = firstIndex To lastIndex
If values(i) = target Then
ArrayContains = True
Exit Function
End If
Next i
Exit Function
NoElements:
Err.Clear
End Function
The function starts out False, which is the answer if no element matches. When it finds an equal value, it sets the return value to True and exits immediately. Reading the lower and upper bounds rather than assuming the array starts at zero matters: arrays can have different lower bounds, and some are declared with a starting index of 1.
Here is a complete caller. It creates a Variant array, checks for a value, and displays the result:
Public Sub CheckFruitArray()
Dim fruits As Variant
Dim found As Boolean
' Array creates a zero-based, one-dimensional Variant array.
fruits = Array("Apple", "Pear", "Plum")
found = ArrayContains("Pear", fruits)
If found Then
MsgBox "Pear is in the array."
Else
MsgBox "Pear is not in the array."
End If
End Sub
Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even if a particular check only displays a message. In Excel for Microsoft 365 on Windows, Alt+F11 opens the Visual Basic Editor; on Mac, use Developer > Visual Basic or Option+F11 (some keyboards require Fn as well). Excel for the web does not run VBA macros.
What the comparison actually means
The expression values(i) = target uses VBA’s equality rules. For strings, the result depends on the module’s Option Compare setting: the default, Option Compare Binary, compares text case-sensitively, while Option Compare Text compares it without regard to case. If a module does not state an option, Binary comparison is the default. For example, under Binary comparison, “Pear” and “pear” are different strings.
If you want the function to make its case-insensitive behavior obvious without changing a module-level setting, use StrComp for text. This version is intended for strings, not mixed data types:
Public Function TextArrayContainsIgnoreCase(ByVal target As String, _
ByRef values As Variant) As Boolean
Dim i As Long
Dim firstIndex As Long
Dim lastIndex As Long
If Not IsArray(values) Then Exit Function
On Error GoTo NoElements
firstIndex = LBound(values, 1)
lastIndex = UBound(values, 1)
On Error GoTo 0
For i = firstIndex To lastIndex
If StrComp(CStr(values(i)), target, vbTextCompare) = 0 Then
TextArrayContainsIgnoreCase = True
Exit Function
End If
Next i
Exit Function
NoElements:
Err.Clear
End Function
Do not use that text-specific version indiscriminately on arrays that may contain Excel error values, Nulls, or objects: converting those elements with CStr can fail. For mixed or potentially unusual data, handle those cases deliberately before comparing, or keep the array values consistently typed.
Using Application.Match instead of a loop
For a straightforward exact lookup, Application.Match can return the matching position. Passing 0 as its third argument requests an exact match. Assign the result to a Variant and test it with IsError; when there is no match, Application.Match returns an error value rather than a normal position.
Public Sub FindWithMatch()
Dim fruits As Variant
Dim matchPosition As Variant
' Array creates a one-dimensional list for this example.
fruits = Array("Apple", "Pear", "Plum")
matchPosition = Application.Match("Pear", fruits, 0)
If IsError(matchPosition) Then
MsgBox "No exact match was found."
Else
MsgBox "Found at array position " & CStr(matchPosition)
End If
End Sub
The returned position is the item’s position in the lookup list, counted from 1, not necessarily its VBA array index. For the example, “Pear” is the second item, so Match returns 2 even though the Array function created indexes 0, 1, and 2. If you need the actual array index, or want custom comparison rules, a loop is often easier to reason about.
Match is not always a drop-in replacement for a loop. Exact text matching is not case-sensitive, and wildcard characters can have special meaning in text searches. Prefer an explicit loop with StrComp when you need case-sensitive matching or literal handling of characters such as an asterisk or question mark.
Checking a worksheet range
If the items are in a single row or column on a worksheet, you can pass the range directly to Match. This example assumes the values are in cells A2:A20 on the active sheet:
Public Sub FindInWorksheetColumn()
Dim matchPosition As Variant
' Search A2:A20 on the active worksheet for an exact match.
matchPosition = Application.Match("Pear", Range("A2:A20"), 0)
If IsError(matchPosition) Then
MsgBox "Pear is not in A2:A20."
Else
MsgBox "Pear is at position " & CStr(matchPosition) & _
" within A2:A20."
End If
End Sub
For production code, qualify the range with a worksheet variable rather than relying on the active sheet; otherwise, the result can change if a different sheet is active when the macro runs. Match returns a position within the searched range, not a worksheet row number. In this example, position 1 corresponds to A2, position 2 to A3, and so on. Keep the lookup range to one row or one column so its search direction is unambiguous.
Why a range’s Value2 array needs different handling
A frequent source of confusion is that a range and an array created with Array do not necessarily have the same shape. Reading multiple cells into a Variant with Range.Value2 produces a two-dimensional array: the first index represents rows and the second represents columns. A one-dimensional function that uses values(i) is not designed for that shape.
For a rectangular range, use two indexes. This example checks every cell in A2:C10 on the active worksheet, skips Excel error values, and looks for an exact value comparison:
Public Sub SearchTwoDimensionalRangeArray()
Dim data As Variant
Dim r As Long
Dim c As Long
Dim target As String
Dim found As Boolean
' Value2 returns a two-dimensional array for this multi-cell range.
data = Range("A2:C10").Value2
target = "Pear"
found = False
For r = LBound(data, 1) To UBound(data, 1)
For c = LBound(data, 2) To UBound(data, 2)
If Not IsError(data(r, c)) Then
If CStr(data(r, c)) = target Then
found = True
Exit For
End If
End If
Next c
If found Then Exit For
Next r
MsgBox "Found: " & CStr(found)
End Sub
This sample uses case-sensitive string comparison and converts non-error cell values to text. If the range may contain Null values or values that should be compared numerically, adjust the comparison instead of assuming all cells are ordinary text. A single-cell range can also behave differently from a multi-cell range when read into a Variant, so check the shape of the data before passing it to an array routine.
Choosing the right approach
Choose the method based on what the caller needs, not just which code looks shortest:
- Use a loop when you need a Boolean answer, custom case handling, an actual array index, or control over how values are compared.
- Use Application.Match for a concise exact lookup in a one-dimensional list or a one-row or one-column worksheet range, when its comparison behavior is suitable.
- Use nested loops when the data is a two-dimensional Variant array from a rectangular range and you need to inspect every cell.
- Use a Dictionary or Collection when you need repeated membership checks against a large set and the added setup is justified. These are lookup structures, not array methods.
A loop takes time proportional to the number of values inspected: if a match is near the beginning, it exits early; if it is absent, it checks every element. For a small list or a check done occasionally, clarity usually matters more than micro-optimizing. If you perform many searches against the same large list, building a lookup structure once may be more appropriate than rescanning the array for every target.
Common errors and practical safeguards
- Assuming every array starts at zero: Use
LBoundandUBound. A fixed lower bound can skip values or cause a subscript error. - Passing a two-dimensional range array to a one-dimensional function: Use nested indexes for
Value2data, or search the worksheet range directly with Match when the range is one row or column. - Using WorksheetFunction.Match for a missing value: That form raises a run-time error when it cannot find a match.
Application.Matchinstead returns an error Variant that can be tested withIsError. - Expecting case-sensitive Match behavior: Match does not distinguish upper- and lowercase text. Use a loop and
StrCompwithvbBinaryCompareif that distinction matters. - Comparing mixed or special values without checking them: Error values, Nulls, objects, and incompatible types can make an equality test fail. Define what should count as a match and guard those cases explicitly.
- Assuming the workbook can undo a macro: Ctrl+Z does not reverse VBA actions. Save a backup before running code that changes workbook content.
Frequently Asked Questions
Does VBA have an array Contains function?
No general built-in Contains method exists for ordinary VBA arrays. Write a loop, or use Application.Match when its lookup behavior fits the data.
How do I check whether a VBA array contains a string without case sensitivity?
Loop through the elements and use StrComp(element, target, vbTextCompare) = 0. This makes the text comparison rule explicit. Skip or separately handle elements that cannot safely be converted to strings.
Can I use Match with an array created by Array?
Yes. For a one-dimensional list, use Application.Match(target, values, 0) for an exact lookup, then use IsError to detect a missing result. Remember that Match returns a position counted from 1.
Why does my array search fail on data from a worksheet range?
A multi-cell range read into a Variant with Value2 is two-dimensional, even when it has one column. Index it with row and column indexes, or search the one-column range itself with Match.
Can I search an array with a different lower bound?
Yes. Read its bounds with LBound and UBound rather than assuming the first index is 0 or 1. The loop function above uses those bounds for a one-dimensional array.
Bottom line
For most VBA code, a bounds-aware loop is the clearest way to answer whether a one-dimensional array contains a value. Use Match for a compact exact lookup when its case-insensitive behavior and returned position suit your needs, and handle worksheet range arrays as two-dimensional data rather than assuming they are ordinary one-dimensional lists.
Related
- Excel Split Cell Into First and Last Name: A Clear Guide
- Excel Split Cell by Space Delimiter: Methods and Tips
- Excel Split Cell Content Into Columns: Methods That Work
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA MAX Function: How to Find the Largest ValueExcel VBA does not have a built-in Max function, but a macro can use Excel’s worksheet function through…
- VBA AutoFilter: Filter Text with Like PatternsTo filter rows in Excel VBA for text that is like a pattern, use the AutoFilter method with…
- Excel VBA Data Validation Custom Formula: How to Use ItIn Excel VBA, create custom data validation with the Validation.Add method, setting Type:=xlValidateCustom and supplying a formula that…
- VBA Range Type: What It Means and How to Use It in ExcelIn Excel VBA, Range is an object type: it represents one cell, a group of cells, or a…
- Excel VBA: Stop on Error or Cancel a Running MacroTo stop an Excel VBA macro when an error occurs, let the error remain unhandled or use an…
- VBA to Save Excel Tabs as Separate FilesYes. A VBA macro can copy each visible worksheet tab into a new workbook and save it as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.