2. Excel object model fundamentals

Every automation script you write against Excel — whether in VBA or Office Scripts — is ultimately a conversation with the Excel object model. Before you can loop over a CAE result table, paste a stress contour summary into a report sheet, or clear stale data between solver runs, you need a precise mental model of how Excel exposes its contents to code. This chapter builds that model from the top down: the application, the workbook, the worksheet, the range, and the cell.

The hierarchy: from Application down to a single cell

Excel's object model is a containment tree. At the root sits the Application object — the Excel process itself. Beneath it are Workbook objects (one per open .xlsx/.xlsm file), each containing Worksheet objects (and chart sheets), and each worksheet containing a Range object that can represent anything from one cell to a rectangular block, a row, a column, or even a non-contiguous selection. A single cell is simply a Range with one row and one column; the legacy Cells accessor is a convenient way to address a cell by row and column index.

The practical consequence is that almost every statement you write is a path through this tree. In VBA, ThisWorkbook.Worksheets("Results").Range("B2") reads as: the workbook containing the running code → the worksheet named "Results" → the range at B2. In Office Scripts the same path is expressed as workbook.getWorksheet("Results").getRange("B2"). The names differ, the containment logic is identical.

Workbook and Worksheet: the containers

In VBA, ThisWorkbook refers to the file that owns the running macro, while ActiveWorkbook refers to whichever file currently has focus. This distinction matters in CAE workflows because a macro is often stored in a personal macro workbook or an add-in while operating on a freshly exported results file. Referencing ThisWorkbook when you meant ActiveWorkbook is one of the most common causes of "the script wrote to the wrong file" bugs. Office Scripts sidesteps the ambiguity: the workbook object always refers to the file the script is attached to, and cross-file access is not permitted.

Worksheets are addressed either by name — Worksheets("Results") — or by index — Worksheets(1). Name-based access is strongly preferred in engineering reports because sheet order changes as templates evolve, whereas a name like "Stress_Summary" is stable. Guard against missing sheets with an existence check before writing:

' VBA: verify the target sheet exists before writing
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Stress_Summary")
On Error GoTo 0
If ws Is Nothing Then
    MsgBox "Sheet 'Stress_Summary' not found — check the export template."
    Exit Sub
End If

The Office Scripts equivalent uses getWorksheet, which throws if the sheet is absent, so wrap it in a try/catch or check getWorksheets() first. Either way, the lesson is the same: never assume a sheet exists just because it existed during development.

Range and Cells: addressing the data

The Range object is where most automation work happens. It can represent a single cell (Range("B2")), a rectangular block (Range("B2:D50")), an entire column (Columns("C")), or a row (Rows(4)). The Cells(row, column) accessor is equivalent to a single-cell Range but takes numeric indices, which makes it ideal inside loops where the row number is a variable:

' VBA: write a computed safety factor into column F for each result row
Dim i As Long
For i = 2 To 50
    Dim allowable As Double, actual As Double
    allowable = ws.Cells(i, 4).Value   ' column D: allowable stress
    actual = ws.Cells(i, 5).Value      ' column E: max von Mises
    If actual > 0 Then
        ws.Cells(i, 6).Value = allowable / actual   ' column F: SF
    End If
Next i

Note the use of .Value rather than reading the cell object itself. Reading .Value transfers a single variant; touching cell formatting properties inside a loop is dramatically slower because each property access crosses the COM boundary. For large CAE result tables — tens of thousands of nodes — read the whole block into a VBA array in one call, process in memory, and write back once:

Dim data As Variant
data = ws.Range("D2:F50001").Value   ' one COM call, 2-D array (1-based)
' ... process data(r, c) in memory ...
ws.Range("D2:F50001").Value = data   ' one COM call back

Office Scripts follows the same principle with getValues() and setValues(), which return and accept a two-dimensional JavaScript array. The performance difference between cell-by-cell and block operations is often two orders of magnitude, and it is the single most important habit to build early.

Relative vs. absolute references in automation

When you record a macro, Excel writes absolute addresses such as Range("B2").Select. These break the moment the data layout shifts by one row. Prefer anchoring ranges to named cells or to computed boundaries. A robust pattern finds the last populated row dynamically:

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Range("F2:F" & lastRow).Formula = "=D2/E2"

This writes a formula down the entire column in one statement, letting Excel fill relative references automatically — far faster and less error-prone than looping. In Office Scripts the equivalent uses getRangeByIndexes with a computed row count, or the getUsedRange() method when the data block is contiguous.

What the model does not do for you

The object model exposes structure, not semantics. It will happily let you write a string into a numeric stress column, overwrite a header, or paste results into the wrong sheet — no error is raised. Validation is your responsibility. A minimal guard before any write operation checks that the target range is empty or that the header row matches expectations:

If ws.Range("F1").Value <> "Safety Factor" Then
    MsgBox "Unexpected header in F1 — aborting to protect the template."
    Exit Sub
End If

With the hierarchy firmly in mind — Application → Workbook → Worksheet → Range → Cell — you are ready to move from addressing data to manipulating it: reading solver exports, computing derived quantities, and writing formatted report blocks. The next chapters build directly on these accessor patterns.