VBA Code Library

Check if a value is in an array with this VBA function. If the value is in the VBA array, the function returns true. If the value is not in the array or the array is empty, the VBA function returns false.

The function accepts two variants, so it can look for a string in a string array, an integer in an integer array, or whatever you’d like as long as you pass it converted data types. The first argument is the value you want to find and the second argument is the array you want to search.

I’ve included a demo at the bottom showing how you can convert a number to a string while using the IsInArray function to see if that number is in an array of strings.

Check if Value is in Array using VBA

Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Function to check if a value is in an array of values
'INPUT: Pass the function a value to search for and an array of values of any data type.
'OUTPUT: True if is in array, false otherwise
Dim element As Variant
On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function

Checking if a value is in an array is an essential skill, but it just scratches the surface of what you can do with VBA arrays. To become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.

How to use the IsInArray VBA Function

Sub Demo()
Dim arr(2) As String
Dim i As Integer
arr(0) = "100"
arr(1) = "50"
arr(2) = "2"
i = 2
MsgBox IsInArray(CStr(i), arr)
End Sub

When you’re ready to start writing your macros faster (without having to search online for answers) check out our best-selling VBA Cheat Sheets for some powerful automation tricks. For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.