Excel vba сделать лист активным
Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.
Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.
В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.
Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.
В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).
Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1).
Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:
Присваивание объекта переменной
В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:
Активный объект
В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.
Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.
Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.
Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:
Смена активного объекта
Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:
Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.
Свойства объектов
Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:
Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:
Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.
Методы объектов
Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.
Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:
Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.
В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.
Рассмотрим несколько примеров
Пример 1
Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.
Пример 2
В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.
В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.
Пример 3
Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.
Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.
В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.
Чаще всего для ввода данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать.
Процесс создания выглядит очень просто:
Dim oExcel As New Excel.Application 'Запускаем Excel
oExcel.Visible = True 'Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add () 'Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Add() ' Создаем новый лист
oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"
Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.
Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":
Dim oExcel As New Excel.Application 'Запускаем Excel
oExcel.Visible = True 'Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Item("Лист 1") ' Находим Лист1
oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"
Обратите внимание, что в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются "Sheet1", "Sheet2" и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.
У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.
У объекта Worksheet — множество важных свойств и методов:
- Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
- EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
- EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
- Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
- PageSetup — как и в Word, возможность получить объект PageSetup, при помощи которого можно настроить те же параметры, что и через меню Файл -> Параметры страницы.
- свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
- QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
- Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
- Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
- UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
- Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).
Некоторые важные методы объекта Worksheet:
- методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()нам уже знакомы . Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
- метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться ниже;
- метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
- SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
- ShowAllData() — показать все скрытые и отфильтрованные данные на листе.
Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.
Все начинающие изучать VBA сталкиваются с тем, что записанные через макрорекордер коды пестрят методами Select и Activate.
Если не знакомы с работой макрорекордера - Что такое макрос и где его искать?
Это значительно ухудшает читабельность кода и, как ни странно - быстродействие. Но есть недостатки и куда более критичные. Если код выполняется достаточно долго и он постоянно что-то выделяет - пользователь может заскучать и забыться и начнет тыкать мышкой по листам и ячейкам, выделяя не то, что выделил ранее код. Что повлечет ошибки логики. Т.е. код может и выполнится, но совершенно не так, как ожидалось. Поэтому избавляться от Select и Activate необходимо везде, где это возможно.
Для начала рассмотрим два кода, выполняющие одни те же действия - запись в ячейку А3 листа Лист2 слова "Привет". При этом сам код запускается с Лист1 и после выполнения код Лист1 должен остаться активным. Чтобы сделать эти действия вручную потребуется сначала перейти на Лист2, выделить ячейку А3, записать в неё слово "Привет" и вернуться на Лист1. Поэтому запись макрорекордером этих действий приведет к такому коду:
Sub Макрос1() Sheets("Лист2").Select 'выделяем Лист2 Range("A3").Select 'выделяем ячейку А3 ActiveCell.FormulaR1C1 = "Привет" 'записываем слово Привет Range("A4").Select 'после нажатия Enter автоматически выделяется ячейка А4 Sheets("Лист1").Select 'возвращаемся на Лист1 End Sub
Нигде не говорится, что в большинстве случаев все эти Select и Activate в кодах не нужны. Однако вышеприведенный код можно значительно улучшить, если убрать все ненужные Select и Activate:
Sub Макрос1() Sheets("Лист2").Range("A3").FormulaR1C1 = "Привет" End Sub
Как видно, вместо 5-ти строк кода получилась одна строка. Которая выполняет ту же задачу, что и код из 5-ти строк.
Прежде чем понять как правильно избавляться от лишнего давайте разберемся зачем же тогда VBA записывает эти Select и Activate? Как ни странно, но здесь все очень просто. VBA просто не знает, что Вы будете делать после того, как выделили Лист2. И когда Вы переходите на Лист2 - VBA записывает именно переход(его активацию, выделение). Когда выделяете ячейку - так же именно это действие записывает VBA. Захотите ли Вы затем выделить еще что-то, или закрасить ячейку, или записать в неё формулу/значение - VBA не знает. Поэтому в дальнейшем VBA работает именно с выделенным объектом Selection на активном листе.
Но при написании кода вручную или при правке записанного рекордером мы уже вольны в выборе и знаем, чего хотели добиться и какие действия нам точно не нужны.
Итак, чтобы записать в ячейку слово "Привет" рекордер предложит нам такой код:
Sub Макрос1() Range("A3").Select 'выделяем ячейку А3 ActiveCell.FormulaR1C1 = "Привет" 'записываем слово Привет Range("A4").Select 'после нажатия Enter автоматически выделяется ячейка А4 End Sub
однако выделять ячейку( Range("A3").Select ) совершенно необязательно. Значит один Select уже лишний. После этого идет обращение к активной ячейке - ActiveCell . .FormulaR1C1 = "Привет" означает запись значения "Привет" в эту ячейку.
Пусть не смущает FormulaR1C1 - VBA всегда так указывает запись и значения и формулы. Т.к. перед словом "Привет" нет знака равно - то это значение.
Т.к. ActiveCell является обращением к выделенной ячейке, а выделили мы до этого А3, значит их можно просто "сократить":
Sub Макрос1() Range("A3").FormulaR1C1 = "Привет" Range("A4").Select 'после нажатия Enter автоматически выделяется ячейка А4 End Sub
Теперь у нас код получился короче и понятнее. Однако остался один Select: Range("A4").Select . Если нет необходимости выделять ячейку А4 после записи в А3 значения, то надо просто удалить эту строку и после выполнения кода активной будет та ячейка, которая была выделена до выполнения(т.е. выделенная ячейка просто не изменится). Таким образом мы с трех строк сократим код до 1-ой:
Sub Макрос1() Range("A3").FormulaR1C1 = "Привет" End Sub
Теперь несложно догадаться, что с листами все в точности так же. Sheets("Лист2").Select - Select хоть и не нужен, но и ActiveSheet после него нет. Здесь необходимо знать некоторую иерархию в Excel. Сначала идет сам Excel - Application, потом книга - Workbook. В книгу входят рабочие листы(Worksheets), а уже в листах - ячейки и диапазоны - Range и Cells(Application ->Workbook ->Worksheet ->Range). Если перед Range или Cells не указывать явно лист: Range("A3").FormulaR1C1 = "Привет" , то значение будет записано на активный лист. Подробнее можно прочесть в статье: Как обратиться к диапазону из VBA
Маленький нюанс: если сокращаем обращение к объектам, то Select-ов быть не должно вообще. Иначе есть шанс получить ошибку "Subscript out of range":
буквально это означает, что указанный индекс вне досягаемости. А появляется эта ошибка потому, что нельзя выделить ячейку НЕактивного листа или лист НЕактивной книги. Легко эту ошибку получить например в таком коде:
Sub Макрос2() Windows("Книга3").Activate 'здесь появится ошибка, т.к. пытаемся выделить лист в Книга2 'а на данный момент активной является Книга3 Windows("Книга2").Sheets("Лист3").Select End Sub
Ошибка обязательно появится, т.к. сначала мы активировали кодом книгу "Книга3", а потом пытаемся активировать лист НЕактивной на этот момент книги "Книга2". А это сделать невозможно без активации той книги, в которой активируемый лист. Т.е. активация должна происходить именно последовательно: Книга ->Лист ->Ячейка. И никак иначе, если мы хотим активировать именно конкретную ячейку конкретного листа в конкретной книге.
И пример с ячейками:
Sub Макрос2() Sheets("Лист3").Select 'здесь появится ошибка, т.к. пытаемся выделить ячейку на листе "Лист1" 'а на данный момент активным является Лист3 Sheets("Лист1").Range("C7").Select End Sub
Так же такая ошибка может появиться и по той простой причине, что указанного листа или книги нет(листа с указанным именем нет в данной книге или книга, к которой обращаемся просто закрыта).
Еще небольшой пример оптимизации:
Sub Макрос2() Windows("Книга3").Activate Sheets("Лист3").Select Range("C7").Select ActiveCell.FormulaR1C1 = "Привет" Range("C7").Select Selection.Font.Bold = True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub
Этот код записывает в ячейку С7 Лист3 книги "Книга3" слово "Привет", потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением:
далее делаем для ячейки жирный шрифт:
With Workbooks("Книга3").Sheets("Лист3").Range("C7").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With
Тут есть нюанс. Windows необходимо всегда заменять на Workbooks - в кодах я сделал именно так. Если этого не сделать, то получите ошибку 438 - объект не поддерживает данное свойство или метод(object dos't support this property or metod), т.к. коллекция Windows не содержит определения для Sheets.
Вместо Workbooks("Книга3.xlsx") можно использовать обращение к активной книге или книге, в которой расположен код. Обращение к Лист3 активной книги, когда активен Лист2 или другой:
Но бывают случаи, когда необходимо производить действия исключительно в той книге, в которой сам код. И не зависеть при этом от того, какая книга активна в данный момент и как она называется. Ведь в процессе книга может быть переименована. За это отвечает ThisWorkbook:
ActiveWorkbook - действия с активной на момент выполнения кода книгой
ThisWorkbook - действия с книгой, в которой записан код
Однако так же бывают случаи, когда необходимо обращаться к новой книге или листу, которые были созданы в процессе работы кода. В таком случае необходимо использовать переменные:
Sub NewBook() 'объявляем переменную для дальнейшего обращения Dim wbNewBook As Workbook 'создаем книгу Set wbNewBook = Workbooks.Add 'теперь можно обращаться к wbNewBook как к любой другой книге 'но уже не указывая её имя wbNewBook.Sheets(1).Range("A1").Value = "Привет" 'Sheets(1) - обращение к листу по его порядковому номеру '(отсчет с начинается с 1 слева) End Sub Sub NewSheet() 'объявляем переменную для дальнейшего обращения Dim wsNewSheet As Worksheet 'добавляем новый лист в активную книгу Set wsNewSheet = ActiveWorkbook.Sheets.Add 'теперь можно обращаться к wsNewSheet как к любому другому листу 'но уже не указывая его имя или индекс wsNewSheet.Range("A1").Value = "Привет" End Sub
Не везде Activate лишний
Но есть и такие свойства и методы, которые требуют обязательной активации книги/листа. Одним из таких свойств является свойство окна FreezePanes(Закрепление областей):
Sub Freeze_Panes() ThisWorkbook.Activate Sheets(2).Activate Range("B2").Select ActiveWindow.FreezePanes = True End Sub
В этом коде нельзя убирать Select и Activate, т.к. свойство FreezePanes применяется исключительно к активному листу и активной ячейке, потому что является оно именно методом окна, а не листа или ячейки.
Так же сюда можно отнести свойства: Split, SplitColumn, SplitHorizontal и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.
Открытие книги Excel из кода VBA. Создание новой книги, присвоение ей имени. Обращение к открытой книге и закрытие. Методы Open, Add и Close объекта Workbooks.
- Открытие существующей книги
- Создание новой книги
- Обращение к открытой книге
- Как закрыть книгу Excel из кода VBA
Открытие существующей книги
Существующая книга открывается из кода VBA Excel с помощью метода Open:
В кавычках указывается полный путь к открываемому файлу Excel. Если такой файл не существует, произойдет ошибка.
Проверить существование файла можно с помощью функции Dir:
Или, если файл существует, можно сразу его открыть:
Создание новой книги
Новая рабочая книга Excel создается в VBA с помощью метода Add:
Созданную книгу, если она не будет использоваться как временная, лучше сразу сохранить:
В кавычках указывается полный путь сохраняемого файла Excel, включая присваиваемое имя, в примере — это «test2.xls».
Обращение к открытой книге
Обращение к активной книге:
Обращение к книге с выполняемым кодом:
Обращение к книге по имени:
Обратиться по имени можно только к уже открытой книге, а чтобы из кода VBA Excel книгу открыть, необходимо указать полный путь к файлу.
Как закрыть книгу Excel из кода VBA
Открытая рабочая книга закрывается из кода VBA Excel с помощью метода Close:
Если закрываемая книга редактировалась, а внесенные изменения не были сохранены, тогда при ее закрытии Excel отобразит диалоговое окно с вопросом: Вы хотите сохранить изменения в файле test1.xlsx? Чтобы файл был закрыт без сохранения изменений и вывода диалогового окна, можно воспользоваться параметром метода Close — SaveChanges:
Закрыть книгу Excel из кода VBA с сохранением внесенных изменений можно также с помощью параметра SaveChanges:
Настоящая заметка продолжает знакомство с VBA, в ней приводятся примеры управления рабочими книгами и листами Excel с помощью VBA.
Рис. 1. Столбцы и строки скрыты за исключением рабочего диапазона
Сохранение всех рабочих книг
Следующая процедура циклически просматривает все рабочие книги в коллекции Workbooks и сохраняет каждый файл, который сохранялся ранее.
Public Sub SaveAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Path " " Then Book.Save
Next Book
End Sub
Обратите внимание на то, как используется свойство Path. Если для какой-либо рабочей книги свойство Path не задано, значит, файл еще не сохранялся (это новая рабочая книга). Данная процедура игнорирует такие рабочие книги и сохраняет только те из них, свойство Path которых имеет ненулевое значение.
Сохранение и закрытие всех рабочих книг
Следующая процедура циклически просматривает коллекцию Workbooks. Программа сохраняет и закрывает все рабочие книги.
Sub CloseAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Name ThisWorkbook.Name Then
Book.Close savechanges:=True
End If
Next Book
ThisWorkbook.Close savechanges:=True
End Sub
Обратите внимание, что процедура использует оператор If, чтобы определить, содержит ли данная рабочая книга текущий выполняемый код. Это необходимо, так как при закрытии рабочей книги, содержащей процедуру, программа автоматически завершает свое выполнение, причем остальные рабочие книги не будут сохранены и закрыты.
Частичное сокрытие элементов рабочего листа
В примере из этого раздела скрываются все строки и столбцы рабочего листа за исключением тех из них, которые находятся в текущем выделенном диапазоне (рис. 1).
Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long
If TypeName(Selection) " Range " Then Exit Sub
' Если последняя строка либо последний столбец скрыты,
' отобразить все и выйти
If Rows(Rows.Count).EntireRow.Hidden Or _
Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If
row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count — 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count — 1
Application.ScreenUpdating = False
On Error Resume Next
' Скрыть строки
Range(Cells(1, 1), Cells(row1 — 1, 1)).EntireRow.Hidden = True
Range(Cells(row2 + 1, 1), Cells(Rows.Count, _
1)).EntireRow.Hidden = True
' Скрыть столбцы
Range(Cells(1, 1), Cells(1, col1 — 1)).EntireColumn.Hidden = True
Range(Cells(1, col2 + 1), Cells(1, _
Columns.Count)).EntireColumn.Hidden = True
End Sub
Чтобы показать все строки и столбцы, встаньте на перекрестие строк и столбцов (выделено красным на рис. 1), и пройдите по меню: Главная –> Ячейки –> Формат –> Скрыть или отобразить –> Отобразить строки. Повторите этот путь, чтобы отобразить столбцы (рис. 2).
Рис. 2. Показать все строки и столбцы
Если выделенный диапазон включает несвязанные диапазоны, видимым останется только первый из них.
Синхронизация выделенного диапазона на листах рабочей книги
Если вы работаете с рабочими книгами, состоящими из нескольких листов, то, вероятно, знаете, что Excel не может «синхронизировать» листы в рабочей книге. Другими словами, не существует автоматического способа сделать так, чтобы все листы имели одинаковые выделенные диапазоны и верхние левые ячейки. Макрос VBA, показанный ниже, берет за основу активный рабочий лист и выполняет следующие действия со всеми остальными рабочими листами в книге:
- выделяет тот же диапазон, что и в активном листе;
- задает ту же левую верхнюю ячейку, что и на активном листе (рис. 3).
Рис. 3. Синхронизация выделенного диапазона на листах рабочей книги
Sub SynchSheets()
' Дублирование выделенного диапазона активного листа
' и верхней левой ячейки активного диапазона на всех листах
If TypeName(ActiveSheet) " Worksheet " Then Exit Sub
Dim UserSheet As Worksheet, sht As Worksheet
Dim TopRow As Long, LeftCol As Integer
Dim UserSel As String
Application.ScreenUpdating = False
' Запоминание текущего листа
Set UserSheet = ActiveSheet
' Сохранение сведений об активной ячейке
TopRow = ActiveWindow.ScrollRow
LeftCol = ActiveWindow.ScrollColumn
UserSel = ActiveWindow.RangeSelection.Address
' Циклический обход рабочих листов
For Each sht In ActiveWorkbook.Worksheets
If sht.Visible Then ' пропуск скрытых листов
sht.Activate
Range(UserSel).Select
ActiveWindow.ScrollRow = TopRow
ActiveWindow.ScrollColumn = LeftCol
End If
Next sht
' Восстановление исходного положения
UserSheet.Activate
Application.ScreenUpdating = True
End Sub
По материалам книги Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA. – М: Диалектика, 2013. – С. 348–351.
Задача состоит в том, чтобы скопировать определенный диапазон текущего листа, открыть другую книгу, и вставить эти скопированные данные в определенную ячейку, сохранить этот файл и закрыть. Ниже приведен код VBA.
Вариант 2: В открывшейся книге запускаем макрос, чтобы он открыл нужную нам книгу, скопировал от туда нужные нам данные и вставил в нашу открытую книгу, закрыв файл из которого эти данные были скопированы
Еще пример — Скопировать диапазоны данных из активной открытой книги Excel нескольких листов (в нашем примере 3-х листов) в другую книгу, которая хранится в определенном месте. Данные будут вставлены как значения, плюс будут перенесены форматы ячеек.
Если статья была вам полезна, то буду благодарен, если вы поделитесь ей со своими друзьями с помощью кнопок расположенных ниже.
Спасибо за внимание.
Иногда мы можем захотеть открыть или создать новую книгу с помощью VBA. Вы можете установить новую созданную книгу на объект, чтобы было легко сослаться на вашу книгу для выполнения дальнейших задач.
Решение(ия):
Следующие макросы позволят открыть или создать новую книгу с помощью метода Добавить.
Создать новую книгу в Excel VBA — примеры:
- Создать новую книгу
- Создание объекта для вновь созданной рабочей книги
Создать новую книгу
Вы можете использовать следующий код для создания новой книги и сохранения на диске C с помощью метода «Добавить».
- Метод добавления рабочей книги добавит новую книгу
- Сохранить как рабочую книгу метод сохранит активную книгу в определенном месте с указанным именем файла
Вывод: Вы должны увидеть недавно открытую книгу вместе с существующей книгой.
Инструкции:
- Откройте рабочую книгу Excel
- Нажмите Alt + F11, чтобы открыть редактор VBA.
- Вставьте новый модуль из меню «Вставка»
- Создайте приведенный выше код и вставьте в окно кода
- Нажмите F5, чтобы увидеть выход
- Вы должны увидеть недавно открытую книгу, как показано выше.
- Сохраните файл в виде макросъемки
Создание объекта для вновь созданной книги
Вы можете установить вновь созданную книгу на объект, так что вам легко обратиться к вашей книге для выполнения дальнейших задач.
Читайте также: