Разработка приложений с помощью Excel 97 и VBA
Разработка приложений
с помощью Excel 97 и VBA
Создание приложения, осуществляющего контроль
за использованием компонентов проектов
В КомпьютерПресс № 8’98 мы уже рассказывали о том, как разрабатывать приложения с помощью Excel 97 и VBA. Сегодня мы продолжаем разговор на эту тему и хотим акцентировать ваше внимание на некоторых других вопросах создания законченных приложений на базе Excel на примере еще одной реальной программы.
В ходе разработки различных приложений у вас накапливается все больше повторно используемых компонентов, и на контроль за ними уходит довольно много времени. К примеру, как определить, какой файл используется в проекте OBJobAgent — SPREAD.VBX или его новая версия SSVBX25.VBX? Где используется модуль OBORD32 — в проекте OBSuper или совместно с проектом AvailAgent? Подобных вопросов огромное множество.
Чтобы облегчить управление повторно используемыми компонентами, применяются хранилища (репозитарии) исходного кода, однако многие небольшие организации не работают с ними. В качестве “упрощенного” решения можно порекомендовать воспользоваться Excel 97 для формирования сводных таблиц, в которых отражено использование компонентов в каких-либо проектах.
Файлы проектов VB представляют собой обычные ASCII-файлы, содержащие пути и имена файлов, из которых состоит проект. До версии VB 3.0 включительно описание проекта хранилось в MAK-файлах и включало достаточно простой список имен компонентов (см. листинг 1). Начиная с VB4, этот файл получил расширение VBP, а состав и структура хранящейся в нем информации существенно изменились (см. листинг 2).
Листинг 1. Фрагмент типичного MAK-файла
в VB3 содержит список компонентов проекта
SHOPVW2.FRM
C:\WINDOWS\SYSTEM\CMDIALOG.VBX
SHOPVIEW.BAS
..\OBORD3.BAS
..\SOTDTS.BAS
..\SUNOP.BAS
C:\WINDOWS\SYSTEM\THREED.VBX
...
FMULTSCH.FRM
FPRTRAV.FRM
ProjWinSize=26,541,257,481
ProjWinShow=2
Command=”\\anl_ver1\database”
IconForm=”FrmMain”
Title=”MapView”
ExeName=”ANALYT.EXE”
Листинг 2. Фрагмент типичного VBP-файла в VB5, содержащий список компонентов проекта.
Type=Exe
Object={00028C01-0000-0000-0000-000000000046}#1.0#0;DBGRID32.OCX
Reference=*\G{00020430-0000-0000-C000-000000000046}#2.0#0# _
C:\WINDOWS\SYSTEM\STDOLE2.TLB#Standard OLE Types
Reference=*\G{00025E04-0000-0000-C000-000000000046}#3.5#0# _
C:\PROGRAM FILES\COMMON FILES\MICROSOFT SHARED\DC:\PROGRAM _
FIL#Microsoft DAO 2.5 Object Library
Object={F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.1#0; _
COMDLG32.OCX
...
Module=OBOrd32; ..\OBOrd32.bas
Module=OBStock32; ..\Obstock32.bas
Form=..\Aboutbox32.frm
Module=Spread32; ..\Spread32.bas
Module=MAPI32; ..\Mapi32.bas
IconForm=”frmMain”
Startup=”frmMain”
HelpFile=””
Title=”MapView”
ExeName32=”Analyt.exe”
...
Идея, которую мы собираем воплотить, заключается в следующем. Работая в Excel, мы будем последовательно открывать интересующие нас файлы проектов и формировать таблицу со всеми используемыми в этих приложениях компонентами, а потом на основе этой информации создадим итоговую сводную таблицу (см. рис. 1) Такая задача может решаться несколькими способами. Например, вы можете написать необходимые процедуры на VB5 и вызвать Excel в качестве сервера ActiveX. Однако здесь мы покажем, как написать эти процедуры в среде самого Excel, используя VBA.
Рис. 1
ПРИМЕЧАНИЕ. Пусть читателей не смущает заголовок “Count of Файл” на рис.1. Мы специально хотели продемонстрировать возможные языковые проблемы, когда русскоязычное приложение делается в английской версии офисного пакета — английские слова в данном случае формируются автоматически мастером создания сводных таблиц.
Создание своих собственных процедур
ШАГ 1.
Запустите Excel, задайте имя новому проекту PivotVBP.xls и перейдите в среду разработки VB (Alt+F11). Создайте там новый BAS-модуль (команда Insert|Module) и назовите его также PivotVBP (в поле Name окна Properties).
Теперь приступим к написанию кода процедур данного модуля (окончательный вариант кода приведен в листинге 3). Обратите внимание, что создавать новые процедуры можно либо используя команду Insert|Procedure, либо введя первую строку заголовка процедуры (Sub ИмяПроцедуры) непосредственно в окне кода модуля.
Листинг 3. Текст модуля кода PivotVBP.
Attribute VB_Name = “PivotVBP”
Option Explicit
Sub CreateVBProjCrossRef()
‘установка заголовков таблицы
SetUpHeadings
‘ LoadProjectFile формирует исходную
‘ таблицу компонентов и возвращает результат
If LoadProjectFile Then ‘есть хотя бы один проект
‘формирование сводной таблицы
CreatePivotTable
End If
End Sub
Sub SetUpHeadings()
Selection = “Проект”
Selection.Offset(0, 1) = “Тип”
Selection.Offset(0, 2) = “Файл”
Selection.Offset(1, 0).Select
End Sub
Function LoadProjectFile() As Boolean
‘ Формирование в цикле исходной таблицы
‘ компонентов проектов.
‘ Значение функции при выходе:
‘ True - был выбран хотя бы один файл проекта
‘ False - нет ни одного файла
Dim File, FileDet, FileNum%, txt$, Project$
Dim ProjectPath$, Path$
Dim Files As New Collection
‘
LoadProjectFile = False
Do
Set Files = New Collection
‘ выводит диалоговое окно для выбора файла проекта
File = Application.GetOpenFilename( _
FileFilter:=”VB Project Files(*.mak;*.vbp), _
*.mak;*.vbp”, Title:=”Загрузить файл VB-проекта”)
‘ если пользователь нажал Cancel - выход из процедуры
If File = False Then Exit Do
‘
ProjectPath$ = File
Call FileNameTest(ProjectPath$, Path$, Project$)
FileNum% = FreeFile
Open File For Input As FileNum%
Input #FileNum%, txt$
‘ определяет, какой тип файла был открыт, а затем
‘ вызывает соответствующую процедуру
If InStr(txt$, “=”) Then ‘ VBP-файл
Call LoadVBP(FileNum%, Project$, Files)
Else
Seek FileNum%, 1 ‘ MAK-файл
Call LoadMAK(FileNum%, Project$, Files)
End If
‘ помещает информацию из файла проекта в
‘ коллекцию Files, а затем из коллекции Files
‘ в электронную таблицу
For Each File In Files
For Each FileDet In File
Selection = FileDet
Selection.Offset(0, 1).Select
Next
Selection.Offset(1, -3).Select
Next
LoadProjectFile = True
Loop
End Function
Private Sub LoadMAK(FileNum%, Project$, Files)
Dim txt$, FileType$
‘ выборка описаний компонентов из MAK-файла
‘ (до VB3 включительно) и формирование коллекции Files
Do While Not EOF(FileNum%)
Input #FileNum, txt$
txt$ = LCase(txt$)
Select Case ParseString(txt$, “.”, 2)
Case “frm”: FileType$ = “Форма”
Case “bas”: FileType$ = “Модуль”
Case “vbx”: FileType$ = “Элемент управления”
Case Else: FileType$ = “”
End Select
‘добавить описание компонента в список Files
Call FilesAdd(Files, Project$, FileType$, txt$, txt$)
Loop
End Sub
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$ = “”
End Select
‘добавить описание компонента в список Files
Call FilesAdd(Files, Project$, FileType$, FileN$, txt$)
Loop
End Sub
Sub CreatePivotTable()
‘ Формирование сводной таблицы
‘
‘ Эта процедура создана с помощью режима записи
‘ макрокоманды в ходе работы с Мастером создания
‘ сводных таблиц. В полученном при этом программном коде
‘ мы добавили такие две строки:
Selection.Offset(-1, 0).Select
Selection.CurrentRegion.Select
‘ и изменили аргумент SourceData в этом операторе:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Selection, TableDestination:=””, _
TableName:=”PivotTable1"
‘ Следующие операторы остались без изменений:
ActiveSheet.PivotTables(“PivotTable1”).AddFields _
RowFields:=Array(“Тип”, “Файл”), ColumnFields:=”Проект”
ActiveSheet.PivotTables(“PivotTable1”). _
PivotFields(“файл”).Orientation = xlDataField
End Sub
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$
End If
End Sub
Мы будем писать код процедур по принципу “сверху-вниз” — классического метода структурного программирования — от высокого уровня компонентов к более низкому. Правильность такого подхода уже давно доказана — необходимо думать хотя бы о коллегах-разработчиках, которые будут потом разбираться в наших программах, корректировать их или использовать в своих проектах. Но, двигаясь “сверху-вниз”, нужно всегда помнить о готовых компонентах нижнего уровня, которые будут составлять в значительной мере основу приложения.
ШАГ 2. Первой напишем процедуру CreateVBProjCrossRef, которая является головной для всего нашего приложения. Она сначала устанавливает заголовки, которые будут использоваться в исходной, а потом и в сводной таблицах. Затем она обращается к функции LoadProjectFile, в которой формируется исходная таблица компонентов приложений. Если функция возвращает значение True (Истина), то это означает, что такая таблица была создана хотя бы для одного проекта. В этом случае вызывается подпрограмма, которая строит сводную таблицу.
ШАГ 3. Процедура SetUpHeadings использует объект Selection активной в данный момент электронной таблицы, чтобы присвоить названия столбцов. Обратите внимание, что метод Offset применяется для смещения вправо при заполнении заголовков для второго и третьего столбцов, а также для выделения ячейки в первом столбце первого ряда, располагающегося под заголовками.
Довольно обычным контрдоводом против программирования “сверху-вниз” является суждение о том, что это мешает поэтапному проведению отладки — приложение можно запустить на выполнение только после написания всего кода. Однако такой тезис неверен: на самом деле поэтапная отладка легко реализуется с помощью использования разнообразных “заглушек” (пустых или реализованных в упрощенном варианте процедур).
Например, на данном этапе можно просто превратить в комментарии все операторы в CreateVBProjCrossRef, оставив только обращение к SetUpHeadings. Запустите программу на выполнение и убедитесь, что заголовки формируются правильно. Обратите внимание на то, что пока они записываются в текущую позицию курсора. Может быть, вы хотите сделать очистку таблицы и установку курсора в ее начало по умолчанию? Пару необходимых для этого строк вы можете написать самостоятельно в любой момент — сейчас или позднее.
Напомним также, что при работе в среде VBA при запуске приложения на выполнение (Run/F5) управление передается компоненте (в данном случае — процедуре), которая в данный момент находится в активном окне среды. Так что перед тем, как нажать F5, установите текстовый курсор в окне кода на процедуру CreateVBProjCrossRef.
ШАГ 4. Пишем процедуру LoadProjectFile, ключевую для всего проекта — формирование исходной таблицы компонентов. Она работает в цикле, обращаясь к стандартному диалоговому окну для определения имени существующего файла (рис. 2). С этой целью используется метод GetOpenFilename объекта Application и расширения .MAK и .VBP для идентификации файлов проектов. Если пользователь вместо выбора файла (кнопка Open) нажимает Cancel, формирование таблицы заканчивается и функция возвращает в вызывающую программу значение True или False в зависимости от того, был ли выбран хотя бы один проект или нет.
Тип выбранного файла (MAK или VBP) можно определить по расширению его имени. Но мы здесь делаем по-другому: файл проекта открывается и проверяется наличие знака равенства в первой строке (см. листинги 1 и 2). Обратите внимание, что в случае MAK-файла необходимо установить указатель чтения/записи внутри файла в его начало, чтобы учесть первую строку при формировании списка компонентов.
Рис. 2
После этого вызывается соответствующая процедура — LoadMAK или LoadVBP, чтобы загрузить информацию из файла проекта в коллекцию Files, а затем поместить эту информацию из коллекции Files в электронную таблицу. Обратите внимание, что первый цикл For...Next просматривает коллекцию Files, а второй цикл For...Next — массив, содержащий детальную информацию для каждого файла.
ШАГ 5. Пишем код процедур LoadMAK, LoadVBP и FilesAdd. Разница между процедурами LoadMAK и LoadVBP заключается в том, как идентифицируется тип компонента в строках файла описания проекта. В первом случае это делается с помощью расширения имени файла, во втором – с помощью ключевого слова в начале строки. Обе процедуры используют вспомогательную подпрограмму FilesAdd, в которой производится выделение короткого имени файла и записи его описания в коллекцию Files.
ВАЖНОЕ ЗАМЕЧАНИЕ. Для выполнения некоторый операций при обработке строк символьных файлов MAK и VBP мы использовали созданные ранее процедуры ParseString и FileNameTest, которые применялись в различных наших VB-приложениях. Они записаны в модулях PARSESUB.BAS и INSTRREV.BAS, соответственно. Загрузить их в создаваемое нами приложение в среде Excel/VBA можно с помощью команды File|Import File, но при этом следует иметь в виду очень важное обстоятельство.
BAS-модуль, загруженный таким образом в Office/VBA (это относится и к Word, и к Excel), автоматически теряет логическую связь с соответствующим BAS-файлом, хранимым на диске, и становится сугубо внутренним компонентом данного приложения. Это одно из существенных отличий логики использования BAS-модулей в обычном VB и VBA. Во втором случае (с которым мы имеем дело сейчас, работая в Excel 97) изменения, сделанные в BAS-файле, никак не влияют на состояние загруженного BAS-модуля и наоборот.
Соответственно, если мы хотим в своих будущих приложениях использовать какие-то модули, сформированные в VBA, то мы должны записать их на диск с помощью специальной команды File|Export. Таким образом, замена названий команд чтения/записи модулей Add/Save на Import/Export на самом деле отражает отличия в механизме работы с модулями.
После загрузки модулей PARSESUB.BAS и INSTRREV.BAS мы увидим, что в Excel/VBA они получили тривиальные имена Module1 и Module2. Такая замена имен представляется правильным шагом, так как — повторим еще раз — это уже другие компоненты, не связанные с родительским файлами. В этом плане вариант, реализованный в Word 97, когда имени внутреннего модуля присваивается имя исходного файла (строго говоря даже не имя файла, а название в первой строке файла Attribute VB_Name — если она имеется), представляется не очень правильным, так как создает иллюзию однозначного соответствия между этими двумя компонентами.
ШАГ 6. Учитывая все это, после загрузки модулей PARSESUB.BAS и INSTRREV.BAS (команда File|Import File), которые преобразятся в компоненты Module1 и Module2 нашего проекта, мы выполним следующие операции. Скопируем текст процедуры ParseString из Module1 в Module (выделив текст, а затем записав его в буфер обмена и восстановив оттуда). Затем удалим Module1 (оставшиеся там процедуры нам не понадобятся), а Module2 переименуем в Service (см. листинг 4).
Листинг 4. Текст модуля кода Service
Attribute VB_Name = “Service”
DefInt I-N
Sub FileNameTest(PathFile$, Path$, File$)
‘
‘ Вход: PathFile$ - полное имя файла
‘ Выход: Path$ - имя каталога
‘ File$ - имя файла
in1 = InstrReverse(PathFile$, “\”)
If in1 <= 0 Then
Path$ = “”: File$ = PathFile$
Else
Path$ = LEFT$(PathFile$, in1)
File$ = Mid$(PathFile$, in1 + 1)
End If
End Sub
Function InstrReverse(Text$, Key$)
‘
‘ поиск ПОСЛЕДНЕГО (а не первого, как в INSTR)
‘ контекста в строке, Text$ - исходная строка
‘ Key$ - разделитель в строке
in1 = InStr(Text$, Key$)
If in1 > 0 Then
Do While in1 < Len(Text$)
in2 = InStr(in1 + 1, Text$, Key$)
If in2 <= 0 Then Exit Do Else in1 = in2
Loop
End If
InstrReverse = in1
End Function
Function ParseString(s$, del$, n) As String
‘ синтаксический анализ строки на наличие разделителя,
‘ возвращает n-ое значение
Dim pos As Long, i As Integer, pos2 As Long
‘
ParseString = s$
pos = InStr(s$, del$)
If pos Then ‘ если есть del$
If n = 1 Then
ParseString = LEFT$(s$, pos - 1)
Else
For i = 1 To n - 1 ‘ подсчет элементов данных
pos2 = InStr(pos + 1, s$, del$)
If pos2 = 0 Then ‘ конец строки
If i = n - 1 Then
ParseString = Trim(Mid$(s$, pos + _
Len(del$)))
‘ длина разделителя
Else
ParseString = “”
‘ n-ый элемент данных не найден
End If
Exit Function
End If
ParseString = Trim(Mid$(s$, pos + Len(del$), _
pos2 - pos - Len(del$)))
pos = pos2
Next
End If
ElseIf n > 1 Then
ParseString = “” ‘ n-ый элемент данных не найден
End If
End Function
Эти замысловатые операции нужны для того, чтобы подчеркнуть отсутствие физической связи между компонентами проекта Excel и исходными BAS-файлами. Такой способ применения повторно используемого программного кода (копирование процедур из модуля в модуль) мы категорически не рекомендуем при работе в обычном VB, но при работе в VBA он вполне допустим, а порой (как в данном случае) — даже полезен. Мы обсудим эти проблемы, связанные со спецификой работы с повторно используемыми процедурами в VB и VBA, в последующих статьях. А пока продолжим создание нашего приложения.
ШАГ 7. Проверим работоспособность нашего приложения на этом этапе. В головной процедуре CreateVBProjCrossRef оставим знак комментария только у обращения к CreatPivotTable. Запустите программу на выполнение и убедитесь, что по мере выбора имен файлов VB-проектов в среде Excel формируется исходная электронная таблица, содержащая их компоненты (рис. 3).
Рис. 3
Создание сводной таблицы
Приступаем к созданию процедуры CreatePivotTable, которая формирует сводную таблицу на основе исходной таблицы компонентов. Как мы сейчас убедимся, программный код этой процедуры довольно небольшой, но писать его вручную — дело довольно утомительное. Поэтому лучше воспользоваться мастером Pivot Table Wizard, а затем модифицировать созданный при этом код в более универсальный вид.
ШАГ 8. Перейдем в среду Excel так, чтобы на экране была видна созданная нами исходная электронная таблица (рис. 3). Зададим режим записи макрокоманды с помощью команды Tools|Macro|Record New Macro (на панели Status Bar появится слово Recording). Оставим имя создаваемой макрокоманды,
предлагаемой по умолчанию. Затем командой Data|PivotTable Report запустим Мастер создания сводных таблиц, который будет последовательно предлагать выполнить четыре операции с помощью выводимых им диалоговых окон.
В первом окне оставим предлагаемый по умолчанию вариант создания сводной таблицы — Microsoft Excel list or database — и нажмем кнопку Next (рис. 4). Следующий шаг также пройдем, ничего не меняя.
Рис. 4
Третий шаг — самый важный: формирование структуры сводной таблицы. На экране появится диалоговое окно с изображением схемы таблицы (рис. 5).
Рис. 5
С правой стороны будут нарисованы три кнопки, которые соответствуют трем колонкам текущей электронной таблицы. В качестве названий кнопок используется содержимое ячеек первой строки таблицы, которое интерпретируется как названия столбцов. Перетаскивая мышью названия правых кнопок, формируем таблицу, изображенную на рис. 6.
Рис. 7
Для этого наименование “Проект” нужно поместить в поле “COLUMN”, “Тип” и “Файл” — в “ROW”, а затем “Файл” — в “DATA”.
Заключительный, четвертый шаг также пройдем, ничего не меняя (будет использоваться переключатель New worksheet) — нажав кнопку Finish. У нас появится созданная мастером сводная таблица. Теперь остановим режим записи макрокоманды — Tools|Macro|Stop Recording.
ШАГ 9. Вернемся в среду VBA. Там в окне проектов мы увидим, что у нас появился новый модуль, а в нем — процедура примерно такого вида:
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=”Sheet1!R1C1:R16C3", _
TableDestination:=””, TableName:=”PivotTable1"
ActiveSheet.PivotTables(“PivotTable1”).AddFields _
RowFields:=Array(“Тип”, “Файл”), _
ColumnFields:=”Проект”
ActiveSheet.PivotTables(“PivotTable1”). _
PivotFields(“Файл”).Orientation = x1DataField
End Sub
Скопируем ее в наш основной модуль PivotVBP, переименуем в CreatePivotTable, а модуль Module1 удалим из проекта. В приведенном выше коде видно, что первая строка использует ссылку на конкретный диапазон из 16 строк. Чтобы использовать данную процедуру для любого числа строк, необходимо добавить только две строки кода для выделения ячейки, расположенной выше на одну строку. При этом мы будем использовать метод CurrentRegion объекта Selection для выделения смежных строк и столбцов. Необходимо также изменить аргумент SourceData = Selection у метода PivotTableWizard, чтобы использовать объект Selection в качестве диапазона. Результат нашей работы приведен в листинге 3.
ШАГ 10.
Убедимся в работоспособности нашего приложения. Запустим на выполнение процедуру CreateVBProjCrossRef. Затем, последовательно выбирая файлы в окне “Загрузить файл VB-проекта”, сформируйте исходную таблицу компонентов. Завершите операцию выбора файлов, нажав в окне кнопку Cansel, и вы увидите сводную таблицу компонентов VB-проектов.
Структура Excel-приложения
ШАГ 11.
Посмотрим внимательнее на структуру созданного нами приложения — рабочей книги PivotVBP.XLS (рис.7). Группа Microsoft Excel Objects представлена двумя компонентами ThisWorkbook и Sheet1 (SourceSheet). Число компонентов Sheet — электронных таблиц — может меняться, причем и непосредственно в ходе работы приложения. Каждому объекту этой группы соответствуют программные модули (для их просмотра следует дважды щелкнуть название объекта в окне проектов), в которых можно задействовать предусмотренные для них событийные процедуры (рис.8).
Рис. 8
Записать какие-либо другие процедуры можно только в модули кода. В данном проекте мы создали два таких модуля — PivotVBP и Service. Распределяя процедуры между двумя модулями, мы хотели подчеркнуть специфику подпрограмм. В PivotVBP находятся процедуры, написанные именно в рамках создания данного приложения, и мы пока не видим потенциальной возможности применения каких-либо из них в других проектах. А вот в модуле Service записаны те процедуры, которые мы взяли готовыми из более ранних разработок.
Продолжение следует...
