Excel макросы самоучитель

VBA Excel. Начинаем программировать с нуля

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

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

1. Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
2. Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

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

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

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

Первая программа на VBA Excel

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

1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

1. Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.

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

1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox “Привет” .

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.

Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

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

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

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

Источник: vremya-ne-zhdet.ru

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

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

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

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

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

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

Объекты:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: analitica.jimdofree.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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. Вы оформляете заказ

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

Появится форма. Выберите период доступа (1/3 или 6 мес.) Заполните поля ФИО (нужно для сертификата), e-mail (для доступа в личный кабинет) и номер телефона. Откроется окно оплаты заказа.

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

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

Принимаем оплату с карты любого банка (РФ, СНГ) или со счёта Яндекс.Денег. Для оплаты от юр.лица – отправьте на почту info@akademia-excel.ru реквизиты (для выставления счёта), ФИО и email (для заведения в системе пользователя/ей).

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

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

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

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

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

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

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

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

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

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

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

Источник: akademia-excel.ru

Excel макросы самоучитель

Макрос — это программа, автоматизирующая выполнение различных часто повторяющихся операций. Понятие макроса и управление его записью и применением аналогичны рассмотренным в текстовом процессоре Word.

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

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

• с помощью “горячих” клавиш;
• посредством выбора имени макроса в меню;
• щелчком мыши по графическому объекту, связанному с макросом.

Начало записи макроса

1. Обратитесь к меню Сервис/Запись макроса. /Начать запись. .
2. В окне “Запись макроса” укажите имя макроса.
3. Если запуск макроса планируется через меню или “горячие” клавиши, то нажмите кнопку “Параметры” и задайте имя пункта меню или (и) сочетание клавиш.
4. Нажмите “Ok” . Признак начала записи — появление небольшого окна с кнопкой “Остановить запись” .

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

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

Завершение записи

1. Нажмите кнопку “Остановить запись” .
2. Если запуск макроса планируется с помощью графического объекта, то выберите требуемый объект на панели “Формы” и разместите его на рабочем листе. Для вызова указанной панели следует обратиться к меню Вид/Панели инструментов. .После размещения объекта типа “Кнопка” откроется окно “Назначить макрос объекту” , в котором требуется указать имя макроса и нажать “Ok” . Для других объектов необходимо щелкнуть по нему правой кнопкой мыши, после чего из выпадающего меню следует выбрать пункт “Назначить макросу” и указать связь с макросом.
3. Опробуйте действие макроса, предварительно восстановив исходное состояние таблицы.

Пример записи макроса

Пусть требуется для выделенного диапазона ячеек, содержащих числа, установить режим форматирования в числовом виде с точностью до одного десятичного знака. Соответствующий макрос должен запускаться с помощью графического объекта “Кнопка”.

1. Выделить диапазон клеток, в котором задается формат. Важно сделать это до запуска макрорекордера, а не после, поскольку макрос должен быть применим для любого выделенного участка. В противном случае он будет форматировать только один диапазон.
2. Запустить макрорекордер.
3. Обратиться к меню Формат/Ячейки. и на вкладке “Число” выбрать числовой формат и “Число десятичных знаков” — 1.
4. Щелкнуть мышью на любой ячейке, чтобы снять выделение диапазона.
5. Нажать кнопку “Остановить запись” .
6. Вызвать панель “Элементы управления” , щелкнуть по объекту “Кнопка” и, установив указатель мыши на свободное место листа, “растянуть” объект. Сменить надпись на кнопке можно двойным щелчком мыши по стандартной надписи, но если объект выделен. Выделяется объект правой кнопкой мыши и нажатием клавиши Esc для закрытия всплывающего меню форматирования объекта.
7. В окне “Назначить макрос объекту” указать имя макроса, связываемого с кнопкой и нажать “Ok” .
8. Щелкнуть мышью вне объекта “Кнопка” , чтобы подготовить макрос к работе.
9. Восстановить исходные данные и опробовать работу макроса для различных выделяемых диапазонов.

На рисунке показан диапазон C1:E2, преобразованный в требуемый формат макросом, запускаемым кнопкой с надписью “Десятичный формат”.

Источник: www.sites.google.com