Microsoft Office Tutorials and References
In Depth Information
Try It
7.
For the second line of code, declare a variable for the array of names you’ll be creating, and
name the variable myArray . For the next line of code, assign the variable name to the array.
In this case, you know what the list of names contains so you can build the array yourself by
simply entering the individual names inside the parentheses. The two lines of code will look
like this:
Dim myArray As Variant
myArray = Array(“Bill”, “Bob”, “Tom”, “Mike”, “Jim”)
8.
The next two lines of code would show the String type variable to represent the string
element you are attempting to verify, and then code to assign the string to that variable. The
String variable, named strVerify , refers to a name you would enter into cell A1 of Sheet1 to
test the macro. For example:
Dim strVerify as String
strVerify = Worksheets(“Sheet1”).Range(“A1”).Value
9.
You will need to declare two more variables. One of these variables will be an Integer type
variable, which will help you loop through each of the five elements in the array. The other
variable is a Boolean data type, which will help to characterize as True or False that the
string in cell A1 of Sheet1 is among the elements in the array.
Dim i as Integer, blnVerify as Boolean
10.
Enter Tom in cell A1 of Sheet1.
11.
Now, to see whether “Tom”exists in the array, loop through each element and compare it to
the string variable. If there is a match, exit the loop and alert the user by unhiding Sheet2. If
the String variable is not found, let the user know that as well, and keep Sheet2 hidden.
For i = LBound(myArray) To UBound(myArray)
If strVerify = myArray(i) Then
blnVerify = True
MsgBox “Yes! “ & myArray(i) & “ is in the array!”, , “Verified”
Worksheets(“Sheet2”).Visible = xlSheetVisible
Exit For
End IfNext i
If blnVerify = False Then _
MsgBox strVerify & “ is not in the array.”, , “No such animal.”
12.
Putting it all together, the macro would look like this:
Sub TestArray ()
‘Establish that Sheet2 is VeryHidden.
Worksheets(“Sheet2”).Visible = xlSheetVeryHidden
‘Declare and assign a Variant type variable for the array.
Dim myArray As Variant
myArray = Array(“Bill”, “Bob”, “Tom”, “Mike”, “Jim”)
Search JabSto ::




Custom Search