Для чего нужны макросы в excel

Для чего нужны макросы в Excel и как их сделать: инструкция со скриншотами

Как с помощью макросов автоматизировать рутинные задачи в Excel? Какие команды они выполняют? Как создать макрос новичку? Разбираемся на примере.

Иллюстрация: Meery Mary для Skillbox Media

Ксеня Шестак

Макрос (или макрокоманда) в Excel — алгоритм действий в программе, который объединён в одну команду. С помощью макроса можно выполнить несколько шагов в Excel, нажав на одну кнопку в меню или на сочетание клавиш.

Обычно макросы используют для автоматизации рутинной работы — вместо того чтобы выполнять десяток повторяющихся действий, пользователь записывает одну команду и затем запускает её, когда нужно совершить эти действия снова.

Например, если нужно добавить название компании в несколько десятков документов и отформатировать его вид под корпоративный дизайн, можно делать это в каждом документе отдельно, а можно записать ход действий при создании первого документа в макрос — и затем применить его ко всем остальным. Второй вариант будет гораздо проще и быстрее.

В статье разберёмся:

какие способы создания макросов существуют и как подготовиться к их записи; и запустить макрос начинающим пользователям — на примере со скриншотами.

Как работают макросы в Excel и какие задачи они решают

Общий принцип работы макросов такой:

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

Макросы могут выполнять любые действия, которые в них запишет пользователь. Вот некоторые команды, которые они умеют делать в Excel:

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

Способы создания макросов в Excel

В Excel и других программах Microsoft Office макросы создаются в виде кода на языке программирования VBA (Visual Basic for Applications). Этот язык разработан в Microsoft специально для программ компании — он представляет собой упрощённую версию языка Visual Basic. Но это не значит, что для записи макроса нужно уметь кодить.

Есть два способа создания макроса в Excel:

Разберёмся на примере, как создать макрос с помощью второго способа.

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

Пользоваться таким отчётом неудобно — нужно сделать его наглядным. Запишем макрос при форматировании таблицы с продажами за январь и затем применим его к двум другим таблицам.

Шаг 1

Готовимся к записи макроса

Кнопки для работы с макросами в Excel находятся во вкладке «Разработчик». Эта вкладка по умолчанию скрыта, поэтому для начала разблокируем её.

В операционной системе Windows это делается так: переходим во вкладку «Файл» и выбираем пункты «Параметры» → «Настройка ленты». В открывшемся окне в разделе «Основные вкладки» находим пункт «Разработчик», отмечаем его галочкой и нажимаем кнопку «ОК» → в основном меню Excel появляется новая вкладка «Разработчик».

В операционной системе macOS это нужно делать по-другому. В самом верхнем меню нажимаем на вкладку «Excel» и выбираем пункт «Параметры…».

В появившемся окне нажимаем кнопку «Лента и панель».

Затем в правой панели «Настроить ленту» ищем пункт «Разработчик» и отмечаем его галочкой. Нажимаем «Сохранить».

Готово — вкладка «Разработчик» появилась на основной панели Excel.

Чтобы Excel смог сохранить и в дальнейшем использовать макрос, нужно пересохранить документ в формате, который поддерживает макросы. Это делается через команду «Сохранить как» на главной панели. В появившемся меню нужно выбрать формат «Книга Excel с поддержкой макросов».

Шаг 2

Записываем макрос

Перед началом записи макроса важно знать об особенностях его работы:

Для начала записи макроса перейдём на вкладку «Разработчик» и нажмём кнопку «Записать макрос».

Появляется окно для заполнения параметров макроса. Нужно заполнить поля: «Имя макроса», «Сохранить в», «Сочетание клавиш», «Описание».

«Имя макроса» — здесь нужно придумать и ввести название для макроса. Лучше сделать его логически понятным, чтобы в дальнейшем можно было быстро его найти.

Первым символом в названии обязательно должна быть буква. Другие символы могут быть буквами или цифрами. Важно не использовать пробелы в названии — их можно заменить символом подчёркивания.

«Сохранить в» — здесь нужно выбрать книгу, в которую макрос сохранится после записи.

Если выбрать параметр «Эта книга», макрос будет доступен при работе только в этом файле Excel. Чтобы макрос был доступен всегда, нужно выбрать параметр «Личная книга макросов» — Excel создаст личную книгу макросов и сохранит новый макрос в неё.

«Сочетание клавиш» — здесь к уже выбранным двум клавишам (Ctrl + Shift в системе Windows и Option + Cmd в системе macOS) нужно добавить третью клавишу. Это должна быть строчная или прописная буква, которую ещё не используют в других быстрых командах компьютера или программы Excel.

В дальнейшем при нажатии этих трёх клавиш записанный макрос будет запускаться автоматически.

«Описание» — необязательное поле, но лучше его заполнять. Например, можно ввести туда последовательность действий, которые планируется записать в этом макросе. Так не придётся вспоминать, какие именно команды выполнит этот макрос, если нужно будет запустить его позже. Плюс будет проще ориентироваться среди других макросов.

В нашем случае с форматированием таблицы заполним поля записи макроса следующим образом и нажмём «ОК».

После этого начнётся запись макроса — в нижнем левом углу окна Excel появится значок записи.

Пока идёт запись, форматируем таблицу с продажами за январь: меняем ширину всех столбцов, данные во всех ячейках располагаем по центру, выделяем шапку таблицы цветом и жирным шрифтом, рисуем границы.

Важно: в нашем случае у таблиц продаж за январь, февраль и март одинаковое количество столбцов, но разное количество строк. Чтобы в случае со второй и третьей таблицей макрос сработал корректно, при форматировании выделим диапазон так, чтобы в него попали не только строки самой таблицы, но и строки ниже неё. Для этого нужно выделить столбцы в строке с их буквенным обозначением A–G, как на рисунке ниже.

Если выбрать диапазон только в рамках первой таблицы, то после запуска макроса в таблице с большим количеством строк она отформатируется только частично.

После всех манипуляций с оформлением таблица примет такой вид:

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

Нажать на кнопку записи в нижнем левом углу.
Перейти во вкладку «Разработчик» и нажать кнопку «Остановить запись».

Готово — мы создали макрос для форматирования таблиц в границах столбцов A–G. Теперь его можно применить к другим таблицам.

Шаг 3

Запускаем макрос

Перейдём в лист со второй таблицей «Февраль_2022». В первоначальном виде она такая же нечитаемая, как и первая таблица до форматирования.

Отформатируем её с помощью записанного макроса. Запустить макрос можно двумя способами:

Нажать комбинацию клавиш, которую выбрали при заполнении параметров макроса — в нашем случае Option + Cmd + Ф.
Перейти во вкладку «Разработчик» и нажать кнопку «Макросы».

Появляется окно — там выбираем макрос, который нужно запустить. В нашем случае он один — «Форматирование_таблицы». Под ним отображается описание того, какие действия он включает. Нажимаем «Выполнить».

Готово — вторая таблица с помощью макроса форматируется так же, как и первая.

То же самое можно сделать и на третьем листе для таблицы продаж за март. Более того, этот же макрос можно будет запустить и в следующем квартале, когда сервис автосалона выгрузит таблицы с новыми данными.

Работа с макросами Excel для чайников

Многие люди боятся использовать макросы в Excel, потому что считают их довольно сложными для понимания. Все потому, что они связаны с программированием. Но на практике все оказывается значительно проще, чем может показаться на первый взгляд. В программировании важно научиться строить алгоритмы. И если человек умеет кодить хотя бы на одном языке, ему значительно проще усвоить все остальные.

Мы приведем некоторые примеры макросов Excel, которые станут верными помощниками в выполнении наиболее частых задач.

Понятие макроса

Термин «Макрос» слышало множество людей. Нередко при запуске таблицы появляется предупреждение: “Этот документ использует макросы, способные навредить данному компьютеру, поэтому они отключены с целью защиты от вредоносных действий”.

Макрос – действенный способ автоматизировать самые частые действия, которые нужно выполнять в электронных таблицах. Макросы – это разновидность программирования. Разработка этих подпрограмм осуществляется с помощью языка VBA. Впрочем, некоторые виды макросов не требуют навыков программирования. Ведь существует еще такое понятие, как макрорекордер. Достаточно его включить и совершить некоторые действия, как далее они будут повторяться по нажатию одной кнопки.

Макросы могут быть реально опасными. Поскольку при их написании используется язык программирования, с его помощью можно создать настоящий вирус, который способен повредить информацию, а также собирать данные для злоумышленников (особенно опасно, если в таблице есть банковские данные, пароли и так далее).

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

Значительно проще объяснить, зачем нужны макросы, на реальном примере. Например, необходимо каждый день удалять из электронной таблицы несколько столбцов, а потом добавлять новые строки. Это невероятно утомительное занятие, отнимающее много времени. Если же воспользоваться макросами, есть реальная возможность значительно его сэкономить.

Макросы можно запускать по нажатию определенной комбинации клавиш. Например, если нажать Ctrl+J, можно запустить подпрограмму.

Интересный факт: известная программа бухгалтерского учета 1C изначально очень напоминала Excel, но потом ее функционал расширился до текущего.

Если же нужно давать компьютеру сложные инструкции, можно воспользоваться редактором Visual Basic, примеры кода в котором мы и рассмотрим немного позже.

Когда какой тип записи макросов использовать?

Если необходимо автоматизировать простейшие действия, достаточно использовать встроенный инструмент для записи макросов. То есть, если не приходится прописывать никаких условий, переменных и других подобных вещей. Просто обычная последовательность действий.

Если же необходимо программировать сложные действия, то тогда придется пользоваться встроенной средой VBA. Например, если необходимо записать в массив все элементы какого-то диапазона значений, определить его длительность, и при условии, что количество элементов массива не превышает определенного числа, выдавать какое-то сообщение. Здесь стандартного инструмента для записи макросов окажется недостаточно, необходимо изучать язык программирования и записывать команды в специальной среде. А интерпретатор в дальнейшем будет выполнять написанный код.

Пример использования макросов №1

Сперва этот пример кода использовался для демонстрации комментариев кода, написанного на VBA. Но поскольку он включает и иные возможности языка, он может применяться для демонстрации следующих функций:

Объявление переменных.
Указание ссылок на ячейки Excel.
Применение цикла типа For.
Применение условного оператора.
Отображение оповещения.

‘ Подпрограмма для поиска ячеек с адресами A1-A100 текущего активного листа

‘ и поиска ячеек, в которых содержится требуемая строка

Sub Find_String(sFindText As String)

Dim i As Integer ‘ Целочисленная переменная, которая используется в цикле типа «For»

Dim iRowNumber As Integer ‘ Целочисленная переменная, предназначенная для сохранения результата

iRowNumber = 0

‘ Цикл через ячейки A1-A100 до тех пор, пока не будет найдена строка ‘sFindText’

For i = 1 To 100

If Cells(i, 1).Value = sFindText Then

‘ Совпадение обнаружено для заданной строки

‘ Сохранение текущего номера строки и выход из цикла

iRowNumber = i

‘ Всплывающее сообщение, информирующее пользователя о найденной строке и ее номере

If iRowNumber = 0 Then

MsgBox «String » & sFindText & » not found»

MsgBox «String » & sFindText & » found in cell A» & iRowNumber

Пример 2

Эта процедура перечисляет все значения числовой последовательности Фибоначчи, вплоть до 1000. В этом примере приводятся следующие возможности макросов Excel:

Объявление переменных.
Цикл Do While.
Ссылки на ячейки текущего листа Excel.
Условный оператор.

‘ Подпрограмма для перечисления всех значений последовательности Fibonacci для всех значений ниже тысячи

Sub Fibonacci()

Dim i As Integer ‘ счетчик для позиции в серии значений

Dim iFib As Integer ‘ сохраняет текущее значение в серии

Dim iFib_Next As Integer ‘ сохраняет следующее значение в серии

Dim iStep As Integer ‘ хранит размер следующего шага

‘ Инициализация переменных variables i и iFib_Next

iFib_Next = 0

‘ Цикл Do While, который исполняется до тех пор, пока номер

‘ числа в последовательности Фибоначчи меньше 1000.

Do While iFib_Next < 1000

If i = 1 Then

‘ Специальный случай для первой записи в серии

‘ Сохраняется следующий размер шага, перед перезаписью

‘ текущей записи в серии

iStep = iFib

iFib = iFib_Next

‘ Печать текущего значения последовательности Фибоначчи для столбца А

‘ текущего листа

Cells(i, 1).Value = iFib

‘ Вычисление следующего значения последовательности и увеличение

‘ маркера позиции на 1

iFib_Next = iFib + iStep

Пример 3

Следующий пример подпрограммы читает значения с ячейки в колонке A активного листа, пока не найдет пустую ячейку. Вся полученная информация сохраняется в массиве. Это простой пример макросов в электронных таблицах, который показывает:

Как объявлять переменные.
Работу динамического массива.
Цикл Do Until.
Ссылки на ячейки в текущем листе Excel.
Встроенную функцию Ubound, которая предназначена для определения размера массива.

‘ Подпрограмма, которая хранит значения колонки А текущего листа

Sub GetCellValues()

Dim iRow As Integer ‘ сохраняется текущий номер строки

Dim dCellValues() As Double ‘ массив, в котором хранятся значения ячеек

ReDim dCellValues(1 To 10)

‘ Цикл Do Until, который извлекает значение каждой ячейки в столбце А

‘ активного листа до тех пор, пока ячейка не окажется пустой

Do Until IsEmpty(Cells(iRow, 1))

‘ Проверка, достаточно ли большой массив dCellValues

‘ Если нет, используется ReDim, чтобы увеличить размер массива на 10 элементов.

If UBound(dCellValues) < iRow Then

ReDim Preserve dCellValues(1 To iRow + 9)

‘ Сохраняется текущая ячейка в массиве CellValues

dCellValues(iRow) = Cells(iRow, 1).Value

iRow = iRow + 1

Пример 4

Следующая процедура «Sub» читает содержимое ячеек из колонки А другого листа, имеющего название «Sheet2» и с этими значениями осуществляет арифметические операции. Результат вычислений пишется в колонке А текущего листа.

Этот пример показывает:

Как объявлять переменные.
Объекты Excel.
Цикл Do Until.
Доступ к листам электронных таблиц и диапазонам ячеек с текущей книги.

‘ Подпрограмма, запускающая цикл через значения в колонке А текущего листа

‘ «Sheet2», perform arithmetic operations on each value, and write the

‘ result into Column A of the current Active Worksheet («Sheet1»)

Sub Transfer_ColA()

Dim i As Integer

Dim Col As Range

Dim dVal As Double

‘ Установить переменную Col в колонку А листа 2

‘ Прохождение цикла через каждую ячейку колонки ‘Col’ до тех пор, пока

‘ не будет обнаружена пустая ячейка

Do Until IsEmpty(Col.Cells(i))

‘ Применение арифметических операций к значению текущей ячейки

dVal = Col.Cells(i).Value * 3 — 1

‘ Команда ниже копирует результат в колонку А

‘ текущего активного листа — без уточнения названия активного листа

Cells(i, 1) = dVal

Пример 5

Этот пример макроса приводит пример кода VBA, связанного с событием. Каждый раз, когда человек выделяет ячейку или диапазон значений, связанное с макросом событие активируется.

‘ Код для отображения диалогового окна ячейки B1 текущего листа в случае, если она выбрана

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘ Проверка, выбрана ли ячейка B1

If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then

‘ При выборе ячейки B1 показать диалоговое окно

MsgBox «You have selected cell B1»

Пример 6

Следующая подпрограмма демонстрирует, как обрабатывать ошибки с помощью операторов OnError и Resume. Также в этом коде описывается, как открывать и читать данные с файла.

‘ Подпрограмма, для для установки определенных значений

‘ в ячейках A1 и B1 документа «Data.xls» на диске C:\

Sub Set_Values(Val1 As Double, Val2 As Double)

Dim DataWorkbook As Workbook

On Error GoTo ErrorHandling

‘ Открытие документа с данными

Set DataWorkbook = Workbooks.Open(«C:\Documents and Settings\Data»)

‘ Выбрать переменные Val1 and Val2 с данных в книге Excel

Val1 = Sheets(«Sheet1»).Cells(1, 1)

Val2 = Sheets(«Sheet1»).Cells(1, 2)

DataWorkbook.Close

ErrorHandling:

‘ Если файл не найден, предложить пользователю найти правильную директорию

‘ после чего продолжить выполнение подпрограммы

MsgBox «Data Workbook not found;» & _

«Please add the workbook to C:\Documents and Settings and click OK»

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

Выводы

Таким образом, макросы – это эффективный инструмент автоматизации рабочих процессов в Excel. Он позволяет автоматизировать даже самые сложные последовательности действий. Если необходимо сделать простую программку, то достаточно воспользоваться встроенной функцией для записи макросов. Для более сложных необходимо освоить язык VBA, который является простым для изучения и очень гибким.

Если используются сторонние макросы, обязательно нужно побеспокоиться о безопасности их использования.

Что такое макрос в Excel и для чего он предназначен?

Доброго времени, друзья. В сегодняшнем выпуске расскажу об интереснейшей функции в самом ходовом офисном пакете программ от Microsoft Office. Тем, кто постоянно работает с таблицами Excel, знает сколько рутинных операций приходится иногда делать с содержимым.

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

Чтобы не повторять одни и те же действия каждый раз можно автоматизировать процесс и сэкономить время. Вы записываете свои действия (выделение ячеек документа, копирование, форматирование, ввод формул) в макрокоманду.

Команда эта помещается в виде кнопки на панель инструментов. При нажатии кнопки все ваши записанные ранее действия выполняются автоматически. Это и есть «макрос».

Лично для меня знакомство с макросами в свое время превратилось в изучение программирования. Дело в том, что Microsoft Office является еще и легкой и понятной средой для этого.

В нем есть свой встроенный язык — Visual Basic for Application или сокращенно VBA. И когда записываете макросы, Вы вы как бы становитесь программистом. При этом не обязательно на первых этапах знать язык. Программный код можно посмотреть уже потом, в редакторе и изучать. Как смотреть — об этом так же расскажу.

VBA позволяет создавать для себя мощные инструменты при работе с табличными документами для решения многих задач в виде отдельных окон, кнопок, списков, флажков — все визуально. А опытные программисты знают, что макросы можно использовать для того чтобы не набирать команды руками, а достать уже готовый код из только что записанного макроса.

Но обо всем по порядку. Тем, кто после прочтения статьи заинтересуется и захочет изучать язык VBA самостоятельно в помощь книга. А сейчас разберемся подробно, что за «макросы», как их включать.

Что означает макросы и как с ними работать?

Итак, макрос — это некий программный код, который описывает ПОСЛЕДОВАТЕЛЬНОСТЬ ваших действий в документе. Записывается даже перемещение вашего курсора по ячейкам, нажатие клавиш. Сначала Вы записываете макрос.

Когда понадобится, Вы в Excel запускаете этот код на выполнение и все записанные ранее действия выполняются автоматически. Например, у Вас есть одна и та же таблица, макет которой Вы каждую неделю создаете по новый, меняете оформление, или очищаете графы. Вы нажимаете кнопку, и вот, то на что вы тратили 5-10 минут заняло у вас всего одну секунду.

Для начала нужно настроить Excel для работы с макросами. Это просто. На примере Office 2007 покажу как . В более поздних редакциях делается все почти аналогично. Сначала заходим в «Параметры»:

Сначала настроим «Ленту»; нужно включить «Режим разработчика»:

Если Вы собираетесь серьезно работать с макросами — нужно включить еще одну настройку. Здесь же переходим в «Центр управления безопасностью» и настраиваем «Параметры центра управления безопасностью»:

Нужно отключить все ограничения на запуск макросов. Это позволит Вам не только работать со своими макросами но и запускать уже готовые кем — то созданные.

Так же нужно доверять доступ к объектной модели проектов VBA. Ведь в Интернете или у коллег можно найти много готовых и интересных проектов VBA написанных для Excel, Word и пользоваться.

Что такое макрос в Excel 2007?

Мы пока что научимся записывать несложные макросы. Сделать это очень просто. В «Ленте» у нас теперь появились соответствующие значки «Разработчик»:

В этом режиме нам будут доступны и остальные функции — «режим конструктора». Можно самостоятельно создавать окна, кнопки. А в редакторе Visual Basic можно редактировать созданные макросы. Итак, покажу, как все это работает. При нажатии кнопки «Запись макроса» сначала нужно будет заполнить его наименование(без пробелов), горячие клавиши вызова (не обязательно) и место где он будет сохранен:

В зависимости от поставленной задачи Вы можете сохранять макросы в отдельном документе либо в «личной книге». В первом случае он записывается в пределах документа и будет выполняться только в документе.

Во втором случае его можно вызвать перед созданием документа. Лучше продемонстрировать наглядно, для чего бывают нужны макросы. Задача: мне нужно создать: документ Excel, в документе создать таблицу определенного формата и вычислениями внутри. Смотрим видео, что получилось:

Видео что такое макрос Excel и как пользоваться?

Вы сами увидели, что процесс создания одной простенькой демо — таблицы занимает от пяти и более минут. А макрокомандой мы таблицу создали за 2 секунды. А если речь идет о сложных проектах? Ответ очевиден, создав один раз процедуру, вы сэкономите время и увеличите производительность своей работы.

После окончания записи при закрытии Excel программа обязательно попросит сохранить изменения в «Личной книге» макросов. Не забываем согласиться; в противном случае записанный макрос пропадет и всё придется делать заново.

Важно! «Личная книга» макросов хранится в папке пользователя по пути C:\Users\%Пользователь компьютера%\AppData\Roaming\Microsoft\Excel\XLSTART. Этой книгой можно делится с другими или удалять, если что то не получилось.

Наш макрос теперь доступен по кнопке «Макросы», оттуда его можно запускать.

Что значит макрос в Excel 2016 и для чего он предназначен?

Процесс записи макросов не отличается от ранних версий; еще в одном простом примере расскажу о другом способе вызова макрокоманд. Создание макросов позволит Вам познакомится с навыками программирования и кто знает, может это интересное дело Вас увлечёт.

Представим, что у нас есть довольно громоздкая таблица, которую каждый раз приходится очищать от данных предыдущего отчета. Задача: сделать в документе кнопку, при нажатии на которую автоматически очищаются нужные ячейки. Начнем.

Для начала не забудем сделать настройки MSoffice 2016. Так же идем в «Параметры», но далее идем в «Настроить ленту»

После «Разработчик» станет видимым на «Ленте». Переходим туда. Наша личная книга макросов по прежнему на месте, откроем ее:

Запустим наш предыдущий макрос, любуемся, как программа сама создает и заполняет таблицу. В нашем примере есть две графы «Количество» и «Цена», содержимое которых приходится очищать. Мы автоматизируем процесс.

Как включить макросы в Excel 2016 видео

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

Начинается программирование �� , из меню «Разработчик» вставляем кнопку в любое место нашего документа. Курсор измениться на перекрестье, затем рисуем кнопку в нужном месте:

Нашей кнопке будет автоматически назначен макрос. Имя макроса, комбинацию клавиш, ставим произвольное, сохраняем в «Этой книге». Это означает, что работать кнопка будет только в этом документе.

Далее, программа предложит способ создания макроса. Если Вы уже продвинутый VBA- програмиист, можно руками написать код, нажав «Создать». Но мы сегодня будем использовать уже знакомый нам способ — запись наших действий:

Запись начата! Мы собираемся очистить выбранные графы от содержимого, поэтому одновременно выделим нужные диапазоны в графах «Количество» и «Цена». Для этого сначала выделим графу «Количество», затем зажимаем клавишу Ctrl и не отпуская ее выделяем нужные ячейки в графе «Цена», после отпускаем клавишу:

Использование нажатой клавиши Ctrl при выделении помогает выделять столбцы и области различных диапазонов ячеек одновременно. Не пренебрегайте этим в случае с большими таблицами.

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

Как и любой объект с кнопкой можно производить различные действия — копировать, удалять, форматировать, назначать им макросы. Мы пока что изменим наименование. Чтобы добраться до свойств кнопки нужно по ней щелкнуть правой кнопкой мыши:

Я изменил текст на «Очистить». Цвет, размер текста шрифт в кнопках так же можно менять по вкусу. Заглянем для этого в «Формат объекта»:

Там все примерно так, как при форматировании ячейки. А изменять размеры самой кнопки и ее местоположение можно растягивая ее за края или перетаскивать:

Ну и на последок давайте заглянем в редактор VBA и посмотрим, как выглядит написанный нами программный код нашего макроса. Для этого идем в наши «Макросы» , выбираем нужный и жмем «Войти» или «Изменить»:

Другой способ — можно щелкнуть правой кнопкой мыши по кнопке и выбрать «Назначить макрос»; результат будет аналогичный. Открылся редактор VBA, в котором можно делать много чего интересного и полезного. Мы пока смотрим, что мы там напрограммировали:

Простой и понятный пример кода; после комментариев (выделенных зеленым) идет сам код «Выбрать диапазон ячеек сначала один, затем другой, затем удаляем содержимое». Закрыть редактор можно как обычное окошко — нажав на крестик. Надеюсь, что такое макросы — Вы разобрались. Заодно узнали, как можно стать программистом. Удачи в освоении программы!

Автоматизация рутины в 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 в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

‘ Процедура. Ничего не возвращает
‘ Перегрузка в VBA отсутствует
Sub foo(a As String , b As String )
‘ Exit Sub ‘ Это значит «выйти из процедуры»
MsgBox a + «;» + b
End Sub

‘ Функция. Вовращает Integer
Function LengthSqr(x As Integer , y As Integer ) As Integer
‘ Exit Function
LengthSqr = x * x + y * y
End Function

Sub FormatPrice()
Dim s1 As String , s2 As String
s1 = «str1»
s2 = «str2»
If s1 <> s2 Then
foo «123» , «456» ‘ Скобки при вызове процедур запрещены
End If

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 Resume Next ‘ Обработка ошибок — игнорировать все ошибки
x = 5 / 0
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

‘ Двухмерный массив.
‘ Можно также менять размеры командой ReDim (Preserve) — см. google
Dim arr(1 to 10, 5 to 6) As Integer
arr(1, 6) = 8

Dim coll As New Collection
Dim coll2 As Collection
coll.Add «item» , «key»
Set coll2 = coll ‘ Все присваивания объектов должны производится командой Set
MsgBox coll2( «key» )
Set coll2 = New Collection
MsgBox coll2.Count
End Sub

Грабли-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, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

Считали группы из очередной строки.
Пробегаемся по всем группам в порядке приоритета (вначале более крупные)
1. Если текущая группа не совпадает, вызываем процедуру AddGroup(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие.
Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function

Function GetCellS(Sheet As String , Col As Integer , Row As Integer ) As Range
Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row))
End Function

Function GetCell(Col As Integer , Row As Integer ) As Range
Set GetCell = Range(GetCol(Col) + CStr(Row))
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

Sheets( «data» ).Activate
I = 2
Do While True
If GetCell(0, I).Value = «» Then Exit Do
‘ .
I = I + 1
Loop
End Sub

Теперь надо заполнить массив Groups:

На месте многоточия

Dim I2 As Integer
For I2 = 1 To GroupsCount
Groups(I2) = GetCell(I2, I)
Next I2
‘ .
For I2 = 1 To GroupsCount ‘ VB не умеет копировать массивы
PrGroups(I2) = Groups(I2)
Next I2
I = I + 1

И создать заголовки:

На месте многоточия в предыдущем куске

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

Sub AddHeader(Ty As Integer , Name As String )
GetCellS( «result» , 1, CurRow).Value = Name
CurRow = CurRow + 1
End Sub

Теперь надо перенести всякую информацию в result

For I2 = 0 To DataCount — 1
GetCellS( «result» , I2, CurRow).Value = GetCell(I2, I)
Next I2

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets( «Result» ).Activate
Columns.AutoFit

Всё. Можно любоваться первой версией.

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Sub AddHeader(Ty As Integer , Name As String )
Sheets( «result» ).Range( «A» + CStr(CurRow) + «:C» + CStr(CurRow)).Merge
‘ Чтобы не заводить переменную и не писать каждый раз длинный вызов
‘ можно воспользоваться блоком With
With GetCellS( «result» , 0, CurRow)
.Value = Name
.Font.Italic = True
.Font.Name = «Cambria»
Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
Case 2 ‘ Производитель
.Font.Size = 12
End Select
.HorizontalAlignment = xlCenter
End With
CurRow = CurRow + 1
End Sub

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Sub AddHeader(Ty As Integer , Name As String )
With Sheets( «result» ).Range( «A» + CStr(CurRow) + «:C» + CStr(CurRow))
.Merge
.Value = Name
.Font.Italic = True
.Font.Name = «Cambria»
.HorizontalAlignment = xlCenter

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 = 0~~ CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. ~~Если кто-нибудь подскажет человеческих файлохостинг, залью туда.~~

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.
Похожие публикации:

Для чего нужны макросы в excel