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

Редактор Visual Basic

После отображения вкладки Разработчик можно открыть редактор Visual Basic — встроенный инструмент, используемый для создания и изменения кода VBA для PowerPoint.

1. Создание макроса в редакторе Visual Basic

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

   В открывшемся диалоговом окне Макрос введем Hello в поле Имя.

   Нажмем кнопку Создать, чтобы открыть редактор Visual Basic с уже введенными основными элементами нового макроса.

   Редактор Visual Basic содержит код, указанный ниже:

   Sub Hello()

   End Sub

   Определение 1

   Sub — это сокращение от Subroutine (подпрограмма), которую в данном случае можно определить как макрос.

   Вызов макроса Hello приведет к выполнению любого кода между строчками Sub Hello() и End Sub.

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

   Sub Hello()

   MsgBox(«Hello world»)

   End Sub

   Вернемся на вкладку Разработчик в PowerPoint и снова нажмем кнопку Макросы. Выберите макрос Hello в появившемся списке, а затем нажмем кнопку Запуск, чтобы отобразить небольшое окно сообщения с текстом «Hello world».

   Замечание 3

   Таким образом, мы создали и запустили пользовательский код VBA в PowerPoint. Нажмем кнопку ОК в окне сообщения, чтобы закрыть его и закончить выполнение макроса.

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

2. Предоставление доступа к макросам

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

   Чтобы создать кнопку для макроса Hello на панели быстрого доступа, выполним следующие действия.

3. Создание кнопки для макроса на панели быстрого доступа

   Переходим на вкладку Файл.

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

   В списке раздела Выбрать команды нужно выбрать пункт Макросы. В появившемся списке найдем текст, похожий на Презентация1!Hello, и выберем его.

   Затем нужно нажать кнопку Добавить >>, чтобы добавить макрос в список справа, потом нажмем кнопку Изменить…, чтобы выбрать изображение кнопки, связываемой с макросом.

   Нажмем кнопку ОК. Новая кнопка должна появиться на панели быстрого доступа над вкладкой Файл.

   

   

   

   

   

   

   

   

   

   

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

Кнопка – элемент ActiveX

Вставка кнопки на лист

1. Выберите вкладку «Разработчик» и нажмите на кнопку «Вставить».
2. Нажмите на значок кнопки в коллекции «Элементы ActiveX».

1. Кликните по рабочему листу и кнопка из коллекции «Элементы ActiveX» появится рядом с курсором.
2. Автоматически включится режим редактирования, включение и отключение которого осуществляется кнопкой «Режим конструктора».

Ухватив кнопку за кружок, можно изменить ее размер, а ухватив за границу – перетащить на другое место. Чтобы отредактировать наименование кнопки, нужно кликнуть на ней правой кнопкой мыши и в контекстном меню выбрать «Объект CommandButton» > «Edit».

Добавление процедуры

Кликните дважды по кнопке из коллекции «Элементы ActiveX» левой кнопкой мыши в режиме конструктора. Это приведет к открытию модуля листа, на который вставлена кнопка, и записи шаблона процедуры «CommandButton1_Click».

Вставьте внутрь шаблона процедуры код, который будет очищать ячейки A1, B1 и C1:

1	Range(«A1:C1»).Clear

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

Теперь кнопка из коллекции «Элементы управления формы» (Кнопка 1) будет заполнять ячейки диапазона «A1:C1», а кнопка из коллекции «Элементы ActiveX» (CommandButton1) очищать их.

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

Для примера возьмём самый обычный файл 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.

Основы программирования на VBA

Рассмотрим использование VBA в Microsoft PowerPoint 2010. Данную версию PowerPoint выберем, поскольку она по сравнению с более ранними имеет множество дополнительных возможностей для создания презентаций.

1. Объекты программироdания связаны друг с другом отношениями иерархии, которые зовутся объектной моделью приложения. Иначе говоря, объектная модель отображает то, что видим в пользовательском интерфейсе, например, объектная модель PowerPoint содержит среди многих других объекты Application, Presentation и Slide. Объектная модель представляет собой схему приложения и его возможностей.

2. Свойства и методы

   Управлять объектами можно, задавая их свойства и вызывая их методы. Задание свойства изменяет некоторое качество объекта. Вызов метода заставляет объект выполнить некоторое действие. Например, объект Slide имеет метод Copy, помещающий копию слайда в буфер обмена, и свойство Layout, представляющее макет слайда.

3. Коллекции

   Многие объекты существуют в версиях единственного и множественного числа — Presentation и Presentations, Slide и Slides и т. д. Версии множественного числа называются коллекциями. Объекты коллекции используются для выполнения действия над несколькими элементами коллекции.

Расширение файлов Excel, которые содержат макросы

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

До Excel 2007 был достаточен один формат файла — .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:

Если вы выберете «Нет», Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете «Да», Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.

Отображение в Microsoft Office 2007 и 2010

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

Office 2007

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

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

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

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

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

Также включить и отключить макрокоманды в Excel 2007 можно более простым способом, ничем не отличающимся от включения их в Excel 2010.

Office 2010

Включить и отключить макрокоманды в Excel 2010 можно, воспользовавшись меню «Разработчик». Однако данное меню не всегда активно в табличном редакторе 2010, особенно в момент первого запуска офисного приложения, поэтому первоначально следует совершить определённые действия, чтобы создать данное меню на панели инструментов. Специалисты рекомендуют выносить на панель инструментов подменю, к которым приходится достаточно часто обращаться в процессе выполнения различных задач.

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

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

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

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

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

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

Итак, макрокоманды в табличных редакторах 2003, 2007 и 2010 способны восприниматься большинством пользователей в качестве лучших помощников, позволяющих рутинную работу автоматизировать и повысить её результативность.

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

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

Нажмите кнопку «Макрос» (или комбинацию Ctrl + F8).
В появившемся окне выбираем наш макрос и нажимаем «Изменить”.

На экране появится окно редактора «Microsoft Visual Basic», в котором мы можем вносить изменения. Структура каждого макроса следующая:
открывается командой «Sub», закрывается командой «End Sub”;
после «Sub» отображается название макроса;
ниже — описание (если есть) и назначенная комбинация клавиш;
команду «Интервал («… »). Выбрать »возвращает номер ячейки. Например, «Диапазон (« B2 »). Выбрать» выбирает ячейку B2.
Строка ActiveCell.FormulaR1C1 указывает значение ячейки или действие в формуле.

Попробуем исправить макрос, т.е добавить ячейку B4 со значением 3. В код макроса нужно добавить следующие строки:
Дальность («В4»). Выбирать
ActiveCell.FormulaR1C1 = «3»

Следовательно, для полученной ячейки D2 вам также необходимо изменить исходное выражение следующим образом:
ActiveCell.FormulaR1C1 = «= RC -2 * R 1 C -2 * R 2 C -2″.
Примечание

Обратите внимание, что адреса ячеек в этой строке (ActiveCell.FormulaR1C1) написаны в стиле R1C1.
Когда все будет готово, вы можете закрыть редактор (просто нажмите на крестик в правом верхнем углу окна).
Запускаем выполнение модифицированного макроса, после чего можем заметить, что в таблице появилась новая полная ячейка (B4 со значением «3») и, кроме того, результат был пересчитан с учетом измененной формулы.
Если мы имеем дело с большим макросом, выполнение которого может занять много времени, ручное редактирование изменений поможет быстрее справиться с задачей.
Добавив в конце команду Application.ScreenUpdating = False, мы можем ускорить работу, так как во время выполнения макроса изменения не будут отображаться на экране.

Если вам нужно снова вернуть отображение на экран, напишите команду: Application.ScreenUpdating = True.

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

Word 2007: три варианта записи макроса

Далее вы можете пойти тремя путями.

Запись без привязки. Вы пишете макрос, не связывая его ни с кнопкой, ни с сочетанием клавиш. Для этого достаточно нажать на ОК.

Привязка макроса к кнопке. Тут следует пойти таким путем:

1. Щелкните на «По кнопке».
2. В «Настройки панели быстрого доступа» выберите все документы или отдельный файл, где можно применять этот макрос.
3. В окне «Выбрать команды» нажмите на нужный макрос. Затем — щелчок на «Добавить».
4. Для настройки кликните на «Изменить».
5. В «Символе» выберите значок для кнопки.
6. «Отображаемое имя» — наименование макроса.
7. Два раза кликните на ОК, чтобы начать запись.

Привязка макроса к сочетанию клавиш. Здесь следующий путь:

1. Остановитесь на «Клавиатуре».
2. В «Командах» найдите макрос, который вы собрались записать.
3. В «Новом сочетании клавиш» введите придуманную комбинацию. Клик на «Назначить».
4. Щелкните на «Закрыть», чтобы начать запись.

Далее путь становится единым для всех вариантов:

1. Выполните последовательность действий, которую нужно включить в макрос.
2. Завершить процесс можно выбором «Остановить запись» во вкладке «Код».