VBA Set Statement: How to Assign Object References

In VBA, Set is a statement—not a function—that assigns an object reference to an object variable. Use it for objects such as Excel workbooks, worksheets, ranges, and dictionaries; use ordinary assignment for values such as text and numbers.

What VBA Set Means

A variable can hold different kinds of things. A value variable holds data, such as the number 12 or the text “Ready.” An object variable refers to an object that exposes properties and methods. In Excel VBA, a Worksheet variable can refer to a particular sheet, and a Range variable can refer to a cell or group of cells.

Set tells VBA to make that reference assignment. For example, Set ws = ThisWorkbook.Worksheets("Data") makes ws refer to the worksheet named Data in the workbook containing the code. It does not copy the worksheet, create another sheet, or copy its cells. The variable is another way for the code to access that existing object.

This distinction explains the common question “What is the VBA Set function?” There is no built-in function named Set in this usage. It is a VBA keyword used in an assignment statement. You will often see it in procedures that work with Excel’s object model.

How Object Assignment Works

Declare an object variable with a type that matches the kind of object it will reference, then use Set to assign an object to it. The general pattern is Set objectVariable = objectExpression. The expression on the right must evaluate to an object reference, not simply a value.

For example, ThisWorkbook refers to the workbook that contains the running VBA code, while ActiveWorkbook refers to the workbook currently active in Excel. They may be the same, but they are not guaranteed to be. Assigning one to a variable with Set gives your code a named reference to that workbook.

The reference is not a frozen copy of an object’s contents. If two object variables refer to the same range, changes made through one reference affect that same range and are visible through the other. Assigning a different object to one variable changes only what that variable refers to; it does not redirect the other variable.

In a declaration such as Dim ws As Worksheet, VBA creates the variable, but it does not assign a worksheet to it. Until you set a reference, the variable is uninitialized. Trying to use it as if it referred to a worksheet can cause a runtime error.

Assigning Excel Objects in a Macro

This example assigns references to a workbook, a worksheet, and a range. It then writes a value into the first cell of that range. The code assumes the workbook contains a sheet named Data; change the sheet name if needed.

Sub WriteToDataSheet()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim target As Range

    ' Refer to this workbook and its Data sheet.
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set target = ws.Range("A1")

    ' Assign a value to the cell; this is not an object assignment.
    target.Value = "Ready"
End Sub

Notice the difference between the last two lines. Set target = ws.Range("A1") assigns the Range object to target. target.Value = "Ready" assigns text to the cell’s Value property, so it does not use Set. Keeping the worksheet and range references in variables can make longer procedures easier to read and reduces repeated references to the same objects.

ThisWorkbook is often safer than ActiveWorkbook when the macro is meant to operate on the file that stores the macro. A user can activate another workbook while a macro is running, so relying on the active workbook may direct an operation to the wrong file. Choose the reference that matches the intended behavior.

Using Set with Functions and Collections

A VBA function can return an object. Inside the function, use Set when assigning the object to the function’s return value. The caller also uses Set to receive that returned object in an object variable.

Function FirstCell(ByVal ws As Worksheet) As Range
    ' Return the top-left cell of the supplied worksheet.
    Set FirstCell = ws.Range("A1")
End Function

Sub ShowFirstCell()
    Dim ws As Worksheet
    Dim first As Range

    ' This example expects a sheet named Data.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set first = FirstCell(ws)

    ' Read the cell's value rather than its object reference.
    MsgBox first.Value
End Sub

Collections also contain objects. For instance, Worksheets("Data") returns a worksheet object, while Range("A1") returns a range object. Assign either result to an appropriately declared object variable with Set. A collection lookup can fail if the requested item does not exist, so check names or handle the relevant error when a workbook’s structure may vary.

Some object types are created with New or through a creation function. For example, a late-bound Scripting.Dictionary can be created with CreateObject and assigned like this:

Sub MakeDictionary()
    Dim items As Object

    ' Create a dictionary object using late binding.
    Set items = CreateObject("Scripting.Dictionary")
    items.Add "Color", "Blue"
End Sub

Here, CreateObject creates the dictionary and returns an object reference; Set assigns that reference. The keyword itself does not create an object. Late binding avoids setting a reference to the dictionary library in the VBA editor, but the required component still needs to be available on the computer running the code.

Set and Nothing

Nothing represents the absence of an object reference. You can use it to clear an object variable with Set ws = Nothing. After that assignment, ws no longer refers to its former worksheet. This does not delete the worksheet, close the workbook, or erase cell contents; it only clears that variable’s reference.

Before using an object variable that might not have been assigned, test it with If ws Is Nothing Then. The Is operator compares object references. The equals sign is for value comparisons and is not the usual way to check whether an object variable is Nothing.

Sub CheckWorksheetReference()
    Dim ws As Worksheet

    ' No worksheet has been assigned yet.
    If ws Is Nothing Then
        MsgBox "The worksheet reference is not set."
        Exit Sub
    End If

    MsgBox ws.Name
End Sub

In ordinary procedures, local object variables are released when the procedure ends. Explicitly setting a variable to Nothing is not required in every macro. It can be useful when a long-running procedure is finished with an object, when code needs to make the variable’s state clear, or when managing references in automation. Releasing a reference does not necessarily destroy the underlying Excel object, which may remain in use elsewhere.

Set Versus Ordinary Assignment

Use Set when the right side supplies an object reference. Do not use it to assign a property value such as a cell’s text, a worksheet’s name, or a number stored in a variable. The distinction is between assigning the object itself and assigning data held by or associated with that object.

Set rng = ws.Range("B2") assigns a Range object to rng.
amount = 25 assigns a numeric value to a variable.
rng.Value = 25 assigns a numeric value to the range’s Value property.
Set otherRng = rng makes both variables refer to the same Range object; it does not copy the cell.

Older VBA terminology may describe ordinary value assignment as Let assignment. In modern VBA, the Let keyword is generally omitted, so a value assignment is written simply as amount = 25. That does not make Set obsolete: it remains necessary for object-reference assignment.

Some Excel objects have default properties, which can make an expression appear to assign an object when it is actually reading or writing a value. For clarity, specify the property you intend, such as rng.Value, rather than relying on a default member. This helps distinguish cell data from the Range object that represents the cell.

Common Errors and Troubleshooting

The most common error is omitting Set when assigning an object. For example, ws = ThisWorkbook.Worksheets("Data") is not the correct way to assign a Worksheet reference. Write Set ws = ThisWorkbook.Worksheets("Data"). Conversely, adding Set to a text or numeric assignment is also wrong because those expressions are values, not object references.

A runtime error saying “Object variable or With block variable not set” usually means the code tried to use an object variable that is Nothing or has not been assigned. Check where the variable is assigned, confirm that the assignment actually ran, and inspect any conditional branches that might skip it. If the variable comes from a lookup, also verify that the requested sheet, workbook, or other item exists.

Another frequent problem is a misspelled or absent worksheet name. Set ws = ThisWorkbook.Worksheets("Data") raises an error if that workbook has no worksheet named Data. The workbook may also be different from the one the code author expected. Inspect whether the code should use ThisWorkbook or ActiveWorkbook, and qualify ranges with their worksheet, such as ws.Range("A1"), rather than relying on whichever sheet is active.

When an assignment fails to compile, check the declared type and the expression’s type. A variable declared As Worksheet cannot hold a Range object. If a procedure needs to accept different object types, it may use As Object, but a specific type such as Worksheet or Range is usually clearer and enables compile-time checking and useful editor features.

Good Habits When Writing Set Statements

Use Option Explicit at the top of modules so VBA requires variables to be declared. Give variables names that reveal what they reference, such as sourceSheet or resultRange, and declare them with the narrowest practical type. Fully qualify workbook, worksheet, and range references to avoid accidental dependence on the active window or selection.

Use Set only when assigning an object reference.
Use .Value, .Name, or another explicit property when assigning or reading data.
Check for Nothing when an object reference may not have been assigned.
Confirm that named worksheets and workbooks exist before relying on them.
Prefer explicit object types over As Object when the object type is known.

Before running a macro that changes a workbook, save a copy of the file. VBA macros generally cannot be undone with Ctrl+Z, so a backup is the reliable way to recover from an unintended change. This applies whether the macro uses Set or not; the keyword assigns references, but the code that follows those references can alter workbook contents.

Frequently Asked Questions

Is Set a function in VBA?

No. In object assignment, Set is a VBA statement keyword. It assigns an object reference to an object variable; it is not called like a function with parentheses.

When do I need to use Set in VBA?

Use it when assigning objects such as a Workbook, Worksheet, Range, or Dictionary to an object variable, and when assigning an object returned by a function to an object variable.

What happens if I forget Set?

An object assignment written without Set will generally produce a compile or runtime error because VBA is not being given the required object-reference assignment syntax. Add Set when the variable is an object variable.

Does Set create a copy of a worksheet or range?

No. It assigns a reference to an existing object. Two variables can refer to the same object, and changing that object through one reference affects the same underlying object.

What does Set variable = Nothing do?

It clears the object reference held by that variable. It does not delete the worksheet, range, or workbook. Use If variable Is Nothing Then to test whether an object variable currently has a reference.

Can I use Set to assign a cell value?

No. Assign the cell’s value through a property, for example rng.Value = "Ready". Use Set rng = ws.Range("A1") to assign the Range object itself.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.