Microsoft Office Tutorials: Step 3 - Learn programming fundamentals

Thursday, April 27, 2017

Step 3 - Learn programming fundamentals

You learned how to record a macro, and looked under the covers to see how VBA procedures are created. The next step is to learn a few more programming fundamentals to lay a solid foundation for VBA programming.

Basic concepts of VBA: objects, methods, and properties

The first thing to know is that VBA code is assembled in a way that mimics how you'd describe things around you. It's made up of the following four concepts:

Concept	Description	Example
Object	A "thing"	Worksheet
Method	Action, or something a "thing" can do	Add a "thing"
Property	Description, or characteristic of a "thing"	Name
Collection	A group of "things"	Worksheets

Together, these four concepts are what programmers call an object oriented programming model. This is a way to accomplish a task by describing the "thing" or "Object/Collection" you want to act on the specific characteristics or "Property" to describe the thing you want to act on, and how or "Method" you want to use to perform your action.

Let's take a look at our code from Step 2:

        Sub DeleteRow()
'
' DeleteRow Macro
'
'
    Selection.Rows.Delete
 
End Sub

In the line, Selection.Rows.Delete, Selectionis theobject, or "thing" that you're referring to in your code. You described that "thing" in further detail by saying it's the Rows property that you wanted to act on. Next, you applied the method of deleting the row with the Delete syntax.

At first, these concepts may seem a little confusing, but try talking out what you're trying to accomplish, and identify what is the "thing" you are trying to work on, what are the characteristics of that "thing", and what action you want to perform on that "thing". With a little practice and perseverance, you will be on your way to creating some powerful VBA macros.

Learn more about Objects, Methods and Properties

Learn more about Collections

Basic control flow statements

Here are two more concepts that you'll want to learn in order to be able to build useful VBA macros.

If this, then that: The If… Then statement is a programming tool, which allows you to control the type of action you want to perform, based on some type of criteria. For instance, say you wanted all Heading 1 text to have Helvetica 16 point bold format, and all Heading 2 text to have a Helvetica 12 point italic format. The If… Then statement is the tool that would enable you to check if the text is a Heading 1 or Heading 2 style, and then apply the appropriate format.

Learn more about the If This, Then That Concept

For Each Loop: This is helpful when you want to process through a collection, or a set of "things" and perform an action. The action could also be to apply the If This, Then That concept, so you can have even more granular control. For instance, say you have a table and you want to look through each row in that table for a certain word, and whenever you find that word yellow, you want to color the cell yellow, and whenever you find the word red, you color the table cell red. You would use the For Each loop and the If… Then statement together to build this procedure.