2 семестр / vba_2002

Размер файлов

Рабочие книги Excel (включая надстройки) всегда страдали от серьезной проблемы — огромного размера. Легко заметить, что размер файлов со временем увеличивается, даже если в рабочую книгу не добавляется содержимое. Это особенно справедливо, если удаляется большое количество кода, который заменяется на другой код. Внесение изменений в содержимое рабочих листов также приводит к увеличению размеров файлов.

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

1.Создайте резервную копию приложения и разместите ее в безопасном месте.

2.Активизируйте редактор VBE и экспортируйте все компоненты проекта, которые содержат код VBA (модули, модули кода, диалоговые окна UserFonm и, возможно, ThisWorkbook, рабочие листы и модули диаграмм). Запишите информацию об именах файлов и их расположении.

3.Создайте новую рабочую книгу.

4.Скопируйте содержимое всех рабочих листов из первоначального приложения в рабочие листы новой рабочей книги. Особенно внимательно отнеситесь к именованным диапазонам в рабочей книге (их необходимо создать повторно).

5.Импортируйте компоненты, которые были экспортированы в п. 2.

6.Откомпилируйте код.

7.Если это необходимо, присоедините панели инструментов, которые использовались в первоначальной рабочей книге.

8.Сохраните новую рабочую книгу.

9.Проверьте новую рабочую книгу, чтобы удостовериться в том, что вами не потеряны компоненты.

Высока вероятность того, что созданный файл будет намного меньше, чем оригинал. Изменение размера зависит от ряда факторов, но, например, мне не удавалось уменьшить размер файла XLA более чем на 55%.

Особые проблемы, связанные с использованием надстроек

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

Правильная установка

В некоторых случаях требуется правильно установить надстройку (с помощью команды Сервис^Надстройки, а не Файл^Открыть). Этот раздел посвящен методам проверки правильности установки надстройки. Если надстройка установлена неправильно, то код VBA выполняет правильную установку (при необходимости добавляя надстройку в коллекцию Addlns) и испольнует окно сообщения для предоставления пользователю информашш о выполненных действиях

Часть V. Совершенные методы программирования

559

Листинг 21.1 содержит модуль кода для данного примера (предназначен для объекта ThisWorkbook). Такая техника основЕ>тается на возникновении события Addlnlnstall перед событием Open.

Листинг21.1. Обеспечениеправильнойустановкинадстройки

Dim InstalledProperly As Boolean

Private Sub Workbook_Addinlnstall()

InstalledProperly = True

End Sub

Private Sub Workbook_Open()

If Not ThisWorkbook.IsAddin Then Exit Sub

	If Not InstallProrerly Then
1	Добавление в коллекцию Addlns
	If Not InAddlnCollectionfThisWorkbook) Then _
	Addlns.Add FileName:-ThisWorkbook.FullName
'	Установка	надстройки
	AddlnTitle	= GetTitle(ThisWorkbook}

Application.EnableEvents = False

Addlns(AddlnTitle).Installed = True

Application.EnableEvents = True

' Информирование пользователя Msg в ThisWorkbook.Name & _

"установлена как надстройка."

Msg = Msg Ь "Используйте команду Надстройки." MsgBox Msg, vblnformation, AddlnTitle

End If End Sub

Если надстройка установлена правильно, то выполняется процедура Workbook_ Addlnlnstall. Эта процедура устанавливает булеву переменную InstallPropertly в значение True. Если надстройка открыта с помощью команды Файл^Открыть, то процедура Workbook_AddInI:nstall не запускается, а переменная InstallProperly имеет значение, принятое по умолчанию—False.

При выполнении процедуры Workbook_Open сначала проверяется, является ли рабочая книга надстройкой. Если это не так, то процедура завершается. Если рабочая книга является надстройкой, то процедура проверяет значение переменной InstalledProperly. В правильно установленной надстройке процедура завершает свое выполнение. В противном случае код вызывает определенную пользователем функцию, которая указывает нз принадлежность надстройки к коллекции Addlns. Если это не так, то надстройка добавляется в коллекцию. После этого процедура завершается установкой файла в виде надстройки и созданием окна сообщения для пользователя. В целом результат использования этого кода заключается в корректной установке надстройки даже в случае выполнения команды Файл^Открыть. Кроме того, при выборе команды Файл^Открыть пользователь получает краткий урок по использованию надстроек.

В предыдущем коде были применены две пользовательские функции, которые отображены в листинге21.2. Функция InAddlnCollection возвращает значение True, если рабочая

Глава 21. Создание ииспользование надстроек

книга, переданная в качестве аргумента, является членом коллекции Addlns. Функция GetTitle возвращает значения свойства T i t l e рабочей книги надстройки.

Листинг 21.2. Замена отсутствующих свойств надстроекExcel

Function InAddInCollection{wb) As Boolean

Dim Item As Addln

For Each Item in Addlns

If Iten.Name = wb.Name Then

InAddlnCollection = True

End If

Next Item

End Function

Function GetTitle(wb) As String

Dim Item As Addln

GetTitle ""

For Each Item In Addlns

If Item.Name = wb.Name Then

GetTilte = Item.Title

End If

Next Item

End Function

Ссылки на другие файлы

Если надстройка использует другие файлы, то необходимо быть особенно внимательным при распространении приложения. Нельзя ничего предположить о файловой структуре системы, в которой пользователь запускает приложение. Самый прострой метод— потребовать от пользователя расположить все файлы приложения в одной папке. Затем используйте свойство Pat h рабочей книги приложения, чтобы указать ссылки на необходимые файлы.

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

Sub GetHelpO

Dim Path As String

Path = ThisWorkbook.Path Application . Help Path & "\USER.HLP"

End Sub

Если приложение использует функции API стандартных библиотек Windows, то можно ожидать, что Windows найдет все необходимые файлы. Однако если используется собственная библиотека DLL, то лучшим выходом будет сохранение этой библиотеки в папке win-- dows\System (которая может называться и по-другому). Для определения пути к папке System воспользуйтесь функцией Windows API GetSystemDirectory.

Указание правильной версии Excel

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

ЧастьV.Совершенныеметодыпрограммирования

561

Sub CheckVersionO

If Val(Application.Version) < 10 Then

MsgBox "Выполняется только в Excel 2002 и вше . "

ThisWorkbook.Close

End if

End Sub

Свойство V e r s i o n объекта A p p l i c a t i o n возвращает строку. Например, свойство может возвратить значение 10.0а. Эта процедура использует функцию Val, которая игнорирует все элементы после первой буквы.

Дополнительная информация о совместимости приводится в главе 26.

Резюме

Вданной главе речь шла о надстройках: их создании и управлении с помощью VBA.

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

562	Глава 21. Создание и использование надстроек

Разработка

приложений

Главы этой части освещают важные вопросы создания приложений для конечных пользователей. Главы 22 и 23 предоставляют информацию по созданию собственных

панелей инструментов и меню. В главе 24 описываются разные способы построения интерактивного справочного руководства. В главе 25 подробно рассматриваются вопросы создания приложений для пользователей.

Создание собственных панелей инструментов

Панели инструментов являются важным элементом поль'швательского интерфейса. Их можно найти практически во веек современных продуктах. В Excel не существует недостатка в панелях инструментов. Данная программа поставляется с более чем четырьмя десятками встроенных панелей инструментов. Кроме того, вы имеете возможность создавать новые панели инструментов и модифицировать существующие (и вручную, и с помощью кода VBA). В настоящей главе рассмотрены вопросы создания и модификации панелей

инструментов.

О командных панелях

Начиная с Excel 97, Microsoft использует совершенно иной способ управления панелями инструментов по сравнению с предыдущими версиями. С технической точки зрения, панель инструментов известна как объект CommandBar. На самом деле тот обьект, который обычно называется панелью инструментов, является частью командной панели.

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

•Строка меню. К встроенным строкам меню относятся Worksheet Menu Bar (Строка меню листа) и Chart Menu Bar {Строка меню диаграммы) (дополнительная информация о них приведена в главе 23).

•Контекстное меню — это меню, которое появляется при щелчке на объекте правой кнопкой мыши (дополнительная информация также содержится в главе 23).

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

Управление панелями инструментов

Впредставленном далее списке отображен набор изменений, которые можно вносить

впанели инструментов Excel,

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

•Добавление элементов управления на встроенные панели инструментов. На любую панель инструментов можно добавить любое количество элементов управления. Эги элементы управления могут быть кнопками, определенными пользователем, а также кнопками других панелей инструментов. Кроме того, на панель инструментов можно добавлять инструменты, которые встроены в Excel.

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

•Изменение функциональности элементов управления на встроенных панелях инструментов. Данная задача выполняется путем добавления собственного макроса к элементу управления.

•Изменение изображения, которое отображается на элементах управления. Excel содержит ограниченный, но функционально насыщенный редактор кнопок панелей инструментов. Для изменения изображений существует несколько дополнительных способов.

Перечисленные выше модификации можно производить вручную с помощью диалогового окна Настройка. Это диалоговое окно отображается при выборе нескольких команд Excel: Вид^Панели инструментов^Настройка, Сервис^Настройка. Вы также можете щелкнуть правой кнопкой мыши на любой панели инструментов и выбрать команду Настройка. Помимо этого, можно изменять панели инструментов с помощью кода VBA.

Не бойтесь экспериментировать с панелями инструментов. Если привести встроенную панель инструментов в нерабочее состояние, то всегда можно восстановить ее в прежнее состояние по умолчанию. Достаточно открыть диалоговое окно Настройка и перейти на вкладку Панели инструментов. После этого выберите панель инструментов в списке и щелкните на кнопке Сброс.

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

Перед началом работы с собственными панелями инструментов важно понимать общие принципы управления панелями инструментов. Представленная далее информация удивит не одного разработчика.

Хранение панелей инструментов

Панели инструментов могут быть присоединены к рабочим листам (XLS) или к файлам надстроек (XLA), что упрощает распространение собственных панелей инструментов вместе с приложениями (дополнительная информация приводится в разделе "Распространение панелей

ЧастьVI.Разработкаприложений

565

инструментов" далее в этой главе). К рабочей книге можно присоединить любое количество панелей инструментов. Когда пользователь открывает файл, присоединенная панель инструментов отображается автоматически. Исключение составляет случай, когда в программе существует панель инструментов с таким же именем. В результате новая панель инструментов не будет заменять старую. Excel хранит информацию о панелях инструментов в файле XLB, который находится в корневой папке Windows (\Windows или \WINNT}. Точное имя и расположение этого файла зависит от конкретней версии Excel.

Файлы XLB являются довольно важными, Предположим, ваш коллега использует рабочую книгу Excel, которая содержит пользовательскую панель инструментов. При открытии рабочей книги эта панель инструментов будет отображена на экране. После изучения документа принимается решение о том, что данная рабочая книга не нужна. Однако при выходе из Excel пользовательская панель инструментов будет сохранена в файле XLB. Если внести в панель инструментов любое изменение (от редактирования встроенной панели инструментов до создания новой панели инструментов), то при выходе из Excel файл XLB будет сохранен повторно. По причине того, что файл XLB загружается в память при запуске Excel, длительность запуска и выхода из Excel будет увеличиваться с ростом объема файла XLB. Кроме того, панели инструментов занимают память и системные ресурсы. Таким образом, лучшим выходом будет отключение панелей инструментов, которые никогда не используются. Для этого необходимо воспользоваться командой Вид^Панели инструментов^Настройка.

Если вы установили Excel 2002 поверх ранней версии Excel, то старый файл XLB не мог интегрироваться в новую версию. Другими словами, при обновлении программы все изменения в панелях инструментов теряются.

Когда панели инструментов работают некорректно

Подход Excel к хранению панелей инструментов может привести к возникновению проблем. Предположим, что вами разработано приложение, которое использует дополнительную панель инструментов и эта панель инструментов присоединена к рабочей книге приложения. Когда конечный пользователь впервые открывает рабочую книгу приложения, панель инструментов отображается на экране. При закрытии Excel панель инструментов сохраняется в файле XLB в компьютере пользователя. Если пользователь каким-либо образом изменит панель инструментов (например, случайно удалит кнопку), то в следующий раз при запуске приложения панель инструментов в исходном виде отображена не будет. Вместо этого вы увидите модифицированную версию пользовательской панели инструментов, где отсутствует удаленная кнопка. Другими словами, панель инструментов со всеми необходимыми элементами управления, подключенная к рабочей книге, отображаться не будет, так как в окне программы открыта другая панель инструментов, которая имеет такое же имя. В большинстве случаев такое поведение не является приемлемым.

К счастью, можно создать код VBA, который будет препятствовать развитию этого сценария. Основной смысл заключается в том, чтобы не допустить добавления неправильной панели инструментов в коллекцию панелей инструментов. Такого результата можно добиться при создании панели инструментов "на лету" в момент открытия приложения и удаления панели инструментов перед завершением работы. При этом панель инструментов никогда не будет сохранена в файле XLIJ в компьютере пользователя. Может показаться, что создание панели инструментов "на лету" — это чрезвычайно медленный процесс. Далее в этой главе показано, что создание панелей инструментов с помощью кода VBA выполняется довольно быстро.

566	Глава 22. Создание собственных панелей инструментов

Ручное управление панелями инструментов и кнопками

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

Важно понимать, что все изменения, которые вносятся в панели инструментов (как встроенные, так и пользовательские), являются "постоянными". Другими словами, изменения остаются в силе даже после перезапуска Excel, кроме того, о,чи не связаны с определенной рабочей книгой. Чтобы вернуть панель инструментов

впервоначальное состояние, ее необходимо сбрось (восстановить).

Орежиме модификации командных панелей

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

•ВидоПанелиинструментов^Настройка.

•Сервис^Настройка.

•Щелкните правой кнопкой мыши на панели инструментов или меню и выберите ко-

манду Настройка из контекстного меню.

Когда Excel переходит в режим модификации командных панелей, отображается диалоговое окно Настройка. В этом диалоговом окне можно вносить любые изменения в меню и панели инструментов, При щелчке на меню и панелях инструментов отображается удобное контекстное меню (рис. 22.1). После применения проведенных модификаций щелкните на кнопке Закрыть в диалоговом окне Настройка.

Диалоговое окно Настройка содержит три вкладки.

•Панели инструментов. Находятся все доступные панели инструментов, включая пользовательские. В этот список входят две строки меню: строка меню листа и строка. меню диаграммы, а также пользовательские строки меню.

•Команды. Перечисляет все доступные команды, разбитые по категориям. Эта вкладка может использоваться для добавления новых, элементов на панель инструментов или панель меню.

•Параметры. Позволяет указывать различные параметры, которые относятся к панелям инструментов и меню. В число этих параметров входят размеры значков, состояние экранных подсказок, а также способ анимации эффектов меню.

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

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

Часть VI, Разработка приложений

567

Рис. 22. J. В режиме модификации командных панелей допускается вноситьизменениявовсепанелиинструментовименю

Скрытие и отображение панелей инструментов

Вкладка Панели инструментов диалогового окна Настройка представляет имя каждой панели инструментов (как встроенной, так и пользовательской), Установите флажок напротив имени панели инструментов для того, чтобы отобразить эту панель на экране. Сброс флажка напротив имени приводит к скрытию панели инструментов. Изменения вступают в силу немедленно.

Создание новой панели инструментов

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

На рис. 22.2 показана пользовательская панель инструментов, которая создана вручную. Эта панель инструментов называется Пользовательская панель и содержит наиболее часто используемые инструменты форматирования. Обратите внимание, что данная панель инструментов кроме обычных кнопок содержит меню раскрывающегося списка.

Рис. 22.2. Пользовательская панель инструментов, котораясодержитинструментыформатирования

Переименование пользовательской панели инструментов

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

Удаление пользовательской панели инструментов

Выберите панель инструментов в списке и щелкните на кнопке Удалить. Удалить встроенную панель инструментов невозможно.

568	Глава 22. Создание собственных панелей инструментов