Microsoft Office Tutorials and References
In Depth Information
In this lesson you verify whether a certain string element is part of an array.
You test whether a certain string element is in an array. At the end of the macro, you’ll show a
Message Box to confirm that the string element either was or was not found to exist in the array.
Like the example earlier in this lesson, say you have this list of names:
Now, say you want to test whether a certain string element is in that array, which in this example
you will enter into a worksheet cell. Enter a good-looking name like Tom into cell A1 of Sheet1. Put
the list of names in an array, and test to see whether “Tom” is among the elements in that list.
To get the sample database files you can download Lesson 10 from the book’s website at
Open Excel and add a new workbook.
Press Alt+F11 to get into the Visual Basic Editor.
From the VBE menu, click Insert ➪➤ Module.
In the new module, type the name of your macro as
Press the Enter key, which will cause Excel to place a set of parentheses after the TestArray
macro name, and also will create the End Sub statement. Your macro so far will look like
For the first line of code, establish that Sheet2 is VeryHidden, as an example to demonstrate
the result of an element being found, or not found, in an array. If the element is found,
Sheet2 will be unhidden.
Worksheets(“Sheet2”).Visible = xlSheetVeryHidden