Skip to content
Tutorials Center
My learning

Topics

Lesson 75 of 93 ยท Automation and Collaboration

VBA Objects: Application, Workbook, Worksheet and Range

Learn the Excel VBA object model: Application, Workbooks, Worksheets, Range, Cells, Offset, CurrentRegion, Find, reading values and formulas, and finding the last row.

Everything you can click in Excel is an object in VBA: the program itself, each open file, each sheet, each range of cells, each chart. Objects have properties (things they are: .Value, .Name, .Count), methods (things they do: .Clear, .Copy, .Find) and sometimes collections (groups of the same kind, such as all the worksheets). Once the pattern is clear, the thousands of names in the documentation stop being intimidating.

Excel cannot run on this site, so the VBA listings are read-only. The TryIt builds a small JavaScript stand-in of the object model (a workbook with sheets, a Range with Offset, CurrentRegion, Find, and a last-row function) so you can try the ideas. It is a teaching model, not Excel's own code.

The hierarchy

TEXT
Application                       Excel itself
 โ””โ”€ Workbooks("Sales.xlsx")       one open file
     โ””โ”€ Worksheets("Q1")          one sheet
         โ””โ”€ Range("A1:C10")       cells

You reach down the tree with dots: Application.Workbooks("Sales.xlsx").Worksheets("Q1").Range("A1").Value. You rarely type all of it, because some levels are implied:

Short form Means
ActiveWorkbook The workbook in front, whichever it is
ThisWorkbook The workbook that contains the running code (usually what you want)
ActiveSheet The sheet on screen
Range("A1") ActiveSheet.Range("A1") (a trap, as you will see)
Selection Whatever is selected right now

Prefer ThisWorkbook and named sheet objects over the Active... shortcuts. They do not change when the user clicks somewhere else.

Application

Application holds program-wide settings. These four come up constantly:

VB
Application.ScreenUpdating = False       ' do not redraw during the macro (set True again!)
Application.DisplayAlerts = False        ' no "Are you sure?" prompts (set True again!)
Application.Calculation = xlCalculationManual
Application.StatusBar = "Importing..."   ' text in the status bar

It also gives access to worksheet functions: Application.WorksheetFunction.Sum(Range("A1:A10")).

Workbooks and Worksheets

VB
Dim wb As Workbook, ws As Worksheet

Set wb = Workbooks.Open("C:\Data\sales.xlsx")
Set ws = wb.Worksheets("Q1")             ' by name
Set ws = wb.Worksheets(1)                ' by position

Debug.Print Workbooks.Count, wb.Worksheets.Count
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summary"
wb.Save
wb.Close SaveChanges:=False

A name or position that does not exist raises error 9, "Subscript out of range". Looping over a collection avoids guessing: For Each ws In wb.Worksheets.

Range and Cells

A Range is one or more cells. There are several ways to name the same cell:

VB
ws.Range("B3")               ' A1 style as text
ws.Cells(3, 2)               ' row 3, column 2: ideal when numbers change in a loop
ws.Range("B3:D8")            ' a block
ws.Range(ws.Cells(3, 2), ws.Cells(8, 4))   ' the same block from two corners
ws.Range("Prices")           ' a named range
ws.Range("A:A")              ' a whole column; ws.Rows(5) is a whole row

Reading and writing values and formulas

VB
ws.Range("A1").Value = "Total"
ws.Range("B1").Formula = "=SUM(B2:B20)"      ' formula as text, English function names
ws.Range("C2:C20").Formula = "=B2*1.2"       ' relative references adjust down the block
Debug.Print ws.Range("B1").Value             ' the result
Debug.Print ws.Range("B1").Formula           ' "=SUM(B2:B20)"

To store a cell's value in a variable, declare a type that fits and assign: Dim total As Double: total = ws.Range("B1").Value. If the cell contains an error such as #N/A, .Value returns an error value that raises a type mismatch when assigned to a number; test with IsError(cell.Value) first (see debugging and error handling).

Use .Value2 instead of .Value when you do not want Excel to convert dates and currencies into special VBA types.

Offset, Resize and CurrentRegion

Offset(rows, columns) moves a range relative to itself. Resize(rows, columns) changes its size. CurrentRegion grows from a cell to the whole block of data around it, like pressing Ctrl+A inside a table.

VB
ws.Range("A1").Offset(2, 1).Value = 5          ' B3
ws.Range("A1").Resize(1, 3).Font.Bold = True   ' A1:C1
Set data = ws.Range("A1").CurrentRegion        ' the whole contiguous table
Debug.Print data.Rows.Count & " rows x " & data.Columns.Count & " columns"
Debug.Print data.Address                       ' "$A$1:$C$6"

CurrentRegion stops at empty rows and columns, so one blank row inside the data splits it in two. UsedRange returns everything the sheet considers used, including formatted but empty cells, so it may be larger than you expect.

Finding the last row

Data grows, so never hard-code A2:A100. Start at the bottom of the sheet and jump up to the last filled cell, like pressing Ctrl+Up:

VB
Dim lastRow As Long, lastCol As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

ws.Range("D2:D" & lastRow).Formula = "=B2*C2"    ' trap a dynamic range

Which column you test matters: use one that is always filled (an ID, say). If the data has no empty cells, CurrentRegion.Rows.Count gives the same answer.

SpecialCells and the used range

End(xlUp) is not the only way to find the edge of your data. Range.SpecialCells selects cells by kind, like Home > Find & Select > Go To Special. It raises an error when nothing matches, so wrap it in On Error Resume Next and test the result.

VB
Dim lastCell As Range, blanks As Range, formulas As Range

Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell)   ' bottom-right of the used area
Debug.Print "Last row " & lastCell.Row & ", last column " & lastCell.Column

On Error Resume Next
Set blanks = ws.Range("B2:B500").SpecialCells(xlCellTypeBlanks)
Set formulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not blanks Is Nothing Then blanks.Interior.Color = vbYellow   ' highlight the gaps

Be careful with xlCellTypeLastCell: it reports the last cell Excel has ever formatted or used, so a stray format far below your data makes it overshoot until you save and close the file. ws.UsedRange has the same weakness. For a dependable last row, prefer End(xlUp) from the bottom of a column that is always filled. The other directions work too: Range("A1").End(xlToRight) and End(xlDown) jump like Ctrl+Arrow, but stop at the first blank cell, so use them only on gap-free data.

Find and FindNext

Range.Find is VBA's version of Ctrl+F and far faster than looping cell by cell. It returns a Range, or Nothing when there is no match, so always test the result.

VB
Dim hit As Range, first As String
Set hit = ws.Range("A:A").Find(What:="Invoice 1042", LookIn:=xlValues, LookAt:=xlWhole)
If hit Is Nothing Then
    MsgBox "Not found"
Else
    hit.Offset(0, 1).Value = "Paid"
End If

' every match
Set hit = ws.Range("A:A").Find("Pending", LookAt:=xlWhole)
If Not hit Is Nothing Then
    first = hit.Address
    Do
        hit.Offset(0, 1).Value = "Chase"
        Set hit = ws.Range("A:A").FindNext(hit)
    Loop While hit.Address <> first
End If

Find remembers its last settings (LookAt, LookIn) between calls, even in the Find dialog, so always spell them out.

Run it: a miniature object model

The stand-in below implements a sheet with Range, Cells, Offset, CurrentRegion, End(xlUp) and a Find that returns null for "Nothing". Cell addresses are plain strings. It models the behavior described above, not Excel's internals.

JS Range, Offset, CurrentRegion, Find and last row

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Three habits that prevent most bugs

  1. Qualify every Range. A bare Range("A1") means the active sheet, which may not be the one you think when the macro runs from a button or while another workbook is open. Write ws.Range("A1").
  2. Avoid .Select and Selection. ws.Range("A1").Value = 5 does the work directly and is faster than selecting first.
  3. Release what you changed. Restore ScreenUpdating, DisplayAlerts and Calculation at the end, and in your error handler too.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which object is the workbook that contains the running macro, regardless of which workbook is in front?

    ThisWorkbook always refers to the file holding the code. ActiveWorkbook changes when the user switches windows.
  2. 2. What does Range.Find return when it finds nothing?

    Find returns a Range object or Nothing. Using the result without checking raises error 91 (object variable not set).
  3. 3. Which line finds the last used row in column A?

    Starting at the bottom of the sheet and moving up skips blank cells in the middle. End(xlDown) from A1 stops at the first gap, and Rows.Count counts every row of the column.
  4. 4. What does ws.Range("A1").Offset(2, 1) refer to?

    Offset(rows, columns) moves down 2 rows and right 1 column from A1, which lands on B3.

Summary

  • The hierarchy is Application > Workbook > Worksheet > Range; use ThisWorkbook and worksheet variables rather than Active....
  • Range("A1"), Cells(row, col), Offset, Resize and CurrentRegion all describe cells; .Value and .Formula read and write them.
  • Find the last row with Cells(Rows.Count, col).End(xlUp).Row and build ranges with "A2:A" & lastRow.
  • Find returns Nothing when there is no match; FindNext continues until the address wraps.
  • Qualify ranges, avoid .Select, and restore any Application settings you change.

Next: VBA Arrays, Collections and Strings.