Microsoft Excel

Макросы в excel 2019 самоучитель с примерами для чайников – Самоучитель по работе с макросами в Excel

Содержание

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

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

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

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

Настройка разрешения для использования макросов в Excel

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

Кликните эту ссылку, чтобы узнать больше о параметрах безопасности макросов в Excel

Редактор Visual Basic

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

Кликните эту ссылку, чтобы узнать больше о редакторе Visual Basic в Excel

Запись макросов

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

Кликните эту ссылку, чтобы узнать больше о записи макросов в Excel

Учебник Excel VBA

Для тех, кто только начинает осваивать язык программирования Excel VBA, предлагаем небольшой вводный курс по Visual Basic for Applications.

Кликните эту ссылку, чтобы перейти к учебнику Excel VBA

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Запуск и создание макросов в Excel для чайников

VBA коды для простых программ макросов необходимых при работе с таблицами на каждый день.

Создание и выполнение макросов

Макрос для копирования листа в Excel c любым количеством копий.
Практический пример с описанием и исходным кодом макроса для одновременного копирования любого количества рабочих листов. Макрос для выделения и удаления пустых столбцов в Excel.
Выделение, удаление, скрытие и добавление пустых столбцов в таблицу по условию пользователя с помощью макросов. Исходные коды предоставляться с описанием и примерами в картинках. Макрос для выделения и удаления пустых строк в Excel.
Примеры макросов с исходными кодами для удаления, выделения, добавления и скрытия пустых строк в исходной таблице данных.  Макрос для выделения ячеек со снятой защитой на листе Excel.
Исходный код с примером и описанием для VBA-макроса выделения ячеек у которых, снятая защита от ввода значений данных и редактирования. Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием.
Исходный VBA-код макроса для поиска выпадающих списков на рабочем листе. Исходный код VBA-макроса для поиска ячеек с условным форматированием. Макрос для выделения ячеек Excel по условию больше меньше.
2 исходных кода макросов для выделения ячеек по условию больше равно или меньше равно относительно их числовых значений. Макрос для выборочного выделения ячеек на листе Excel.
Исходный код макроса для автоматического выделения отдельных ячеек по заданному условию в критериях поиска значений. Как быстро выделять несмежные диапазоны макросом? Макрос для добавления строк с заданной высотой в таблицу Excel.
Пошаговое руководство по написанию кода макроса VBA для умной вставки пустых строк между ячейками таблицы. Как автоматически вставить строки через одну ячейку? Как сделать границы ячеек макросом в таблице Excel.
Как автоматически рисовать границы в таблицах с помощью макроса? Автоматизированное форматирование границ ячеек по разным цветам, стилям и толщине линий используя макрос. Макрос для объединения повторяющихся ячеек в таблице Excel.
Практический пример и пошаговый разбор исходного VBA-кода макроса для автоматического объединения повторяющихся одинаковых ячеек в строках таблицы. 1 2 3 4

exceltable.com

Учимся работать с макросами в эксель, и программировать на VBA

В 1982 году появился дедушка современного Exel. Это был фактически простой сумматор, позволяющий быстро и без ошибок сводить табличные расчёты по строкам и столбцам. Тогда ещё трудно было представить, что через тридцать лет он до неузнаваемости изменит представление об автоматизации обработки большинства экономических расчётов.

Вводная часть – зачем нужны макросы


С 1993 года в разработке приложений для компьютеров началась революция, когда был создан объединённый продукт Microsoft Office, где Exel стал играть одну из ключевых ролей. Именно в это время появляется мощное дополнение VBA, позволяющее автоматизировать задачи Exel. Табличный процессор получил возможность не только предоставлять удобный интерфейс для умного пересчёта ячеек, но и стал полноценным продуктом для решения прикладных задач.

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

  • Работаем в приложении Office 365 для дома. При этом рассматриваются те возможности, которые не принципиальны при переходе на работу в расширенные версии. Например, для малого бизнеса, где наиболее полноценно раскрываются возможности табличного процессора и макросы excel 2010.
  • При описании будет использоваться интерфейс Exel 2016.
  • VBA, диалект предметно ориентированного языка Visual Basic применяемый при работе с приложениями Microsoft Office.
  • Макрос или макрокоманда — программный алгоритм действий, определённый пользователем. Кроме того, это понятие применяется для символьного имени книги-шаблона, в которой хранится код действий.
  • Используется только функциональность, предусмотренная базовым вариантом табличного процессора, использующего макросы для excel 2013.
  • Предполагается, что читатель знаком с возможностями табличного процессора, но никогда не использовал макрос в excel 2007.

Рассмотрим, как создать макрос в excel 2007 на примере решения очень простой задачи:

В течение шести дней с понедельника по субботу на склад поступал цемент. Работали разные кладовщики. Информация вносится в компьютер последовательно не сортируя.

Задача — используя информацию кладовщиков (см. рис.1) сделать сводную таблицу за неделю. Посчитать общий вес поступившего цемента и по каждому кладовщику отдельно.

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



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

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

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

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

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

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

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

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

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

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

Предостережения

Формат статьи не позволяет раскрыть все возможности такого мощного инструмента как Макросы и тем более работы с кодами языка VBA, на котором прописываются все действия по работе с таблицами. Желающим использовать макросы в excel самоучитель Office 365, без сомнений, будет полезен. Там же можно найти примеры макросов excel. Здесь будут даны только некоторые наиболее общие понятия.

Чтобы посмотреть VBA программный код в меню открывается вкладка «Вид» и активируется «Макросы». Появится новое окно где будут видны имена всех записанных макросов. Выбирается нужный и с помощью клика «Изменить» смотрим коды Visual Basic for Applications. При определённом опыте непосредственно в этом окне можно менять последовательность действий. Более того, наиболее сложные макросы пишутся именно в кодах VBA. Но это уже другая история. Отметит только, что средства языка позволяют автоматизировать расчёт таблиц и построение графиков любой сложности и последовательности. Экспортировать данные в Exel с документов различного формата, включая интернет-ресурсы.

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

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

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

komp.guru

Руководство и примеры работы с макросами в Excel

Многие люди думают, что макросы в Excel – это очень трудно, но это не так. В этой статье Вы найдёте несколько интересных и простых примеров, как можно автоматизировать Ваши повседневные задачи в Excel. Макрос – это программа на Visual Basic, созданная для того, чтобы автоматизировать задачи в Microsoft Office. В своих примерах я использую Excel 2010, но с таким же успехом Вы можете использовать Excel 2007.

Создаем макрос при помощи команды «Запись макроса»

  1. Для начала откройте вкладку View (Вид) на Ленте. В выпадающем списке Macros (Макросы) нажмите кнопку Record Macro (Запись макроса).Откроется диалоговое окно Record Macro (Запись Макроса).

  2. Задайте имя макросу (не допускаются пробелы и специальные символы), клавишу быстрого вызова, а также, где бы Вы хотели сохранить свой макрос. При желании, Вы можете добавить описание.
  3. С этого момента макрос записывает действия. Например, Вы можете ввести слово «Hello» в ячейку A1.
  4. Теперь снова нажмите иконку Macros (Макросы) и в раскрывшемся меню выберите Stop Recording (Остановить запись).

Доступ к записанному макросу можно получить с помощью команды View Macros (Макросы), которая находится на вкладке View (Вид) в выпадающем меню Macros (Макросы). Откроется диалоговое окно Macro (Макрос), в котором Вы сможете выбрать нужный. Дважды кликните по имени макроса, чтобы выполнить программу.

Кроме этого, Вы можете связать макрос с кнопкой. Для этого:

  1. На вкладке File (Файл) нажмите Options (Параметры) > Quick Access Toolbar (Панель быстрого доступа).
  2. В поле Choose commands from (Выбрать команды из) выберите All Commands (Все команды).
  3. Найдите команду Option Button (Кнопка), нам нужна та, что относится к разделу Form Control (Элементы управления формы). Выделите ее и нажмите Add (Добавить). Затем нажмите ОК, чтобы закрыть параметры Excel.
  4. Выберите команду, только что добавленную на Панель быстрого доступа, и начертите контур кнопки на рабочем листе Excel.
  5. Назначьте макрос объекту.

Примечание: Если у вас включена вкладка Developer (Разработчик), то получить доступ к элементам управления формы можно с нее. Для этого перейдите на вкладку Developer (Разработчик), нажмите на иконку Insert (Вставить) и из раскрывающегося меню выберите нужный элемент.

Не знаете, как отобразить вкладку Developer (Разработчик)? Excel 2007: жмем на кнопку Office > Excel Options (Параметры Excel) > Popular (Основные) и ставим галочку напротив опции Show Developer tab in the Ribbon (Показывать вкладку «Разработчик» на ленте). Excel 2010: жмем по вкладке File (Файл) > Options (Параметры) > Customize Ribbon (Настройка ленты) и в правом списке включаем вкладку Developer (Разработчик).

Цикл FOR

В следующем примере Вы увидите, как использовать цикл FOR. Цикл FOR позволяет нам выполнить повторение цикла с разными значениями. Давайте посмотрим, как можно заполнить числами от 1 до 5 ячейки A1:A5.

Для этого на вкладке Developer (Разработчик) нажмите Visual Basic. Дважды кликните по объекту из списка Microsoft Excel Objects, в котором должен быть сохранён макрос. Введите вот такой код:

Sub Macro1 ()
   For n = 1 To 5
      Cells(n, 1) = n
   Next n
End Sub

Сохраните файл. Чтобы выполнить макрос, перейдите View > Macros > View Macros (Вид > Макросы > Макросы), выберите из списка название нужного макроса и нажмите Run (Выполнить).

Следующий код отображает фразу «Hello World» в окне сообщений Windows.

Sub MacroName()
   MsgBox ("Hello World!")
End Sub

В следующем примере мы создаём сообщение с выбором Yes (Да) или No (Нет). Если выбрать вариант Yes (Да), то значение ячейки будет удалено.

Sub MacroName()
   Dim Answer As String
 
   Answer = MsgBox("Are you sure you want to delete the cell values ?", 
      vbQuestion + vbYesNo, "Delete cell")
 
   If Answer = vbYes Then
      ActiveCell.ClearContents
   End If
End Sub

Давайте проверим этот код. Выделите ячейку и запустите макрос. Вам будет показано вот такое сообщение:

Если Вы нажмёте Yes (Да), значение в выделенной ячейке будет удалено. А если No (Нет) – значение сохранится.

Конструкция IF

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

Sub MacroName()
   Dim CellValue As Integer
   CellValue = ActiveCell.Value

   If CellValue > 20 Then
      With Selection.Font
         .Color = -16776961
      End With
   Else
      With Selection.Font
         .ThemeColor = xlThemeColorLight2
         .TintAndShade = 0
      End With
   End If
End Sub

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

Когда Вы запустите макрос, цвет шрифта изменится на красный:

При выполнении второго условия шрифт станет синим:

Конструкция CASE

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

Sub MacroName()
   Dim CellValue As Integer
   CellValue = ActiveCell.Value
 
   Select Case CellValue
      Case 60 To 200
         MsgBox "The person is old"

      Case 30 To 59
         MsgBox "The person is adult"
 
      Case 18 To 29
         MsgBox "The person is young"
 
      Case 0 To 17
         MsgBox "The person is a child"
 
      Case Else
         MsgBox "Unknown age"
 
   End Select
End Sub

Чтобы протестировать этот пример, Вы должны выбрать ячейку со значением и запустить макрос. Если значение выбранной ячейки, к примеру, равно 44, то будет показано вот такое сообщение:

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Макросы в Excel для начинающих

Макросы в Excel для начинающих

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

 

Итак, что такое макросы и как их писать?

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

Макросы пишутся на языке VBA — Visual Basic for Applications. Эту аббревиатуру стоит запомнить и использовать в поисковых запросах при поиске нужной информации. VBA — объектно-ориентированный, иерархический язык. Это значит, что управлять придется объектами, подчиненными друг другу. Например, книга Excel — объект. В ней есть листы, на листах есть ячейки. Листы, ячейки, диапазоны и многое другое — это объекты. Подчиненность можно примерно приравнять вложенности — например, ячейка вложена в лист, а лист в книгу.

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

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

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

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

Объекты:

Workbook — рабочая книга Excel.

Sheet — лист.

Range — диапазон.

Cell — ячейка.

Row — строка.

Column — столбец.

 

Действия с объектами

Activate — активировать, то есть, «поставить курсор». Активировать можно книгу, лист, ячейку.

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

Delete — удалить. Удалить можно тоже строки и столбцы, диапазоны ячеек или одну ячейку, лист.

Copy — копировать.

И отдельно идет действие Paste — вставить. Если за всеми предыдущими действиями стоит слово «что?» (что активировать, что скопировать), то за словом вставить идет вопрос «куда?». Поэтому и при написании программы нужно указывать не что вставить, а куда вставить.

 

Кроме выполнения программ Excel может «отдавать информацию» по заданным командам. Вот несколько примеров таких команд:

Sheets.Count — выдает количество листов в книге.

Date — выдает сегодняшнюю дату в формате строки.

Len(«строка») — выдает длину строки в количестве символов. В этом примере длина равна 6.

 

Теперь нужно пару слов сказать о типах данных (переменных).

Для начала достаточно знать одно: данные бывают числами, а бывают строками, то есть текстом. С числами можно совершать одни действия (складывать и т.д.), со строками — другие (узнавать первый символ, например).

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

И числа, и строки можно присваивать переменным.

Например:

 

    i=1

    s=»Привет»

 

Можно переопределять значения переменных, например, так:

 

    i=i+10

    s=s & «, мир!»

 

После этого i=11, а s=»Привет, мир!».

Тут мы столкнулись с первой операцией над строковыми величинами. Знак & означает, что две строки нужно соединить. Порядок важен: если написать

s=», мир!» & s,  то получим, s=», мир!Привет».

 

Так как переменная s уже хранит в себе кавычки, при её использовании не нужно заключать её в кавычки. Наоборот, именно отсутствие кавычек подскажет Excel’ю, что воспринимать её нужно как переменную, а не как текст. То есть, записи:

 

   s=»Привет, мир!»

   h=s

 

и

 

   h=»Привет, мир!»

 

дадут одинаковый результат — присвоят переменной h значение «Привет, мир!»

Но запись

 

   s=»Привет, мир!»

   h=»s»

 

присвоит переменной h значение «s».

 

Надо сказать, что объекты в Excel иногда пишут в единственном числе, иногда во множественном. Как запомнить, в каком случае что используется? Можно использовать такое правило: всё, чего в Excel’е много, пишется во множественном числе, всё, что в единственном экземпляре — в единственном. В Excel’е много книг, много листов и очень много ячеек. Все они одинаковы для Excel’я и отличить их можно только по имени или координатам. Поэтому в программе используется множественное число. Например:

 

    Workbooks(«Книга1»).Activate

    Sheets(«Лист1»).Copy

    Rows(1).Delete

 

Ячейки определяются по координатам: первая — номер строки, вторая — столбца.

Например, команда

 

    Cells(1,1).Activate

 

поставит курсор в левую верхнюю ячейку.

 

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

 

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

    i=ActiveCell.Row

 

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

Например, команда

 

    Cells(1,1).Copy

 

скопирует верхнюю левую ячейку на активном листе.

А команда

 

    Sheets(«Лист1»).Cells(1,1).Copy

 

скопирует верхняя левую ячейку на листе «Лист1», независимо от того, активен этот лист сейчас или нет.

 

После этого вы уже можете писать макросы 🙂

Но лучше прочитайте еще про циклы и условный оператор, а потом про то, что такое коллекции объектов и что они нам могут дать.

webhamster.ru

Как создать макрос в Excel

     Добрый день!

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

     Как вы знаете из собственного опыта, при работе с макросом есть очень много «рутинны», то есть производятся одни и те же операции и действия которые нужны для получения результата, это могут быть заполнение однотипных таблиц ну или бланков, обработка данных, похожие как близнецы еженедельные, ежемесячные отчёты, создание дашбордов и т.д. А вот использование макросов позволит вам производить эти действия в автоматическом режиме, используя возможности Excel на полную катушку, скидая эти рутинные и монотонные операции на мощные плечи Excel. Также причиной использования макросов может быть добавления нужных возможностей, которые еще не реализованы в стандартных функциях Excel (например, вывод суммы прописью, сбор данных на одном листе и прочее).

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

     Итак, создать макрос в Excel возможно 2 способами:

  1. Создать макрос в Excel с помощью макрорекордера;
  2. Создать макрос в Excel в редакторе Visual Basic.

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

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

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

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

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

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

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

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

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

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

    Запуск редактора программ на VBA происходит по-разному, в зависимости от версии вашей программы Excel:

  • в версиях Excel 2003 и более старше, нам нужно в меню «Сервис», выбрать пункт «Макрос» и нажать «Редактор Visual Basic»;
  • в версиях Excel 2007 и более новее, нам нужно на вкладке «Разработчик» нажать кнопку «Редактор Visual Basic». В случае когда вы не находите эту вкладку вам нужно ее активировать выбрав пункт меню «Файл» — «Параметры» — «Настройка ленты» и в диалоговом окне флажком активируем вкладку «Разработчик».

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

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

     Все наши макросы будут храниться в так называемых программных модулях. В любой из книг любой версии Excel нам доступно создание любых программных модулей в любом количестве и размещать в них все созданные нами макросы. Один модуль в состоянии содержать в себе любое количество нужных вам или созданных макросов. Модули доступны в окне «Project Explorer» и размещаются в верхнем левом углу редактора макросов (так же его можно вызвать комбинацией клавиш CTRL+R).

     Программные модули в редакторе VBA существуют в нескольких видах и используются для разных вариантов и ситуаций:

  • Обычные модули – самый распространённый вариант при создании макросов. Если вам нужно создание именно такого модуля, тогда выберете в пункте меню «Insert», пункт «Module». В открывшемся новом пустом окне нового модуля теперь можно вводить нужные команды на VBA посредством клавиатуры или же попросту копируя их с других источников, например с моего сайта;
  • Модуль «Эта книга» — размещен там же где и предыдущий в том же окне «Project Explorer» и в данный модуль сохраняют макросы, которые срабатывают при определенных действиях, которые имеют место в книге Excel, это могут быть отправка файла на печать, открытие или закрытие документа и т.п.
  • Модуль листа – может быть доступен там, где и предыдущие, а также через контекстное меню ярлыка листа, выбрав пункт «Исходный текст». В этот модуль записывают те макросы, которые нужно исполнять при выполнении событий на листе, такие как пересчёт листа, изменение данных в ячейке, удаление или копирование листа ну и т.д.

     Собственно сам макрос при работе в стандартном модуле выглядит следующим образом:

     Рассмотрим на примере работающего макроса «Расширенный фильтр»:

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

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

Создание кнопки для запуска макросов в панели инструментов

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

  1. Кнопка в панели инструментов в MS Excel 2003 и более старше. Вам нужно в меню «Сервис» в пункте «Настройки» перейти на доступную вкладку «Команды» и в окне «Категории» выбрать команду «Настраиваемая кнопка» обозначена жёлтым колобком или смайликом, кому как понятней или удобней. Вытащите эту кнопку на свою панель задач и, нажав правую кнопку мыши по кнопке, вызовите ее контекстное меню, в котором вы сможете отредактировать под свои задачи кнопку, указав для нее новую иконку, имя и назначив нужный макрос.
  2. Кнопка в панели вашего быстрого доступа в MS Excel 2007 и более новее. Вам нужно клацнуть правой кнопкой мышки на панели быстрого доступа, которое находится в верхнем левом углу окна MS Excel и в открывшемся контекстном меню выбираете пункт «Настройка панели быстрого доступа». В диалоговом окне настройки вы выбираете категорию «Макросы» и с помощью кнопки «Добавить» вы переносите выбранный со списка макрос в другую половинку окна для дальнейшего закрепления этой команды на вашей панели быстрого доступа.

    Создание графической кнопки на листе Excel

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

  • В MS Excel 2003 и более старше переходите в меню «Вид», выбираете «Панель инструментов» и нажимаете кнопку «Формы».
  • В MS Excel 2007 и более новее вам нужно на вкладке «Разработчик» открыть выпадающее меню «Вставить» и выбрать объект «Кнопка».

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

Как создать пользовательские функции на VBA

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

     К примеру, для создания пользовательской функции налога на добавленную стоимость, он же НДС, нам нужно открыть наш редактор VBA и добавить новый модуль, выбрать в меню «Insert» пункт «Module» и ввести туда текст для нашей функции:      Следует заметить, что главное отличие функции от макроса это заголовок Function заменило Sub и в наличии заполненный список аргументов, в нашем примере это Summa. После того как наш код будет введен, он станет доступен в стандартном окне Мастера функций, который находится в меню «Формулы», пункт «Вставить функцию».        И выбираем категорию «Определенные пользователем» в которой и будет отображаться наша написанная функция «NDS».      После выбора нашей функции можно установить курсор на ячейку с аргументом, в которой будет содержаться та сумма, для которой мы считаем НДС, всё происходит, как и с обычной функцией.        А на этом у меня всё! Я очень надеюсь что статья о том как создать макрос в Excel вам была понятной и полезной. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

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

  1. «Как использовать расширенный фильтр в Excel»
  2. «8 способов как сравнить две таблицы в Excel»
  3. «5 вариантов, как удалить пустые строки в Excel»
  4. «Как скрыть в Excel значение ячеек»
  5. «5 быстрых способов как заменить точки на запятые в Excel»
  6. «Как правильно скрыть лист в Excel»

       Не забудьте поблагодарить автора!

Не уметь переносить бедность постыдно; не уметь избавиться от неё трудом – ещё постыднее. Перикл

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

topexcel.ru

Примеры макросов Excel — Microsoft Excel для начинающих

Следующие простые примеры макросов Excel иллюстрируют некоторые возможности и приёмы, описанные в самоучителе по Excel VBA.

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

Первоначально эта процедура Sub была приведена, как пример использования комментариев в коде VBA. Однако, здесь также можно увидеть, как объявляются переменные, как работают ссылки на ячейки Excel, использование цикла For, условного оператора If и вывод на экран окна сообщения.

'Процедура Sub выполняет поиск ячейки, содержащей заданную строку
'в диапазоне ячеек A1:A100 активного листа

Sub Find_String(sFindText As String)

   Dim i As Integer 'Целое число типа Integer, используется в цикле For
   Dim iRowNumber As Integer 'Целое число типа Integer для хранения результата

   iRowNumber = 0

   'Просматривает одну за другой ячейки A1:A100 до тех пор, пока не будет найдена строка sFindText

   For i = 1 To 100

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

         'Если совпадение с заданной строкой найдено
         'сохраняем номер текущей строки и выходим из цикла For
         iRowNumber = i
         Exit For

      End If
   Next i

   'Сообщаем пользователю во всплывающем окне найдена ли искомая строка
   'Если заданная строка найдена, указываем в какой ячейке найдено совпадение

   If iRowNumber = 0 Then
      MsgBox "Строка " & sFindText & " не найдена"
   Else
      MsgBox "Строка " & sFindText & " найдена в ячейке A" & iRowNumber
   End If

End Sub

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

Следующая процедура Sub – пример использования цикла Do While. Здесь также можно увидеть, как объявляются переменные, работу со ссылками на ячейки Excel и применение условного оператора If.

'Процедура Sub выводит числа Фибоначчи, не превышающие 1000

Sub Fibonacci()

   Dim i As Integer 'Счётчик для обозначения позиции элемента в последовательности
   Dim iFib As Integer 'Хранит текущее значение последовательности
   Dim iFib_Next As Integer 'Хранит следующее значение последовательности
   Dim iStep As Integer 'Хранит размер следующего приращения

   'Инициализируем переменные i и iFib_Next
   i = 1
   iFib_Next = 0

   'Цикл Do While будет выполняться до тех пор, пока значение
   'текущего числа Фибоначчи не превысит 1000

   Do While iFib_Next < 1000

      If i = 1 Then
         'Особый случай для первого элемента последовательности
         iStep = 1
         iFib = 0
      Else
         'Сохраняем размер следующего приращения перед тем, как перезаписать
         'текущее значение последовательности
         iStep = iFib
         iFib = iFib_Next
      End If

      'Выводим текущее число Фибоначчи в столбце A активного рабочего листа
      'в строке с индексом i
      Cells(i, 1).Value = iFib

      'Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1
      iFib_Next = iFib + iStep
      i = i + 1
   Loop

End Sub

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

Эта процедура Sub просматривает ячейки столбца A активного листа до тех пор, пока не встретит пустую ячейку. Значения записываются в массив. Этот простой макрос Excel показывает работу с динамическими массивами, а также использование цикла Do Until. В данном примере мы не будет совершать какие-либо действия с массивом, хотя в реальной практике программирования после того, как данные записаны в массив, над ними такие действия, как правило, совершаются.

'Процедура Sub сохраняет значения ячеек столбца A активного листа в массиве

Sub GetCellValues()

   Dim iRow As Integer 'Хранит номер текущей строки
   Dim dCellValues() As Double 'Массив для хранения значений ячеек

   iRow = 1
   ReDim dCellValues(1 To 10)

   'Цикл Do Until перебирает последовательно ячейки столбца A активного листа
   'и извлекает их значения в массив до тех пор, пока не встретится пустая ячейка

   Do Until IsEmpty(Cells(iRow, 1))

      'Проверяем, что массив dCellValues имеет достаточный размер
      'Если нет – увеличиваем размер массива на 10 при помощи ReDim
      If UBound(dCellValues) < iRow Then
         ReDim Preserve dCellValues(1 To iRow + 9)
      End If

      'Сохраняем значение текущей ячейки в массиве dCellValues
      dCellValues(iRow) = Cells(iRow, 1).Value

      iRow = iRow + 1
   Loop

End Sub

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

В этом примере процедура Sub считывает значения из столбца A рабочего листа Лист2 и выполняет с ними арифметические операции. Результаты заносятся в ячейки столбца A на активном рабочем листе. В этом макросе продемонстрировано использование объектов Excel. В частности, производится обращение процедурой Sub к объекту Columns, и показано, как доступ к этому объекту осуществляется через объект Worksheet. Показано так же, что при обращении к ячейке или диапазону ячеек на активном листе, имя этого листа при записи ссылки указывать не обязательно.

'Процедура Sub при помощи цикла считывает значения в столбце A рабочего листа Лист2,
'выполняет с каждым значением арифметические операции и записывает результат в
'столбец A активного рабочего листа (Лист1)

Sub Transfer_ColA()

   Dim i As Integer
   Dim Col As Range
   Dim dVal As Double

   'Присваиваем переменной Col столбец A рабочего листа Лист 2

   Set Col = Sheets("Лист2").Columns("A")
   i = 1

   'При помощи цикла считываем значения ячеек столбца Col до тех пор,
   'пока не встретится пустая ячейка

   Do Until IsEmpty(Col.Cells(i))

      'Выполняем арифметические операции над значением текущей ячейки

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

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

      Cells(i, 1) = dVal
      i = i + 1

   Loop

End Sub

Макрос 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: пример 6

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

'Процедура Sub присваивает аргументам Val1 и Val2 значения ячеек A1 и B1
'из рабочей книги Data.xlsx, находящейся в папке C:\Documents and Settings

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 и Val2 значения из заданной рабочей книги

      Val1 = Sheets("Лист1").Cells(1, 1)
      Val2 = Sheets("Лист1").Cells(1, 2)

      DataWorkbook.Close

   Exit Sub

ErrorHandling:

   'Если файл не найден, пользователю будет предложено поместить искомый файл
   'в нужную папку и после этого продолжить выполнение макроса

   MsgBox "Файл Data.xlsx не найден! " & _
      "Пожалуйста добавьте рабочую книгу в папку C:\Documents and Settings и нажмите OK"
   Resume

End Sub

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *