Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Атанас Йонков Блоггер, Веб-разработчик s@

18 готовых макросов VBA Excel

В этом уроке я покажу Вам самые популярные макросы в 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 и компьютерных наук в целом.

Макрос для экспорта MS Word таблиц в Excel листов

У меня есть документ word со многими таблицами. Кто-нибудь знает, как написать макрос для экспорта таких таблиц на разные листы Excel?

4 ответа

  • Создание диаграмм в MS Word с использованием обычных таблиц MS Word

Я работаю над программой, которая может генерировать MS Word файлов, содержащих таблицы. Я хочу создать макрос, который взял бы данные из таблиц, содержащихся в файле MS Word, и создал бы из них диаграмму (гистограмму, круговую или линейную). До сих пор мне это удавалось: Выберите часть таблицы.

Существует ли какой-либо бесплатный инструмент с открытым исходным кодом для экспорта данных .csv в MS Word, Excel или PDF? Он также должен эффективно генерировать многостраничный отчет.

Вот некоторый код, который считывает таблицу из Word в активный лист Excel. Он запрашивает документ word, а также номер таблицы, если Word содержит более одной таблицы.

Этот макрос должен быть вставлен в Excel (не Word) и помещен в стандартный модуль макроса, а не в модули кода событий рабочего листа или книги. Для этого перейдите к VBA (клавиатура Alt-TMV), вставьте модуль макросов (Alt-IM) и вставьте код в панель кода. Запустите макрос из интерфейса Excel, как и любой другой (Alt-TMM).

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

VBAexpert Excel Консалтинг (Моя другая жизнь: http://damonostrander.com )

Я изменил этот вариант с добавлением цикла для всех таблиц (начиная с выбранной таблицы):

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

  • Создайте макрос в Excel, который генерирует Word doc

Я хотел бы создать макрос в Excel, чтобы скопировать данные из определенных ячеек в файле Excel и заполнить эти значения в форму MS Word с помощью Office 2007. Мне нужно было бы создать новый документ Word в том же шаблоне для каждой строки Excel. При добавлении новых строк создается.

У меня есть процесс экспорта, который передает данные из моих таблиц доступа в файл Excel. Пару раз у меня были проблемы, когда процесс не генерировал один или несколько листов (1 лист = 1 таблица) в Excel. Поэтому, когда передача будет завершена, я хочу получить доступ, чтобы проверить, все ли.

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

Большое спасибо, Деймон и @Tim

Я изменил его, чтобы открыть файлы docx, переместил чистую строку рабочего листа после проверки на экранирование пользователем.

Вот окончательный код:

Похожие вопросы:

У меня есть макрос для экспорта определенных листов в рабочей книге для разделения PDF (для целей отчетности). Он работает правильно для меня в одной книге, однако в другой книге он экспортирует ALL.

У меня есть макрос (созданный с помощью мастера макросов), который запускает несколько запросов, а затем выводит таблицу в excel. Таблица имеет более чем 65 000 записей для экспорта форматированных.

Читайте так же:
Как в яндекс браузере закрыть все вкладки

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

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

Существует ли какой-либо бесплатный инструмент с открытым исходным кодом для экспорта данных .csv в MS Word, Excel или PDF? Он также должен эффективно генерировать многостраничный отчет.

Я хотел бы создать макрос в Excel, чтобы скопировать данные из определенных ячеек в файле Excel и заполнить эти значения в форму MS Word с помощью Office 2007. Мне нужно было бы создать новый.

У меня есть процесс экспорта, который передает данные из моих таблиц доступа в файл Excel. Пару раз у меня были проблемы, когда процесс не генерировал один или несколько листов (1 лист = 1 таблица).

Я использую следующий код для экспорта выбранных листов из Excel 2010 в один файл pdf. ThisWorkbook.Sheets(Array(Sheet1, Sheet2, Sheet3)).Select ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF.

У меня есть много объектов Excel, встроенных в документ MS-Word. Я хочу вычислить общую сумму: при суммировании итогов они есть в каждом указанном объекте excel и возвращают эту общую сумму в.

Я обращаюсь к вам, полностью осознавая свою полную неосведомленность в vba; поэтому я прошу помощи с макросом для экспорта из Ms Project Professional определенных дат в Ms Project и, если это.

Редактор электронных таблиц Excel

Всем нам приходится — кому реже, кому чаще — повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую «рутинную составляющую» — одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

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

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP. ), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

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

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно — редактор программ на VBA, встроенный в Microsoft Excel.

  • В старых версиях (Excel 2003 и старше) для этого идем в меню СервисМакросРедакторVisual Basic(Toos — Macro — Visual Basic Editor) .
  • В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик(Developer). Выбираем Файл — Параметры — Настройка ленты(File — Options — Customize Ribbon) и включаем в правой части окна флажок Разработчик(Developer). Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)

macro1.png

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться

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

Читайте так же:
Можно ли видеть гостей в инстаграме

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

macro3.png

· Модуль Эта книга — также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):

macro4.png

· Модуль листа — доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа — команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.)

macro5.png

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

macro6.png

Давайте разберем приведенный выше в качестве примера макрос Zamena:

· Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.

· Любой макрос должен заканчиваться оператором End Sub.

· Все, что находится между Sub и End Sub — тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

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

Способ 2. Запись макросов макрорекордером

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

  • Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу — запись останавливается.
  • Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
  • Если во время записи макроса макрорекордером вы ошиблись — ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) — во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Читайте так же:
Макрос объединение файлов excel в один

Чтобы включить запись необходимо:

  • в Excel 2003 и старше — выбрать в меню СервисМакросНачатьзапись(Tools — Macro — Record New Macro)
  • в Excel 2007 и новее — нажать кнопку Запись макроса(Record macro) на вкладке Разработчик(Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

macro7.png

  • Имя макроса — подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
  • Сочетание клавиш — будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис — Макрос — Макросы — Выполнить(Tools — Macro — Macros — Run) или с помощью кнопки Макросы(Macros)на вкладке Разработчик(Developer) или нажав ALT+F8.
  • Сохранить в. — здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:

o Эта книга — макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel

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

o Личная книга макросов — это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording).

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик(Developer) или — в старых версиях Excel — через меню Сервис — Макрос — Макросы (Tools — Macro — Macros):

macro8.png

· Любой выделенный в списке макрос можно запустить кнопкой Выполнить (Run).

· Кнопка Параметры (Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.

· Кнопка Изменить (Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.

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

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

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис — Настройка (Tools — Customize) и перейдите на вкладку Команды (Commands). В категории Макросы легко найти веселый желтый «колобок» — Настраиваемую кнопку (Custom button):

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа(Customise Quick Access Toolbar):

macro11.png

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

macro12.png

Кнопка на листе

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

  • В Excel 2003 и старше — откройте панель инструментов Формы через меню Вид — Панели инструментов — Формы(View — Toolbars — Forms)
  • В Excel 2007 и новее — откройте выпадающий список Вставить(Insert) на вкладке Разработчик(Developer)

Выберите объект Кнопка (Button):

macro13.png

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

Читайте так же:
Интерполяция данных в excel
Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция — только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert — Module и введем туда текст нашей функции:

macro14.png

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка — Функция) в категории Определенные пользователем(User Defined):

macro15.png

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

Как записать макрос для сводной таблицы Excel 2010

Взгляните на сводную таблицу, показанную на рис. 11.1. Можно обновить эту сводную таблицу, щелкнув внутри нее правой кнопкой мыши и выбрав команду Обновить (Refresh Data). Если во время обновления сводной таблицы вы записывали действия в виде макроса, то вы или любой другой пользователь сможете воспроизвести эти действия и обновить сводную таблицу в результате запуска макроса.

Рис. 11,1. Запись действий во время обновления этой сводной таблицы позволит в дальнейшем обновлять данные в результате запуска макроса

Рис. 11.1. Запись действий во время обновления этой сводной таблицы позволит в дальнейшем обновлять данные в результате запуска макроса

Первый этап в записи макроса — это вызов диалогового окна Запись макроса (Record Macro). Перейдите на вкладку Разработчик (Developer) ленты и щелкните на кнопке Запись макроса (Record Macro).

Не можете отыскать на ленте вкладку Разработчик? Выберите вкладку Файл (File), расположенную в левом верхнем углу окна Excel, и щелкните на кнопке Параметры (Options). В появившемся диалоговом окне Параметры Excel (Excel Options) выберите категорию Настройка ленты (Customize Ribbon). В расположенном справа списке установите флажок Разработчик (Developer). В результате выполнения этого действия на ленте появится вкладка Разработчик.

В диалоговом окне Запись макроса введите следующую информацию о макросе.

  • Имя макроса (Macro Name). Имя должно описывать действия, выполняемые макросом.
  • Сочетание клавиш (Shortcut Key). В это поле можете ввести любую букву. Она станет частью комбинации клавиш, которая будет использоваться для воспроизведения макроса. Комбинацию клавиш задавать не обязательно.
  • Сохранить в (Store Macro In). Здесь указывается место хранения макроса. Если вы распространяете отчет сводной таблицы, следует выбрать параметр Эта книга (This Workbook), чтобы макрос был доступен для клиентов.
  • Описание (Description). В это поле вводится описание создаваемого макроса.

Поскольку макрос обновляет сводную таблицу в которой указаны все мини-отели СПб, ему задается имя RefreshData. Можно также назначить макросу «горячую» клавишу R. Легко заметить, что в диалоговом окне, показанном на рис. 11.2, макросу назначена комбинация клавиш . Помните, что после создания макроса вы будете использовать эту комбинацию клавиш для его запуска. В качестве места хранения макроса выберите параметр Эта книга. Щелкните на кнопке ОК.

Рис. 11.2. Настройте параметры диалогового окна Запись макроса

Рис. 11.2. Настройте параметры диалогового окна Запись макроса

После щелчка в диалоговом окне Запись макроса на кнопке ОК начинается запись макроса. На этом этапе все выполняемые вами действия в Excel будут регистрироваться. Щелкните правой кнопкой мыши в области сводной таблицы и выберите команду Обновить (Refresh Data). После обновления сводной таблицы можете остановить процесс записи макроса с помощью кнопки Остановить запись (Stop Recording) вкладки Разработчик.

Поздравляем! Вы только что записали свой первый макрос. Теперь можете выполнить макрос с помощью комбинации клавиш Ctrl+Shift+R.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector