Здесь мы акцентируем ваше внимание на некоторых других вопросах создания законченных приложений на базе Excel на примере еще одной реальной программы.
В ходе разработки различных приложений у вас накапливается все больше повторно используемых компонентов, и на контроль за ними уходит довольно много времени. К примеру, как определить, какой файл используется в проекте OBJobAgent — SPREAD.VBX или его новая версия SSVBX25.VBX? Где используется модуль OBORD32 — в проекте OBSuper или совместно с проектом AvailAgent? Подобных вопросов огромное множество.
Чтобы облегчить управление повторно используемыми компонентами, применяются хранилища (репозитарии) исходного кода, однако многие небольшие организации не работают с ними. В качестве “упрощенного” решения можно порекомендовать воспользоваться Excel 97 для формирования сводных таблиц, в которых отражено использование компонентов в каких-либо проектах.
Файлы проектов VB представляют собой обычные ASCII-файлы, содержащие пути и имена файлов, из которых состоит проект. До версии VB 3.0 включительно описание проекта хранилось в MAK-файлах и включало достаточно простой список имен компонентов (см. листинг 1). Начиная с VB4, этот файл получил расширение VBP, а состав и структура хранящейся в нем информации существенно изменились (см. листинг 2).
Программирование макросов на VBA в Excel
Листинг 1. Фрагмент типичного MAK-файла
в VB3 содержит список компонентов проекта
Листинг 2. Фрагмент типичного VBP-файла в VB5, содержащий список компонентов проекта.
C:WINDOWSSYSTEMSTDOLE2.TLB#Standard OLE Types
C:PROGRAM FILESCOMMON FILESMICROSOFT SHAREDDC:PROGRAM _
FIL#Microsoft DAO 2.5 Object Library
Идея, которую мы собираем воплотить, заключается в следующем. Работая в Excel, мы будем последовательно открывать интересующие нас файлы проектов и формировать таблицу со всеми используемыми в этих приложениях компонентами, а потом на основе этой информации создадим итоговую сводную таблицу (см. рис. 1) Такая задача может решаться несколькими способами. Например, вы можете написать необходимые процедуры на VB5 и вызвать Excel в качестве сервера ActiveX. Однако здесь мы покажем, как написать эти процедуры в среде самого Excel, используя VBA.
ПРИМЕЧАНИЕ. Пусть читателей не смущает заголовок “Count of Файл” на рис.1. Мы специально хотели продемонстрировать возможные языковые проблемы, когда русскоязычное приложение делается в английской версии офисного пакета — английские слова в данном случае формируются автоматически мастером создания сводных таблиц.
Создание своих собственных процедур
Теперь приступим к написанию кода процедур данного модуля (окончательный вариант кода приведен в листинге 3). Обратите внимание, что создавать новые процедуры можно либо используя команду Insert|Procedure, либо введя первую строку заголовка процедуры (Sub ИмяПроцедуры) непосредственно в окне кода модуля.
Листинг 3. Текст модуля кода PivotVBP.
Attribute VB_Name = “PivotVBP”
‘установка заголовков таблицы
‘ LoadProjectFile формирует исходную
‘ таблицу компонентов и возвращает результат
If LoadProjectFile Then ‘есть хотя бы один проект
‘формирование сводной таблицы
Selection.Offset(0, 1) = “Тип”
Selection.Offset(0, 2) = “Файл”
Function LoadProjectFile() As Boolean
‘ Формирование в цикле исходной таблицы
‘ Значение функции при выходе:
‘ True — был выбран хотя бы один файл проекта
‘ False — нет ни одного файла
Dim File, FileDet, FileNum%, txt$, Project$
Dim ProjectPath$, Path$
Dim Files As New Collection
Set Files = New Collection
‘ выводит диалоговое окно для выбора файла проекта
File = Application.GetOpenFilename( _
FileFilter:=”VB Project Files(*.mak;*.vbp), _
*.mak;*.vbp”, Title:=”Загрузить файл VB-проекта”)
‘ если пользователь нажал Cancel — выход из процедуры
If File = False Then Exit Do
Call FileNameTest(ProjectPath$, Path$, Project$)
Open File For Input As FileNum%
Input #FileNum%, txt$
‘ определяет, какой тип файла был открыт, а затем
‘ вызывает соответствующую процедуру
If InStr(txt$, “=”) Then ‘ VBP-файл
Call LoadVBP(FileNum%, Project$, Files)
Seek FileNum%, 1 ‘ MAK-файл
Call LoadMAK(FileNum%, Project$, Files)
‘ помещает информацию из файла проекта в
‘ коллекцию Files, а затем из коллекции Files
‘ в электронную таблицу
For Each File In Files
For Each FileDet In File
Private Sub LoadMAK(FileNum%, Project$, Files)
Dim txt$, FileType$
‘ выборка описаний компонентов из MAK-файла
‘ (до VB3 включительно) и формирование коллекции Files
Do While Not EOF(FileNum%)
Input #FileNum, txt$
Select Case ParseString(txt$, “.”, 2)
Case “frm”: FileType$ = “Форма”
Case “bas”: FileType$ = “Модуль”
Case “vbx”: FileType$ = “Элемент управления”
Case Else: FileType$ = “”
‘добавить описание компонента в список Files
Call FilesAdd(Files, Project$, FileType$, txt$, txt$)
Private Sub LoadVBP(FileNum%, Project$, Files)
Dim txt$, itm$, FileN$, FileType$
‘ выборка описаний компонентов из VBP-файла (VB4 и выше)
‘ и формирование коллекции Files
Do While Not EOF(FileNum%)
Input #FileNum, txt$
itm$ = ParseString(txt$, “=”, 1)
txt$ = LCase(ParseString(txt$, “=”, 2))
‘ определение имени FileN$ и типа файла FileType$
Select Case itm$
Case “Object”: FileType$ = “Элемент управления”
FileN$ = ParseString(txt$, “;”, 2)
Case “Reference”: FileType$ = “Ссылка”
FileN$ = ParseString(txt$, “#”, 4)
Case “Module”: FileType$ = “Форма”
FileN$ = ParseString(txt$, “;”, 2)
Case “Form”: FileType$ = “Модуль”: FileN$ = txt$
Case Else: FileType$ = “”
‘добавить описание компонента в список Files
Call FilesAdd(Files, Project$, FileType$, FileN$, txt$)
‘ Формирование сводной таблицы
‘ Эта процедура создана с помощью режима записи
‘ макрокоманды в ходе работы с Мастером создания
‘ сводных таблиц. В полученном при этом программном коде
‘ мы добавили такие две строки:
‘ и изменили аргумент SourceData в этом операторе:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Selection, TableDestination:=””, _
‘ Следующие операторы остались без изменений:
RowFields:=Array(“Тип”, “Файл”), ColumnFields:=”Проект”
Private Sub FilesAdd(Files, Project$, FileType$, FileN$, txt$)
Dim Path$, Filename$
‘ формирование коллекции Files — описаний компонентов
If FileType$ <> “” Then ‘ задан компонент
‘ выделение имени файла из полного
Call FileNameTest(FileN$, Path$, Filename$)
‘ добавить описание компонента (проект, тип, имя
‘ файла) в коллекцию Files
Files.Add Array(Project$, FileType$, Filename$), txt$
VBA Excel: что это такое, как пользоваться объектами и применить макросы
Первая версия Excel появилась в 1985 году. С этого времени ПО продолжать развиваться, в результате чего появился vba excel. Что это такое и для чего можно использовать, стоит рассмотреть более подробно.
Что это такое
При создании программы Excel использовался специальный технический язык Visual Basic for Application. Первоначально его устанавливают в процессор Microsoft. Технические специалисты отмечают, что данный язык легко изучить и использовать в работе.
Однако существует нюанс, о котором должен знать каждый пользователь: не все версии могут быть совместимы. Это связано с тем, что функционально VBA связана с новой версией продукта, при этом в старых программах таких возможностей нет. Кроме того, код открыт для посторонних пользователей, поэтому его легко изменить. Однако посредством IBM Lotus Symphony можно задать комбинацию пароля, чтобы просматривать документы смог только один пользователь.
Объекты, коллекции, свойства и методы
Для пользователей, которые планируют в будущем использовать VBA, необходимо разобраться с ключевыми понятиями и узнать, что такое объект. Обратившись к программе Excel, здесь в данном случае объектами являются:
Все объекты создают определенную иерархию, в которой подчиняются друг другу. Имеющийся Application должен соответствовать продукту Excel. Так, например, чтобы использовать определенную ячейку, пользователь должен задать определенный путь к ней.
Коллекция – это группа объектов, принадлежащих к тому же классу с форматом записи ChartObjects. Отдельные составляющие также принадлежат категории объектов.
Еще одно понятие, с которым важно ознакомиться, это «Свойства». Каждый объект имеет обязательную характеристику. Для Range, как правило, Value и Formula.
Методы – это задаваемые команды, которые требуется осуществить пользователю. Если требуется заполнить команду в форме VBA, то в обязательном порядке нужно проставлять точки, отделяя объект. В качестве примера: Cells (1,2).Select. Данная команда предполагает, что требуется выбрать ячейку под номером 1.2. Если значение было задано неверно или потребуется удалить все содержимое ячейки, то необходимо задать команду Selection.ClearContents.
Как начать этим пользоваться
Для начала необходимо создать файл в формате Excel и сохранить на рабочем столе. Важно, чтобы файл поддерживал макросы. Далее нужно перейти к VB. Для этого потребуется зажать комбинацию из клавишей «Alt+F1». Потом требуется следовать инструкции:
- Рядом с Excel в верхней части можно найти специальное меню;
- Из перечисленных функций выбрать «Mudule»;
- Сохранить и нажать на изображение с надписью «floppy disk»;
- Вписываем код.
Формат кодировки должен выглядеть в таком формате: Sub program-‘код-End Sub.
Внимание: строка, в которой должен прописываться код будет выделена зеленым цветом. Перед тем, как прописывать кодировку, крайне важно поставить апостроф. Он означает, что после шифра должен проставляться определенный комментарий.
Это все, что понадобиться, чтобы сформировать инструмент VBA. Для пользователей, которые имеют представление о том, как использовать Visual Basic, весь процесс займет минимум времени. Но процесс также не предполагает сложностей для тех пользователей, которые ранее не сталкивались с решением подобной задачи.
Макросы в Excel
Макросом называют специальный код, который записывается на специальном языке VBA. Их можно создавать в ручном или автоматическом режиме. Для этого потребуется использовать специальный макроредактор.
Программа, которая записывает каждое действие, произведенное в программе Excel, преобразует их в специальный язык VBA. Код, который создается посредством макрорекодера, легко поддается редактированию и удобен в использовании. Важно отметить, что макрос можно использовать неограниченное количество раз. Даже если пользователь не знает, каким образом нужно обращаться с VBA, он может максимально автоматизировать весь процесс.
Чтобы самостоятельно создать макрос, рекомендуется использовать следующую инструкцию:
- Кликнуть правой кнопкой мыши на созданную ленту и жать на функцию настройки. С его помощью можно открыть параметры диалогового окна:
- В открывшемся окне появится параметр настройки ленты. В правой части находятся основные функции, где потребуется поставить галочку напротив разработчика:
- Подтвердить действие, нажимая кнопку «ОК».
Если все действия выполнены в правильной последовательности, в основной меню должно появиться дополнительное меню Разработчик, как указано на скриншоте:
Чтобы воспользоваться расширенными функциями таблицы Excel, стоит попробовать самостоятельно записать макрос. Он будет самостоятельно выбирать ячейку и формировать в ней заданный текст. Для этого потребуется сделать следующие шаги:
- Перейти в созданную вкладку разработчика;
- В меню кода выбрать функцию записи. Должно открыться отдельное окно
- В окне записи потребуется задать имя для будущего макроса. Однако важно отметить, что важно выполнить несколько правил, чтобы правильно задать имя: нельзя использовать пробелы. Поэтому лучше задавать слитное название, даже если оно состоит из двух слов. Также разрешается использовать нижний дефис.
- Если возникает необходимость, то можно задать комбинацию из клавиш. В таком случае нужно одновременно зажать следующее сочетание: «Ctrl+Shift+N». Важно запомнить, что данная комбинация при использовании способна отменить абсолютно любые заданные действия посредством горячих клавиш. Если ранее для автоматического сохранения использовалось сочетание «Ctrl+S», то более для этой функции его нельзя использовать.
- Перед тем, как сохранить документ, нужно проверить, правильно ли задана опция. Это необходимо для того, чтобы удостовериться в том, что макрос является составляющей выбранной книги. Он будет присутствовать даже при автоматическом сохранении или если потребуется с кем-то поделиться файлом.
- Как дополнение можно придумать описание к файлу. Чаще всего это требуется, если пользователь планирует создать несколько макросов подряд, чтобы в будущем не запутаться, какой из них за что отвечает;
- Остается нажать «OK». После этого макрос начнет автоматически записывать информацию в книге. Если нужно остановить запись, то кликаем по соответствующей кнопке рядом с меню разработчика;
- Выбираем другую ячейку, например А2;
- Вводим любой текст и нажимает кнопу «Enter»
- Далее приостанавливаем запись.
На этом все действия выполнены и остается использовать макрос при необходимости. Стоит отметить, что с его помощью каждый пользователь сможет понять, каким образом устроен макрорекодер.
Чтобы ознакомиться с полученными результатами, достаточно перейти в VB в Excel:
- Открыть вкладку разработчика
- В меню кодировки открыть Visual Basic.
Чтобы перейти к кодировке VBA или его редактор, можно использовать комбинацию из клавиш Alt и F11. Для ознакомления, каким образом выглядит сам редактор, представлен следующий скриншот:
Меню Visual Basic
Когда записывается новый макрос, должны происходить следующие изменение:
- Появление нового модуля
- Появление набранного названия
- Добавляется новая функция.
Чтобы показалось окно ввода, остается дважды кликнуть на модуль, чтобы появилась следующая запись, как показано на скриншоте:
Важно принять к сведению, что самостоятельно записанный код эффективен в определенных областях применения (финансовой или математической). Кроме того, зачастую макрорекодер может к имеющемуся инструментарию предлагать совершенно новые и ненужные функции.
Функции VBA
На примере инструкций, которые были приведены ранее, можно сделать вывод о том, что использование VBA и макросов не предполагает больших сложностей. Общее количество функций, которые были созданы специально для программирования на языке VBA, составляет 160 возможностей. Все функции подразделяются на несколько групп:
Решение задачи с использованием программирования на VBA
Чтобы просчитать возможные отклонения, требуется ввести обозначения и получить заданную формулу. Для того, чтобы просчитать процентное обозначение потребуется использовать следующую формулу: (F-P)разделить на P*100, суммарное значение должно получиться при действии (F-P). Полученные результаты рекомендуется сразу вводить в таблицу.
Чтобы сформировать в надлежащем виде прогноз, нужно применить следующую формулу: ItogP=ItogP + P, ItogF=ItogF+ F. Для просчета отклонений требуется использовать (ItogF – ItogP) / ItogP * 100. Как только все действия будут выполнены, значения вносятся в ячейки, а значит присваивать переменные нет необходимости.
Чтобы осуществить запуск, в первую очередь требуется сохранить созданную книгу. Также стоит обращать внимание и на другие условия. Так, например, добавлять строки придется каждый раз после введения новых обозначений в таблице.
Если требуется создать таблицу для отчетности, то по данной функции достаточно кликнуть все раз. Как только работа подойдет к завершению, нужно нажать на закончить, а затем переключиться в соответствующее окно программы Excel.
Заключение
Выше были изучены все аспекты, каким образом можно использовать макросы, для чего нужен VBA в Excel и каким образом его применять. Стоит отметить, что бла ря простым инструкциям, научиться этим действиям сможет любой начинающий пользователь, который ранее не имел возможность изучать Visual Basic. Подобные возможности помогают в организации финансовой отчетности и проведения математических функций.
Источник: activation-keys.ru
Для создания макроса в Excel используется специальное окно — редактор программ на языке программирования Visual Basic for Applications (VBA).
По умолчанию, вкладка, отвечающая за работу с макросами не отображается в ленте Excel, поэтому ее надо активировать.
Для этого в ленте переходим на вкладку «Файл» -> «Параметры» -> «Основные» и включаем в правой части окна флажок Показывать вкладку «Разработчик» на ленте:
Добавление вкладки «Разработчик» на ленту
Теперь на ленте вкладок доступна новая вкладка «Разработчик», в которой доступны основные инструменты для работы с макросами:
Вкладка «Разработчик» на панели вкладок
Для перехода в редактор VBA нажимаем кнопку «Visual Basic».
Интерфейс редактора VBA выглядит следующим образом:
Интерфейс редактора VBA
Данная часть программы не переводится на русский язык, поэтому для работы здесь придется пользоваться английскими командами.
Макросы в Excel хранятся в модулях, доступ к которым осуществляется с помощью окна Project Explorer. В зависимости от поставленных задач выделяют 3 типа модулей:
Виды модулей для макросов
- Модуль листа — модуль используется для макросов, которые выполняются при наступлении определенных условий на данном листе (например, пересчет формул, копирование листа и т.д.);
- Модуль «ЭтаКнига» — модуль используется для макросов, которые выполняются при наступлении определенных условий в данной книге (например, при окрытии книги, при сохранении и т.д.);
- Обычные модули — модуль предназначен для любых макросов.
Рассмотрим стандартный вид макроса из любого модуля:
Код макроса на языке программирования VBA
На конкретном примере разберем структуру макроса:
- Начало макроса — любой макрос начинается с оператора Sub, за которым следует имя макроса и список аргементов;
- Тело макроса — команды, которые будут выполняться при запуске макроса (в данном конкретном примере макрос каждой ячейке из выделенной области присваивает случайное число от 1 до 100 и заменяет формулы на значения);
- Конец макроса — любой макрос заканчивается оператором End Sub.
Без знаний основ программирования и наличия опыта работы с VBA могут возникнуть определенные трудности при создании макросов. В этом случае можно начать с более простого инструмента и создать макрос в Excel с помощью макрорекордера.
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
Источник: tutorexcel.ru