Написать макрос в excel для новичков чайников

Введение

Зачастую нам приходится выполнять одни и те же действия в Excel большое количество раз. C такой «рутинной работой» рано или поздно сталкивается почти каждый пользователь Excel — формирование ежедневных и еженедельных отчетов, обработка однообразных данных и т.д.

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

Макрос в Excel — это программа, записанная на языке программирования Visual Basic for Applications (VBA), которая выполняет определенную последовательность действий.

Синтаксис макроса

Макросы — это команды, написанные на языке VBA (Visual Basic for Applications). И синтаксис кода макроса не отличается от записи кода в Visual Basic.

Любой макрос имеет следующий вид:

Sub Имя_Макроса_Без_Пробелов()

‘ комментарии к макросу — они нужны для вас, VBA не воспринимает такие строки как команды

команды, написанные на языке VBA

End Sub

3 обязательных блока макроса:

1. Начало макроса. Всегда начинается с команды Sub. Далее идет имя макроса — оно может быть на русском языке, но не должно содержать пробелы и специальные символы.

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

2. Блок команд. В нашем примере он состоит из одной строки: Selection.NumberFormat = “#,##0”

Каждая команда должна начинаться с новой строки. Если текст команды очень длинный и не помещается на экране, его можно разбить на несколько строк, заканчивая строку символом нижнего подчеркивания _ (далее в примере мы это увидим).

3. Конец макроса. Всегда обозначается как End Sub.

Есть и один необязательный блок — это комментарии, которые вы можете оставлять в любом месте внутри кода макроса, поставив перед началом комментариев знак апострофа ‘. Например, вы можете описать, что именно делает тот или иной макрос.

Обратите внимание!

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

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

Например, информационная система выдает отчет «Бюджет на месяц» без выделения групповых значений цветом или шрифтом.

Нам необходимо:

• выделить групповые строки полужирным шрифтом;
• отформатировать на печать — расположить отчет по центру листа, задать масштаб 75 %, вывести в колонтитулы название отчета (рис. 4).

Рис. 4. Изменения после написания макроса

Запишем алгоритм форматирования отчета в макрос.

Нажимаем кнопку записи макроса и выполняем следующие действия:

• Даем макросу имя Форматирование_БДР, в блоке описания записываем, что будет делать этот макрос (например, Выделяет жирным курсивом итоги, форматирует на печать). Жмем Ок.
• Выделяем столбцы А:С, ставим автофильтр — на закладке Данные находим кнопку Фильтр.
• По столбцу КОД задаем условие не содержит точку: Текстовые фильтры – Не содержит и в поле текста ставим символ точки без пробелов (рис. 5).

Рис. 5. Использование автофильтра по столбцу «КОД»

• Выделяем отфильтрованный диапазон и задаем ему полужирный шрифт.
• Снимаем автофильтр (повторное нажатие на закладке Данные кнопки Фильтр).
• Заходим в меню форматирования на печать (Кнопка Файл/Office – Печать – Предварительный просмотр – Параметры страницы) и задаем там три параметра:

1) на вкладке Страница задаем масштаб 75 %;

2) на вкладке Поля отмечаем пункт Горизонтально в блоке Центрировать на странице>;

3) на вкладке Колонтитулы создаем верхний колонтитул с текстом Бюджет на январь.

• Выходим из параметров страницы.
• Заканчиваем запись макроса.
• Нажимаем Alt+F11 и смотрим, что получилось (см. рис. 4).

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

Работа с макросами

Если вы не уверены в источнике, с которого скачали файл, то лучше не использовать макросы, так как они могут заразить ПК вирусным ПО. Именно по этой причине разработчики утилиты предусмотрели возможность включения и отключения макросов. Ниже будет подробно рассматриваться работа с этими программными алгоритмами.

Включение/выключение макросов с помощью меню разработчика

В этой статье преимущественно будет рассматриваться работа с автоматическими алгоритмами в Excel 2010, так как на сегодня именно эта версия является самой популярной у пользователей.

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

1. Зайдите в блок «Файл» и откройте раздел «Параметры».

В боковом меню нажмите на строку «Настройки ленты», после чего в правой области окна поставьте флажок напротив пункта «Разработчик».
Для сохранения внесенных изменений кликните «ОК».

В верхнем меню у вас должен будет появиться новый блок «Разработчик». Перейдите в него и кликните по иконке с изображением восклицательного знака в желтом треугольнике.
Перед вами отобразится центр управления безопасностью. Перейдите в раздел «Параметры макросов» и поставьте флажок напротив пункта «Включить все макросы…». Обратите внимание, вы активируете опцию на свой страх и риск.

Кликните «ОК», для того чтобы сохранить настройки.

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

Работа с макросами через настройки софта

Разработчики утилиты предусмотрели еще один метод работы с макросами через параметры программы. Для того чтобы изменить настройки алгоритмов, проделайте такие шаги:

1. Зайдите в меню «Файл», после чего перейдите в раздел «Параметры».
2. Откройте блок «Центр управления безопасностью» и кликните по строке «Параметры центра…».
3. Таким образом, вы попадете непосредственно в «Центр управления безопасностью», где во вкладке «Параметры макросов» можно будет регулировать настройки программных алгоритмов. Как это сделать, подробно рассматривалось в предыдущем способе.

Работа с макросами в других версиях Excel

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

Обычно трудности возникают только при работе с Excel 2007. Для того чтобы отключить/включить макросы, необходимо нажать на логотип программы, после чего перейти в блок «Параметры». После этого вы сможете установить необходимые настройки макросов. В более ранних версиях утилиты необходимо будет перейти в блок «Сервис» и открыть раздел «Макрос». Во вкладке «Безопасность» можно указать необходимые настройки работы программных алгоритмов. На выбор доступно 4 варианта безопасности: низкая, средняя, высокая и очень высокая. Каждый из пунктов соответствует настройкам в Excel 2010.

Как видите, в каждой новой версии все сложнее работать с макросами. Разработчики софта стараются обезопасить пользователей, поэтому включать/отключать программные алгоритмы могут только опытные юзеры, которые способны самостоятельно оценить все риски работы с макросами.

Как запустить макросы в Excel

Теперь рассмотрим, как выполняются все созданные ранее макросы в документе Excel.

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

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

Откроется диалоговое окно «Макрос». В списке, выбираем нужное имя макроса: «MergeCell», и жмем «Выполнить».

Макрос выполнился: нужные ячейки объединены, и текст в них сохранен.

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

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

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

Если Вы создали пользовательскую функцию, то вставлять в документ ее также надо как функцию. У меня такая функция должна рассчитывать НДС.

Например, применим ее к ячейке А1, со значением «100». Выделяем ячейку, в которую будет вписан результат. Затем переходим на вкладку «Формулы» и нажимаем кнопочку «Вставить функцию».

Откроется окно «Мастер функций». В поле «Категория» выбираем из выпадающего списка «Определенные пользователем», в поле «Выберите функцию» выбираем название из списка: «NDS». Нажимаем «ОК».

Затем выделяю ячейку, для которой нужно посчитать НДС, и нажимаю «ОК».

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

Загрузка…  

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

PublicSubPrimer2() ‘Объявляем переменные x, y, z Dimx,y,z ‘Присваиваем значение переменной x x=25 ‘Присваиваем значение переменной y y=35 ‘Присваиваем переменной z сумму ‘значений переменных x и y z=x+y ‘Выводим информационное сообщение ‘со значением переменной z MsgBoxz EndSub

Пример 3
Присвоение переменным строковых значений:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

PublicSubPrimer3() ‘Объявляем переменные x, y, z Dimx,y,z ‘Присваиваем строку переменной x x=»Добрый» ‘Присваиваем строку переменной y y=»день!» ‘Присваиваем переменной z строку, ‘состоящую из строк x и y ‘с пробелом между ними z=x&» «&y ‘Выводим информационное сообщение ‘со значением переменной z MsgBoxz EndSub

Скопируйте примеры процедур в стандартный модуль и запустите их на выполнение.

Основные сведения о макросах

Макрос определяет действие, которое должно выполняться в том случае, если используется элемент интерфейса пользователя. Он может быть такой же простой, как команды (например, circle), и включать специальные символы (например, ^C^C).

Например, макрос ^C^C_.circle 1 строит окружность с радиусом, равным 1 единице. Компоненты, определяющие этот макрос, описаны в следующей таблице.

Компоненты макроса CIRCLE
Компонент	Тип компонента	Результат
^C^C	Последовательность специальных управляющих символов	Эта последовательность аналогична двойному нажатию клавиши ESC.
_	Специальный управляющий символ	Указание того, что выполняемая команда будет использовать глобальное имя команды, а не локализованное.
	Специальный управляющий символ	Указание того, что выполняемая команда будет использовать встроенное определение команды, а не повторное определение команды, которая уже существует.
КРУГ	Имя команды	Запуск команды КРУГ.
	Специальный символ	Оставляет столько же места, сколько при нажатии клавиши ПРОБЕЛ при использовании команды.
	Специальный управляющий символ	Формирование паузы для ввода данных пользователем; в данном примере это пауза для центра окружности.
1	Входное значение	Ответ на запрос радиуса круга; в данном примере это значение 1.

Сохранение файла Excel с названием из ячейки — без привязки к ячейке

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

Замените код процедуры следующим кодом, который совсем немного, но изменен.

 Sub SaveFile() 'Объявление переменных Dim CellValue As String Dim Path As String Dim FinalFileName As String 'Временно отключаем показ вспомогательных сообщений Application.DisplayAlerts = False 'Задаём каталог сохранения файла (в данном случае текущий каталог) Path = ThisWorkbook.Path & "" 'Проверка значения ячейки If ActiveCell.Value = "" Then MsgBox "В ячейке отсутствует значение", vbCritical, "Ошибка!" Exit Sub End If 'Получаем значение активной ячейки CellValue = ActiveCell.Value 'Формируем итоговый путь и название файла FinalFileName = Path & CellValue 'Сохраняем файл ActiveWorkbook.SaveAs FileName:=FinalFileName, _ FileFormat:=xlOpenXMLWorkbook 'FileFormat:=xlOpenXMLWorkbookMacroEnabled 'Для сохранения файла с макросом 'Включаем вывод сообщений Application.DisplayAlerts = True MsgBox "Файл успешно сохранен с названием - " & CellValue, vbInformation, "Результат" End Sub 

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

Как видим, все отработало.

Корректируем макрос

Созданный макрос можно изменить. Самая распространенная причина, которая приводит к такой необходимости – сделанные при записи ошибки. Вот как можно отредактировать макрос:

Нажимаем кнопку “Макросы” (или комбинацию Ctrl+F8).
В появившемся окошке выбираем наш макрос и щелкаем “Изменить”.
На экране отобразится окно редактора “Microsoft Visual Basic”, в котором мы можем внести правки. Структура каждого макроса следующая:
открывается с команды “Sub”, закрывается – “End Sub”;

после “Sub” отображается имя макроса;
далее указано описание (если оно есть) и назначенная комбинация клавиш;
команда “Range(“…”).Select” возвращает номер ячейки. К примеру, “Range(“B2″).Select” отбирает ячейку B2.
В строке “ActiveCell.FormulaR1C1” указывается значение ячейки или действие в формуле.

Давайте попробуем скорректировать макрос, а именно, добавить в него ячейку B4 со значением 3

В код макроса нужно добавить следующие строки: Range(“B4”).Select ActiveCell.FormulaR1C1 = “3”
Для результирующей ячейки D2, соответственно, тоже нужно изменить начальное выражение на следующее: ActiveCell.FormulaR1C1 = “=RC*RC*RC” .Примечание: Обратите внимание, что адреса ячеек в данной строке (ActiveCell.FormulaR1C1) пишутся в стиле R1C1.
Когда все готово, редактор можно закрывать (просто щелкаем на крестик в правом верхнем углу окна).
Запускаем выполнение измененного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением “3”), а также, пересчитан результат с учетом измененной формулы.
Если мы имеем дело с большим макросом, на выполнение которого может потребоваться немало времени, ручное редактирование изменений поможет быстрее справиться с задачей.
Добавив в конце команду Application.ScreenUpdating = False мы можем ускорить работу, так как во время выполнения макроса, изменения на экране отображаться не будут.
Если потребуется снова вернуть отображение на экране, пишем команду: Application.ScreenUpdating = True .

Чтобы не нагружать программу пересчетом после каждого внесенного изменения, в самом начале пишем команду Application.Calculation = xlCalculationManual , а в конце – Application.Calculation = xlCalculationAutomatic. Теперь вычисление будет выполняться только один раз.

Создание макроса – практический пример

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

Как уже было сказано, макрос – это код, написанный на языке программирования VBA. Но в Excel Вы можете создать программу, не написав и строчки кода, что мы и сделаем прямо сейчас.

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

Дайте своему макросу имя (без пробелов) и нажмите ОК.

Начиная с этого момента, ВСЕ Ваши действия с документом записываются: изменения ячеек, пролистывание таблицы, даже изменение размера окна.

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

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

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

Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):

• =SUM(B2:K2) или =СУММ(B2:K2)
• =AVERAGE(B2:K2) или =СРЗНАЧ(B2:K2)
• =MIN(B2:K2) или =МИН(B2:K2)
• =MAX(B2:K2) или =МАКС(B2:K2)
• =MEDIAN(B2:K2) или =МЕДИАНА(B2:K2)

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

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

Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:

Соответственно:

• =SUM(L2:L21) или =СУММ(L2:L21)
• =AVERAGE(B2:K21) или =СРЗНАЧ(B2:K21) – для расчёта этого значения необходимо взять именно исходные данные таблицы. Если взять среднее значение из средних по отдельным строкам, то результат будет другим.
• =MIN(N2:N21) или =МИН(N2:N21)
• =MAX(O2:O21) или =МАКС(O2:O21)
• =MEDIAN(B2:K21) или =МЕДИАНА(B2:K21) – считаем, используя исходные данные таблицы, по причине указанной выше.

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

Далее, изменим внешний вид заголовков столбцов и строк:

• Жирное начертание шрифта.
• Выравнивание по центру.
• Заливка цветом.

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.

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

Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).

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

Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Создать макрос в Excel с помощью макрорекордера

Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.

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

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

• Записать макрорекордер может только то, что может пощупать, а значит записывать действия он может только в том случае, когда используются кнопки, иконки, команды меню и всё в этом духе, такие варианты как сортировка по цвету для него недоступна;
• В случае, когда в период записи была допущена ошибка, она также запишется. Но можно кнопкой отмены последнего действия, стереть последнюю команду которую вы неправильно записали на VBA;
• Запись в макрорекордере проводится только в границах окна MS Excel и в случае, когда вы закроете программу или включите другую, запись будет остановлена и перестанет выполняться.

Для включения макрорекордера на запись необходимо произвести следующие действия:

• в версии Excel от 2007 и к более новым вам нужно на вкладке «Разработчик» нажать кнопочку «Запись макроса»>;>
• в версиях Excel от 2003 и к более старым (они еще очень часто используются) вам нужно в меню «Сервис» выбрать пункт «Макрос» и нажать кнопку «Начать запись».

Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса», где:

• поле «Имя макроса» — можете прописать понятное вам имя на любом языке, но должно начинаться с буквы и не содержать в себе знаком препинания и пробелы;
• поле «Сочетание клавиш» — будет вами использоваться, в дальнейшем, для быстрого старта вашего макроса. В случае, когда вам нужно будет прописать новое сочетание горячих клавиш, то эта возможность будет доступна в меню «Сервис» — «Макрос» — «Макросы» — «Выполнить» или же на вкладке «Разработчик» нажав кнопочку «Макросы»>;
• поле «Сохранить в…» — вы можете задать то место, куда будет сохранен (но не послан) текст макроса, а это 3 варианта:
  • «Эта книга» — макрос будет записан в модуль текущей книги и сможет быть выполнен только в случае, когда данная книга Excel будет открыта;
  • «Новая книга» — макрос будет сохранен в тот шаблон, на основе которого в Excel создается пустая новая книга, а это значит, что макрос станет доступен во всех книгах, которые будут создаваться на этом компьютере с этого момента;
  • «Личная книга макросов» — является специальной книгой макросов Excel, которая называется «Personal.xls» и используется как специальное хранилище-библиотека макросов. При старте макросы из книги «Personal.xls» загружаются в память и могут быть запущены в любой книге в любой момент.
• поле «Описание» — здесь вы можете описать, что и как должен делать макрос, для чего он создавался и какие функции несет, это чисто информативное поле, что называется на память.

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

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

На странице «Сводный» делаем шаблон исходной таблицы (смотреть рисунок).

Единственное, что надо знать для написания программы в excel — как включить макросы. По ряду существенных причин по умолчанию они отключены. На панели вкладок находим закладку «Вид» и активируем её. Если в базовой версии такой вкладки нет, то добавляем. На всплывающей панели активируем функцию «Макросы». Активируем кнопку «Запись Макроса». Теперь любое действие с таблицей будет фиксироваться и записываться в виде специальных кодов.

Выбираем имя, например «Склад».

• Назначаем сочетание клавиш, активирующих программу – «Ctrl +Я».
• Выбираем сохранить в таблице – «Эта книга».
• Описание – «Сортировка данных кладовщиков».
• Подтверждаем клавишей OK (результат действий приведён на рис.).

Кликом «Остановить запись» прекращается работа. С этого момента, все действия с таблицей будут запоминаться и изменив начальные данные их можно повторить.

Используя стандартные операции и формулы заполняем шаблон (на рис. ниже приведена получившаяся форма).

Завершаем запись. Теперь при всех изменениях исходных данных, нажатием клавиш «Ctrl +Я», будут меняться значения в таблице-шаблоне. Даже на очень простом примере видно, как написать макрос в excel 2007.

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

• XLSM, в отличие от стандартного варианта XLSX, таблица с таким расширением поддерживает макрокоманды.
• XLSB обычно используется для больших таблиц и хранит информацию в бинарном формате.
• XLTM для идентификации шаблонов с поддержкой макрокоманд.

10) Защита электронных таблиц с логином и паролем

Если вы прочитали это далеко, нет ничего более справедливого, чем удивление еще одним пунктом в этом списке. И это макрос, к которому вы можете получить доступ прямо сейчас. Многим людям трудно ограничить данные в электронной таблице, когда они делятся ими с партнерами, сотрудниками и т. Д.

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

Это всего лишь несколько примеров макросов, которые вы можете использовать ежедневно

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

А вы уже используете какой-нибудь макрос в повседневной жизни? Поделитесь с нами своим опытом, оставив комментарий или связавшись!

Вы сомневаетесь? Заходи LUZ Excel Forum и отправьте свой вопрос!

Вариант 1: Автоматическая запись макросов

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

Подробнее: Включение и отключение макросов в Microsoft Excel

Когда все готово, приступаем к записи.

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

Открывается окно настройки записи макроса. Тут можно указать любое имя для него, если установленное по умолчанию вас не устраивает. Главное, чтобы имя это начиналось с буквы, а не с цифры, а также в названии не должно быть пробелов. Мы оставили название по умолчанию – «Макрос1».
Тут же при желании можно установить сочетание клавиш, при нажатии на которые макрос будет запускаться. Первой клавишей обязательно должна быть Ctrl, а вторую пользователь устанавливает самостоятельно. Мы в качестве примера установили клавишу М.
Далее следует определить, где будет храниться макрос. По умолчанию он расположен в этой же книге (файле), но при желании можно установить хранение в новой книге или в отдельной книге макросов. Мы оставим значение по умолчанию.
В самом нижнем поле можно оставить любое подходящее по контексту описание макроса, но это делать не обязательно. Когда все настройки выполнены, жмем на кнопку «OK».

После этого все ваши действия в данной книге (файле) Excel будут записываться в макрос до тех пор, пока вы сами не остановите запись.
Для примера запишем простейшее арифметическое действие: сложение содержимого трех ячеек (=C4+C5+C6).

Когда алгоритм был выполнен, щелкаем на кнопку «Остановить запись». Эта кнопка преобразовалась из кнопки «Запись макроса» после включения записи.

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

Для проверки того, как работает записанный макрос, выполним несколько простых действий.

1. Кликаем в том же блоке инструментов «Код» по кнопке «Макросы» или жмем сочетание клавиш Alt + F8.

После этого открывается окно со списком записанных макросов. Ищем макрос, который мы записали, выделяем его и кликаем на кнопку «Выполнить».

Можно поступить еще проще и не вызывать даже окно выбора макросов, так как на первом этапе мы задали сочетание клавиш для быстрого вызова макроса. В нашем случае это Ctrl + М. Жмем данную комбинацию на клавиатуре, после чего он запускается.
Как видим, он выполнил в точности все те действия, которые были записаны ранее.

Редактирование макроса

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

1. Снова щелкаем на кнопку «Макросы». В открывшемся окне выбираем нужный и кликаем по кнопке «Изменить».

Открывается «Microsoft Visual Basic» (VBE) – среда, где происходит их редактирование.

Запись каждого макроса начинается с команды , а заканчивается командой . Сразу же после указывается имя макроса. Оператор указывает выбор ячейки. Например, при команде «Range(«C4»).Select» выбирается ячейка «C4». Оператор используется для записи действий в формулах и других расчетов.
Попытаемся немного изменить макрос, дописав выражение:

Выражение заменим на .

Закрываем редактор и запускаем макрос. Как видим, вследствие введенных нами изменений была добавлена дополнительная ячейка с данными. Она также была включена в расчет общей суммы.
В случае если макрос слишком большой, его выполнение может занять значительное время, но внесением ручного изменения в код мы можем ускорить процесс. Добавляем команду . Она позволит сохранить вычислительные мощности, а значит, ускорить работу. Это достигается путем отказа от обновления экрана во время выполнения вычислительных действий. Чтобы возобновить обновление после выполнения макроса, в его конце пишем команду .
Добавим также команду в начало кода, а в его конец дописываем . Этим мы сначала отключаем автоматический пересчет результата после каждого изменения ячеек, а в конце макроса – включаем. Таким образом, Excel подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.