VBA Excel. Оператор Set

Оператор Set и присваивание переменным ссылок на объекты в VBA Excel. Синтаксис оператора Set, примеры использования, причины ошибок, рекомендации.

Содержание

Оператор Set в VBA Excel

В VBA Excel для назначения (присвоения) данных переменной есть два основных оператора: Let и Set. Let назначает простые типы данных, а Set — ссылки на объекты.

Оператор Set назначает переменной ссылку на объект: Workbook, Worksheet, Range, Chart, Dictionary, пользовательский класс и т.д.

Set не копирует объект, а создаёт ссылку на существующий в памяти объект. Это означает, что переменная и исходный объект указывают на одну и ту же область памяти, и изменение через одну из переменных повлияет на все ссылки (в ситуации, если несколько переменных ссылаются на один и тот же объект).

Проще говоря, если вы хотите сохранить в переменную ссылку на объект, а не значение какого-либо из его параметров — вам нужен именно оператор Set.

Синтаксис оператора Set

Set объектная_переменная = [ New ] выражение_объекта | Nothing

1	Set объектная_переменная = [ New ] выражение_объекта \| Nothing

Синтаксис оператора Set состоит из следующих элементов:

Элемент	Описание
`объектная_переменная`	Обязательный параметр. Имя переменной, соответствующее стандартным соглашениям об именовании переменных.
`New`	Необязательный параметр. Когда `New` используется с `Set`, он создает новый экземпляр класса. Ключевое слово `New` не может использоваться для создания новых экземпляров любого встроенного типа данных и не может использоваться для создания зависимых объектов.
`выражение_объекта`	Обязательный параметр, если не используется `Nothing`. Выражение, состоящее из имени объекта, другой объектной переменной того же типа, функции или метода, которые возвращают объект того же типа.
`Nothing`	Обязательный параметр, если не используется `выражение_объекта`. Прекращает связь элемента `объектная_переменная` с любым определённым объектом. При этом освобождаются все системные ресурсы и ресурсы памяти, связанные с ранее указанным объектом, если на него не ссылается другая переменная.

Виды объектов, требующие Set

К объектам, требующим оператор Set для присвоения ссылки переменным в VBA Excel относятся:

Объекты Excel: Application, Workbook, Worksheet, Range, Chart, Shape, ListObject и др.
Объекты других приложений (Word, Access, Outlook) через соответствующие библиотеки.
Объекты коллекций: Collection, Dictionary.
Пользовательские классы (Class Module).
Формы и элементы управления: UserForm, CommandButton, TextBox.

Примеры с оператором Set

Присваивание ссылки на диапазон

Sub ExampleRange()
    Dim cell As Range
    Set cell = ThisWorkbook.Worksheets("Лист1").Range("B2")
    cell.Value = 100
    cell.Font.Bold = True
End Sub

Sub ExampleRange()

Dim cell As Range

Set cell = ThisWorkbook.Worksheets("Лист1").Range("B2")

cell.Value = 100

cell.Font.Bold = True

End Sub

Работа с листами и книгами

Sub ExampleWorksheets()
    Dim ws As Worksheet
    Dim wb As Workbook
    
    Set wb = Workbooks("Книга1.xlsm")
    Set ws = wb.Worksheets("Лист3")
    
    MsgBox ws.Range("A10").Value
    
    ' Удаляем ссылки на объекты, освобождаем ресурсы
    Set wb = Nothing
    Set ws = Nothing
End Sub

Sub ExampleWorksheets()

Dim ws As Worksheet

Dim wb As Workbook

Set wb = Workbooks("Книга1.xlsm")

Set ws = wb.Worksheets("Лист3")

MsgBox ws.Range("A10").Value

' Удаляем ссылки на объекты, освобождаем ресурсы

Set wb = Nothing

Set ws = Nothing

End Sub

Создание нового объекта через New

Новый объект с помощью ключевого слова New можно создать двумя способами. Рассмотрим оба способа на примере создания новых объектов Collection.

Sub CreateNewObject()

    ' Способ 1: неявное использование Set, то есть без него
    Dim col_1 As New Collection
    
    ' Способ 2: явное использование Set
    Dim col_2 As Collection
    Set col_2 = New Collection
    
    col_1.Add "Коллекция 1 Элемент 1"
    col_1.Add "Коллекция 1 Элемент 2"
    
    col_2.Add "Коллекция 2 Элемент 1"
    col_2.Add "Коллекция 2 Элемент 2"
    
    MsgBox col_1(2)
    MsgBox col_2(1)
    
    ' Удаляем ссылки на объекты, освобождаем ресурсы
    Set col_1 = Nothing
    Set col_2 = Nothing
    
End Sub

Sub CreateNewObject()

' Способ 1: неявное использование Set, то есть без него

Dim col_1 As New Collection

' Способ 2: явное использование Set

Dim col_2 As Collection

Set col_2 = New Collection

col_1.Add "Коллекция 1 Элемент 1"

col_1.Add "Коллекция 1 Элемент 2"

col_2.Add "Коллекция 2 Элемент 1"

col_2.Add "Коллекция 2 Элемент 2"

MsgBox col_1(2)

MsgBox col_2(1)

' Удаляем ссылки на объекты, освобождаем ресурсы

Set col_1 = Nothing

Set col_2 = Nothing

End Sub

Присваивание одной объектной переменной другой

Sub CopyObjectReference()
    Dim rng1 As Range
    Dim rng2 As Range
    
    Set rng1 = Range("A1:B10")
    Set rng2 = rng1   ' Обе переменные ссылаются на один и тот же диапазон
    rng2.Interior.Color = vbYellow   ' Закрасит A1:B10 в жёлтый цвет
End Sub

Sub CopyObjectReference()

Dim rng1 As Range

Dim rng2 As Range

Set rng1 = Range("A1:B10")

Set rng2 = rng1 ' Обе переменные ссылаются на один и тот же диапазон

rng2.Interior.Color = vbYellow ' Закрасит A1:B10 в жёлтый цвет

End Sub

Использование универсального типа Object

Sub CreateObjectDictionary()
    Dim obj As Object
    Set obj = CreateObject("Scripting.Dictionary")
    obj.Add "Ключ1", "Значение1"
    obj.Add "Ключ2", "Значение2"
    obj.Add "Ключ3", "Значение3"
    MsgBox obj("Ключ2")
    Set obj = Nothing
End Sub

Sub CreateObjectDictionary()

Dim obj As Object

Set obj = CreateObject("Scripting.Dictionary")

obj.Add "Ключ1", "Значение1"

obj.Add "Ключ2", "Значение2"

obj.Add "Ключ3", "Значение3"

MsgBox obj("Ключ2")

Set obj = Nothing

End Sub

Типичные ошибки при работе с Set

Ошибка 91

Ошибка 91: Переменная объекта или переменная блока With не задана (Object variable or With block variable not set) возникает, когда переменная начинает использоваться без присвоения ей ссылки на объект через оператор Set, или когда ссылка на объект назначается через обычное присваивание.

Dim rng As Range
rng.Value = 5   ' Ошибка 91 - ссылка на объект не присвоена
rng = Range("A1:B10")  ' Ошибка 91 - обычное присваивание (без Set)

Dim rng As Range

rng.Value = 5 ' Ошибка 91 - ссылка на объект не присвоена

rng = Range("A1:B10") ' Ошибка 91 - обычное присваивание (без Set)

Ошибка 91: Object variable or With block variable not set

Ошибка 13

Ошибка 13: Несоответствие типов (Type mismatch) возникает, когда переменной присваивается несовместимый тип данных.

Dim rng As Range
Set rng = 42   ' Ошибка 13 - число не является объектом

1 2	Dim rng As Range Set rng = 42 ' Ошибка 13 - число не является объектом

Ошибка 13: Несоответствие типов (Type mismatch)

Ошибка 424

Ошибка 424: Требуется объект (Object required) возникает, когда необъектной переменной присваивается объект.

Dim rng As Double
Set rng = Range("A10")  ' Ошибка 424 - числовой переменной нельзя присвоить ссылку на объект

1 2	Dim rng As Double Set rng = Range("A10") ' Ошибка 424 - числовой переменной нельзя присвоить ссылку на объект

Ошибка 424: Требуется объект (Object required)

VBA Excel. Оператор Set

Оператор Set в VBA Excel

Синтаксис оператора Set

Виды объектов, требующие Set

Примеры с оператором Set

Присваивание ссылки на диапазон

Работа с листами и книгами

Создание нового объекта через New

Присваивание одной объектной переменной другой

Использование универсального типа Object

Типичные ошибки при работе с Set

Ошибка 91

Ошибка 13

Ошибка 424

Рекомендации по работе с Set