Что такое макросы в word и excel, как ими пользоваться

Самоучитель по макросам в excel

Курс «Макросы в Excel»

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

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

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

Вы сами решаете на сколько нужен доступ:

1 мес – 2 990 руб.

3 мес – 4 990 руб.

Экономия 56% – самый популярный вариант

6 мес – 6 990 руб.

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

Всего более 9 часов видео материалов

Блок 1. Основы написания макросов

Блок 2. Объектная модель

Блок 3. Работа с Application, Workbook, Worksheet

Блок 4. Методы копирования и удаления ячеек/диапазонов

Блок 5. Способы определения посл. заполненной ячейки

Блок 6. Проверка условий (If. Then. ; Select Case)

Блок 7. Работа с переменными

Блок 8. 3 вида циклов

Блок 9. Диалог с пользователем

Блок 10. Функции VBA

Блок 11. Строим сводные таблицы макросом

Сборник 100+ готовых примеров макросов

Нажмите на названии пункта, чтобы раскрыть детали

1. Вы оформляете заказ

Нажмите Купить доступ.

Также информация о заказе автоматом уйдёт к вам на почту.

2. Оплачиваете картой

3. Получаете доступ

Переходите в личный кабинет (письмо с логином и паролем уже у вас в почте). Внутри курс Макросы в Excel. Состоит из 11 блоков, каждый разбит на уроки. В уроке текстовое объяснение материала, задание в Excel файле и тест на проверку знаний. Сдаёте 80% тестов – получаете сертификат (в электронном виде на почту и в личном кабинете).

4. Применяете знания в работе

Мы постарались простым и понятным языком рассказать вам про функционал, который в разы увеличит вашу Excel-производительность. Видео, тексты, тесты, практические примеры – это всё хорошо, но теперь дело за вами! Уже с первого урока начните применять полученные знания, иначе получится, что мы зря старались.

Отзывы учениковАкадемии Excel

Хочу поблагодарить вас за курс по макросам. С excel работаю давно. Про макросы слышала, но всегда считала, что это только для программистов. Посмотрела ваши бесплатные уроки и изменила свое мнение. Самое главное, что ушёл страх. Раньше боялась, что как я, с базовым немецким (лучше бы английский учила), который последний раз открывала лет 30 назад в школе смогу разобраться в программировании. Да, сначала было не просто, но вы очень доступно и понятно объясняете.

Не всегда получается с первого раза, но я выработала для себя стратегию. Сначала просто смотрю видео и даже не пытаюсь повторять вслед за вами. Тут главное уловить идею, логику что ли. Потом уже начинаю делать задания параллельно с вашими объяснениями. Спасибоза качественные видео и доп.материалы. Одного месяца точно мало, мне понадобилось 5 мес. Училась стабильно 2-3 дня в неделю. В среднем по 1-1,5 часа. Но, возможно на меня не стоит ориентироваться. Спасибо за всё!

Дмитрий, Вы очень грамотно и доходчиво объясняете материал. Всё как-то логично, структурировано, что ощущение, что в рот положили, осталось только прожевать. Ваши короткие видео удобно пересматривать, так как не нужно проматывать полуторачасовые вебинары с кучей воды, как у других. Спасибо, что делитесь своими готовыми примерами. Просто брал и применял, практически без изменений.

Я не представляю сколько времени ушло на подготовку всего этого материала, но могу с большой степенью вероятности сказать, что это уникальный материал!

Спасибо, за ваш курс: Макросы в ExcelКак всегда, доступное изложение материала по принципу от простого к сложному с практическими примерами. До записи к вам на курс уже пробовала писать макросы, использовала макрорекордер, где-то находила готовые примеры и пыталась изменить под свои задачи. Эта была бессистемна работа. Постоянно не понимала почему в примере работает, а если что-то меняю, то сразу появляются какие-то ошибки. Ваш курс расставил всё по местам.

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

Вариант 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 подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.

Альтернативный вариант открытия файла

 Set objExcel = New Excel.Application Set wb = objExcel.Workbooks wb.Open fname, local:=True Set ws = wb.Item(1).ActiveSheet 

При открытии файла можно использовать доп.параметры (приведу некоторые):

UpdateLinks – обновлять или нет внешние ссылки при открытии файла;ReadOnly – открытие в режиме только для чтения;Format – используемый при открытии разделитель (1 – символ tab, 2 – запятые, 3 – пробелы, 4 – точка с запятой, 5 – без разделителя, 6 – пользовательский разделитель, заданный в Delimiter);Delimiter – пользовательский разделитель (в случае, если Format = 6);Origin – тип операционной системы (xlMacintosh, xlWindows или xlMSDOS);Local – использование в Excel языка такого же, как в открываемом файле.

Теперь можно выполнять какие-то действия с открытым файлом, просто обращаясь через wb и ws.

 ws.Cells(1, 1).Value = "Test" ws.Cells(1, 1).Font.Size = 18 ' Поменять размер шрифта ws.Cells(1, 1).HorizontalAlignment = xlCenter ' 

Как увидеть программный код макроса?

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

Для этого нужно последовательно выполнить команды:

• развернуть вкладку «Вид»; • открыть «Макросы»; • выбрать имя необходимого макроса; • вызывать окно с его кодом при помощи кнопки «Изменить».

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

Работа с Excel файлами (WorkBook)

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

Макросы для сводных таблиц

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

Пример 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

Exit Sub

ErrorHandling:

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

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

MsgBox “Data Workbook not found;” & _

       “Please add the workbook to C:\Documents and Settings and click OK”

Resume

End Sub

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

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

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

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

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

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

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

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

Активация и выключение макросов во вкладке Разработчика

Сразу стоит отметить, что в процессе выполнения этой задачи некоторые пользователи могут столкнуться с трудностями. Все из-за того, что вкладка “Разработчик” по умолчанию выключена и ее, для начала, нужно активировать.

1. Щелкаем левой кнопкой мыши по меню «Файл».
2. Затем, внизу раскрывшегося списка выбираем пункт «Параметры».
3. В параметрах программы нас интересует пункт «Настройка ленты». Далее ставим галочку напротив вкладки “Разработчик”. Теперь подтверждаем действие нажатием кнопки «OK».

По завершении этих действий вкладка «Разработчик» будет активирована. Теперь можно приступить к включением макросов.

Щелкаем по вкладке “Разработчик”. В левом углу будет находиться требуемый раздел, где нажимаем кнопку «Безопасность макросов» в виде восклицательного знака.
В появившемся окне с настройками можно активировать сразу все макросы. Для этого необходимо выбрать из всех предложенных параметров вариант “Включить все макросы”

Нажатием кнопки “OK” подтверждаем внесенные изменения и выходим из параметров.Однако, следует обратить внимание на то, что разработчики Microsoft не рекомендуют выбирать этот вариант, так как есть вероятность запуска опасной программы, что может навредить компьютеру. Поэтому, выполняя эту операцию, помните, что вы действуете на свой страх и риск.

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

Как следует из названия, в самом нижнем варианте исправно будут работать все макросы, имеющие цифровую подпись. А в двух первых вариантах они будут отключены полностью. После того, как мы сделали выбор, нажимаем кнопку «OK».

Макросы для работы с данными

Эта часть посвящена управлению данными: их очистке, форматированию и т. д.

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

Атанас Йонков Блоггер, Веб-разработчик yonkov.atanas@gmail.com

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

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

Table of Contents

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

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

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

2. Отображение скрытых строк

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

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

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

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

Для того чтобы включить записанный макрос в других документах, необходимо сохранить его, предварительно очистив таблицу. Формат документа нужно задать как XLTM (именно он поддерживает макросы).

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

Макрос Excel: пример 5

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

'Данный код показывает окно с сообщением, если на текущем рабочем листе
'выбрана ячейка 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 "Вы выбрали ячейку B1"

   End If

End Sub

Как написать макрос в Excel — создание, запись и редактирование VBA макросов в Excel

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

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

По умолчанию, вкладка, отвечающая за управление и навигацию макросов в Excel, скрыта. Чтобы активировать данную опцию перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры Excel, перейдите по вкладке Настройка ленты, в правом поле со списком ставим маркер напротив вкладки Разработчик. Данные действия актуальны для версий Excel 2010 и старше.

На ленте появиться новая вкладка Разработчик с элементами управления автоматизации Excel.

Написание макросов в Excel

Во вкладке Разработчик в группе Код, нажмите кнопку Запись макроса. Появиться диалоговое окно Запись макроса, которая запрашивает некоторую информацию о будущем записываемом коде. Если вы впервые создаете макрос, можете просто нажать кнопку ОК. С данного момента Excel будет фиксировать каждое действие пользователя в модуле VBA, будь то ввод данных, форматирование или создание диаграмм. Чтобы остановить запись макроса, нажмите кнопку Остановить запись, которая находится в той же группе Код.

Вы также можете воспользоваться альтернативным вариантом записи макросов, воспользовавшись кнопкой Запись макроса, которая находится в левом нижнем углу рабочей книги Excel (правее статуса Готово).

Теперь вы можете посмотреть список всех созданных макросов, нажав на кнопку Макрос, находящуюся в группе Код. В появившемся диалоговом окне вы можете дать более описательные имена своим кодам или задать сочетания клавиш, которые бы запускали тот или иной макрос. Альтернативным вариантом запуска данного окна является нажатие клавиш Alt + F8.

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

Поздравляю! Вы написали свой первый макрос. Логичным будет проверить теперь, какой же код сгенерировал нам Excel. Сгенерированный код написан на языке VBA (Visual Basic for Applications). Чтобы увидеть его, нужно открыть Редактор VB (VBE), который запускается нажатием клавиш Alt + F11 или кнопкой Visual Basic на вкладке Разработчик.

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

Предлагаю на данном этапе подробнее изучить различные окна и меню редактора VBA. В дальнейшем это поможет вам сэкономить кучу времени.

Для просмотра кода, щелкните по ветке Modules в окне проектов и дважды щелкните по появившейся ветке Module1. Редактор откроет окно с кодом, как изображено на картинке.

Здесь можно редактировать сгенерированный код, который был записан при работе в Excel. К примеру, вам требуется заполнить определенный столбец значениями от 1 до 10. У вас уже есть первые три шага, которые вводят значения 1, 2 и 3 в первые три ячейки столбца А. Нам необходимо дописать оставшиеся семь шагов.

Работа с диапазонами (Range)

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

• Как выбрать и изменить форматирование диапазона
• Как создать и выбрать именованный диапазон
• Как сделать перебор диапазона ячеек
• Как выбрать и форматировать диапазон
• Как вставить пустые строки в диапазон
• Как отобразить все скрытые строки и столбцы
• Как удалить пустые строки
• Как удалить пустые столбцы
• Как отобрать и отформатировать все формулы на листе
• Как найти первую пустую строку или столбец
• Как применить дополнительный цвет заливки
• Как отсортировать диапазоны по двойному щелчку
• Как ограничить диапазон прокрутки в той или иной области
• Как автоматически задать область печати листа

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

Существует несколько проверенных способов запуска добавленных или созданных алгоритмов действий в Excel. Самый простой метод – запуск макроса через VBA:

1. Зайти во вкладку “Разработчик” на главной панели с инструментами.
2. Перейти в панель с инструментами “Код”, нажать на кнопку “Макросы”.
3. Из открывшегося окна нужно выбрать требуемый алгоритм с помощью ЛКМ, нажать на кнопку “Выполнить”.

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

1. Необходимо зайти в окно с готовыми или добавленными алгоритмами действий точно так же, как было описано в способе выше.
2. Перейти во вкладку “Параметры” которая расположена в правой части появившегося окна.
3. Откроется еще одно окно, в котором можно настроить алгоритм.

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

Запуска алгоритма через панель быстрого доступа:

1. На панели быстрого доступа найти значок стрелки, направленной вниз.

Добавление макроса в панель быстрого доступа

1. Из открывшегося списка выбрать функцию “Другие команды”. Должно появиться окно с параметрами Excel.
2. Из списка в левой стороне зайти во вкладку “Выбрать команду”. Нажать на функцию “Макросы”.
3. Далее необходимо выбрать желаемый алгоритм действий.

Доступный список изображений для значка быстрого доступа к алгоритму действий

На панели быстрого доступа перед стрелкой вниз должен появиться произвольный значок выбранного макроса. Его можно изменить через настройки. Чтобы сохранить установленные параметры, необходимо нажать кнопку “ОК”.

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

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

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

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

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

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

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

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

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

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

Знакомство с редактором VBA

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

1. Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

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

Чтобы сделать макрос, воспользуйтесь простой инструкцией для пользователей Эксель:

1. Перейдите на вкладку «Разработчик». Кликните по кнопке «Запись макроса», которая расположена на ленте в блоке инструментов «Код».
2. Открывается окно настройки записи макроса. Тут можно указать любое имя для него, если установленное по умолчанию вас не устраивает. Главное, чтобы имя это начиналось с буквы, а не с цифры, а также в названии не должно быть пробелов. Мы оставили название по умолчанию – «Макрос1».
3. Тут же при желании можно установить сочетание клавиш, при нажатии на которые макрос будет запускаться. Первой клавишей обязательно должна быть Ctrl, а вторую пользователь устанавливает самостоятельно. Мы в качестве примера установили клавишу М.
4. Далее следует определить, где будет храниться макрос. По умолчанию он расположен в этой же книге (файле), но при желании можно установить хранение в новой книге или в отдельной книге макросов. Мы оставим значение по умолчанию.
5. В самом нижнем поле можно оставить любое подходящее по контексту описание макроса, но это делать не обязательно. Когда все настройки выполнены, жмем на кнопку «OK».
6. После этого все ваши действия в данной книге (файле) Excel будут записываться в макрос до тех пор, пока вы сами не остановите запись.
7. Для примера запишем простейшее арифметическое действие: сложение содержимого трех ячеек (=C4+C5+C6).
8. Когда алгоритм был выполнен, щелкаем на кнопку «Остановить запись». Эта кнопка преобразовалась из кнопки «Запись макроса»после включения записи.

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