Author: Oscar Cronquist Article last updated on April 04, 2019

The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object.

For example, the DIM statement (among others) only declare a variable but it will be empty, nothing is referred until you assign an object using the SET statement.

What is an Excel object? It can be a chart, worksheet, a cell range or a shape among other things basically. Each object has usually a number of properties that you may change, for example, a worksheet may be hidden by changing the visible property to False.

The following macro is used in the above example.

'Name macro
Sub Macro1()

'Declare variable
Dim rng As Range

'Assign object
Set rng = Worksheets("Sheet1").Range("B2:C9")

'Show object address
MsgBox rng.Address

End Sub

Excel Statement Syntax

Set objectvar = {[ New ] objectexpression | Nothing }

Syntax Explained

Excel Name Description
objectvar The name of the object variable.
New Optional. Is used to create an object.
objectexpression A name of an object or another object variable with the same object type.
Nothing Deletes object. Recommended if an object holds a lot of memory that you don't need anymore.

Comments

How can I create a new worksheet and assign it to an object variable?
The following macro creates a new worksheet and then shows the worksheet name using the assigned object.

Sub Macro2()

Set obj = Sheets.Add
MsgBox obj.Name

End Sub

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.