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:
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,
Thanks Nguyen!
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)
Thank you Saurabh! Happy to hear you found the article useful.
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
Thank you for the simple way you have explained the topic. God bless you as I am enriched.
Thank you. On point.
Wish you all the best.
Regards
Thank you very much for this! It’s really clear and the extra tips (like With making code more efficient) are very helpful! I use VBA quite a lot at work but haven’t been very sure I’m doing it in a good way – these articles were really interesting.