Overview

In working with VBA and arrays, occasionally I come to a point where I need to locate a specific value inside an array I’m working with. It’s a fairly common scenario, and knowing how to effectively navigate through array elements can save time and hassle. Luckily, there’s a block of code that makes this search straightforward.

Firstly, I set up my environment by declaring the necessary variables and the array that holds the values I’m interested in. I use a simple integer array named myArray with ten elements:

Dim myArray(10) As Integer

Then comes the intriguing part. I populate myArray with ten random numbers. Each value is an integer between 1 to 10. I enjoy seeing this random generation in action, as it involves using the Rnd function to generate these numbers and outputting each to the Immediate Window:

For i = 1 To 10
    myArray(i) = Int(Rnd * 10)
    Debug.Print myArray(i)
Next i

With the array filled, I prompt for the value to search for in the array. This is done through an input box, adding a bit of interactivity to the process:

varUserNumber = InputBox("Enter a number between 1 and 10 to search for:", "Linear Search Demonstrator")

Input validation is key here. I make sure the entered value is numeric and within the anticipated range with a snippet of code:

If Not IsNumeric(varUserNumber) Then GoTo Loopback

Upon ensuring a valid number is entered, I use a For loop to scan through the array. This linear search method compares each array element with the input value:

For i = 1 To UBound(myArray)
    ' ... comparison code ...
Next i

If a match is found, a success message is crafted, pinpointing the position where the value resides in the array. If not, a message informs that the value wasn’t found. Regardless of the outcome, I find that presenting results via a message box is a great way to wrap up the search operation:

MsgBox strMsg, vbOKOnly + vbInformation, "Linear Search Result"

There you have it, coding this search functionality in VBA is quite the engrossing experience, yielding immediate, clear results as to whether your sought-after number prefers to play hide and seek within the randomized confines of an array.

Similar Posts