Примеры макросов в excel

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

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

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

Я подготовил для вас несколько самых полезных примеров 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 можно содержать скрытые строки для большей ясности И для лучшего удобства пользователей. Вот один макрос, который отобразит все строки из активной рабочей таблицы:

3. Удаление пустых строк и столбов

Пустые строки в Excel – может быть проблемой для обработки данных. Вот как избавиться от них:

4. Нахождение пустых ячеек

13. Создание сводной таблицы

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 и т. Д.

18. Защита данных в MS Excel

Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

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

Источник: yonkov.github.io

Примеры макросов Excel

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

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

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

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

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

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

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

Читайте также:  Макросы в excel 2010

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

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

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

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

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

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

Источник: office-guru.ru

10 Примеры основных макросов в Excel

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

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

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

  • Использование Macro Recorder: В этой процедуре Excel сохраняет каждое действие, которое будет частью макроса, а затем средство записи макросов преобразует эти действия в команды Visual Basic для приложений (VBA).
  • Использование VBA: Если вы немного разбираетесь в программировании или даже владеете языком VBA, вы можете создавать свои макросы самостоятельно. Для этого просто используйте редактор VBA, присутствующий в последних версиях Microsoft Excel.

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

1) Показывать сообщение

Просто вставьте команду MsgBox в свой макрос. Вот как в следующем примере:

MsgBox “текст сообщения”

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

2) Запустите макрос при открытии листа

В некоторых случаях макрос необходимо открывать вместе с рабочим листом. В этом случае просто смонтируйте макрос с параметром Auto_Open (). Проверьте пример

Sub Auto_Open ()
MsgBox “Чтобы узнать все о Excel, перейдите на страницу www.https: //luz.vc/”
End Sub

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

3) Рабочий лист с текущей датой и временем

Вы можете написать текущую дату и время на листе. В следующем примере эти данные вставляются в ячейку A1, см.

Sub writeDataEora ()
Диапазон (“A1”) = Теперь
End Sub

4) Выполните одно и то же действие для каждой выбранной ячейки.

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

Sub to doSpeedCell ()
Для каждой ячейки в Selection.Cells
Ячейка MsgBox
Следующяя
End Sub

5) То же самое на всех выделенных ячейках

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

Sub to doSomethingAllAsCells ()
Selection.Cells.Value = “Привет”
End Sub

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

6) Идентификация формул в каждой ячейке

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

Sub CheckFormula ()
Если Range (“A1”) HasFormula = True, тогда
MsgBox «Есть Формула»
Еще
MsgBox “Не формула”
End If
End Sub

7) Изменить цвет ячейки при наведении мыши

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

Читайте также:  Макросы в excel 2010 отключить

Private Sub Worksheet_SelectionChange (ByVal Target As Range)

Димская домашняя линия как диапазон
Дальность
Dim Line2 как длинный

Set Line Start = Range («A» и Line2, Target)

Закрашивает выделенную ячейку в столбце 5
Set Line = Range (ячейки (Target.Row, 1), ячейки (Target.Row, 5))

С линейкой
.Interior.ColorIndex = 12
Конец с

8) Изменение цвета внутри и шрифтов

Этот макрос изменяет цвета внутри и у источника ячеек в соответствии с буквой ячеек.

Sub Colorir_interior_letra ()
Для N = 1 до диапазона («O65536»). End (xlUp) .Row

Выберите диапазон шкалы («O» и N)
Случай “А”
Диапазон («O» и N) .Interior.ColorIndex = 3
Диапазон («O» и N) .Font.ColorIndex = 1

Случай “B”
Диапазон («O» и N) .Interior.ColorIndex = 4
Диапазон («O» и N) .Font.ColorIndex = 2

Случай “C”
Диапазон («O» и N) .Interior.ColorIndex = 5
Диапазон («O» и N) .Font.ColorIndex = 3

Случай “D”
Диапазон («O» и N) .Interior.ColorIndex = 7
Диапазон («O» и N) .Font.ColorIndex = 12

Case Else
Диапазон («O» и N) .Interior.ColorIndex = 6
Диапазон («O» и N) .Font.ColorIndex = 4
End Select

9) Говорящий макрос

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

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

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

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

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

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

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

Источник: blog.luz.vc

Как написать простой макрос в программе Excel

Макрос в «Эксель» — небольшая программка (скрипт) написанная на языке VBA (Visual Basic for Applications) разновидности языка Basic ( Бейсик).

В макросах, как правило, прописывают последовательность действий с данными в таблице Excel.

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

Как создать простой макрос.

Создавать макросы в Excel можно двумя способами:

  • При помощи опции «Запись макроса»;
  • Написать макрос на языке VBA в редакторе макросов и применить его к документу.

Самый простой способ «Запись макроса», его и рассмотрим.

Для начала необходимо включить вкладку «Разработчик», если она не включена.

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

После того, как включили панель разработчика можно начинать запись макроса.

Чтобы записать макрос, следует:

  1. Войти во вкладку «разработчик».
  2. Выбрать запись макроса.
  3. Выбрать имя макроса (в имени нельзя использовать пробелы и дефисы);
  4. Можно выбрать сочетание клавиш, при нажатии которых будет начинаться запись макроса;
  5. Выбрать место сохранения:

— при сохранении в «Эта книга» макрос будет работать только в текущем документе;

— при сохранении в «Личная книга» макрос будет работать во всех документах на Вашем компьютере.

  1. Можно добавить описание макроса, оно поможет Вам вспомнить, какие действия совершает макрос.
  2. Нажать «Ок».
  3. Если вы не указали сочетание клавиш, запись начнется сразу после нажатия кнопки «Ок».
  4. Когда идет запись, Вы должны совершать требуемую последовательность действий.
  5. Когда закончите, нажимайте кнопку остановить запись.

Записанные макросы отображаются в книге макросов.

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

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

Попробуем отредактировать макрос.

Например, продолжить его еще на несколько ячеек.

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

Источник: ruexcel.ru

Макросы VBA Excel

Получение списка файлов в папке и подпапках средствами VBA

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

  • 159 комментариев
  • Читать далее
  • 245211 просмотров
  • 2 прикреплённых файла

Выпадающий календарь в ячейке (выбор даты на листе Excel)

Надстройка samradDatePicker (русифицированная) для облегчения ввода даты в ячейки листа Excel.

Читайте также:  Как написать макрос в excel подробная инструкция

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

Поместите файл надстройки из вложения в папку автозагрузки Excel (C:Program FilesMicrosoft OfficeOFFICExxXLSTART).

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

  • 79 комментариев
  • Читать далее
  • 242331 просмотр
  • 2 прикреплённых файла

Удаление (скрытие) строк по условию

макрос удалит на листе все строки, в которых содержится искомый текст:

  • 284 комментария
  • Читать далее
  • 197826 просмотров
  • 2 прикреплённых файла

Поиск в Excel

Наверняка, вы сталкивались с ситуацией, когда необходимо производить поиск некоторого значения по всей книге Excel (искать частичное совпадение на всех листах активной книги)

Штатными средствами Excel вывести поле для поиска на панель инструментов не удаётся, а вызывать каждый раз диалоговое окно нажатием комбинации клавиш Ctrl + F не всегда удобно.

На помощь придёт эта надстройка – она формирует в строке меню Excel 2003 поле для поиска по всем листам:

Достаточно ввести искомый текст, и нажать клавишу Enter, – и перед вами полный список всех подходящих ячеек со всех листов книги.

  • 42 комментария
  • Читать далее
  • 180657 просмотров
  • 1 прикреплённый файл

Вывод диалоговых окон выбора файла и папки средствами VBA (выбрать файл или папку)

Функции GetFileName и GetFilePath по сути аналогичны, и предназначены для вывода диалогового окна выбора файла
(при этом можно указать стартовую папку для поиска файла, и тип/расширение выбираемого файла)

Функция GetFilenamesCollection позволяет выборать сразу несколько файлов в одной папке.

Функция GetFolderPath работает также, только служит для вывода диалогового окна выбора папки.

Источник: excelvba.ru

Канал в Telegram

Вы здесь

Примеры макросов в Excel. Диалоговое VBA сообщение msgBox

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

Пример 1. Простое диалоговое сообщение msgBox в VBA

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

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

Итак, для того чтоб создать макрос, Вам необходимо открыть окно всеми любимого редактора Visual Basic (VB). Для этого выполняем следующие действия: Сервис-Макрос-Редактор Visual Basic (Alt+F11).

Откроется окно редактора MS Visual Basic.

Если у Вас отсутствуют левые окна, то их необходимо включить. Для этого нажмите F4 – Открывает окно свойств Properties Window, и сочетание клавиш Ctrl+R – открывает окно Project Explorer. Без этих окон в дальнейшем затруднительно работать. Все! Сделали.

Что мы видим в окне Project? В данном окне отражается как раз таки структура нашей книги. Объекты книги – Лист1, 2, 3, Эта книга. Более подробно изучим данные объекты в последующих статьях, а пока возвращаюсь к примеру.

Что необходимо сделать для того чтоб начать писать код? Необходимо создать модуль. Примечание: Вообще, в дальнейшем рекомендую разделять код обработчиков на разные модули. Это облегчит понимание кода и создаст порядок в структуре.

Создаем модуль: Insert – Module

Перед нами открылось пустое окно модуля, напоминает блокнот. Такое окно мы уже видели, когда записывали первый макрос в прошлой статье. По правилам «хорошего тона» дадим имя нашему модулю, назовем его «MacroBook». Для этого перейдите в окно свойств и введите имя в поле (Name)

Переходим в окно редактирования кода и пишем следующее:

Готово! Открываем окно рабочей книги Excel, жмем Alt+F8 и видим наш макрос «Hello»

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

Пример 2. Расширенное диалоговое сообщение msgBox в VBA

Рассмотрим еще один вид диалоговых сообщений, которые содержат дополнительные кнопки «Да», «Нет», «Отмена»

Переходим к коду нашего макроса «Hello» и дописываем к команде msgbox следующее:

MsgBox “Hello, World!”, vbYesNoCancel, “Мой макрос”

Обратите внимание, когда вы поставите запятую после «Hello, World!», у Вас должен появиться список всех доступных типов диалоговых окон

Поэкспериментируйте с каждым для понимания.

Как Вы заметили, я добавил еще один параметр к команде msgBox – “Мой макрос”. Это подпись нашего окна. Этот параметр не обязателен, но я рекомендую все диалоги подписывать наименованием своей разработки или иной информацией.

Запустим наш макрос

Теперь у нас открылась совсем иная форма сообщения.

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

Видео: Пример работы msgbox в vba

Источник: www.programm-school.ru