I Can't Understand This Code

As I mentioned before, if you've taken a class in a procedural language such as BASIC or COBOL, then you might be really confused when you look at VBA code. Yes, VBA stands for "Visual Basic for Applications," but it is an object-oriented version of BASIC. Here is a bit of VBA code:

Selection.End(xlDown).Select Range("A14").Select ActiveCell.FormulaR1C1 = "'Total" Range("E14").Select

Selection.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)" Selection.AutoFill Destination:=Range(_ "E14:G14"), Type:=xlFillDefault

This code will likely make no sense to anyone who knows only procedural languages, and unfortunately, your first introduction to programming in school (assuming you are over 20 years old) would have been a procedural language.

Here is a section of code written in BASIC:

Print Rpt$(" ",x); Print "*" Next x

If you run this code, you get a pyramid of asterisks on your screen:

I Can't Understand This Code 29

Understanding the Parts of VBA "Speech" . . .30

Is VBA Really This Hard? No! 33

Examining Recorded Macro Code—

Using the VB Editor and Help 36

Using Debugging Tools to Figure Out Recorded Code 42

The Ultimate Reference to All Objects, Methods, Properties 51

Five Easy Tips for Cleaning Up Recorded Code 53

Putting It All Together—Fixing the Recorded Code 56

Case Study 56

Next Steps 59

If you've ever been in a procedural programming class, you can probably look at the code and figure out what is going on. I think that a procedural language is more English-like than object-oriented languages. The statement Print "Hello World" follows the verb-object format and is how you would generally talk. Let's step away from programming for a second and think about a concrete example.

Understanding the Parts of VBA "Speech"

If you were going to play soccer using BASIC, the instruction to kick a ball would look something like

"Kick the Ball"

Hey—this is how we talk! It makes sense. You have a verb (kick) and then a noun (the ball). In the BASIC code in the preceding section, you have a verb (print) and a noun (an asterisk). Life is good.

Here is the problem. VBA doesn't work like this. No object-oriented language works like this. In an object-oriented language, the objects (the nouns) are most important (hence, the name—object-oriented). If you are going to play soccer with VBA, the basic structure would be


You have a noun—the Ball. It comes first. In VBA this is an object. Then you have the verb— to kick. It comes next. In VBA, this is a method.

The basic structure of VBA is a bunch of lines of code where you have Object.Method

Sorry, this is not English. If you took a romance language in high school, you will remember that they used a "noun adjective" construct, but I don't know anyone who speaks in "noun verb" when telling someone to do something. Do you talk like this:




Of course not. That is why VBA is so confusing to someone who once stepped foot in a procedural programming class.

Let's carry the analogy on a bit. Imagine you walk onto a grassy field and there are five balls in front of you. There is a soccer ball, a basketball, a baseball, a bowling ball, and a tennis ball. You want to instruct the kid on your soccer team to

Kick the soccer ball

0 0

Post a comment