Intro to VBA: Reading, Writing and Running Code (Part 3 of 3) - Excel Campus
5

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”.

Reading VBA Code Backwards - Intro to VBA

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.

VBA With Statement Helps Shorten Code

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.

VBA Code Runs From Top Down

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.  

Step Through VBA Code with the F8 Key

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.

MasterChef Kitchen Pantry

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:

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 5 comments
Farouk Sellami - November 8, 2017

Dear Mr.Jon,

Thank you very much for this lesson. It helped me a lot to understand the VBA basics.
I was scared when I had to learn VBA in a short period of time for my work, however, now I am more confident about it. I liked the examples you used. Even though I am quite familiar with Oriented-Object programming, I still find it very helpful to clarify my thoughts.

I wish you the best
Best Regards,
Farouk Sellami

Reply
Saurabh - June 28, 2016

Dear Mr.Jon,

i really want to thanks to you for your basic lesson with pictures and meaningful way i am from India and learning VBA but i was so confused about this objects method and properties and i was searching on internet that at least something i will get i will understand this concept but i got your Blog and trust me it is very useful.

Regards
Saurabh Kanhed (India)

Reply
Nguyen Doan Tuong - March 22, 2016

Dear Mr.Jon Acampora

I don’t know to say what to thank you about file, clip lesson that you have to support every people on youtube and website.

for things that to support my job a lot.

Thanks and best regards,

Reply

Leave a Reply: