Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 37 ยท Objects and Collections

With Blocks and Object Variables (Set, Nothing, Early vs Late Binding)

Store objects in variables with Set, shorten code with With...End With, release with Nothing, and compare early and late binding with CreateObject.

Chains such as ThisWorkbook.Worksheets("Data").Range("A1").Font.Bold are long and slow to type. Object variables and With blocks shorten code, make it faster (VBA resolves the chain once) and easier to read.

Object variables and Set

Declare a variable of an object type and assign with the keyword Set:

VB
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")

Dim rng As Range
Set rng = ws.Range("A1:C10")

ws.Range("A1").Value = "Header"
rng.Font.Bold = True
Debug.Print ws.Name, rng.Cells.Count        ' Data   30

Forgetting Set is a classic mistake. ws = Worksheets("Data") tries to assign the default property of the sheet and fails with run-time error 91 (object variable not set) or 438 (object doesn't support this property or method). Rule: if the right side is an object, use Set.

A variable can be typed generically as Object, or (better) as the specific class such as Worksheet, Range, Workbook, Chart. A specific type gives autocomplete and compile-time checks.

Nothing

An object variable that points to nothing is Nothing. Test with Is:

VB
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets("Missing")      ' error 9 is skipped; ws stays Nothing
On Error GoTo 0
If ws Is Nothing Then
    MsgBox "Sheet not found"
End If

Release a variable with Set ws = Nothing. VBA frees objects automatically when variables go out of scope, but set to Nothing when you hold large objects (an Application, a database connection) and are done with them.

Variables are references

Set b = a makes both variables point to the same object; it does not copy it:

VB
Dim a As Range, b As Range
Set a = Range("A1")
Set b = a
b.Value = "changed"
Debug.Print a.Value       ' changed

With...End With

With evaluates an object once and lets you refer to it with a leading dot:

VB
With ThisWorkbook.Worksheets("Data").Range("A1:D1")
    .Font.Bold = True
    .Font.Size = 12
    .Interior.Color = RGB(221, 235, 247)
    .HorizontalAlignment = xlCenter
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
End With

Benefits: less typing, the long reference is resolved only once (faster), and the block shows clearly that all lines act on the same thing. Blocks can be nested:

VB
With ws
    .Range("A1").Value = "Title"
    With .Range("A1").Font
        .Bold = True
        .Size = 16
    End With
End With

Inside With, a statement beginning with a dot belongs to the With object; without the dot it is a normal reference. Do not jump into or out of a With with GoTo.

Early and late binding

Early binding declares a specific type and references the library (Tools > References):

VB
Dim xl As Excel.Application      ' needs the "Microsoft Excel Object Library" reference
Set xl = New Excel.Application
  • Autocomplete and compile-time checks, fast, constants such as xlUp are known.
  • The reference must exist on every user's machine and version.

Late binding uses Object and CreateObject (the library is found at run time):

VB
Dim xl As Object
Set xl = CreateObject("Excel.Application")
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
  • No reference needed, works across versions, but no autocomplete, slower, and library constants (xlUp) must be replaced by their numeric values.

A common workflow: develop with early binding, then switch declarations to Object and use numeric constants before sharing.

New creates a fresh object from a class (Set c = New Collection); GetObject attaches to an existing one or a file.

Passing objects to procedures

VB
Sub Highlight(ByVal target As Range)
    target.Interior.Color = vbYellow
End Sub

Sub Demo()
    Highlight Worksheets("Data").Range("A1:A3")
End Sub

Objects are always references: even with ByVal, you receive a copy of the reference, so changes to the object's properties are visible to the caller (you just cannot repoint the caller's variable).

Stand-in: references and With in JavaScript

The JavaScript stand-in (not VBA) shows that assigning an object variable shares one object, and a local alias works like With.

JS Stand-in: shared references and With

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

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement is correct for assigning a Worksheet to a variable?

    Object assignment requires the Set keyword.
  2. 2. How do you test whether an object variable is unassigned?

    Use Is Nothing: = compares values, Is compares references.
  3. 3. What does a leading dot mean inside With...End With?

    The dot refers to the object named at the top of the With block.
  4. 4. What is a downside of late binding?

    Late binding trades IntelliSense and constants for independence from library versions.

Summary

  • Dim x As Type plus Set x = ... stores an object reference; use Is Nothing to test it.
  • Object variables share one object; assignment does not copy.
  • With...End With shortens repeated references and is faster.
  • Early binding gives help and speed; late binding (CreateObject) gives portability.

Next: Collections and the Dictionary Object