Листы и книги

Учимся обращаться к листам и книгам, не полагаясь на то, «что сейчас открыто», и находим последнюю заполненную строку — приём, без которого не обходится ни один рабочий макрос.

Worksheet — лист книги. Workbook — сам файл Excel. Коллекция Worksheets живёт внутри книги, коллекция Workbooks — внутри Excel.

Зачем это нужно

В прошлом уроке мы договорились писать ws.Range("A1") вместо Range("A1"). Осталось разобраться, откуда берётся сам ws — и как не промахнуться листом или, что куда обиднее, целым файлом.

Промах файлом — самая дорогая ошибка новичка. Макрос лежит в Отчёт.xlsm, открывает выгрузку Продажи.xlsx, а потом пишет результат в ActiveWorkbook — и результат уходит в чужой файл, который через минуту закроется без сохранения. Данные не потеряны, их просто никогда и не было.

Иерархия: Excel → книга → лист → ячейка

Полный, ничем не сокращённый адрес ячейки в VBA выглядит так:

Application.Workbooks("Отчёт.xlsm").Worksheets("Данные").Range("A1").Value = 100

Читается справа налево как почтовый адрес: ячейка A1, на листе «Данные», в книге «Отчёт.xlsm», в приложении Excel. На практике так не пишут — Application подразумевается по умолчанию, да и книгу обычно кладут в переменную. Но держать эту цепочку в голове полезно: каждый раз, когда вы опускаете звено, Excel подставляет туда «активное». Опустили книгу — подставится активная книга. Опустили лист — активный лист.

Три способа взять лист

Sub ThreeWaysToGetSheet()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Данные")   ' 1. по имени вкладки
    Set ws = ThisWorkbook.Worksheets(1)          ' 2. по позиции слева направо
    Set ws = Sheet1                              ' 3. по кодовому имени (CodeName)

    MsgBox "Имя вкладки: " & ws.Name & vbCrLf & _
           "Кодовое имя: " & ws.CodeName
End Sub

Способ	Плюс	Риск
`Worksheets("Данные")`	читается понятнее всего	пользователь переименовал вкладку — макрос упал (ошибка 9, «Subscript out of range»)
`Worksheets(1)`	работает при любом имени	пользователь перетащил вкладку — макрос молча пишет не туда
`Sheet1` (CodeName)	переименование и перетаскивание не страшны	работает только внутри своей книги; имя видно лишь в редакторе VBA

Что такое CodeName. Откройте редактор VBA (Alt+F11) и посмотрите в дерево слева: там листы подписаны как Лист1 (Данные). В скобках — имя вкладки, которое видит пользователь и может менять сколько угодно. А Лист1 (или Sheet1 в английской версии) — кодовое имя. Оно задаётся в свойствах листа в редакторе и обычным способом из интерфейса Excel не меняется. Обратиться к листу по кодовому имени можно напрямую, без Worksheets(...) — это самый устойчивый вариант для листов вашей собственной книги.

Worksheets или Sheets? Коллекция Sheets включает не только обычные листы, но и листы диаграмм. Если в книге есть лист-диаграмма, Sheets(2) может неожиданно оказаться не тем, что вы ждали, а присвоение Set ws As Worksheet = Sheets(2) упадёт с ошибкой типов. Пишите Worksheets — и получите ровно листы с ячейками.

ThisWorkbook против ActiveWorkbook

ThisWorkbook — книга, в которой лежит выполняющийся код. ActiveWorkbook — книга, которая сейчас на переднем плане.

Пока открыт один файл, это одно и то же, и разницу невозможно заметить. Она вылезает ровно в тот момент, когда макрос открывает второй файл — а это и есть типичный сценарий автоматизации.

' ПЛОХО: после Workbooks.Open активной становится ОТКРЫТАЯ книга
Sub ImportBad()
    Workbooks.Open "C:\Обмен\Продажи.xlsx"

    ' автор думал, что пишет в свою книгу с макросом,
    ' а ActiveWorkbook — это уже Продажи.xlsx!
    ActiveWorkbook.Worksheets("Свод").Range("A1").Value = "Импорт выполнен"
    ' → ошибка 9: листа «Свод» в Продажах нет
End Sub

' ХОРОШО: обе книги в переменных, никаких «активных»
Sub ImportGood()
    Dim wbMe As Workbook, wbSrc As Workbook
    Dim wsSrc As Worksheet, wsDst As Worksheet
    Dim lastRow As Long

    Set wbMe = ThisWorkbook
    Set wbSrc = Workbooks.Open("C:\Обмен\Продажи.xlsx")

    Set wsSrc = wbSrc.Worksheets(1)
    Set wsDst = wbMe.Worksheets("Свод")

    lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row

    ' переносим значения одним присваиванием, без буфера обмена
    wsDst.Range("A1").Resize(lastRow, 3).Value = _
        wsSrc.Range("A1").Resize(lastRow, 3).Value

    wbSrc.Close SaveChanges:=False
    MsgBox "Перенесено строк: " & lastRow
End Sub

Ключевая строка — Set wbSrc = Workbooks.Open(...). Метод Open не просто открывает файл, он возвращает объект открытой книги. Ловите его в переменную сразу — и дальше вам никогда не понадобится гадать, какая книга активна.

Обратите внимание и на wbSrc.Close SaveChanges:=False: закрываем именно исходник, явно указав, что сохранять нечего. Без параметра SaveChanges Excel покажет пользователю диалог «Сохранить изменения?» — и макрос зависнет, ожидая ответа, а на сервере без человека это означает вечное ожидание.

Последняя заполненная строка — приём, который нужен всем

Данные меняются: вчера в выгрузке было 300 строк, сегодня 1450. Жёстко прописать For r = 2 To 300 нельзя. Нужен способ спросить у листа: «где у тебя заканчиваются данные?»

Канонический ответ выглядит так:

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Строка выглядит колдовством, но читается просто, если разобрать её справа налево:

ws.Rows.Count — сколько всего строк на листе. В современном Excel это 1 048 576. Не пишите это число руками: в старом формате .xls строк было 65 536, и жёсткая константа сломает код на чужом файле.
ws.Cells(ws.Rows.Count, "A") — самая нижняя ячейка столбца A, то есть A1048576. Заведомо пустая.
.End(xlUp) — то же, что встать в эту ячейку и нажать Ctrl+↑: прыжок вверх до первой непустой ячейки.
.Row — номер строки, куда мы прилетели.

Мы идём снизу вверх — и в этом весь фокус. Популярная альтернатива ws.Range("A1").End(xlDown).Row идёт сверху вниз и останавливается на первой же дырке: одна пустая ячейка в середине таблицы — и половина данных потеряна. Ещё один сомнительный вариант, ws.UsedRange.Rows.Count, считает «использованную область», в которую Excel включает и просто отформатированные пустые ячейки. Удалили данные, но не форматирование — и UsedRange будет годами помнить строки, которых давно нет.

Соберём всё в рабочий макрос:

Sub CalcTotals()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long, r As Long

    Set ws = ThisWorkbook.Worksheets("Продажи")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   ' последний столбец шапки

    ' страховка: на листе только шапка или он вовсе пуст
    If lastRow < 2 Then
        MsgBox "На листе «Продажи» нет данных", vbExclamation
        Exit Sub
    End If

    ' D = B * C для каждой строки данных
    For r = 2 To lastRow
        ws.Cells(r, "D").Value = ws.Cells(r, "B").Value * ws.Cells(r, "C").Value
    Next r

    ' строка итога — сразу под данными
    ws.Cells(lastRow + 1, "A").Value = "Итого"
    With ws.Cells(lastRow + 1, "D")
        .Formula = "=SUM(D2:D" & lastRow & ")"
        .Font.Bold = True
    End With

    MsgBox "Обработано строк: " & (lastRow - 1) & vbCrLf & _
           "Столбцов в шапке: " & lastCol
End Sub

Результат для таблицы с данными в строках 2–150 и шапкой A1:D1:

Обработано строк: 149
Столбцов в шапке: 4

Два предостережения, которые стоит запомнить сразу.

Первое: выбирайте столбец, который заполнен всегда. End(xlUp) по столбцу A найдёт последнюю строку, где заполнен именно A. Если в вашей выгрузке в столбце A стоят номера по порядку, а последние клиенты почему-то без номера — вы недосчитаетесь строк. Ориентируйтесь на «ключевой» столбец: дату операции, ID, артикул. Симметричный приём для столбцов — End(xlToLeft) от самой правой ячейки строки шапки, он использован в примере выше.

Второе: на пустом листе приём вернёт 1. Ведь End(xlUp) с самого низа прилетит в A1, а она хоть и пустая, но существует. Поэтому проверка If lastRow < 2 Then Exit Sub — не паранойя, а обязательная часть приёма: без неё цикл For r = 2 To 1 просто не выполнится (это ещё полбеды), а формула итога встанет в A2 поверх пустоты.

Существует ли лист? Создаём и пересоздаём

Обращение к несуществующему листу — ошибка 9 «Subscript out of range», и она валит макрос целиком. Проверить наличие листа заранее готовой команды нет, поэтому пишут маленькую функцию-помощник:

Function SheetExists(wb As Workbook, sheetName As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next          ' временно: «не падай, если не найдёшь»
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0               ' обязательно вернуть обычную обработку ошибок!

    SheetExists = Not ws Is Nothing   ' нашли объект — значит, лист есть
End Function

Sub RecreateReportSheet()
    Dim wb As Workbook, ws As Worksheet
    Set wb = ThisWorkbook

    ' старый отчёт удаляем — без вопроса «вы уверены?»
    If SheetExists(wb, "Отчёт") Then
        Application.DisplayAlerts = False
        wb.Worksheets("Отчёт").Delete
        Application.DisplayAlerts = True
    End If

    ' Add возвращает созданный лист — сразу ловим его в переменную
    Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    ws.Name = "Отчёт"
    ws.Range("A1").Value = "Отчёт сформирован " & Format(Now, "dd.mm.yyyy hh:nn")
End Sub

On Error Resume Next — опасный инструмент: он глушит все ошибки подряд. Включать его допустимо ровно на одну строку, где вы точно знаете, какая ошибка может случиться и почему она вас устраивает. On Error GoTo 0 сразу после — не рекомендация, а требование безопасности.

Application.DisplayAlerts = False подавляет системный вопрос «Данные могут содержаться на удаляемых листах. Удалить?». Не забудьте вернуть True: иначе Excel до конца сеанса будет молча соглашаться со всем, включая перезапись файлов.

Как это работает

Worksheets и Workbooks — это коллекции, то есть пронумерованные наборы объектов. К элементу коллекции обращаются либо по номеру (Worksheets(2)), либо по ключу-имени (Worksheets("Данные")). Если ни номера, ни имени нет — коллекция возвращает не «пусто», а именно ошибку 9. Отсюда и необходимость в SheetExists.

ThisWorkbook и ActiveWorkbook — это не переменные, а свойства объекта Application, которые Excel пересчитывает на лету. ActiveWorkbook меняется, как только меняется фокус окна: открылся файл, пользователь щёлкнул по другому окну Excel, макрос создал новую книгу. ThisWorkbook не меняется никогда — он жёстко привязан к файлу, в котором физически лежит модуль с кодом. Поэтому в макросах, которые живут в вашей рабочей книге, ThisWorkbook — почти всегда правильный выбор.

Отдельно про Set: он нужен потому, что переменная-объект хранит не данные, а ссылку на живой объект Excel. Set ws = wb.Worksheets("Данные") означает «пусть ws указывает вот на этот лист». Пока ссылки нет, переменная равна Nothing — специальному значению «указывает в пустоту». Именно его и проверяет Not ws Is Nothing.

И последнее, что часто удивляет: обратиться к закрытой книге нельзя. Workbooks("Продажи.xlsx") сработает только если файл уже открыт в этом же экземпляре Excel. Иначе — сначала Workbooks.Open, и путь указывайте полный: относительные пути VBA считает от «текущей папки» Excel, а она может оказаться где угодно.

Частые ошибки

ActiveWorkbook вместо ThisWorkbook. Работает ровно до первого Workbooks.Open, после чего результат уезжает в чужой файл. Если код должен писать в свою книгу — пишите ThisWorkbook.
Жёсткое число строк. ws.Cells(65536, "A").End(xlUp) — реликт формата .xls: в современной книге такой поиск начнётся с середины листа и не увидит данные ниже. Всегда ws.Rows.Count.
Rows.Count без указания листа. Внутри ws.Cells(Rows.Count, "A") голое Rows относится к активному листу. Обычно счёт строк совпадает, и ошибка не проявляется — до первой книги в старом формате. Пишите ws.Rows.Count.
End(xlDown) вместо End(xlUp). Сверху вниз — остановка на первой пустой ячейке. Всегда идите снизу вверх.
Нет проверки на пустой лист. lastRow = 1 при пустых данных, и итоговая формула лезет в шапку.
Workbooks.Open без присвоения в переменную. Дальше приходится ловить книгу через ActiveWorkbook — и надеяться, что за эти миллисекунды ничего не изменилось. Ловите объект сразу: Set wb = Workbooks.Open(путь).
Забыли вернуть DisplayAlerts = True. Excel до конца сеанса перестанет спрашивать подтверждения — включая те, которые действительно нужны пользователю.

Итоги

Полная цепочка: Application → Workbooks → Worksheets → Range. Опустили звено — Excel подставит «активное».
Лист берут по имени (Worksheets("Данные")), по номеру (Worksheets(1)) или по кодовому имени (Sheet1 — устойчиво к переименованию вкладки).
Worksheets — только листы с ячейками; Sheets включает и листы диаграмм.
ThisWorkbook — книга с кодом, не меняется никогда. ActiveWorkbook — книга на переднем плане, меняется от любого чиха. По умолчанию берите ThisWorkbook.
Set wb = Workbooks.Open(полный_путь) — ловите открытую книгу в переменную сразу. Закрывайте через wb.Close SaveChanges:=False, иначе макрос повиснет на диалоге.
Последняя строка: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Последний столбец: ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column.
Проверяйте lastRow < 2 — на пустом листе приём возвращает 1.