If you tell him kick the ball (or ball.kick), you really aren't sure which one he will kick. Maybe he will kick the one closest to him. This could be a real problem if he is standing in front of the bowling ball.

For almost any noun, or object, in VBA, there is a collection of that object. Think about Excel. If you can have a row, you can have a bunch of rows. If you can have a cell, you can have a bunch of cells. If you can have a worksheet, you can have a bunch of worksheets. The only difference between an object and a collection is that you will add an "s" to the name of the object:

Row becomes Rows Cell becomes Cells Ball becomes Balls

When you refer to something that is a collection, you have to tell the programming language to which item you are referring. There are a couple of ways to do this. You can refer to an item by using a number:


This will work fine, but it seems a dangerous way to program. It might work on Tuesday, but if you get to the field on Wednesday and someone has rearranged the balls, then Balls(2).Kick might be a painful exercise.

The other way is to use a name for the object. To me, this is a far safer way to go. You can say:


With this method, you always know that it will be the soccer ball that it being kicked.

So far, so good. You know you can kick a ball and you know that it will be the soccer ball. For most of the verbs, or methods, in Excel VBA, there are parameters that tell how to do the action. These act as adverbs. You might want the soccer ball to be kicked to the left and with a hard force. Most methods have a number of parameters that tell how the program should perform the method.

Balls("Soccer").Kick Direction:=Left, Force:=Hard

As you are looking at VBA code, when you see the colon-equals combination, you know that you are looking at parameters of how the verb should be performed.

Sometimes, a method will have a list of 10 parameters. Some may be optional. Perhaps the .Kick method has an Elevation parameter. You might have this line of code:

Balls("Soccer").Kick Direction:=Left, Force:=Hard, Elevation:=High

Here is the radically confusing part. Every method has a default order for its parameters. If you are not a conscientious programmer and you happen to know the order of the parameters, you can leave off the parameter names. The following code is equivalent to the previous line of code:

0 0

Post a comment