Оглавление
- Совет 1. Используйте листы книги макросов для хранения констант
- Совет 2. Используйте скрытую форму
- Совет 3. Печатайте на матричном принтере
- Совет 4. Полезный шаблон для Word
- Совет 5. Автоматизируйте вставку закладок
- Совет 6. Макросы для работы со списками
- Совет 7. Используйте разнообразные типы элементов управления Office
- Совет 8. Использование Word и Excel для подготовки графических файлов
- Совет 9. Запишите макрос переопределения языка
Совет 1. Используйте листы книги макросов для хранения констант
Особенность хранения макросов в Excel заключается в том, что все они записаны в модулях, связанных с определенной рабочей книгой. Даже макросы и функции пользователя, разработанные для общего применения, привязаны к книге PERSONAL.XLS, которая всегда незримо присутствует при вызове приложения. Для доступа к этой книге достаточно выполнить команду Окно/Отобразить… и выбрать в открывшемся списке окно книги Personal (обычно это имя - единственное в списке скрытых окон). Поскольку все современные макросы пишутся на VBA, то листы рабочей книги макросов, как правило, не используются.
Вместе с тем листы рабочей книги представляют собой удобную среду хранения разнообразной информации с уникальными возможностями. Так, только здесь допускается запись символьных строк с различной кодировкой. Например, одновременно можно использовать кириллицу, латинский и балтийский алфавиты, а также специальные символы. Ни в каких других файлах, кроме непосредственно «рабочей» поверхности документов Excel или Word, подобное невозможно, не говоря уже о текстах программных модулей. Например, русскоязычные пользователи в странах Балтии не могут использовать смесь родного и государственного языка в базах данных Access, формах Outlook, заимствованных элементах управления и программных модулях, предусматривающих указание единственного набора символов.
Подобное ограничение можно обойти при программировании в Excel, если вынести символьные константы на листы книги. С помощью этого приема можно, например, написать многоязыковые функции «сумма прописью» или «дата прописью», а также программы - переводчики для несовместимых алфавитов.
Кроме символьных констант, на листах книги макросов можно хранить справочники, организованные в виде списков, и разного рода внедренные элементы, например держать коллекцию иконок для последующего использования в качестве источников для метода PasteFace в процедурах, создающих динамические кнопки на панелях управления Office.
Совет 2. Используйте скрытую форму
При работе над проектом VBA часто возникает желание использовать элементы управления ActiveX общего назначения, такие как CommonDialog, таймер, MAPI, FTP и др. Чаще всего такие элементы не удается внедрить непосредственно в тело офисного документа, а диалоговые формы интерфейсом разработки могут быть не предусмотрены.
В этом случае удачным решением может стать включение в проект формы, которая никогда не будет видна пользователю, то есть постоянно скрытой формы. Чаще всего подобная форма не будет содержать и никакого кода поскольку не будут происходить события, связанные с вмешательством пользователя .
В редакторе VBE создадим новую форму и «перетащим» на нее необходимые элементы управления, причем такие, что даже при отображении формы остаются невидимыми. На рис. 1 показан пример подобной формы, на которой расположены CommonDialog, Internet Transfer Control, IETimer и пара элементов MAPI.
Рис. 1. Скрытая форма с «невидимыми» элементами управления
Теперь у нас в проекте появились дополнительные объекты, к которым весьма просто обращаться
Рассмотрим работу со скрытой формой на примере использования CommonDialog для открытия и сохранения текстовых файлов. Напишем пользовательскую функцию, возвращающую имя файла и полный путь, выбранный пользователем в окне стандартного диалога, а также True - в случае успешного завершения операции и False - если пользователь нажмет кнопку Cancel.
Function ComDlgFile(ByRef strName As String, _ ByRef strPath As String, _ strTitle As String, _ blnOpen As Boolean) As Boolean '-- Show Common Dialog and set FileName & FullPath Load FormHidden On Error Resume Next With FormHidden .CommonDialog.CancelError = True .CommonDialog.FileName = strName .CommonDialog.DialogTitle = strTitle .CommonDialog.Filter = _ "All Files(*.*)/*.*/Text Files(*.txt)/*.txt" If blnOpen Then .CommonDialog.ShowOpen Else .CommonDialog.ShowSave End If If Err = 0 Then On Error GoTo 0 strName = .CommonDialog.FileTitle strPath = .CommonDialog.FileName ComDlgFile = True Else ComDlgFile = False End If End With Unload FormHidden End Function
Обратите внимание, что все действия выполняются с объектом, заключенным в контейнер FormHidden. Это не что иное, как наша скрытая форма, которой присвоено подобающее имя.
Прежде чем обращаться к объектам формы, ее нужно загрузить. Делается это с помощью инструкции Load. При работе с «нормальной» формой в этом месте обычно используется метод Show. В конце функции предусмотрено выполнение инструкции Unload, освобождающей память от формы и ее объектов.
Обращение к функции может быть таким:
Sub DialogSetFile() '-- Show Common Dialog Dim strName As String Dim strPath As String strName = "" strPath = "" If ComDlgFile(strName, strPath, "Pick Up File", True) Then MsgBox "Name:" & Chr(9) & strName & Chr(13) _ & "Path:" & Chr(9) & strPath, vbExclamation Else MsgBox "Cancel By User", vbCritical End If End Sub
Использование скрытой формы весьма удобно также и для тиражирования. Элементы управления не привязаны к документам, поэтому всю конструкцию можно экспортировать с помощью команд Импорт и Экспорт файла контекстного меню проекта в редакторе VBE.
Совет 3. Печатайте на матричном принтере
Одним из самых распространенных применений классических приложений Office является печать документов, отформатированных в виде бланков. Хорошо если бланк представляет собой стандартный документ предприятия и хранится на компьютере в виде шаблона, - тогда его можно заполнить и быстро отпечатать целиком в заданном количестве экземпляров, используя, например, лазерный принтер.
Однако существует целый ряд достаточно сложных документов, которые требуется оформлять на специальных бланках, отпечатанных в типографии. Это прежде всего таможенные декларации и международные транспортные накладные, которые изготавливаются по специальной технологии с применением копирующего слоя между страницами. Такие бланки рассчитаны на использование печатающих устройств ударного типа. За счет этого достигается одновременное тождественное заполнение всех необходимых копий документа.
Такое удобство при печати имеет и свою оборотную сторону - трудность использования обычных форм на основе документов Word или Excel - из-за отсутствия режима выборочной печати полей ввода с сохранением их пространственного расположения на странице. Поэтому часто приходится наблюдать, что подобные документы печатаются при помощи специально разрабатываемых программ (обычно под DOS) или на старых добрых пишущих машинках.
Вместе с тем не составляет труда использовать всю мощь приложений Office и в этом случае. Достаточно только предусмотреть несложную процедуру подготовки документа к печати. Тогда работа с бланком может выглядеть так, как показано на рис. 2.
Рис. 1. Скрытая форма с «невидимыми» элементами управления
Принцип работы макроса «Подготовка к печати» заключается в выделении только необходимых для печати полей, что эквивалентно сокрытию всех вспомогательных элементов формы. Самое простое решение - это сделать копию станицы и просто удалить все лишнее. Поскольку «лишними» в данном случае окажутся рамки, заливка и графические элементы, то такой макрос можно сделать универсальным - не зависящим от вида бланка документа. Вот этот макрос, разработанный для Microsoft Excel:
Sub PrePrint()
'-- Копирование формы и удаление элементов оформления
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.Unprotect
Cells.Select
'-- удаляем заливку
Selection.Interior.ColorIndex = xlNone
'-- удаляем рамки
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'-- удаляем графические элементы (надписи, линии и пр.)
If ActiveSheet.Shapes.Count > 0 Then
ActiveSheet.Shapes.SelectAll
Selection.ShapeRange.Delete
End If
ActiveSheet.Range("A1").Select
End Sub
Единственная тонкость, которую следует учитывать, заключается в следующем: здесь предполагается, что все элементы, не предназначенные для печати, отличаются от обычного текста. Поэтому текстовые комментарии, заголовки и названия полей следует оформлять в виде графических элементов - надписей.
Совет 4. Полезный шаблон для Word
Очень часто приходится слышать сетования пользователей на громоздкие коды HTML-документов, которые генерирует Microsoft Word. Однако это можно существенно поправить, даже не прибегая к специальным разработкам. Первое, что необходимо сделать при использовании Word в качестве инструмента для подготовки HTML-документов, это правильно подобрать шаблон. Идея заключается в том, чтобы ограничиться только стандартными стилями, присущими HTML-документу, и не переопределять их в Word. Тогда результирующий код не будет содержать многих «лишних» тэгов, а надлежащее форматирование можно будет применить потом, с помощью CSS ( Cascading Style Sheets - каскадные таблицы стилей. - Прим.ред.)
Для изготовления «нормального» шаблона HTML-документа достаточно иметь чистую HTML-страницу с минимумом информации, например создать новую страницу с помощью FrontPage:
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1251"> <title>FrontPage</title> </head> <body> </body> </html>
Если такую страницу открыть в Word, то список доступных стилей пополнится стандартным набором стилей HTML, так, как это показано на рис. 3.
Рис. 3. Стандартные стили HTML-документа
Теперь остается сохранить страницу в качестве шаблона документа и использовать ее всегда, когда предполагается формирование документа HTML. Целесообразно даже сохранить такую страницу в качестве шаблона Normal, чтобы привыкнуть к работе со стандартными стилями современных документов. Единственная неприятность заключается в наличии трех стандартных стилей Word: Заголовок 1, 2 и 3. Поскольку эти стили являются системными, их нельзя удалить из шаблона. Остается только запомнить, что в HTML-документе для выделения заголовков следует использовать стили H1-H6, а стили Заголовок 1, Заголовок 2 и др. вообще лучше не применять.
Совет 5. Автоматизируйте вставку закладок
Иногда в документах Word требуется задавать большое количество закладок (bookmarks), особенно если готовится обширный документ HTML, в котором закладки будут играть роль объектов Anchor - элементов для организации системы перекрестных ссылок.
В подобных случаях использование стандартной команды Вставка/Закладка… достаточно утомительно, поскольку требует придумывания имен, к тому же удовлетворяющих правилам именования подобных объектов.
Для упрощения этого процесса лучше написать специальный макрос, который полностью задает закладку одним нажатием командной кнопки или комбинации «быстрых» клавиш. Для определения закладки необходимо сделать две вещи: выделить диапазон и присвоить ему надлежащее имя. Можно ускорить этот процесс, установив собственные правила именования, например использовав в качестве имени выделенный текст:
Sub SetBookmark() '-- Вставка закладки для выделенного текста Dim sName As String sName = LCase(Trim(Selection.Text)) If Len(sName) < 1 Then MsgBox "Bookmark not valid", vbCritical Exit Sub End If '-- замена недопустимых символов sName = ConvertName(sName) With ActiveDocument.Bookmarks '-- проверка на первую цифру, что недопустимо If IsNumeric(Left(sName, 1)) Then sName = "a_" & sName .Add Range:=Selection.Range, Name:=sName .DefaultSorting = wdSortByName .ShowHidden = False End With End Sub
В приведенном макросе над выделенным текстом производятся некоторые преобразования. Во-первых, с помощью функции ConvertName недопустимые в имени пробелы, знаки препинания, специальные символы заменяются символом «подчеркивание»:
Private Function ConvertName(sName As String) '-- Замена недопустимых символов Dim sRes As String Dim sChar As String Dim i As Integer sRes = "" For i = 1 To Len(sName) sChar = Mid(sName, i, 1) Select Case sChar Case " ", ".", "-", ",", ";", ":", "'", "!", "?", """" sRes = sRes & "_" Case Else sRes = sRes & sChar End Select Next ConvertName = sRes End Function
А во-вторых, проверяется, не является ли первый символ цифрой, что также недопустимо для имени закладки, однако это часто происходит, например при необходимости вставить закладки для пронумерованных разделов документа. Если подобное имеет место, то к имени закладки слева добавляются символы «a» (от слова Anchor). Если придерживаться единых правил формирования имен как функции от контекста, то также легко можно будет автоматизировать и формирование гиперссылок на основе оглавления. Можно придумать и другое правило именования, например: имя документа плюс последовательный номер закладки в коллекции.
Совет 6. Макросы для работы со списками
Популярной формой хранения и обработки информации являются списки на листах Excel. Они позволяют использовать все операции, свойственные базе данных, не требуя особых усилий по проектированию. Напомним, что список отличается от общей таблицы тем, что все его строки имеют одинаковую структуру и формат, а это позволяет рассматривать их как записи базы данных. К тому же первая строка выделяется оформлением и содержит заголовки столбцов (или полей записи). Еще одна особенность списков заключается в том, что каждая строка, как правило, содержит формулы.
При работе с такой структурой важнейшими операциями, влияющими на производительность труда пользователя, осуществляющего ввод данных, являются команды добавления и удаления строк. Стандартные команды Excel, выполняющие подобные действия, достаточно громоздки, так как требуют либо однозначного выделения диапазонов, либо его уточнения в форме диалога. Кроме того, стандартные команды вставки выполняют добавление строк выше диапазона выделения, тогда как при массовом вводе было бы естественнее заполнять список последовательно сверху вниз. Что же касается формул, то их приходится постоянно копировать.
Логичным выходом в данной ситуации является применение специальных макросов, выполняющих добавление и удаление строк-записей. Как показывает опыт разработки многочисленных приложений со списками, целесообразно иметь три подобных макроса: один будет добавлять «чистую» запись ниже строки, на которой находится курсор ввода (в произвольной колонке), второй будет удалять целиком несколько строк, задаваемых произвольным выделением, третий, подобно первому, будет добавлять запись, но при этом копировать содержимое предыдущей записи. Это оправданно, поскольку часто в потоке ввода следуют записи, имеющие одинаковые значения нескольких реквизитов.
Для добавления записей в список прежде всего необходимо разобраться с наличием строк-прототипов, содержащих необходимые формулы и форматирование. Проблема заключается в выборе места, где должны храниться такие прототипы. Возможными вариантами здесь являются строки выше заголовка списка, строки ниже самой последней записи или строки на отдельном листе. Первые две возможности не совсем удобны, поскольку по правилам список должен отделяться от прочего контекста по крайней мере одной пустой строкой, что повлияет на внешний вид документа. Если же скрыть строки прототипа, например расположенные выше списка, то это приведет к копированию и вставке «скрытой» записи. Если затем установить для вставленной строки «нормальную» высоту, то будет отменено свойство автоматического подбора высоты строки при заданном в формате переносе длинных символьных значений, а это иногда бывает необходимо. Таким образом, самым надежным местом для хранения прототипов остается отдельный лист настроек, который во время работы пользователя можно скрывать целиком. В этом случае нужно только не забывать об имеющемся прототипе при изменении форматирования готового списка.
Сами по себе макросы довольно просты и «программируются» в основном с помощью «макрорекордера». Естественно, перед записью макросов необходимо строкам-прототипам присвоить имена диапазонов:
Sub InsertCopy()
'-- Вставка копии текущей строки
Dim strSelAddr As String '-- позиция курсора
Dim objCurRange As Range '-- текущий объект Range
ActiveCell.Select
strSelAddr = Selection.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Cells(2, 1))
'-- проверка на допустимость операции
If InStr(1, strSelAddr, "-", vbTextCompare) Then
MsgBox "This Selection Not Valid", vbCritical
Else
Set objCurRange = Selection
objCurRange.EntireRow.Select
Selection.Copy
Selection.Range("A2").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
'-- рекомендуемая позиция ввода
Selection.Range("A1").Select
End If
End Sub
Sub DeleteRows()
'-- Удаление строк
Dim strSelAddr As String '-- позиция курсора
Dim intAns As Integer '-- ответ пользователя
Selection.Select
strSelAddr = Selection.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Cells(2, 1))
'-- проверка на допустимость операции
If InStr(1, strSelAddr, "-", vbTextCompare) Then
MsgBox "This Selection Not Valid", vbCritical
Exit Sub
End If
intAns = MsgBox("Delete Selection" & Chr(13) _
& "Are You Sure", vbQuestion + vbOKCancel)
If intAns = vbOK Then Selection.EntireRow.Delete
End Sub
Sub InsertRow()
'-- Вставка строки Item
Dim strSelAddr As String '-- позиция курсора
Dim objCurRange As Range '-- текущий объект Range
Dim strSheetName As String '-- имя активного листа
Dim varSection As Variant '-- номер раздела
ActiveCell.Select
strSelAddr = Selection.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Cells(1, 1))
'-- проверка на допустимость операции
If InStr(1, strSelAddr, "-", vbTextCompare) Then
MsgBox "This Selection Not Valid", vbCritical
Else
strSheetName = ActiveSheet.Name
Set objCurRange = Selection
'-- на всякий случай копируем общий образец строки
Sheets("Settings").Range("List1Item").Copy
'-- копируем образец строки для текущего листа
On Error Resume Next
Sheets("Settings").Range(strSheetName & "Item").Copy
objCurRange.EntireRow.Select
varSection = Selection.Range("A1").Value
Selection.Range("A2").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
'-- рекомендуемая позиция ввода
Selection.Range("A1").Select
End If
End Sub
Единственное существенное отличие этих макросов от записей «макрорекордера» заключается в наличии проверок допустимости операций. Так, для макросов InsertCopy и DeleteRows позиция курсора при вызове не должна находиться на строке заголовков, тогда как при вставке строк-прототипов ниже позиции курсора в макросе InsertRow - такое вполне допустимо. В приведенном примере предполагается, что строка заголовков - первая. Если это не так, следует откорректировать значение параметра RelativeTo. Проверка позиции курсора производится достаточно экзотическим способом: по наличию отрицательного значения относительного адреса. Но это совсем не обязательно - можно просто сравнивать абсолютные номера строк.
Для повышения надежности в макросе DeleteRows предусмотрен диалог для подтверждения пользователем «разрушающей» операции, а в макросе InsertRow - возможность отсутствия строки прототипа для данного списка. Эта проблема решается путем вставки прототипа «по умолчанию». Подобный прием позволяет иметь в книге несколько листов со списками одинаковой структуры, без необходимости создания для каждого из них строки-прототипа.
Совет 7. Используйте разнообразные типы элементов управления Office
В справочной документации, посвященной панелям управления Office, внимание уделяется только таким элементам управления, как Button и ComboBox. Вместе с тем офисная коллекция CommandBarControls поддерживает большое количество разнообразных элементов управления, которые задаются с помощью свойства Type.
Одним из таких малоиспользуемых элементов управления является CommandBarControl с типом msoControlEdit. Это не что иное, как аналог обычного текстового поля TextBox, присутствующего практически во всех формах.
Наряду с этим ControlEdit позволяет существенно разнообразить интерфейс прикладной разработки. Предположим, что разрабатывается приложение, предусматривающее выбор товара из списка. Можно создать контекстное меню, «всплывающее» в позиции курсора, в котором с помощью поля ControlEdit запрашивается количество товара. В этом случае офисное меню будет выполнять функцию удобной формы ввода.
Другой пример, показанный на рис. 4, использует поле ControlEdit для хранения нераспределенной суммы в задаче учета оплаты по счетам. Сложность такой задачи заключается в опасности получить оплату сразу по нескольким счетам, что потребует приостановки алгоритма регистрации оплаты и перемещения фокуса с формы на основной документ. Вследствие модальности форм Office 97 такая задача вообще неразрешима. Хотя в Office 2000 эта проблема устранена после введения свойства Show Modal, допускающего потерю фокуса формой, - решение с плавающей панелью управления выглядит более изящным.
Рис. 4. Использование панели инструментов в качестве формы
Создать такую панель можно с помощью кода, приведенного ниже:
Private Sub Workbook_Open() '-- Создание динамической панели Оплата Dim cbPayment As CommandBar Set cbPayment = Application.CommandBars _ .Add(Name:="Payment", _ Position:=msoBarFloating, _ Temporary:=True) With cbPayment .Controls.Add Type:=msoControlButton .Controls(1).Caption = "Payment" .Controls(1).Style = msoButtonIconAndCaption .Controls(1).OnAction = "PaymentContinue" .Controls(1).FaceId = 1643 .Controls.Add Type:=msoControlEdit .Controls(2).Text = "0.00" .Controls(2).Enabled = False .Visible = False End With End Sub
Обратите внимание на тип элемента управления msoControlEdit и на его основное рабочее свойство Text, не описанное в справочной документации.
Совет 8. Использование Word и Excel для подготовки графических файлов
Приложения Office все чаще используются в качестве инструмента для подготовки Web-страниц. И если формирование законченных HTML-документов с помощью только Word или Excel пока еще вызывает заслуженные нарекания пользователей, то ничто не мешает применить эти приложения для изготовления отдельных элементов, например полноценных графических файлов.
Так, в Excel, для объектов Chart предусмотрен метод Export, позволяющий сохранять диаграммы в виде отдельных графических файлов:
Charts(1).Export _ OutputFileName:="my_chart.gif", FilterName:="GIF"
В Word, хотя там и нет явного метода для экспорта графических элементов, данная возможность также существует. При выполнении стандартного метода SaveAs при указании формата HTML все объекты Shape экспортируются в виде отдельных графических файлов, причем в Word 97 это происходит в формате GIF, а в Word 2000 - в JPEG. Можно воспользоваться этим обстоятельством, а ненужный HTML-документ удалить:
Sub ExportAllShapes() '-- Экспорт всех рисунков в виде графических файлов Dim objShape As Shape Dim strActiveDocName As String Dim strNewDocName As String If ActiveDocument.Shapes.Count <= 0 Then Exit Sub strActiveDocName = ActiveDocument.Name Documents.Add strNewDocName = ActiveDocument.Name '-- копируруем все рисунки в новый документ For Each objShape In Documents(strActiveDocName).Shapes objShape.Select Selection.Copy Documents(strNewDocName).Activate Selection.PasteSpecial Link:=False, _ DataType:=wdPasteMetafilePicture, _ Placement:=wdFloatOverText, DisplayAsIcon:=False Next '-- сохраняем новый документ в формате HTML ActiveDocument.SaveAs FileName:=strNewDocName & ".htm", _ FileFormat:=114, LockComments:=True, _ Password:="", AddToRecentFiles:=False, _ WritePassword:="", _ ReadOnlyRecommended:=False, _ EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, _ SaveFormsData:=False, SaveAsAOCELetter:=False ActiveWindow.Close Documents(strActiveDocName).Activate '-- удаляем ненужный файл Kill strNewDocName & ".htm" End Sub
Microsoft Office обладает богатой коллекцией картинок Clipart, хранящихся в формате WMF. С помощью этого нехитрого макроса, записанного «макрорекордером», можно очень легко осуществлять «пакетное» преобразование графических форматов для этих файлов.
Особо следует остановиться на графических объектах WordArt. Эти объекты не являются объектами типа Shape, и приведенная выше процедура будет их игнорировать. Для того чтобы использовать эти объекты на Web-страницах, достаточно преобразовать объекты WordArt в рисунки с помощью команд копирования и специальной вставки.
Совет 9. Запишите макрос переопределения языка
При подготовке в Word больших статей на русском языке, содержащих термины с использованием латинского алфавита, неоднократно возникают ситуации, когда документ «теряет» выбор языка, установленного по умолчанию. При этом фоновая программа проверки правописания начинает подчеркивать правильно написанные русские слова. Чтобы быстро восстановить утраченное свойство, запишите следующий макрос и вызывайте его с помощью комбинации «быстрых» клавиш:
Sub SetRussian() Dim objAll As Range Set objAll = Selection.Range objAll.WholeStory objAll.LanguageID = wdRussian End Sub