Intro to VBA: Reading, Writing and Running Code (Part 3 of 3)

A Tip for Reading a Line of Code – Go Backwards

When you are learning to write code, it's best if you know how to read it.  Sometimes you'll see a long line of code with a lot of dots and think, “what does that mean???”

I find that reading code backwards helps.  Start with last word and work your way left, replacing the dots/periods with the words, “of” or “of the”.

Let's try this on the following line of code.

Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value = 100

Starting from the right…  Set the Value of the Range A1 of Sheet1 of Book1.xlsx of the Application (Excel) equal to 100.

Here's another example:

Range(“A1:F1”).Font.Bold = True

Set the Bold property of the Font property of Range A1:F1 to True.

This methodology will help you work your way back up the hierarchy.  This will help when writing code and making sure your object references are fully qualified.

Shortening Code & Improving Efficiency – Get WITH It!

The With Statement can help us make our code easier to read and write, while still keeping it fully qualified.

Instead of typing out the fully qualified line of code for each line, we can use the With Statement to repeat the reference.  Each line that starts with a dot between the With and End With line will use the With Statement as its qualifier.

This not only makes it easier to read and write, but also allows VBA to work more efficiently when running the code.  In the example above, it won't have to figure out which workbook and worksheet you are referring to in every line of code.  It only has to evaluate that one time.

What Order does the Macro Run In?

When the macro is run, the code is read from top down.  This is similar to how you read.  You start at the top of the page and work your way down.

Only one line of code is run/evaluated at a time, and each line of code is evaluated in order.  Once VBA finishes evaluating a line of code or performing the action, it then moves to the next line.

You can step through and run each line of code by putting your text cursor anywhere in the macro and pressing the F8 key on the keyboard.

That is the basics, but it can get more complicated.  We can use statements in VBA to manipulate the order of how the code is run.  Statement like If Then Else, Select Case, loops, and GoTo can tell VBA to skip, repeat, or jump to different sections of the code.

You can also call other macros and functions from a macro and have that code run.  When another macro is called from a macro, VBA will run that entire macro, then return to the calling macro and continue running the code.

The basic principle is still the same.  VBA is only going to run/evaluate one line of code at a time from top down.  

The Excel Object Model Is Your MasterChef Kitchen

The Excel Object model in VBA is massive, and there is a lot to learn. Understanding how the hierarchy works will help you navigate through the library and learn VBA.

But don't let the massive size of the object library scare you. When you go to cook a meal you aren't going to need every tool in the kitchen, and you're not going to use every ingredient in the pantry. Same is true when you are writing macros. It's best to start with the basics and work your way up. Start with the bowl of cereal and work your way up to the 5 course gourmet meal.

The cool part is that you can program just about every object in Excel with VBA. If you have ever seen the TV show MasterChef, the contestants on the show have a giant kitchen and pantry to work with. They have just about every kitchen tool and ingredient you can think of at their disposal. The Excel object model is similar to this. We can use VBA to create just about any automated process or application that will save you and your users a lot of time.

Previous Articles:

• Part 1 – Programming Objects with VBA
• Part 2 – The Object Hierarchy & Excel Object Model