VBA в Excel
Автор: Субботин Б.П.
VBA в Excel
Программирование в Excel с помощью VBA.
Язык VBA позволяет писать макросы в Excel. Как это делать рассматривается ниже.
Application
Application — это объект, занимающий вершину иерархии объектов в Excel. Application – это и есть Excel. Вот примеры использования Application:
окно нормальных размеров
окно максимальных размеров
Workbooks
Workbooks — это коллекция рабочих книг, которые открыты. Каждая рабочая книга представлена объектом Workbook. Узнать сколько рабочих книг сейчас в коллекции можно так:
Обатиться к нужной рабочей книге в коллекции Workbooks можно обратиться по его номеру или по имени:
здесь мы активизировали второй элемент коллекции Workbooks.
Другие примеры мы увидим ниже.
Workbook
Workbook — это рабочая книга.
Сохранить рабочую книгу:
И где же в этом примере рабочая книга Workbook? Здесь: Workbooks.Item(1), это выражение возвращает первый элемент коллекции Workbooks, а это и есть Workbook.
Закрыть рабочую книгу:
Sheets
Sheets — это все листы рабочей книги. Листы рабочей книги представлены двумя типами: рабочие листы — это обычные листы Excel и второй тип — это листы диаграмм. Те и другие и составляют коллекцию Sheets.
Charts
Charts — это только диаграммы рабочей книги.
Chart
Chart — это объект, представляющий одину диаграмму. Если диаграмма содержится в обычном рабочем листе, то она объектом Chart не является.
Worksheets
Worksheets — это только рабочие листы рабочей книги.
Добавим рабочий лист в коллекцию Worksheets:
Worksheet
Worksheet — это один лист рабочей книги Excel.
Все ячейки рабочего листа Worksheet:
Range. Работа с ячейками в Excel
Range — это одна ячейка листа или несколько ячеек. Этот объект рассмотрим подробнее. Установим кнопку на рабочий лист Excel. Откройте панель инструментов «Элементы управления» (правой кнопкой по свободному полю панели инструментов и выбираем из выпавшего меню). В этой панели выбираем кнопку и устанавливаем её на свободное место рабочего листа, в его правой части. Правой кнопкой мыши по нашей кнопке — > Свойства. Установите значение свойства Name — CommandButton, а свойства Caption — Range Test.
Закройте окно свойств кнопки, и кликаем по нашей кнопке правой кнопкой мыши, из выпавшего меню выбираем «Исходный текст». Открывается окно редактора VBA, оно называется Microsoft Visual Basic. В нём уже есть заготовка обработчика нажатия на кнопку CommandButton:
Кстати, помотрите на эту заготовку, ключевое слово Sub говорит, что это процедура, а слово Private указывает, что эта процедура видна лишь в данном модуле.
Всё у нас готово для начала изучения работы с ячейками рабочего листа Excel.
Как задать активную ячейку?
Сделаем активной ячейку A2:A2:
Далее сохраняем, далее Debug — > Compile VBA Project. Если нет ошибок, то запускаем нашу программу: Run — > Run Sub/UserForm.
Выделим любую другую ячейу и нажимём на кнопку Range Test. Вновь выделится ячейка «А2». Это значит, что обработчик нажатия на кнопку работает исправно.
Как выделить ряд ячеек?
Выделим интервал ячеек «A2:D2». Вносим в нашу заготовку макроса новый код:
Далее сохраняем, далее Debug — > Compile VBA Project. Переходим на рабочий лист Excel и жмем на кнопку Range Test.
Как установить значение в ячейку?
Внесем число 123 в ячейку «A2:A2» для этого изменим код макроса так:
Далее сохраняем, далее Debug — > Compile VBA Project. Переходим на рабочий лист Excel и жмем на кнопку Range Test.
Источник: sbp-program.ru
Управление рабочими книгами и листами в VBA
Настоящая заметка продолжает знакомство с VBA, в ней приводятся примеры управления рабочими книгами и листами Excel с помощью VBA.[1]
Рис. 1. Столбцы и строки скрыты за исключением рабочего диапазона
Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)
Сохранение всех рабочих книг
Следующая процедура циклически просматривает все рабочие книги в коллекции 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. Синхронизация выделенного диапазона на листах рабочей книги
Источник: baguzin.ru
Select и Activate – зачем нужны и нужны ли?
Все начинающие изучать 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.
Важный момент: лучше всегда указать имя книги вместе с расширением(.xlsx, xlsm, .xls и т.д.). Если в настройках ОС Windows(Панель управления –Параметры папок -вкладка Вид –Скрывать расширения для зарегистрированных типов файлов) указано скрывать расширения – то указывать расширение не обязательно – Workbooks(“Книга2”). Но и ошибки не будет, если его указать. Однако, если пункт “Скрывать расширения для зарегистрированных типов файлов” отключен, то указание Workbooks(“Книга2”) обязательно приведет к ошибке.
Вместо 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 и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.
Статья помогла? Поделись ссылкой с друзьями!
Источник: www.excel-vba.ru
Канал в Telegram
Вы здесь
Определяем количество открытых книг, страниц и их имена. Конструкция For Each . Next
В статье “Иерархия(структура) рабочей книги Excel” была рассмотрена структура рабочей книги, листа и приложения в целом. Из этого возникает вопрос, каким образом можно получить, например кол-во страниц в книге Excel, или их имена. С количеством все просто, к примеру, для того чтобы получить кол-во открытых книг, достаточно воспользоваться следующей командой:
Application.Workbooks.Count
Чтобы получить кол-во страниц в активной книге:
ActiveWorkbook.Worksheets.Count
Если же требуется получить кол-во страниц в определенной книге для этого воспользуйтесь:
Workbooks(1).Worksheets.Count
или
Workbooks(“имя книги.xls”).Worksheets.Count
В первом случае обращение к нужной книге происходит по индексу. Индекс присваивается в порядке открытия книг. Этот вариант не совсем удобен если необходимо обратится к конкретной книге т.к. книги могут открываться в разном порядке. Поэтому второй вариант команды позволяет обратиться к книге по ее полному имени.
Теперь перейдем к рассмотрению работы с конструкцией For Each . Next.
Синтаксис конструкции For Each . Next
For Each Элемент In Коллекция(Группа)
[команды][Exit For]
[команды]Next [Элемент]
Данная конструкция позволяет поочередно обратится к каждому элементу группы или коллекции. Коллекция это группа одинаковых (однотипных) объектов. Например, массивы, или коллекции WorkBooks, Worksheets .
For Each . Next по принципу работы похож на цикл For. Next, который был рассмотрен здесь. Схожесть в том что, перебирается каждый элемент коллекции или группы по порядку. Не буду томить скучной теорией перейдем к рассмотрению примеров.
Пример 1. Необходимо получить имена всех открытых книг Excel. Имена всех рабочих книг доступны в коллекции Workbooks.
Sub GetWorkBooksName()
Dim WBooks As Workbook
Dim Msg As String
For Each WBooks In Workbooks
Msg = Msg & WBooks.Name & Chr(13)
Next WBooks
MsgBox Msg
End Sub
Пример 2. Необходимо получить имена всех страниц в активной книге. Имена страниц содержатся в коллекции Worksheets.
Sub GetWorkSheetsName()
Dim Item As Worksheet
For Each Item In ActiveWorkbook.Worksheets
MsgBox “Отображаемое имя листа ” & CStr(Item.Index) & ” – ” & Item.Name
Next Item
Пример 3. Необходимо посчитать кол-во страниц во всех открытых книгах Excel.
Sub GetAllCountSheets()
Dim WBooks As Workbook
Dim kolSheet As Long
Dim Msg As String
kolSheet = 0
For Each WBooks In Workbooks
kolSheet = kolSheet + Workbooks(WBooks.Name).Worksheets.Count
Next WBooks
MsgBox “Всего страниц в открытых книгах: ” & CStr(kolSheet)
Пример 4. Необходимо преобразовать весь текст в выделенном диапазоне ячеек в верхний регистр т.е. сделаем буквы большими. Для этого воспользуемся коллекцией Selection (тип Range – хранит область выделенных ячеек) и функцией перевода символов в верхний регистр UCase (если необходим перевод в нижний регистр, то воспользуйтесь функцией LCase).
Sub RangeUpCase()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = UCase(Cell.Value)
Next Cell
Выделяем нужные ячейки или диапазон ячеек с текстом и запускаем макрос.
Пример 5. И заключительный пример, закроем все рабочие книги Excel, кроме активной, без сохранения.
Sub CloseBooks()
Dim WBook As Workbook
For Each WBook In Workbooks
If WBook.Name <> ActiveWorkbook.Name Then WBook.Close False
Next WBook
За закрытие книги Excel отвечает команда WBook.Close. Параметр False указывает, что закрываем все книги без сохранения. Если этот параметр изменить на True, то все книги будут закрываться предварительно сохранившись. Если же этот параметр убрать, то при закрытии книг с внесенными изменениями выскочит диалоговое окно с вопросом о сохранении.
Все примеры, рассмотренные в статье, Вы можете скачать ниже. Все.
Источник: www.programm-school.ru
Adblock
detector
