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
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
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
Before I go, I’m going to take a few seconds to wish all my readers in the States a very Happy Independence Day! I hope you found this 4th of July VBA Code Library example to be a real treat.
Submit a comment below if you like this macro or have more questions. As always, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content!
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow