Excel Analysis Blog
BETA
Придется немного поработать


Макросы. Урок четвертый

Объектные переменные

Переходим к следующему циклу занятий. Сегодня будут объектные переменные. Как мы уже говорили, в переменных мы можем хранить любые данные. Кроме этого, в переменных мы можем хранить ссылки на различные объекты (книги, листы и т.д.). Рассмотрим именно два первых. Типы данных следующие:

  • Workbook — Книги
  • Worksheet — Листы

Типы данных объявляются также через оператор Dim.

Dim wbk As Workbook

После объявления типа, нужно сделать собственно ссылку на объект. Данная операция проходит через оператор Set. Сейчас продемонстрируем код, который и постоянно использую в работе. Присвоим переменную книге, одновременно ее открыв.

Set wbk = Application.Workbooks.Open("C:/VBA/book3.xlsb")

Разберем этот код. Application — это команда, что объект находится в коллекции Excel. Далее Workbook — это означает, что элемент коллекции является книгой. Затем Open (команда открытия этого документа). Далее, в скобочках пишем полный путь до документа. В том числе и формат самого документа (.xlsb).

Путь до документа можно также хранить в виде переменной. Т.е. код может выглядеть так

Sub test()
    Dim qw As String
    Dim wbk As Workbook
    
    qw = "C:/VBA/book3.xlsb"
    Set wbk = Application.Workbooks.Open(qw)   
End Sub

Также можно делать ссылку на саму книгу, внутри которой лежит макрос.

Set wbk = ThisWorkbook

После этого, могу делать ссылку на необходимый лист. Причем, поскольку мне известна переменная, под которым лежит книга, то я ее уже могу использовать. Делаю ссылку на Лист1

Sub test2()
    Dim wbk As Workbook
    Dim sht As Worksheet
    
    Set wbk = ThisWorkbook
    Set sht = wbk.Worksheets("Лист1")
End Sub

Хотя если я знаю, что буду использовать только один лист в работе то переменная книги явно лишняя и забивает память. Поэтому лист можно было бы объявлять сразу

Set sht = ThisWorkbook.Worksheets(1)

Как видите, здесь я не написал название листа, а поставил его порядковый номер в документе (также тоже можно).

Работа с документом

Поскольку ранее мы все делали внутри кода, давай те постепенно переходить к работе с документом. Создаем новый документ, внутри которого будет писать макросы. На первом листе, в ячейку А1 пишем любую цифру. Для начала, нам нужно загнать в переменную данные из этой ячейки. Для этого будем использовать новый оператор Cells (ячейки). Данные получаются довольно легко. Для оператора Cells важны две координаты, номер строки и столбца.

Sub test3()
    Dim sht As Worksheet
    Dim a As Long
    
    Set sht = ThisWorkbook.Worksheets(1)
    a = sht.Cells(1, 1)
End Sub

После слова Cells в скобочках я в первую очередь указываю номер строки, затем номер столбца. Можете проверить код пошагово, убедиться, что переменная приобрела нужное значение. Точно также, я могу провести и обратное присвоение. Т.е. взять и данные переменной записать на лист. Добавим одну строчку в код.

Sub test3()
    Dim sht As Worksheet
    Dim a As Long
    
    Set sht = ThisWorkbook.Worksheets(1)
    a = sht.Cells(1, 1)

    sht.Cells(1, 2) = a 'здесь я запишу данные из переменной в ячейку B1
End Sub

Проверяем лист, видим что данные действительно записались. Подобное копирование можно было бы делать и без использования переменной.

Sub test3()
    Dim sht As Worksheet
    Dim a As Long
    
    Set sht = ThisWorkbook.Worksheets(1)
    a = sht.Cells(1, 1)
    
    sht.Cells(1, 3) = sht.Cells(1, 1)
End Sub

Но, как мы помним, использование переменной лучше, потому что так мы сможем ее перед записью миллионы раз изменить и пересчитать.

Безопасное открытие книг. Закрытие их с сохранением и без

В начале урока, мы выполняли подобный код:

qw = "C:/VBA/book3.xlsb"
Set wbk = Application.Workbooks.Open(qw)

Тут важно понимать, что если вы открываете какую-то книгу, и при этом собираетесь оттуда только считать информацию, не изменять ее саму, то лучше подстраховаться от внесения случайных изменений. Для этого рекомендуется открывать книгу в режиме «Только для чтения». Это делается добавлением небольшой команды:

Sub test4()
    Dim qw As String
    Dim wbk As Workbook
    
    qw = "C:/VBA/book3.xlsb"
    Set wbk = Application.Workbooks.Open(qw, ReadOnly:=True)
   
End Sub

Команда ReadOnly в зависимости от своего параметра может открыть книгу в режиме чтения или редактирования. Если такой команды у вас не стоит, то все книги, по умолчанию, открываются в режиме редактирования.

Открыть книгу мало, после завершения работы, еще и желательно закрыть ее. Тут команда простая:

wbk.Close

Как мы помним, под переменной wbk у меня хранится сама ссылка ни книгу. Но тут есть еще одна вещь. Как мы помним, когда мы закрываем Excel, в котором что-то изменяли, то нам выдают такое меню

Мы же хотим полностью автоматизировать данный процесс. Слава богу, это тоже можно запрограммировать.

wbk.Close SaveChanges:=True ‘в данном случае документ закроется с сохранением
wbk.Close SaveChanges:=False ‘а вот так без него.

Единственное, вы не можете делать так:

Sub test()
    Dim wbk As Workbook
    
    Set wbk = ThisWorkbook
    wbk.Close
End Sub

Макрос живет в той книге, в какой он работает. В этом же примере, мы пытаемся закрыть рабочую книгу из самой рабочей книги.

Теперь самостоятельная работа.

Скачать примеры урока

Скачать самостоятельное задание