Progress-servis55.ru

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

Excel vba готовые решения

Полезности для VBA программиста

Если вы произносите слово «макрОсы» с придыханием от ужаса и ударением на втором слоге, а фраза «Visual Basic for Applications» для вас звучит как заклинание, то эта статья — не для вас. Во всяком случае — пока 🙂

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

MZ-Tools — «швейцарский нож» для программиста

После установки в редакторе VBE в меню Tools появится подменю MZ-Tools и новая панель инструментов для быстрого вызова тех же функций:

Умеет очень и очень много. Из самого ценного, на мой взгляд:

  • Автоматически добавлять «рыбы-заготовки» для создания процедур, функций, обработчиков событий и ошибок с правильным наименованием переменных по венгерской системе.
  • Копировать элементы управления на пользовательских формах вместе с их кодом.
  • Делать закладки (Favorites) на процедуры и быстро к ним переходить в большом проекте.
  • Разбивать длинные строки кода на несколько и собирать обратно (split и combine lines).
  • Выдавать подробную статистику по проекту (кол-во строк кода, процедур, элементов на формах и т.д.)
  • Проверять проект на наличие неиспользуемых переменных и процедур (Review Source)
  • Создать свою базу заготовок кода (Code Templates) для типичных случаев и быстро вставлять их потом в новые макросы.
  • Автоматически создавать длинную и страшную строку для подключения к внешним источникам данных по ADO.
  • Вешать горячие клавиши на любую функцию из надстройки.

Однозначный мастхэв для программиста любого уровня. Если у вас последняя версия Office, то скачивайте обязательно свежую версию MZ-Tools 3.00.1218 от 1 марта, т.к. в ней исправлен баг, при работе с Excel 2013.

Smart Indenter — автоматическая расстановка отступов в коде

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

Очень удобно повесить это действие на любое удобное сочетание клавиш в разделе Indenting Options и делать в одно касание.

К сожалению, автор программы забросил ее в 2005 году (почему, Карл!?) и последняя версия на сайте предназначена для Excel 97-2003. Тем не менее, программа вполне успешно работает и с более новыми версиями. Единственный нюанс: если у вас Excel 2013, то перед установкой Smart Indenter нужно установить сначала MZ-Tools последней версии, т.к. она содержит нужную для работы Indenter’а динамическую библиотеку.

VBE Tools — микроподстройка элементов в формах

Выравнивание элементов управления (кнопок, полей ввода, текстовых надписей и т.д.) на сложной форме может быть весьма мучительным процессом. Стандартная привязка к сетке редактора через меню Tools — Options — General — Align Controls to Grid иногда не очень помогает и даже начинает мешать, особенно если нужно сдвинуть, например, кнопку совсем на чуть-чуть. В этом деле поможет надстройка VBE Tools, которая после установки отобразить простую панельку, где можно для выбранного элемента произвести микроподстройку размеров и положения на форме:

Сдвиг положения также можно будет делать с помощью сочетаний клавиш Alt+стрелки, а изменение размеров с помощью Shift+Alt+стрелки и Ctrl+Alt+стрелки.

Также по щелчку правой кнопки мыши по элементу можно его переименовать сразу вместе с кодом.

VBA Diff — поиск отличий в коде

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

Есть 30-дневный бесплатный период, а потом надстройка попросит заплатить за нее 39 фунтов (около 3.5 тыс.руб по текущему курсу).

Говоря откровенно, мне она пригодилась в этой жизни всего раза 3-4 на супербольших проектах, но сэкономила мне тогда несколько дней и много-много нервных клеток 🙂 Ну и всегда есть, конечно, бесплатная альтернатива: экспортировать код в текстовый файл (правой кнопкой мыши по модулю — Export) и сравнивать их потом в Microsoft Word с помощью команды Рецензирование — Сравнить документы, но с помощью VBA Diff это на порядок удобнее.

Moqups и Wireframe Sketcher — прототипирование интерфейса

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

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

Это онлайновый редактор, который:

  • Не требует установки отдельных программ. Всегда можно приехать к клиенту в офис и прямо на сайте открыть-показать-подправить созданный интерфейс.
  • Содержит все основные элементы диалоговых окон (надписи, кнопки, вып.списки и т.д.) в вариантах для Windows и Mac.
  • Позволяет экспортировать созданный интерфейс в PNG или PDF форматах или отправить клиенту ссылочку для просмотра онлайн.
  • Фактически бесплатен. Есть ограничения на количество графических элементов, но мне еще ни разу не удалось за них выйти. Если не будет хватать места или захочется хранить сразу несколько больших проектов, то всегда можно перейти на премиум-версию за 99$ в год.
Читать еще:  Сортировка ячеек в excel

В общем и целом, для задач разработчика на VBA — более, чем достаточно, я считаю.

Если для кого принципиально нужен офлайновый вариант (для работы без доступа к интернету на берегу моря, например), то рекомендую Wireframe Sketcher:

После бесплатного демо-периода на 2 недели попросит купить за те же 99$.

Invisible Basic — обфускатор кода

Надежно закрыть исходный код ваших макросов паролем в Microsoft Excel, к сожалению, невозможно. Однако, существует целый класс программ, называемых обфускаторами (от англ. obfuscate — сбивать с толку, запутывать), которые так меняют внешний вид VBA-кода, что прочитать и понять его будет крайне сложно, а именно:

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

Откровенно говоря, я не фанат использования подобных методов. В частности, с PLEX я решил, что лучше буду отдавать покупателям полной версии открытый, понятный и прокомментированный исходный код — это мне кажется более правильным. Тем не менее, у моих знакомых программистов неоднократно были случаи, когда подобная программка очень пригодилась бы (программист выполнил работу, а клиент не заплатил и т.д.) Так что если понадобится — знайте, где взять. «Мы мирные люди, но наш бронепоезд. » и все такое.

Code Cleaner — очистка кода

В процессе работы над проектом (особенно, если он большой и долгий) в модулях кода и формах начинает накапливаться «мусор» — обрывки служебной информации редактора VBE, которые могут привести к неожиданным и нежелательным глюкам. Утилита Code Cleaner чистит эту гадость простым, но надежным способом: экспортирует код из модулей в текстовые файлы, а потом импортирует его чистеньким обратно. Очень рекомендую при работе над большими проектами периодически проводить такую «уборку».

Ribbon XML Editor

Если для запуска ваших макросов хочется создать на ленте Excel собственную вкладку с красивыми кнопками, то вам не обойтись без редактора XML-файлов интерфейса. Однозначно, самым удобным и мощным на сегодняшний день является в этом плане отечественная программа Ribbon XML Editor, созданная Максимом Новиковым.

Совершенно замечательный софт, который:

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

Долгие годы Microsoft упорно в лоб игнорирует разработчиков на VBA, считая его, судя по всему, неполноценным языком программирования. Периодически проскальзывают слухи, что в следующей версии Office Visual Basic уже не будет или его заменят на JavaScript. Регулярно выходят новые версии Visual Studio с новыми плюшками, а редактор VBE так и застрял в 1997 году, до сих пор не умея стандартными средствами расставить отступы в коде.

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

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

Атанас Йонков Блоггер, Веб-разработчик
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) Теперь пришло время написать свой первый макрос!

Читать еще:  Если ячейка не пустая то excel

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

Excel vba готовые решения

БлогNot. Решаем счётные задачи с помощью Excel VBA

Решаем счётные задачи с помощью Excel VBA

Visual Basic for Applications (VBA) – диалект языка Visual Basic, включённый в состав пакета Microsoft Office. Программы на VBA, называемые макросами, могут выполняться прямо из документа Word или Excel, используя при этом в качестве интерфейса пользователя стандартные для Windows кнопки, поля ввода, списки, окна диалога или переключатели.

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

Перед началом работы:

1. Включите настройку Кнопка Office — Параметры Excel (или Word) — Основные — «Показывать вкладку Разработчик на ленте». В последних версиях офиса настройка может называться иначе, но она там есть 🙂

2. На вкладке Разработчик нажмите кнопку Безопасность макросов и разрешите выполнение макросов:

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

3. Нажмите вкладку Разработчик – Макросы, дайте новой программе имя и нажмите кнопку Создать:

Откроется редактор Visual Basic, в котором можно писать, отлаживать, выполнять и сохранять программы.

На скрине ниже показана программа, позволяющая вычислить, сколько процентов составляет значение A от B.

Вот листинг почти программки такого же типа, только ещё проще.

В простых случаях нам достаточно с помощью окна InputBox получить значение переменной (третьим аргументом ей можно дать значение по умолчанию), при необходимости проверить, корректно ли введены данные (так как введённая в InputBox величина возвращается в виде строки, можно получить её числовое значение функцией Val или узнать, введено ли вообще числовое значение функцией IsNumeric ), произвести расчёты и вывести результаты в новом окне сообщения, полученном функцией MsgBox . Её первым аргументом мы передаём строку, выводимую в окне, её можно получить сложением строк в двойных кавычек и/или числовых значений, которые нужно преобразовать к строковым функцией Str .

Теперь можно нажать зелёный треугольничек или клавишу F5 в редакторе VBA, чтобы запустить программу. Если доступно несколько программ или текстовый курсор не установлен внутри программы, компьютер может попросить выбрать нужную по имени:

Читать еще:  Как отключить гиперссылки в excel

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

Обычно мы хотим запускать программу не из Visual Basic, а прямо из документа, например, нажимая кнопку.

Чтобы встроить кнопку непосредственно в документ Word или Excel, действуем так:

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

2. Потом курсором-крестиком «нарисуем» кнопку в документе и нажмём «Создать» в окне «Назначить макрос объекту», чтобы кнопке была назначена пустая процедура-обработчик её основного события (то есть, нажатия):

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

Обращаться к ячейкам Excel из программы VBA тоже очень легко, вот несколько примеров:

Ну и немного более законченного кода.

Попробуйте скопировать в VBA и выполнить эти 2 несложных программы, и начальный опыт программирования в нём у Вас появится 🙂

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

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

Затем от введённых чисел рассчитывается сумма и записывается в ячейку B6.

Вторая программа предполагает, что в ячейках B12 и B13 рабочего листа записаны 2 даты. Это могут быть строки, интерпретируемые Вашим Excel как даты, например, 01.01.2001 или даты, полученные формулой, скажем, =СЕГОДНЯ()

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

В противном случае мы вычисляем и выводим в ячейку B16 количество дней между датами, а в C12 и C13 — дни недели по русски. Добавьте небольшое оформление и получите простейший калькулятор дат:

09.05.2018, 10:56; рейтинг: 3215

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
Присвоение переменным числовых значений:

Ссылка на основную публикацию
Adblock
detector