Chad's Macro Mania
~~Chad K. Welch

ARRAYS and the Olympics
Light the Fire Within 

This month the world is coming to my home town.  Things sure are shaping up here in Salt Lake City, Utah.  Lately it seems that I am eating and breathing Olympics, and this month’s article is no exception.  We’ll discuss arrays and random numbers and, of course, bring in the Olympic spirit! 

ARRAYS

First off, what are arrays?  Dictionary.com defines an array as an arrangement of memory elements in one or more planes.  WHAT!?!  Cap’n Patt explains them a whole lot better here.   

For example:  suppose I have some sports (luge, curling and hockey) stored as variables in memory.  Arrays offer a way of grouping those values.  Now I can refer to that array and perform actions on all elements.  I can order the elements; I can count the number of elements in the array; etc.  Of course, the same things can be done without arrays but not nearly as efficiently. 

Arrays are used the same way as other variables except that arrays also have an index.  When declaring the array, indicate in parentheses the number of values that will be stored in the array:

            Dim Sports(3) As String

Now each value can be referenced using the index:

            Sports(0) = “Luge”

            Sports(1) = “Curling”

            Sports(2) = “Hockey”

Notice that the index begins at zero and ends one less than the number of indices declared.  Unlike most programming languages, Visual Basic allows us to change where the index begins.  Insert the line “Option Base 1” before any of the sub procedures in your module and you can reference the variables in a more intuitive way:

            Sports(1) = “Luge”

            Sports(2) = “Curling”

            Sports(3) = “Hockey” 

Random Numbers

Now that we understand the way arrays are used, lets look at a practical way to use them.  To illustrate lets use random numbers.  A random number is generated  by calling the built-in function Rnd().  The random number generated is a floating point number between 0 and 1, non-inclusive.  

However, we want a random integer number from 1 to 3 so that we can find a random sport in our array.  To do that, we will multiply the random number by 3 to find a number between 0 and 3, non-inclusive.  Now if we store that result in an integer variable we have a number 0, 1 or 2.  Add one to that and we have a number 1, 2 or 3:

            Dim rndNumb As Integer

            rndNumb = Rnd() * 3 + 1

Now we can reference the random sport:

            rndSport = Sports(rndNumb) 

Are They Truly Random?

One thing you may notice: if you run your code and find a random sport then close the application.  Open it again and run the code again, you will have the same random sport.  It doesn’t matter if your array has 3 elements or 300,000 the result will be the same.  

Doesn’t seem very random does it?  To produce different random numbers call the Randomize() function in your application before running the code.  Now your code will produce different values each time the application is opened and run. 

Sample Code

Now lets put it all together and use a couple of different arrays and random numbers to predict the outcome of the 2002 Winter Olympic Games.  If you feel that your computer is psychic and produces psychic numbers rather than random numbers you may even want to use this code in your gambling circles or take it to Las Vegas.  Just remember, my responsibility is contingent upon your earnings.  You lose – I’m not responsible; you win – I claim responsibility and get half of the earnings! 

This code is application independent.  It will run in Word, Excel, Access, or etc. 

Option Base 1

Sub Arrays_and_Randoms()

    Dim Countries(5) As String

    Dim Sports(5) As String

    Dim Gender(2) As String

    Dim rndCountry As Integer

    Dim rndSport As Integer

    Dim rndGender As Integer

    Countries(1) = "Australia"

    Countries(2) = "Brazil"

    Countries(3) = "Canada"

    Countries(4) = "England"

    Countries(5) = "United States"

    Sports(1) = "biathlon"

    Sports(2) = "luge"

    Sports(3) = "super-g slalom"

    Sports(4) = "ice hockey"

    Sports(5) = "freestyle aerials"

    Gender(1) = "men"

    Gender(2) = "women"

    Call Randomize

    rndCountry = Rnd() * (UBound(Countries) - 1) + 1

    rndSport = Rnd() * (UBound(Sports) - 1) + 1

    rndGender = Rnd() * (UBound(Gender) - 1) + 1

    MsgBox "I Predict that the " & Gender(rndGender) & _

        " from " & Countries(rndCountry) & " will win " & _

        "the gold in " & Sports(rndSport) & ".", _

        vbOKOnly + vbInformation, "Light the Fire Within"

End Sub

Good luck to each of your country’s athletes!  Enjoy the games, I know I will!

Last month I offered $5.00 to anyone who sent me an idea to use in Chad’s Macro Mania.  This month, because of the winter games, I am offering the Olympic pin shown below.  Please send your idea to chad@welchkins.com by Feb 24 (the closing day of the Winter Olympics).  If you are the first to send me the idea and I use it, I’ll send you the pin!  I’ll even pay postage.

Chad K. Welch works as a technician/enabler in Utah.  He is available for consulting or application programming with Microsoft Office and VBA.  Do you have a question or tip you’d like to have Chad address in this column?  Send an email to linda@personal-computer-tutor.com or contact him directly at chad@welchkins.com for more information.