Microsoft Office Tutorials and References
In Depth Information
Try It
Try iT
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:
Bill
Bob
Tom
Mike
Jim
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.
lesson requirements
To get the sample database files you can download Lesson 10 from the book’s website at
www.wrox.com .
step-by-step
1.
Open Excel and add a new workbook.
2.
Press Alt+F11 to get into the Visual Basic Editor.
3.
From the VBE menu, click Insert ➪➤ Module.
4.
In the new module, type the name of your macro as
Sub TestArray
5.
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
this:
Sub TestArray()
End Sub
6.
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
 
Search JabSto ::




Custom Search