Excel VBA object model

A macro is a Sub procedure, a named block of statements. Its skeleton is two lines:

Sub SayHello()
    MsgBox "Hello, analyst"
End Sub

Sub SayHello() opens the procedure and names it, and End Sub closes it. Everything in between runs top to bottom when you invoke it. The empty parentheses hold arguments, and there are none here. A comment starts with an apostrophe (') and Excel ignores it, so use comments generously. A Function is the sibling that returns a value for use in a formula, while Subs do things. For now, you'll live in Sub-land.

Now for the object model. Everything you can touch in Excel is an object, and the objects are arranged in a strict containment hierarchy. You read it from the top down:

Application            ' Excel itself
  └─ Workbook          ' one open file
       └─ Worksheet    ' one tab
            └─ Range    ' one or more cells
               Cells   ' the same cells, addressed numerically

You reach an object by walking down the tree with dots, and each step names a member of the thing on its left. Application.Workbooks("Budget.xlsx").Worksheets("Sales").Range("B6") is a full path from Excel to a workbook, then to a sheet, then to a cell. In practice you'll rarely start at Application. You start at a workbook shortcut instead:

ThisWorkbook or ActiveWorkbook: which should you use?

• ThisWorkbook is the workbook that holds the code. It's almost always what you want, because it's unambiguous and it doesn't change when the user opens another file.
• ActiveWorkbook / ActiveSheet / ActiveCell mean whatever is active right now. They're convenient and dangerous. "Active" is a moving target, and code that depends on it breaks when the wrong thing is in front. Prefer explicit names.

So a professional's first move is to pin the sheet down explicitly, usually in a variable:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("B6").Value = 4785000

Read the last line as a sentence: on the Sales sheet of this workbook, put 4785000 into cell B6. There's no doubt about which sheet, and nothing depends on what's selected. Name the worksheet, then work through it. That one habit prevents a whole category of bugs, and the common mistakes come back to it under ActiveSheet.

What are properties and methods in VBA?

Objects expose properties and methods. Properties are things an object has, such as .Value, .Font, .Interior.Color and .NumberFormat, and you read or assign them. Methods are things it does, such as .Copy, .ClearContents and .Sort, and you call them. ws.Range("B6").Value = 100 assigns a property, while ws.Range("B2:B6").ClearContents calls a method. When an object variable is involved you assign it with Set (Set ws = ...). Plain values take a bare =. That Set-versus-= distinction trips beginners once and then never again. Object references need Set and ordinary values don't.