Если у вас есть Microsoft Excel задачи, которые вы делаете несколько раз, вы можете записать макрос, чтобы автоматизировать эти задачи. Макрос — это действие или набор действий, которые можно выполнить сколько угодно раз. При создании макроса записуются щелчки мышью и нажатия клавиш. После создания макроса его можно отредактировать, чтобы внести незначительные изменения в его работу.
Предположим, что каждый месяц вы создаете отчет для бухгалтера. Вы хотите отформатировать имена клиентов с просроченными учетными записями красным цветом, а также применить полужирное на форматирование. Вы можете создать и запустить макрос, который быстро применяет эти изменения форматирования к выбранным ячейкам.
Процедура
Перед записью макроса
Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки «Разработчик».
В группе Код на вкладке Разработчик нажмите кнопку Запись макроса.
При желании введите имя макроса в поле Имя макроса, введите сочетания клавиш в поле Сочетания клавиш и описание в поле Описание, а затем нажмите кнопку ОК, чтобы начать запись.
Выполните действия, которые нужно автоматизировать, например ввести шаблонный текст или заполнить столбец данных.
На вкладке Разработчик щелкните Остановить запись.
Подробнее о макросах
Вы можете узнать немного о языке программирования Visual Basic путем редактирования макроса.
Чтобы изменить макрос, в группе Код на вкладке Разработчик нажмите кнопку Макрос, выберите имя макроса и нажмите кнопку Изменить. При этом Visual Basic редактора.
Узнайте, как записанные действия отображаются как код. Возможно, какой-то код вам понятен, а часть может показаться немного неявным.
Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, не произойдет ли что-то другое!
Дальнейшие действия
Дополнительные информацию о создании макроса см. в теме Создание и удаление макроса.
Чтобы узнать, как запускать макрос, см. в этой теме.
Процедура
Перед записью макроса
Убедитесь, что на ленте отображается вкладка Разработчик. По умолчанию вкладка Разработчик не отображается, поэтому сделайте следующее:
Перейдите в Excel > параметры. > ленты & панель инструментов.
В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.
На вкладке Разработчик нажмите кнопку Запись макроса.
При желании введите имя макроса в поле Имя макроса, введите сочетания клавиш в поле Сочетания клавиш и описание в поле Описание, а затем нажмите кнопку ОК, чтобы начать запись.
Выполните действия, которые нужно автоматизировать, например ввести шаблонный текст или заполнить столбец данных.
На вкладке Разработчик в группе Код нажмите кнопку Остановить запись.
Подробнее о макросах
Вы можете узнать немного о языке программирования Visual Basic путем редактирования макроса.
Узнайте, как записанные действия отображаются как код. Возможно, какой-то код вам понятен, а часть может показаться немного неявным.
Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, не произойдет ли что-то другое!
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Автоматизация рутины в Microsoft Excel при помощи VBA
В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.
VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.
Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.
Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.
Поэтому, увы, будем учить Visual Basic.
Чуть-чуть подготовки и постановка задачи
Итак, поехали. Открываем Excel.
Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.
Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):
То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по Типу, потом по Производителю).
Результат, которого хотим добиться, выглядит примерно так:
Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли?
Кодим
Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».
И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».
Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:
Напишем Hello World:
Sub FormatPrice() MsgBox «Hello World!» End Sub
И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.
Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.
Примеры синтаксиса
Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит Dim i As Integer ‘ Цикл всегда состоит из нескольких строк For i = 1 To 10 res = res + CStr(i) ‘ Конвертация чего угодно в String If i = 5 Then Exit For Next i
Dim x As Double x = Val( «1.234» ) ‘ Парсинг чисел x = x + 10 MsgBox x
On Error GoTo Err ‘ При ошибке перейти к метке Err x = 5 / 0 MsgBox «OK!» GoTo ne
ne: On Error GoTo 0 ‘ Отключаем обработку ошибок
‘ Циклы бывает, какие захотите Do While True Exit Do
Loop ‘While True Do ‘Until False Exit Do Loop Until False ‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны. ‘ Val также умеет возвращать Integer Select Case LengthSqr(Len( «abc» ), Val( «4» )) Case 24 MsgBox «0» Case 25 MsgBox «1» Case 26 MsgBox «2» End Select
Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.
Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.
Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.
Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.
Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.
Кодим много и под Excel
В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.
Sub FormatPrice() Sheets( «result» ).Cells.Clear Sheets( «data» ).Activate End Sub
Работа с диапазонами ячеек
Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.
Примеры работы с Range
Sheets( «result» ).Activate Dim r As Range Set r = Range( «A1» ) r.Value = «123» Set r = Range( «A3,A5» ) r.Font.Color = vbRed r.Value = «456» Set r = Range( «A6:A7» ) r.Value = «=A1+A3»
Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:
Для упрощения работы рекомендую определить следующие функции-сокращения:
Function GetCol(Col As Integer ) As String GetCol = Chr(Asc( «A» ) + Col) End Function
Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».
Глобальные переменные
Option Explicit ‘ про эту строчку я уже рассказывал Dim CurRow As Integer Const GroupsCount As Integer = 2 Const DataCount As Integer = 3
FormatPrice
Sub FormatPrice() Dim I As Integer ‘ строка в data CurRow = 1 Dim Groups(1 To GroupsCount) As String Dim PrGroups(1 To GroupsCount) As String
Теперь надо заполнить массив Groups:
На месте многоточия
И создать заголовки:
На месте многоточия в предыдущем куске
For I2 = 1 To GroupsCount If Groups(I2) <> PrGroups(I2) Then Dim I3 As Integer For I3 = I2 To GroupsCount AddHeader I3, Groups(I3) Next I3 Exit For End If Next I2
Не забудем про процедуру AddHeader:
Перед FormatPrice
Теперь надо перенести всякую информацию в result
Подогнать столбцы по ширине и выбрать листresult для показа результата
После цикла в конце FormatPrice
Sheets( «Result» ).Activate Columns.AutoFit
Всё. Можно любоваться первой версией.
Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:
Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:
Поэтому чуть-чуть меняем код с добавлением стиля границ:
Select Case Ty Case 1 ‘ Тип .Font.Bold = True .Font.Size = 16 .Borders(xlTop).Weight = xlThick Case 2 ‘ Производитель .Font.Size = 12 .Borders(xlTop).Weight = xlMedium End Select .Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline End With CurRow = CurRow + 1 End Sub
Осталось лишь добится пропусков перед началом новой группы. Это легко:
В начале FormatPrice
Dim I As Integer ‘ строка в data CurRow = 0 ‘ чтобы не было пропуска в самом начале Dim Groups(1 To GroupsCount) As String
В цикле расстановки заголовков
If Groups(I2) <> PrGroups(I2) Then CurRow = CurRow + 1 Dim I3 As Integer
В точности то, что и хотели.
Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0CurRow = 1.
Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.
Спасибо за внимание.
Буду рад конструктивной критике в комментариях.
UPD: Перезалил пример на Dropbox и min.us.
UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.
Что такое макрос в Excel и для чего он предназначен?
Доброго времени, друзья. В сегодняшнем выпуске расскажу об интереснейшей функции в самом ходовом офисном пакете программ от Microsoft Office. Тем, кто постоянно работает с таблицами Excel, знает сколько рутинных операций приходится иногда делать с содержимым. Если документов много — приходится одни и те же действия повторять многократно. От выделения мышкой и копирования до более сложных — таких как вычисления, заполнение, очистка отдельных граф, создание таблиц, макетов и так далее.
Чтобы не повторять одни и те же действия каждый раз можно автоматизировать процесс и сэкономить время. Вы записываете свои действия (выделение ячеек документа, копирование, форматирование, ввод формул) в макрокоманду. Команда эта помещается в виде кнопки на панель инструментов. При нажатии кнопки все ваши записанные ранее действия выполняются автоматически. Это и есть «макрос».
Лично для меня знакомство с макросами в свое время превратилось в изучение программирования. Дело в том, что Microsoft Office является еще и легкой и понятной средой для этого. В нем есть свой встроенный язык — Visual Basic for Application или сокращенно VBA. И когда записываете макросы, Вы вы как бы становитесь программистом. При этом не обязательно на первых этапах знать язык. Программный код можно посмотреть уже потом, в редакторе и изучать. Как смотреть — об этом так же расскажу.
VBA позволяет создавать для себя мощные инструменты при работе с табличными документами для решения многих задач в виде отдельных окон, кнопок, списков, флажков — все визуально. А опытные программисты знают, что макросы можно использовать для того чтобы не набирать команды руками, а достать уже готовый код из только что записанного макроса. Но обо всем по порядку. Тем, кто после прочтения статьи заинтересуется и захочет изучать язык VBA самостоятельно в помощь книга. А сейчас разберемся подробно, что за «макросы», как их включать.
Что означает макросы и как с ними работать?
Итак, макрос — это некий программный код, который описывает ПОСЛЕДОВАТЕЛЬНОСТЬ ваших действий в документе. Записывается даже перемещение вашего курсора по ячейкам, нажатие клавиш. Сначала Вы записываете макрос. Когда понадобится, Вы в Excel запускаете этот код на выполнение и все записанные ранее действия выполняются автоматически. Например, у Вас есть одна и та же таблица, макет которой Вы каждую неделю создаете по новый, меняете оформление, или очищаете графы. Вы нажимаете кнопку, и вот, то на что вы тратили 5-10 минут заняло у вас всего одну секунду.
Сначала настроим «Ленту»; нужно включить «Режим разработчика»:
Если Вы собираетесь серьезно работать с макросами — нужно включить еще одну настройку. Здесь же переходим в «Центр управления безопасностью» и настраиваем «Параметры центра управления безопасностью»:
Нужно отключить все ограничения на запуск макросов. Это позволит Вам не только работать со своими макросами но и запускать уже готовые кем — то созданные.
Так же нужно доверять доступ к объектной модели проектов VBA. Ведь в Интернете или у коллег можно найти много готовых и интересных проектов VBA написанных для Excel, Word и пользоваться.
Что такое макрос в Excel 2007?
Мы пока что научимся записывать несложные макросы. Сделать это очень просто. В «Ленте» у нас теперь появились соответствующие значки «Разработчик»:
В этом режиме нам будут доступны и остальные функции — «режим конструктора». Можно самостоятельно создавать окна, кнопки. А в редакторе Visual Basic можно редактировать созданные макросы. Итак, покажу, как все это работает. При нажатии кнопки «Запись макроса» сначала нужно будет заполнить его наименование(без пробелов), горячие клавиши вызова (не обязательно) и место где он будет сохранен:
В зависимости от поставленной задачи Вы можете сохранять макросы в отдельном документе либо в «личной книге». В первом случае он записывается в пределах документа и будет выполняться только в документе. Во втором случае его можно вызвать перед созданием документа. Лучше продемонстрировать наглядно, для чего бывают нужны макросы. Задача: мне нужно создать: документ Excel, в документе создать таблицу определенного формата и вычислениями внутри. Смотрим видео, что получилось:
Вы сами увидели, что процесс создания одной простенькой демо — таблицы занимает от пяти и более минут. А макрокомандой мы таблицу создали за 2 секунды. А если речь идет о сложных проектах? Ответ очевиден, создав один раз процедуру, вы сэкономите время и увеличите производительность своей работы.
После окончания записи при закрытии Excel программа обязательно попросит сохранить изменения в «Личной книге» макросов. Не забываем согласиться; в противном случае записанный макрос пропадет и всё придется делать заново.
Важно! «Личная книга» макросов хранится в папке пользователя по пути C:\Users\%Пользователь компьютера%\AppData\Roaming\Microsoft\Excel\XLSTART. Этой книгой можно делится с другими или удалять, если что то не получилось.
Наш макрос теперь доступен по кнопке «Макросы», оттуда его можно запускать.
Что значит макрос в Excel 2016 и для чего он предназначен?
Процесс записи макросов не отличается от ранних версий; еще в одном простом примере расскажу о другом способе вызова макрокоманд. Создание макросов позволит Вам познакомится с навыками программирования и кто знает, может это интересное дело Вас увлечёт.
Представим, что у нас есть довольно громоздкая таблица, которую каждый раз приходится очищать от данных предыдущего отчета. Задача: сделать в документе кнопку, при нажатии на которую автоматически очищаются нужные ячейки. Начнем.
Для начала не забудем сделать настройки MSoffice 2016. Так же идем в «Параметры», но далее идем в «Настроить ленту»
После «Разработчик» станет видимым на «Ленте». Переходим туда. Наша личная книга макросов по прежнему на месте, откроем ее:
Запустим наш предыдущий макрос, любуемся, как программа сама создает и заполняет таблицу. В нашем примере есть две графы «Количество» и «Цена», содержимое которых приходится очищать. Мы автоматизируем процесс.
Как включить макросы в Excel 2016 видео
Можно вызывать макросы на исполнение, так как это мы уже делали в прошлом примере. Это не наглядно и иногда не очень удобно. Поэтому мы сделаем иначе — создадим в документе кнопку, с помощью которой будем очищать содержимое.
Нашей кнопке будет автоматически назначен макрос. Имя макроса, комбинацию клавиш, ставим произвольное, сохраняем в «Этой книге». Это означает, что работать кнопка будет только в этом документе.
Далее, программа предложит способ создания макроса. Если Вы уже продвинутый VBA- програмиист, можно руками написать код, нажав «Создать». Но мы сегодня будем использовать уже знакомый нам способ — запись наших действий:
Запись начата! Мы собираемся очистить выбранные графы от содержимого, поэтому одновременно выделим нужные диапазоны в графах «Количество» и «Цена». Для этого сначала выделим графу «Количество», затем зажимаем клавишу Ctrl и не отпуская ее выделяем нужные ячейки в графе «Цена», после отпускаем клавишу:
Использование нажатой клавиши Ctrl при выделении помогает выделять столбцы и области различных диапазонов ячеек одновременно. Не пренебрегайте этим в случае с большими таблицами.
Теперь, когда нужный диапазон ячеек выбран, нажимаем на клавиатуре клавишу Del и этим удаляем содержимое ячеек. Останавливаем запись макроса:
Как и любой объект с кнопкой можно производить различные действия — копировать, удалять, форматировать, назначать им макросы. Мы пока что изменим наименование. Чтобы добраться до свойств кнопки нужно по ней щелкнуть правой кнопкой мыши:
Я изменил текст на «Очистить». Цвет, размер текста шрифт в кнопках так же можно менять по вкусу. Заглянем для этого в «Формат объекта»:
Там все примерно так, как при форматировании ячейки. А изменять размеры самой кнопки и ее местоположение можно растягивая ее за края или перетаскивать:
Другой способ — можно щелкнуть правой кнопкой мыши по кнопке и выбрать «Назначить макрос»; результат будет аналогичный. Открылся редактор VBA, в котором можно делать много чего интересного и полезного. Мы пока смотрим, что мы там напрограммировали:
Простой и понятный пример кода; после комментариев (выделенных зеленым) идет сам код «Выбрать диапазон ячеек сначала один, затем другой, затем удаляем содержимое». Закрыть редактор можно как обычное окошко — нажав на крестик. Надеюсь, что такое макросы — Вы разобрались. Заодно узнали, как можно стать программистом. Удачи в освоении программы!
