Web-studio46.ru

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

Основы программирование в excel vba

Основы программирование в excel vba

������� ���������� ��������� �� ����� VBA – �������, ����� �������� �� ���������� ������, � ����������� �� ��������� ����������. ���� ���������� �������� ���������, ������� ����� ����������� ������������ ���������� ��� ��������, ����������� �������������� ����������� ������������ ���������� Microsoft Office, �� ���������� ������� ����������� ������. ��� ���������� ���� ��������� ����� ��������� � ������� ������� ���������� ������, ������� ������� ����� �������� ���������� ���������. ��� ����� � ���������� ���������� �������� ������ ������������ � ������� ������� ��� ������ ������������ �������� ����������, � ����� ������� ������ � ��������������� ����������� �����. ���� ��������� ��������� � ������� ������� ������ ������ �������.

���������� “�����������” ��������� (��� ���������� ������� ��������� ��������� ����, � ���������� ���������� ����������) ����� �������� ��� �����. ������ ���� – ���� ����������� ����������������, �� ������� ��������� ���� (�����) ���������, ��� ������������� ����������� �������� ����������. ������ – ���� ����������������, �� ������� ��������� ����� ��������� (���������), ������������� � ����� �� ������������ �������. �������� ��������, ��������, ������ ����� ������� ���� �� ��������� ������ (������� Click), ������� ������� �� ���������� (������� KeyPress) � �.�. ������������ ����� ���������� ����� �������� ������ – «������ �������».

Range(“�����”)

Cells(i, j)

Rows(� ������)

Columns(� �������)

Sheets(“���”)

Sheets(� �����)

WorkSheet

Range(“A5:A10”). Value = 0 ��� Range(“A5:A10”) = 0 – � �������� ����� A5:A10 ��������� �������� 0.

Cells(2, 4). Value = n ��� Cells(2, 4) = n – � ������, ����������� �� ����������� 2-� ������ � 4-�� ������� (������ � ������� “D2”), ��������� �������� ���������� n.

Xn = Cells(1, 2).Value ��� Xn = Range(“B1”).Value – ���������� Xn ������������� �������� �� ������ B1 �������� �������� �����.

Sheets(2).Activate ������� �������� ���� � �2.

Sheets(“���������”).Delete ������� ���� � ������ “���������”.

Range(«A5:A10»).Clear – �������� �������� ����� A5:A10.

Range(«A2:B10»).Select – �������� �������� ����� A2:B10.

Application.Quit — ���������� ������ � Excel.

Знакомство с программированием в Excel

Добавление на ленту вкладки разработчика

Написание кода Ваших процедур и редактирование макросов производится в редакторе Visual Basic, который доступен с вкладки «Разработчик». Данная вкладка по умолчанию скрыта. Для ее отображения необходимо выполнить следующие действия:

Для Excel 2007. Зайдите в параметры Excel, используя кнопку Office, и в «основных параметрах работы с Excel» установите галочку на пункте «Показывать вкладку Разработчик на ленте».

Для Excel 2010 и 2013. В параметрах Excel нужно выбрать пункт «Настроить ленту» и в категории основные вкладки установить галочку для вкладки «Разработчик».

Знакомство с редактором Visual Basic в Excel

Чтобы попасть в редактор кода, кликните на вкладке разработчика в области «Код» по кнопке «Visual Basic».

После этого на экране появится новое окно редактора VBA. Рассмотрим ниже некоторые его элементы, которые могут понадобиться в самом начале.

№1 на изображении. Кнопка сохранить. Сохраняет текущую книгу Excel. Необходимо быть внимательнее при работе с несколькими книгами, так как текущей считается та, которая выделена в окне проектов (№3 на изображении).

№2 на изображении. Кнопки запуска, остановки и прерывания выполнения кода. Во время запуска начинает выполняться код той процедуры, на которой размещен курсор. Данные кнопки дублируются вкладкой «Run» основного меню редактора.

№3 на изображении. Окно управления проектами VBA открытых книг (Project Explorer) и кнопка, отображающая это окно.

№4 на изображении. Кнопка, отображающая окно свойств объектов (Properties Window), выбранных в окне №3, и элементов пользовательских форм (работа с объектами, их свойствами и методами будет рассмотрена в отдельной статье этой категории).

№5 на изображении. Кнопка вызова окна объектов (Object Browser). В нем описаны все доступные для работы объекты, их свойства и методы.

Кнопки №3, №4 и №5 так же доступны на вкладке «View» главного меню редактора. Там же можно вызвать другие полезные для работы окна, которые не будут рассмотрены в этом материале.

Написание простой процедуры на Visual Basic for Application

Код любой процедуры (подпрограммы) располагается в модуле, поэтому необходимо его добавить, чтобы приступить к программированию. Выберите пункт «Module» на вкладке «Insert» основного меню редактора VBA. В основной области редактора (на изображении имеет серый фон) должно появиться новое окно кода – Имя_книги – имя_модуля (Code), а в окне управления проектами (№3) к дереву выбранного проекта добавится вновь созданный модуль.

Читать еще:  Отзывы о нетологии программирование

Добавьте в модуль следующий код:

Данная процедура выводит на экран диалоговое окно с сообщением «Привет пользователь!». Протестируйте ее работу, нажав кнопку Rub Sub (№2 на изображении) или вызвав как обычный макрос.

Теперь более подробно разберем приведенный код.

Любая процедура начинается со строки объявления, состоящей из оператора Sub (сокращение от Subprogram), после которого следует имя подпрограммы. За именем следует перечень аргументов, заключенный в скобки.

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

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

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

Последняя строка содержит оператор End с последующим ключевым словом Sub и сообщает о завершении процедуры.

Учимся пользоваться Object Browser

Выше была упомянута функция MsgBox, но ее возможности полностью не рассмотрены. Это хороший случай, чтобы научиться использовать браузер объектов (№5 на изображении окна редактора VBA).

Нажмите кнопку на панели или клавишу F2, чтобы отобразить Object Browser:

Выпадающий список №1 содержит перечень всех подключенных библиотек. Используйте его только в том случае, если точно знаете, к какой библиотеке относится изучаемый класс, функция и т.д. (подключить или отключить любую из доступных библиотек можно, выбрав пункт «References» на вкладке «Tools» главного меню редактора VBA).

Примечание: Библиотеки предоставляют стандартные и дополнительные возможности работы с объектами Excel (ячейки, листы, книги и т.д.), файловой системы, изображениями, базами данных и другими.

Поле №2 предназначено для поиска свойств, методов, классов подключенных библиотек.

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

Поле №4 содержит перечень всех классов, выбранных в поле №1 библиотек.

В области №5 содержится список всех членов класса, выбранного в поле №4.

Поле №6 содержит краткую справку о выделенном результате поиска, классе или члене класса.

Примечание: библиотеки, классы, функции и процедуры (методы), свойства и т.п. в Object Browser имеют различные обозначения, представленные набором пиктограмм. Краткая справка (поле №6) всегда указывает какой тип элемента выделен. Обращайте на это внимание, чтобы в дальнейшем лучше ориентироваться в работе с объектами.

Примечание: Понятие объекта не относится к теме данной статьи и будет рассмотрено в следующих материалах.

Теперь найдем функцию MsgBox с помощью браузера объектов. В поле поиска (№2) впишите ее название и кликните по кнопке с изображением бинокля. В поле результатов поиска выделите строку со значением MsgBox в столбце «Member». В поле №6 появилась краткая информация, сообщающая нам, что выбранный член является функцией, имеет перечень аргументов, возвращает результат типа VbMsgBoxResult, относиться в классу Interaction библиотеки VBA.

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

Изучите самостоятельно возможности функции MsgBox.

Ссылка на процедуру VBA

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

Для лучшего понимая рассмотрим пример (в примере используется псевдокод):

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

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

VBA-Урок 1. Что такое VBA. Основные понятия.

В данном разделе мы рассмотрим что такое VBA, посколько именно на нем и пишутся макросы.

Что такое VBA?

VBA — язык (расшифровывается как Visual Basic for Application) был разработан компанией Microsoft. Данный язык не является самостоятельным, а предназначен для автоматизации процессов в пакете MS Office. VBA широко используется в Excel, а также в Access, Word и других программах пакета.

VBA — простой язык программирования, которому может научиться любой желающий. Изучив его, вы сможет предоставлять команды Excel, что делать с колонками, строками, значениями в ячейках, перемещать/добавлять/сортировать листы, выводить заранее запрограммированные сообщения, писать свои формулы и функции и т.д. Суть языка заключается в оперировании объектами (что относит его к объектно-ориентированному программированию).

Читать еще:  Язык программирования excel

Чтобы работать с VBA кодом, нам нужен редактор, который уже установлен по умолчанию. Вы можете открыть его, нажав комбинацию клавиш » ALT + F11 «.

Объекты (Objects)

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

Главный объект это Application, что соответствует самой программе Excel. Далее следует Workbooks (книга), Worksheets (лист), Range (диапазон, или отдельная ячейка). Например, чтобы обратиться к ячейке «A1» на листе нам нужно будет прописать следующий путь с учетом иерархии:

Application.Workbooks(«Архив»).Worksheets(«Аркуш1»).Range(«A1»).

Таким образом, мы научились обращаться до наименьшего объекта в Excel — ячейки.

Коллекции (Collections)

В свою очередь объекты имеют «коллекции». Коллекция — это группа объектов одинакового класса. Отдельные элеметы коллекции являются также объектами. Так, объекты Worksheets являются элементами коллекции объекта Worksheet, который содержит также и другие коллекции и объекты:

  • ChartObjects (элемент коллекции объекта ChartObject)
  • Range
  • PageSetup
  • PivotTables (элемент коллекции объекта PivotTable).

Свойства (Properties)

Каждый объект имеет свойства . Например, объект Range имеет свойство Value или Formula.

Worksheets(“Sheet1”).Range(“A1”).Value або Worksheets(“Sheet1”).Range(“A1”).Formula

В данном примере, свойство отображает значение, которое введено в ячейку или введенную формулу.

Также, через свойство Formula можно не только получить формулу, но и записать ее:

MsgBox Range(“A1”).Formula — получим сообщение с формулой в ячейке «А1«;

Range(“B12”).Formula = “=2+6*100” — вписываем формулу =2+6*100 в ячейку B12.

Методи (Methods)

Теперь давайте рассмотрим, каким образом мы можем управлять содержимым диапазона или ячейки. Для этого в VBA существуют, так-называемые методы (команды «что сделать»). При написании кода методы отделяются от объекта точкой, например:

Range(«A1»).Select или Cells(1, 1).Select

Данный метод указывает выбрать (Select) ячейку «A1».
Далее, давайте, удалим значение в данной ячейке. Для этого напишем следующий код:

Selection.ClearContents

Здесь программа «берет» то, что мы выделили (Selection) и удаляет его содержимое (ClearContents ).

ПРОГРАММИРОВАНИЕ В VISUAL BASIC FOR APPLICATIONS

ЦЕЛЬ ТРЕНИНГА

Научиться эффективно применять Visual Basic for Applications в Microsoft Excel для выполнения задач

КРАТКОЕ ОПИСАНИЕ

В ходе тренинга будут освоены навыки программирования в Visual Basic for Applications, необходимые для самостоятельного написания кода с нуля, отладки кода, анализа чужого кода, а также приобретены навыки и знания по ориентированию в редакторе Visual Basic Editor, понимание объектной модели Excel «изнутри»

ПРЕИМУЩЕСТВА ТРЕНИНГА

• Разработка и создание в ходе проведения тренинга файлов Microsoft Excel, используемых участниками для решения своих рабочих задач;
• Учет специфики работы и корпоративной культуры Заказчика;
• Обеспечение консультационной поддержки участников тренинга после его проведения.

РЕЗУЛЬТАТ

Сокращение времени, затрачиваемого на выполнение различных задач в Microsoft Excel, повышение качества и эффективности работы

ПРОДОЛЖИТЕЛЬНОСТЬ ТРЕНИНГА: 24 академических часа

ПРОГРАММА ТРЕНИНГА

Модуль 1. Введение

• Необходимые основы и теоретические знания, которые надо знать, чтобы начать программировать на Visual Basic for Applications (VBA) в Microsoft Excel.
• Основные понятия и термины, чтобы говорить на одном языке.
• Плюсы и минусы использования макросов, когда они могут быть вам полезны, а когда лучше воспользоваться иными возможностями Excel.
• Способы создания макросов (макрорекордер, самостоятельное написание кода, копирование и адаптация фрагментов чужого кода и т.д.)

Модуль 2. Редактор VBE

• Этот модуль посвящен подробному разбору основного инструмента программиста на VBA в Microsoft Excel — среды программирования и редактора кода Visual Basic Editor (VBE).
• Основные элементы окна VBE — их назначение, использование, горячие клавиши и приемы ускорения работы
• Структура проекта VBA Project — как устроена книга Excel с точки зрения программирования, где и как может храниться код VBA, в чем различия и особенности использования модулей разного типа, что такое формы и т.д.
• Процедуры (Sub) и функции (Function) – что из себя представляет каждый тип и в чем их различия и особенности.

Модуль 3. Объектная модель Microsoft Excel

• Рассматривается набор объектов (листов, ячеек, диаграмм и т.д.) со своими свойствами и специфическими методами и событиями. Знание и понимание этих свойств и взаимодействия объектов позволяет легко манипулировать любыми встроенными инструментами в Excel, привлекая их для решения задач. Этот модуль даст понимание устройства общей объектной структуры Excel, принципов и подходов для работы с ней, свойств ключевых объектов книги.
• Теоретический минимум (иерархия объектов, коллекции, свойства, методы и события)
• Получение справки по объектам
• Сокращенное обращение к объекту (для упрощения и упорядочивания ссылок на объекты)
• Подробный разбор свойств и методов объектов Workbook и Worksheet

Модуль 4. Синтаксис языка VBA

• В этом модуле на живых примерах разберем все основные программные конструкции и синтаксические особенности языка VBA, нюансы их использования в реальных задачах и проектах.
• Объявление переменных и констант (правила выбора имен для переменных, типы данных, области видимости переменных).
• Обзор способов организации взаимодействия с пользователем (MsgBox, InputBox, Application.InputBox, Debug.Print и т.д.)
• Математические и текстовые операторы для обработки данных
• Способы проверки условий (конструкции if. then, select case и т.д.), операторы сравнения.
• Способы организации циклов (различные виды безусловных циклов for. next, циклы по условию do. loop и т.д.)
• Обработка ошибок: завершение кода при возникновении ошибки; переход в определенное место в коде; сброс ошибок. Почему стоит избегать переходов при ошибках.

Модуль 5. Работа с диапазонами

• Безусловно, почти любой макрос создается для взаимодействия с отдельными ячейками и диапазонами листа Excel. В этом модуле подробно рассматриваются все свойства и методы объекта Range и работа с ним на практических задачах из реальной жизни.
• Свойство Selection, обработка выделенных областей на листе
• Объект Range, его свойства, методы и события. Выполнение различных операций над диапазонами
• Коллекция Cells, варианты и особенности ее использования в коде

Модуль 6. Обработка событий

• События в Microsoft Excel — это некие специальные ситуации, возникающие в определенный момент (сохранение книги, изменение данных на листе, печать документа и т.д.) Умение перехватывать и обрабатывать события открывает перед разработчиком очень широкий набор мощных возможностей для применения в проектах. Этот модуль посвящен детальному разбору принципов и алгоритмов обработки событий в Excel.
• Концепция событий Microsoft Excel. Последовательность возникновения событий. Отключение и включение событий. Почему события необходимо отключать.
• События книги, примеры использования в реальных проектах
• События листа, примеры
• Перехват событий любой открытой книги, листа.

Модуль 7. Пользовательские формы

• Для многих программ часто необходимо взаимодействие с пользователем: запрос данных, отображение, визуализация. Все это не сделать при помощи одних Msgbox и InputBox, поэтому нельзя представить более-менее объемную программу на VBA без пользовательских форм — UserForms. Данный модуль поможет научиться создавать пользовательские формы и применять их в проекте.
• Добавление формы в проект
• Использование элементов управления: кнопка, надпись, текстовое поле, выпадающий список
• Обработка событий элементов управления

Модуль 8. Варианты запуска процедур. Защита проекта VBA

• Создание кнопок на листе для запуска процедур
• Создание своей панели на ленте с кнопками для запуска процедур. Использование событий для создания кнопок на панели при открытии книги.
• Защита проекта VBA.

Модуль 9. Функции API

• Краткое введение в понятие функций API
• Когда имеет смысл применять данные функции, а когда лучше воздержаться.
• Объявление функций API, особенности объявления в различных версиях OS Windows.

Скачать программу тренинга:

ПРОГРАММИРОВАНИЕ В VBA (480,7 KiB, 5 870 скачиваний)

ОРГАНИЗАЦИЯ ТРЕНИНГА

Для проведения тренинга на вашей территории необходимо:

• подходящее помещение(достаточное для комфортного размещения всех участников тренинга);
• проектор и экран(или что-то, что может данный экран заменить);
• компьютеры для каждого участника с установленным на них Microsoft Office Excel 2007 или старше(2003 так же подойдет, но желательно все же 2007 или старше).

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

ЧТО ОСТАНЕТСЯ ПОСЛЕ ТРЕНИНГА (кроме новых знаний)

  • методическое пособие с описанием пошаговых действий всех основных тем тренинга
  • все файлы примеров, используемые на тренинге: пустые, чтобы можно было еще потренироваться и заполненные, чтобы можно было вспомнить как именно выглядит решение
  • несколько удобных файлов-шпаргалок по работе в Excel
  • набор надстроек и программ для работы в Excel, включая полную версию многофункциональной надстройки MulTEx Lite, добавляющей более 60 новых функций в Excel:
  • красивый сертификат

Заинтересовались? Для получения более подробной информации об условиях проведения тренинга пишите на info@excel-vba.ru

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