Определение VBA, преимущества, возможности применения
VBA ( Visual Basic for Applications) — это диалект языка Visual Basic, расширяющий возможности Visual Basic и предназначенный для работы с приложениями Microsoft Office и другими приложениями от Microsoft и третьих фирм.
Set oWord = CreateObject(«Word.Application»)
oWord.Selection.TypeText («Привет от VBScript»)
Однако VBA обычно — самый удобный язык для работы с приложениями Office.
Главная причина проста — язык VBA встроен в приложения Office (и не только), и код на языке VBA можно хранить внутри документов приложений Office — документах Word, книгах Excel, презентациях PowerPoint и т.п. Конечно же, этот код можно запускать оттуда на выполнение, поскольку среда выполнения кода VBA (на программистском сленге — хост) встроена внутрь этих приложений.
В настоящее время VBA встроен:
У VBA есть также множество других преимуществ:
VBA — универсальный язык. Освоив его, вы не только получите ключ ко всем возможностям приложений Office и других, перечисленных выше, но и будете готовы к тому, чтобы:
VBA изначально был ориентирован на пользователей, а не на профессиональных программистов (хотя профессионалы пользуются им очень активно), поэтому создавать программы на нем можно очень быстро и легко. Кроме того, в Office встроены мощные средства, облегчающие работу пользователя: подсказки по объектам и по синтаксису, макрорекордер и т.п.
При создании приложений на VBA вам, скорее всего, не придется заботиться о установке и настройке специальной среды программирования и наличии нужных библиотек на компьютере пользователя — MS Office есть практически на любом компьютере.
Несмотря на то, что часто приложения VBA выполняются медленнее, чем бы вам хотелось, они нересурсоемки и очень хорошо работают, например, на сервере терминалов. Но, как правило, для программ на VBA особых требований про производительности и нет: для написания игр, драйверов, серверных продуктов они не используется. По моему опыту, чаще всего проблемы с производительностью VBA-приложений — это не проблемы VBA, а проблемы баз данных, к которым они обращаются. Если проблемы действительно в VBA (обычно тогда, когда вам требуется сложная математика), то всегда есть возможность написать важный код на C++ и обращаться к нему как к обычной библиотеке DLL или встраиваемому приложению (Add-In) для Word, Excel, Access и т.п.
Программы на VBA по умолчанию не компилируются и поэтому вносить в них исправления очень удобно. Не нужно разыскивать исходные коды и перекомпилировать программы.
В среде программистов-профессионалов считается, что самый короткий путь «с нуля» и программ типа «Hello, World» до профессиональных программ, которые делаются под заказ — именно через связку Office- VBA (а конечно, не через C++, Java или Delphi).
Microsoft Visual Basic for DOS, Microsoft Visual Basic for Windows
Содержание
Microsoft Visual Basic — средство разработки программного обеспечения, разрабатываемое корпорацией Microsoft и включающее язык программирования и среду разработки. Язык Visual Basic унаследовал дух, стиль и отчасти синтаксис своего предка — языка Бейсик, у которого есть немало диалектов. В то же время Visual Basic сочетает в себе процедуры и элементы объектно-ориентированных и компонентно-ориентированных языков программирования. Среда разработки VB включает инструменты для визуального конструирования пользовательского интерфейса.
Visual Basic считается хорошим средством быстрой разработки прототипов программы, для разработки приложений баз данных и вообще для компонентного способа создания программ, работающих под управлением операционных систем семейства Microsoft Windows.
Первое признание серьёзными разработчиками Visual Basic получил после выхода версии 3 — VB3. Окончательное признание как полноценного средства программирования для Windows — при выходе версии 5 — VB5. Версия VB6, входящая в состав Microsoft Visual Studio 6.0, стала по-настоящему зрелым и функционально богатым продуктом. После этого разработчики из Microsoft существенно изменили направление развития данной технологии.
Эволюция Visual Basic
Основные разновидности Visual Basic
Классический Visual Basic (версии 5-6)
Этот язык очень сильно привязан к своей среде разработки и к операционной системе Windows, являясь исключительно инструментом написания Windows-приложений. Привязка к среде заключается в том, что существует большое количество средств, предназначенных для помощи и удобства программирования: встроенный отладчик, просмотр переменных и структур данных на лету, окно отладки, всплывающая подсказка при наборе текста программы (IntelliSense). Все эти преимущества делают бесполезным и даже невозможным использование Visual Basic вне среды разработки, например в обычном текстовом редакторе.
Visual Basic for Applications (VBA)
Это средство программирования, практически ничем не отличающееся от классического Visual Basic, которое предназначено для написания макросов и других прикладных программ для конкретных приложений. Наибольшую популярность получил благодаря своему использованию в пакете Microsoft Office. Широкое распространение Visual Basic for Applications в сочетании с изначально недостаточным вниманием к вопросам безопасности привело к широкому распространению макровирусов.
Visual Basic Scripting Edition (VBScript)
Скриптовый язык, являющийся несколько усечённой версией обычного Visual Basic. Используется в основном для автоматизации администрирования систем Windows, а также для создания страниц ASP и сценариев для Internet Explorer.
Достоинства и недостатки
Достоинства
Недостатки
Критика
Однако стоит отметить, что все недостатки языка вытекают из его основного достоинства — простоты разработки графического интерфейса. Поэтому многие программисты используют Visual Basic для разработки интерфейса пользователя, а функциональность программы реализуют в виде динамически подключаемых библиотек (DLL), написанных на другом языке (чаще всего C++).
Автоматизация рутины в Microsoft Excel при помощи VBA
В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.
VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.
Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.
Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.
Поэтому, увы, будем учить Visual Basic.
Чуть-чуть подготовки и постановка задачи
Итак, поехали. Открываем Excel.
Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.
Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):
То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по Типу, потом по Производителю).
Результат, которого хотим добиться, выглядит примерно так:
Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли?
Кодим
Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».
И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».
Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:
Напишем Hello World:
Sub FormatPrice() MsgBox «Hello World!» End Sub
И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.
Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.
Примеры синтаксиса
Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит Dim i As Integer ‘ Цикл всегда состоит из нескольких строк For i = 1 To 10 res = res + CStr(i) ‘ Конвертация чего угодно в String If i = 5 Then Exit For Next i
Dim x As Double x = Val( «1.234» ) ‘ Парсинг чисел x = x + 10 MsgBox x
On Error GoTo Err ‘ При ошибке перейти к метке Err x = 5 / 0 MsgBox «OK!» GoTo ne
ne: On Error GoTo 0 ‘ Отключаем обработку ошибок
‘ Циклы бывает, какие захотите Do While True Exit Do
Loop ‘While True Do ‘Until False Exit Do Loop Until False ‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны. ‘ Val также умеет возвращать Integer Select Case LengthSqr(Len( «abc» ), Val( «4» )) Case 24 MsgBox «0» Case 25 MsgBox «1» Case 26 MsgBox «2» End Select
Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.
Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.
Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.
Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.
Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.
Кодим много и под Excel
В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.
Sub FormatPrice() Sheets( «result» ).Cells.Clear Sheets( «data» ).Activate End Sub
Работа с диапазонами ячеек
Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.
Примеры работы с Range
Sheets( «result» ).Activate Dim r As Range Set r = Range( «A1» ) r.Value = «123» Set r = Range( «A3,A5» ) r.Font.Color = vbRed r.Value = «456» Set r = Range( «A6:A7» ) r.Value = «=A1+A3»
Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:
Для упрощения работы рекомендую определить следующие функции-сокращения:
Function GetCol(Col As Integer ) As String GetCol = Chr(Asc( «A» ) + Col) End Function
Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».
Глобальные переменные
Option Explicit ‘ про эту строчку я уже рассказывал Dim CurRow As Integer Const GroupsCount As Integer = 2 Const DataCount As Integer = 3
FormatPrice
Sub FormatPrice() Dim I As Integer ‘ строка в data CurRow = 1 Dim Groups(1 To GroupsCount) As String Dim PrGroups(1 To GroupsCount) As String
Теперь надо заполнить массив Groups:
На месте многоточия
И создать заголовки:
На месте многоточия в предыдущем куске
For I2 = 1 To GroupsCount If Groups(I2) <> PrGroups(I2) Then Dim I3 As Integer For I3 = I2 To GroupsCount AddHeader I3, Groups(I3) Next I3 Exit For End If Next I2
Не забудем про процедуру AddHeader:
Перед FormatPrice
Теперь надо перенести всякую информацию в result
Подогнать столбцы по ширине и выбрать лист result для показа результата
После цикла в конце FormatPrice
Sheets( «Result» ).Activate Columns.AutoFit
Всё. Можно любоваться первой версией.
Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:
Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:
Поэтому чуть-чуть меняем код с добавлением стиля границ:
Select Case Ty Case 1 ‘ Тип .Font.Bold = True .Font.Size = 16 .Borders(xlTop).Weight = xlThick Case 2 ‘ Производитель .Font.Size = 12 .Borders(xlTop).Weight = xlMedium End Select .Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline End With CurRow = CurRow + 1 End Sub
Осталось лишь добится пропусков перед началом новой группы. Это легко:
В начале FormatPrice
Dim I As Integer ‘ строка в data CurRow = 0 ‘ чтобы не было пропуска в самом начале Dim Groups(1 To GroupsCount) As String
В цикле расстановки заголовков
If Groups(I2) <> PrGroups(I2) Then CurRow = CurRow + 1 Dim I3 As Integer
В точности то, что и хотели.
Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0CurRow = 1.
Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.
Спасибо за внимание.
Буду рад конструктивной критике в комментариях.
UPD: Перезалил пример на Dropbox и min.us.
UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.
Microsoft Visual Basic — язык программирования, а также интегрированная среда разработки программного обеспечения, разрабатываемое корпорацией Microsoft. Язык Visual Basic унаследовал дух, стиль и отчасти синтаксис своего предка — языка BASIC, у которого есть немало диалектов. В то же время Visual Basic сочетает в себе процедуры и элементы объектно-ориентированных и компонентно-ориентированных языков программирования. Интегрированная среда разработки VB включает инструменты для визуального проектирования пользовательского интерфейса, редактор кода с возможностью IntelliSense и подсветкой синтаксиса, а также инструменты для отладки приложений.
Visual Basic также является хорошим средством быстрой разработки (RAD) приложений баз данных для операционных систем семейства Microsoft Windows. Множество готовых компонентов, поставляемых вместе со средой, призваны помочь программисту сразу же начать разрабатывать бизнес-логику приложения, не отвлекая его внимание на написание кода запуска программы, подписки на события и других механизмов, которые VB реализует автоматически.
Первое признание серьёзными разработчиками Visual Basic получил после выхода версии 3. Окончательное признание как полноценного средства программирования для Windows — при выходе версии 5. Версия VB6, входящая в состав Microsoft Visual Studio 6.0, стала по-настоящему зрелым и функционально богатым продуктом.
Содержание
История развития [ ]
Visual Basic 1.0 для MS-DOS. Дизайнер форм
Visual Basic 1.0 для MS-DOS. Редактор кода
Visual Basic 1.0 для Windows
Элементы языка [ ]
Типы данных [ ]
Переменные в Visual Basic объявляются оператором Dim после которого указывается ключевое слово As и тип переменной. Если тип опущен, то переменная объявляется как Variant. Если не указать конструкцию Option Explicit в разделе деклараций, то явного объявления переменных не требуется и они автоматически будут созданы при первом использовании. Данное поведение может привести к снижению скорости выполнения программы, так как необъявленные переменные будут иметь тип Variant, а также возможны логические ошибки связанные с неверным вводом имени имеющейся переменной или риска конфликтов в приложении, когда область определения переменной не совсем ясна.
Таблица основных типов Visual Basic 6.0
Операторы [ ]
Язык Visual Basic содержит множество операторов, которые отвечают за арифметические операции, операции сравнения и присвоения, а также конкатенации.
Управляющие конструкции [ ]
Оператор ветвления [ ]
Оператор выбора [ ]
Пример: вывод различных сообщений в зависимости от значения переменной
Определение VBA, преимущества, возможности применения
VBA ( Visual Basic for Applications) — это диалект языка Visual Basic, расширяющий возможности Visual Basic и предназначенный для работы с приложениями Microsoft Office и другими приложениями от Microsoft и третьих фирм.
Однако VBA обычно — самый удобный язык для работы с приложениями Office.
Главная причина проста — язык VBA встроен в приложения Office (и не только), и код на языке VBA можно хранить внутри документов приложений Office — документах Word, книгах Excel, презентациях PowerPoint и т. п. Конечно же, этот код можно запускать оттуда на выполнение, поскольку среда выполнения кода VBA (на программистском сленге — хост) встроена внутрь этих приложений.
В настоящее время VBA встроен:
· во все главные приложения MS Office — Word, Excel, Access, PowerPoint, Outlook, FrontPage, InfoPath;
· в другие приложения Microsoft, например, Visio и M icrosoft Project;
· в более чем 100 приложений третьих фирм, например, CorelDraw и CorelWordPerfect Office 2000, AutoCAD и т. п.
У VBA есть также множество других преимуществ:
VBA — универсальный язык. Освоив его, вы не только получите ключ ко всем возможностям приложений Office и других, перечисленных выше, но и будете готовы к тому, чтобы:
· создавать полноценные приложения на Visual Basic (поскольку эти языки — близкие родственники);
· использовать все возможности языка VBScript (это — вообще урезанный VBA). В результате в вашем распоряжении будут универсальные средства для создания скриптов администрирования Windows (об этом — в конце курса), для создания Web-страниц (VBScript в Internet Explorer), для создания Web-приложений ASP, для применения в пакетах DTS и заданиях на MS SQL Server, для создания серверных скриптов Exchange Server и многое-многое другое.
VBA изначально был ориентирован на пользователей, а не на профессиональных программистов (хотя профессионалы пользуются им очень активно), поэтому создавать программы на нем можно очень быстро и легко. Кроме того, в Office встроены мощные средства, облегчающие работу пользователя: подсказки по объектам и по синтаксису, макрорекордер и т. п.
При создании приложений на VBA вам, скорее всего, не придется заботиться о установке и настройке специальной среды программирования и наличии нужных библиотек на компьютере пользователя — MS Office есть практически на любом компьютере.
Несмотря на то, что часто приложения VBA выполняются медленнее, чем бы вам хотелось, они нересурсоемки и очень хорошо работают, например, на сервере терминалов. Но, как правило, для программ на VBA особых требований про производительности и нет: для написания игр, драйверов, серверных продуктов они не используется. По моему опыту, чаще всего проблемы с производительностью VBA-приложений — это не проблемы VBA, а проблемы баз данных, к которым они обращаются. Если проблемы действительно в VBA (обычно тогда, когда вам требуется сложная математика), то всегда есть возможность написать важный код на C++ и обращаться к нему как к обычной библиотеке DLL или встраиваемому приложению (Add-In) для Word, Excel, Access и т. п.
Программы на VBA по умолчанию не компилируются и поэтому вносить в них исправления очень удобно. Не нужно разыскивать исходные коды и перекомпилировать программы.
Задание для самостоятельной работы 1: Запись макроса для автоматического ввода текста в Word
Ситуация:
Вам несколько раз в день необходимо передавать распоряжения в бухгалтерию. Каждое распоряжение должно заканчиваться строками, аналогичными представленным на рис. 1.10.
Рис. 1.10 Строки, ввод которых нужно автоматизировать
Задание:
напишите при помощи макрорекордера макрос, который бы автоматически создавал такие строки (вместо «» подставьте ваши данные).
Созданный вами макрос должен быть доступен для всех создаваемых вами документов.
Созданный вами макрос должен запускаться по нажатию кнопки с рожицей и всплывающей подсказкой «Подпись» (см. рис. 1.11):
Рис. 1.11 Веселая кнопка для пользователя
Создайте новый документ, запустите макрос на выполнение и убедитесь, что он работает
Решение:
В окне Настройка на вкладке Команды перетащите элемент Normal. NewMacros. Подпись в нужное место на панели управления. Затем щелкните по перемещенному элементу правой кнопкой мыши, в контекстном меню выберите пункт Выбрать значок для кнопки, затем выберите изображение улыбающейся рожицы. Еще раз щелкните правой кнопкой мыши по этому элементу и в контекстном меню выберите Основной стиль. Нажмите на кнопку Закрыть. Начнется запись макроса.
Введите нужный текст, а затем нажмите на кнопку Остановить запись (или в меню Сервис->Макрос выберите команду Остановить запись.
Создайте новый документ Word и убедитесь, что новая кнопка будет работать и там.
Прежде чем начать работать с редактором Visual Basic, нужно его открыть. Во всех приложениях Office это делается одинаково:
· самый быстрый способ: нажать + ;
· можно также воспользоваться кнопкой на панели инструментов Visual Basic (предварительно сделав ее видимой);
· можно вызвать редактор при возникновении ошибки в макросе;
· можно открыть готовый макрос на редактирование в диалоговом окне Макрос.
Всего в редакторе Visual Basic предусмотрены 9 окон:
· Project Explorer — окно проводника проекта. По умолчанию оно открыто и находится в левой части окна редактора Visual Basic. В нем можно просмотреть компоненты проекта и выполнить с ними множество операций. Подробнее о возможностях работы в этом окне — в следующем разделе этой главы;
· UserForm — окно формы. Появляется тогда, когда вы редактируете пользовательскую форму из окна дизайнера формы. Подробнее про пользовательские формы и работу с ними — в главе 5 этой книги.
· Toolbox — панель инструментов управления. Из него можно добавить элементы управления в форму или в документ. Появляется вместе с окном дизайнера форм и рассматривается в той же главе для формы.
· Properties — одно из самых важных окон. Через него можно просмотреть свойства элемента управления или компонента проекта и их изменить. Он нем — в этой главе.
· Code — окно программного кода. В этом окне выполняется основная работа по написанию кода макроса. При открытии программного модуля открывается автоматически. Приемы работы с этим окном будут рассмотрены в этой главе.
· Object Browser — обозреватель объектов. Необходим для получения информации о классах, доступных программе. Подробная информация — в главе 4 этой книги, которая посвящена работе с объектами.
· Watch — окно контролируемых выражений. Используется во время отладки для отслеживания значений выбранных переменных программы и выражений. Работа в этом окне, так же, как работа с окном Locals и Immediate, будет рассмотрена в главе 6, в которой рассказывается про перехват ошибок и отладку.
· Locals — окно локальных переменных. Нужно для отслеживания во время отладки значений переменных текущей процедуры.
· Immediate — возможность опять-таки при отладке выполнить отдельные строки программного кода и немедленно получить результат.
Найти какое-окно, если вы не поняли, где оно, можно очень просто: нужно выбрать его в меню View, и если оно было скрыто, оно появится в редакторе.
И еще один момент, который для многих пользователей может оказаться разочаровывающим. В русских версиях приложений Office для редактора Visual Basic предусмотрен англоязычный интерфейс. Справка по языку VBA и объектным моделям приложений Office — тоже только на английском. К сожалению, русифицированных вариантов не существует. Однако, по моим наблюдениям, знание английского языка для того, чтобы писать программы в VBA, не критично (хотя и очень полезно): программы вполне можно создавать и не зная английского
2.2 Окно Проводника проектов (Project Explorer) и структура проекта VBA
Окно проводника проектов (Project Explorer), структура проекта VBA, стандартные модули, модули классов, пользовательские формы, свойства проекта VBA
Окно Проводника проектов при первой активизации редактора Visual Basic обычно открыто. Если оно случайно было закрыто, то вызвать его можно:
· нажав на кнопку Project Explorer на панели Standard ;
В окне Project Explorer представлено дерево компонентов вашего приложения VBA.
Самый верхний уровень — это проект (Project), которому соответствует документ Word, рабочая книга Excel, презентация PowerPoint и прочие файлы, с которыми работает данное приложение. Например, если вы открыли редактор Visual Basic из Word, то в Project Explorer будут представлены все открытые в настоящее время файлы Word и еще шаблон Normal. dot. Если редактор Visual Basic открыт из Excel, то в Project Explorer будут открытые книги Excel и специальная скрытая книга PERSONAL. XLS.
Помимо того, что обычно содержится в документах Office (текст, рисунки, формулы и т. п.), каждый проект (который и есть документ) — это одновременно и контейнер для хранения стандартных модулей, модулей классов и пользовательских форм. Добавить в проект каждый из этих компонентов можно при помощи меню Insert или через контекстное меню в Project Explorer.
Стандартные модули — это просто блоки с текстовым представлением команд VBA. В нем может быть только два раздела:
· раздел объявлений уровня модуля (объявление переменных и констант уровня модуля);
· раздел методов модуля (расположение процедур и функций).
При работе макрорекордера в Word в проекте Normal. dot или текущем документе (в зависимости от места сохранения макроса) автоматически создается стандартный модуль NewMacros (в Excel — Module1), куда и записываются все создаваемые макрорекордером макросы.
В большинстве проектов VBA используется только один стандартный модуль, куда и записывается весь код. Создавать новые стандартные модули есть смысл только из следующих соображений:
· для удобства экспорта и импорта (из контекстного меню в Project Explorer). Так можно очень удобно обмениваться блоками кода между приложениями VBA (и обычного VB);
· для повышения производительности. При вызове любой процедуры модуля происходит компиляция всего модуля, поэтому иногда выгоднее разместить процедуры в разных модулях, чтобы компилировать только нужный в данный момент код;
· для улучшения читаемости. Если ваше приложение выполняет разные группы задач, то код, относящийся к каждой группе, лучше поместить в свой модуль.
Модули классов позволяют создавать свои собственные классы — чертежи, по которым можно создавать свои собственные объекты. Обычно используются только в очень сложных приложениях. Применяются они в обычных приложениях VBA редко, и здесь рассматриваться не будут.
Пользовательские формы являются одновременно хранилищем элементов управления и программного кода, который относится к ним, самой форме и происходящими с ними событиями. Подробнее про формы будет рассказано в модуле 5.
Еще один важный контейнер в Project Explorer — контейнер References (в Excel его нет), то есть контейнер ссылок. В нем показывается, ссылки на какие другие проекты (документы Word) есть в нашем проекте, и, соответственно, какие «чужие» программные модули мы можем использовать. По умолчанию в каждый проект Word помещается ссылка на Normal (то есть шаблон normal. dot) — и вы в любом файле можете использовать макросы оттуда.
Обратите внимание, что в этом контейнере — только ссылки на другие документы. Про добавление ссылок на другие объектные библиотеки мы поговорим в модуле 4.
· изменить имя проекта. Эта возможность потребуется, если у вас есть ссылки на проект с одинаковыми именами;
· ввести описание проекта, информацию о файле справки и параметры, которые будут использоваться компилятором;
· защитить проект, введя пароль. Не зная этот пароль, проект нельзя будет просмотреть или отредактировать.
И все-таки, что обычно приходится делать в окне Project Explorer?
Если вы уже создавали макросы в этом проекте (макрорекордером или вручную), то модуль будет уже создан. Его можно будет увидеть под контейнером Modules. Чтобы его открыть в окне редактора кода, достаточно щелкнуть по нему два раза мышью. Там можно будет найти созданные вами средствами макрорекордера макросы.
Внимание! Обязательно подумайте, где вам будет нужен создаваемый код — только в одном документе или во всех документах данного приложения. Если он нужен будет только в одном документе, используйте стандартный программный модуль этого документа. Если во всех, то используйте программные модули проекта Normal (в Word) или PERSONAL. XLS (в Excel).
Теперь, когда программный модуль создан (или найден), можно приступать к работе с редактором кода VBA. Об этом — в следующем разделе.
Задание для самостоятельной работы 2: Редактирование макроса
Задание:
Измените созданный вами на самостоятельной работе 1 макрос таким образом, чтобы он запрашивал фамилию ответственного исполнителя. Для этого:
впечатайте первыми строки кода макроса (над первой строкой Selection. TypeText) строки
Сохраните измененный макрос, закройте окно редактора кода и убедитесь, что макрос теперь работает по-новому.
Задание для самостоятельной работы 3.1: Работа с переменными и операторами
Подготовка:
· Создайте новую книгу Excel и сохраните ее как C:\LabVariablesOperators. xls. Введите в ячейки A1, A2 и A3 этой книги любые значения.
· Откройте редактор Visual Basic в Excel и создайте в этой книге новый стандартный модуль Module1.
· Введите в этом стандартном модуле следующий код:
Public Sub FromExcelToWord()
Set oWord = CreateObject(«Word. Application»)
oWord. Visible = True
Set oDoc = oWord. Documents. Add()
oWord. Selection. TypeText «Вставляемый текст»
· Убедитесь, что этот код работает без ошибок.
Этот код выводит в окна сообщений значения ячеек A1, A2 и A3, а затем открывает Word и впечатывает в начало нового документа строку «Вставляемый текст».
Задание:
Измените код этой процедуры таким образом, чтобы вместо строки «Вставляемый текст» выводилось значение ячеек A1, A2 и A3 вместе.
· Запустите этот код на выполнение и убедитесь, что он выполняется без ошибок. Этот код должен вставлять в ячейку A1 первого листа вашей книги текстовое значение вида «Вы нажали кнопку: 6» в зависимости от того, какая кнопка была нажата в окне сообщения.
Задание 1:
Измените код этой процедуры таким образом, чтобы вместо чисел в ячейку прописывалось строковое значение нажатой кнопки (например, «Вы нажали кнопку: Повтор»). Используйте при этом синтаксическую конструкцию IF…THEN…ELSE.
Измените вашу процедуру таким образом, чтобы она вставляла в ячейку A1 значения «Отменить», «Повторить» или «Пропустить» в зависимости от того, какая кнопка была нажата в окне сообщения. Используйте при этом синтаксическую конструкцию Select…Case.
Решение к заданию 1:
Итоговый код для вашей процедуры может быть таким:
Массивы используются для хранения в памяти множества значений. Вместо того, чтобы объявлять множество похожих друг на друга переменных, часто гораздо удобнее воспользоваться массивом.
Объявление массива производится очень просто:
Dim MyArray (2) As Integer
Такой массив может хранить три целочисленных элемента. 2 — это верхняя граница массива (upper bound). Количество элементов, которое может хранить массив, — от 0 до верхней границы включительно.
Если вам хочется, чтобы нумерация элементов в массиве начиналась с 1, то в раздел объявлений модуля нужно внести команду
В принципе, тип данных для массива можно не объявлять:
В этом случае для элементов массива будет использован тип Variant. Такой массив сможет хранить в себе элементы разных типов данных, но требования к памяти у него будут выше и работать он будет чуть медленнее.
Присвоить значение отдельному элементу массива (в нашем случае — первому) можно очень просто:
А затем это значение можно будет извлечь:
Массивы вполне могут быть многомерными:
В каждой строке многомерного массива удобно хранить данные, относящиеся к одному объекту (например имя сотрудника, уникальный номер, номер телефона). В VBScript в одном массиве может быть до 60 измерений.
Часто необходимы массивы динамические — те, размер которых можно изменять в ходе выполнения. Динамический массив объявляется следующим образом:
Dim MyArray () ‘- объявляем массив без верхней границы, эту строку можно ‘пропустить
ReDim MyArray (4) ‘ — изменяем размер массива
Команда ReDim не только изменяет размер массива, но и удаляет из него все старые значения. Чтобы старые значения сохранить, используется ключевое слово Preserve:
ReDim Preserve MyArray (7)
Однако если новый размер массива меньше, чем кол-во помещенных в него элементов, слово Preserve не поможет — часть данных все равно будет потеряна.
Массивы можно создавать и заполнять одновременно при помощи встроенной функции Array():
MyArray = Array(100, 200, 300, 400, 500)
Указывать размер массива необязательно — он будет автоматически настроен в соответствии с кол-вом передаваемых элементов.
Очистить массив можно командой Erase:
Массив фиксированной длины просто очищается, динамический массив разинициализируется — его придется инициализировать (определять размер) заново.
В динамических массивах часто не известно, сколько элементов в массиве. Для определения кол-ва элементов используется функция UBound() (если массив одномерный или вас интересует размер первого измерения, то измерение передавать не надо):
UBound (имяМассива [, измерение])
Как ни удивительно, но при программировании в VBA вам редко придется сталкиваться с массивами. Вместо них в объектных моделях приложений Office обычно используются коллекции. Коллекции — это специальные объекты, которые предназначены для хранения наборов одинаковых элементов. Например, в Word предусмотрена коллекция Documents для хранения элементов Document — то есть всех открытых документов, в Excel — коллекции Workbooks (открытые книги) и Worksheets (листы в книге) и т. п. Коллекции обычно удобнее, чем массивы: они изначально безразмерны и в них предусмотрен стандартный набор свойств и методов (метод Add() для добавления нового элемента, свойство Count для получения информации о количестве элементов, метод Item() для получения ссылки на нужный элемент)
Задание для самостоятельной работы 3.3 Работа с циклами
Подготовка:
· Создайте новый документ Word с именем C:\LabLoops. doc. Наберите в этом документе несколько предложений с текстом.
· Откройте редактор Visual Basic и создайте в этом документе новый стандартный модуль.
Задание 1:
Создайте в этом стандартном модуле процедуру ForNextSub(), которая бы выводила бы последовательно 10 окон сообщений с цифрами от 1 от 10.
Задание 2:
Создайте в этом программном модуле процедуру ForEachSub(), которая бы выводила в окна сообщений последовательно каждое слово в вашем документе LabLoops.
Коллекцию (массив) всех слов документа можно получить при помощи конструкции ThisDocument. Words. Значение каждого слова можно получить при помощи свойства Text элемента этой коллекции.
Решение к заданию 1:
Итоговый код процедуры может быть таким:
Public Sub ForNextSub()
Public Sub ForNextSub2()
Do While i Procedure. Разницы не будет никакой.
3.8.4 Передача параметров
Передача параметров процедурам и функциям в VBA, необязательные (optional) параметры, передача по ссылке (ByRef) и по значению (ByVal), применение ссылок при передаче параметров
Параметры — значения, которые передаются от одной процедуры другой. В принципе, можно обойтись и без параметров, воспользовавшись только переменными уровня модуля, но при использовании параметров читаемость программы улучшается. Чтобы процедура имела возможность принимать параметры, ее вначале нужно объявить с параметрами. Например, вот пример простой функции, которая складывает два числа и выводит результат:
Function fSum (nItem1 As Integer, nItem2 As Integer)
fSum = nItem1 + nItem2
Вызов ее может выглядеть так:
В данном случае мы объявили оба параметра как обязательные, и поэтому попытка вызвать функцию без передачи ей какого-либо параметра (например, так: MsgBox (fSum(3))) приведет к ошибке «Argument not optional» — «Параметр не является необязательным». Чтобы можно было пропускать какие-то параметры, эти параметры можно сделать необязательными. Для этой цели используется ключевое слово Optional:
Function fSum (nItem1 As Integer, Optional nItem2 As Integer)
В справке по встроенным функциям VBA необязательные параметры заключаются в квадратные скобки.
Для проверки того, был ли передан необязательный параметр, используется либо функция IsMissing (если для этого параметра был использован тип Variant), либо его значение сравнивается со значениями переменных по умолчанию (ноль для числовых данных, пустая строка для строковых и т. п.)
Вызов функции с передачей параметров может выглядеть так:
Однако здесь есть несколько моментов, которые необходимо рассмотреть.
В нашем примере мы передаем параметры по позиции, то есть значение 3 присваивается первому параметру (nItem1), а значение 2 — второму (nItem2). Однако параметры можно передавать и по имени:
nResult = fSum (nItem 1 := 3, nItem 2 := 2)
Обратите внимание, что несмотря на то, что здесь выполняется вполне привычная операция — присвоение значений, оператор присвоения используется не совсем обычный — двоеточие со знаком равенства, как в C++. При использовании знака равенства возникнет ошибка.
Конечно, вместо явной передачи значений (как у нас — 3 и 2) можно использовать переменные. Однако что произойдет с переменными после того, как они «побывают» в функции, если функция изменяет их значение? Останется ли это значение за пределами функции прежним или изменится?
Все зависит от того, как именно передаются параметры — по ссылке (по умолчанию, можно также использовать ключевое слово ByRef или по значению — нужно использовать ключевое слово ByVal).
Если параметры передаются по ссылке, то фактически в вызываемую процедуру передается ссылка на эту переменную в оперативной памяти. Если эту переменную в вызываемой процедуре изменить, то значение изменится и в вызывающей функции. Это — принятое в VBA поведение по умолчанию.
Если параметры передаются по значению, то фактически в оперативной памяти создается копия этой переменной и вызываемой процедуре передается эта копия. Конечно же, чтобы вы не сделали с этой копией, на исходную переменную это никак не повлияет и в вызывающей процедуре не отразится.
Продемонстрировать разницу можно на простом примере:
Private Sub TestProc ()
‘Объявляем переменную nPar1 и присваиваем ей значение
Dim nPar1 As Integer
‘Передаем ее как параметр nItem1 функции fSum
MsgBox (fSum(nItem1:=nPar1, nItem2:=2))
‘А теперь проверяем, что стало в нашей переменной nPar1, ‘после того, как она побывала в функции fSum:
Function fSum(nItem1 As Integer, nItem2 As Integer)
‘Используем значение переменной
fSum = nItem 1 + nItem 2
Проверьте, что будет, если поменять строку объявления функции
Function fSum(nItem1 As Integer, nItem2 As Integer)
на следующую строку :
Function fSum(byVal nItem1 As Integer, nItem2 As Integer)
Можно продемонстрировать компилятору VBA, что то, что возвращает функция, наш совершенно не интересует. Для этого достаточно не заключать ее параметры в круглые скобки. Например, в случае со встроенной функцией MsgBox это может выглядеть так:
а для нашей функции —
Такой код будет работать совершенно нормально. Однако, если нам потребуется все-таки узнать, что возвращает MsgBox, то придется передаваемые ему параметры заключать в круглые скобки:
Для многих встроенных функций компилятор VBA в принципе не дает возможности игнорировать возвращаемое значение, заставляя помещать параметры в круглые скобки и принимать возвращаемое значение.
3.8.5 Запуск и завершение работы процедур
Способы запуска и завершения работы процедур в VBA, конструкция End Sub, конструкция Exit Sub
С примерами вызова процедур и функций из кода мы уже сталкивались:
Достаточно записать имя процедуры/функции, передать ей необходимые параметры, а для функции еще и принять необходимые значения. Одни процедуры можно вызывать из других процедур. Но как дать пользователю возможность запустить самую первую процедуру?
В нашем распоряжении следующие возможности:
· создать макрос (то есть специальную процедуру, не принимающую параметров, в модуле NewMacros) и запустить его по имени, кнопке или комбинации клавиш (см. раздел 1.4). Макрос затем может вызвать другие процедуры;
· создать форму и воспользоваться набором событий этой формы и элементов управления на ней (об этом рассказано в разделе, посвященном работе с формами и элементами управления), или просто элементом управления на листе Excel или документе Word;
· вместо специального имени для макроса использовать событие: например, событие запуска приложения, событие открытия документа и т. п. Это — рекомендованный Microsoft способ обеспечения автоматического запуска программного кода;
· можно запустить приложение из командной строки с параметром /m и именем макроса, например:
winword. exe /mMyMacros
Очень удобен в использовании ярлык Windows, в котором прописан этот параметр запуска.
В VBA вполне допустима ситуация, когда функция запускает на выполнение саму себя. Однако подобных ситуаций лучше избегать (по возможности заменяя их на циклы). Причина — проблемы с читаемостью и возможное (в случае бесконечного запуска функцией самой себя) исчерпание оперативной памяти (переполнение стека), чреватое серьезными системными ошибками.
Для завершения выполнения процедуры в VBA предусмотрены конструкции End и Exit. Синтаксис их очень прост:
Делают они одно и то же — завершают работу текущей процедуры. Однако используются они в разных ситуациях:
· оператор End — это завершение работы процедуры после того, как все сделано. После оператора End код процедуры заканчивается;
· оператор Exit — это немедленное завершение работы функции в ходе ее работы. Обычно помещается в блок оператора условного перехода, чтобы произвести выход, как только выяснилось, что функции по каким-то причинам дальше выполняться нельзя (например, дальше идет код обработчика ошибок).
Задание для самостоятельной работы 3.4 Работа с процедурами и функциями
Задание:
· создайте в модуле NewMacros шаблона Normal. dot новую функцию fMultiply(), которая бы:
o принимала в качестве входящих параметров два числа;
o перемножала их и возвращала полученное значение.
· создайте в модуле NewMacros шаблона Normal. dot новую процедуру AutoNew() со следующим кодом:
Public Sub AutoNew()
Dim nMult1 As Integer
Dim nMult2 As Integer
Dim nResult As Integer
nMult1 = CInt(InputBox(«Введите первое число: «))
nMult2 = CInt(InputBox(«Введите первое число: «))
Selection. InsertAfter nResult
Selection. Collapse wdCollapseEnd
· измените процедуру AutoNew() таким образом, чтобы она передавала значения переменных nMult1 и nMult2 функции fMultiply() и принимала от нее значение для переменной nResult (это значение должно использоваться вместо жестко определенного значения 10);
· создайте в Word новый документ и убедитесь, что созданные вами процедуры и функции работают правильно;
· чтобы созданная вами процедура AutoNew() не мешала дальнейшей работе, закомментируйте весь ее код
Решение:
· Вставьте в модуль NewMacros следующие строки для функции fMultiply():
Public Function fMultiply(nM1 As Integer, nM2 As Integer)
fMultiply = nM1 * nM2
Код для процедуры AutoNew() может выглядеть так (измененный код выделен полужирным):
Public Sub AutoNew()
Dim nMult1 As Integer
Dim nMult2 As Integer
Dim nResult As Integer
nMult1 = CInt(InputBox(«Введите первое число: «))
nMult2 = CInt(InputBox(«Введите первое число: «))
nResult = fMultiply(nMult1, nMult2)
Selection. InsertAfter nResult
Selection. Collapse wdCollapseEnd
Для того, чтобы закомментировать код AutoNew(), выделите весь код для этой процедуры (включая Public Sub AutoNew() и End Sub) и нажмите на кнопку Comment Block в панели инструментов Edit.
3.9.1 Встроенные функции языка Visual Basic For Applications
Встроенные функции VBA, справка по встроенным функциям
В языке программирования VBA предусмотрено несколько десятков встроенных функций. Они доступны в любой программе на языке VBA, при этом безразлично, в среде какого программного продукта мы находимся — Excel, Word, Access или, к примеру, AutoCAD. Используются они очень активно, и во многих ситуациях без них не обойтись. Профессиональные программисты применяют их совершенно автоматически, а обычным пользователям хочется посоветовать потратить несколько часов на знакомство с ними — потому что без знаниях этих функций эффективно работать в VBA не получится. Дополнительный аргумент в пользу их изучения: практически идентичный набор функций есть в обычном Visual Basic и VBScript, а многие из этих функций с теми же названиями и синтаксисом встречаются и в других языках программирования — C++, Delphi, Java, JavaScript и т. п.
В справке по VBA встроенные функции сгруппированы по буквам (см. рис. 3.2).
Рис. 3.2 Справка по встроенным функциям
Многие слушатели на курсах задавали вопрос: а нет ли справки по этим функциям на русском языке? К сожалению, такой справки мне найти не удалось, поэтому попытаюсь привести такую справку в этом курсе. Ниже будет рассказано про большинство активно используемых функций языка VBA (математические функции, которые в практической работе почти не используется, типа косинуса или тангенса, и финансовые функции мы рассматривать не будем). Полный синтаксис для экономии места приводиться не будет: главное — понимание, что делает каждая функция и в каких ситуациях ее можно использовать.
Функции в следующих разделах сгруппированы по своей функциональности
3.9.2 Функции преобразования и проверки типов данных
Встроенные функции преобразования и проверки типов данных VBA, функции CBool(), CByte(), CCur(), CDate(), CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar(), CVDate(), CVErr(), Str(), Val(), IsNumeric(), IsDate(), IsEmpty(), IsError(), IsMissing(), IsNull(), IsObject(), IsArray(), Hex(), Oct()
В программах на VBA очень часто приходится преобразовывать значения из одного типа данных в другой. Несколько типичных ситуаций, когда этим приходится заниматься:
· преобразование из строкового значение в числовое при приеме значения от пользователя через InputBox();
· преобразование значения даты/времени в строковое, когда нам нужно отобразить дату или время единообразно вне зависимости от региональных настроек на компьютерах пользователей;
· преобразование значения из строкового в дату/время для применения специальных функций даты/времени.
Чаще всего для конвертации типов данных используются функции, имя которых выглядит как C (от слова Convert) + имя типа данных. Вот перечень этих функций: CBool(), CByte(), CCur(), CDate(), CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar(), CVDate(), CVErr(). Просмотреть, что в итоге получилось, можно при помощи функции TypeName(), например:
nVar1 = CInt(InputBox(«Введите значение»))
Кроме того, еще несколько полезных для конвертации функций:
· Str() — позволяет перевести числовое значение в строковое. Делает почти то же самое, что и CStr(), но при этом вставляет пробел впереди для положительных чисел.
· Val() — «вытаскивает» из смеси цифр и букв только числовое значение. При этом эта функция читает данные слева направо и останавливается на первом нечисловом значении (допускается единственное нечисловое значение — точка, которая будет отделять целую часть от дробной). Очень удобно, когда у нас вперемежку с числовыми данными прописываются единицы измерения или валюта.
Чтобы не возникло ошибок при конвертации, можно вначале проверять значения на возможность конвертации при помощи функций IsNumeric() и IsDate(). Для проверки на соответствие специальным значениям можно использовать функции IsArray(), IsEmpty(), IsError(), IsMissing(), IsNull() и IsObject(). Все эти функции возвращают True или False в зависимости от результатов проверки переданного им значения.
Для того, чтобы преобразовать десятичные данные в строковое представление шестнадцатеричных и восьмеричных значений, используются функции Hex() и Oct(). Для обратного преобразования специальных функций не предусмотрено, но вы можете указать компилятору VBA, что эти числа записаны в шестнадцатеричном или восьмеричном формате, записав их, например, как &O12 и &HA.
Это — наиболее часто используемые функции. Требуются они постоянно, и знать их необходимо очень хорошо.
· ASC() — эта функция позволяет вернуть числовой код для переданного символа. Например, ASC(«D») вернет 68. Эту функцию удобно использовать для того, чтобы определить следующую или предыдущую букву. Обычно она используется вместе с функцией Chr(), которая производит обратную операцию — возвращает символ по переданному его числовому коду. Например, такой код в Excel позволяет написать в ячейки с A1 по A20 последовательно буквы русского алфавита от A до У:
Dim n, nCharCode As Integer
Do While n Надстройки установить флажок напротив строки «Пакет анализа», в Excel будет добавлен дополнительный набор аналитических научных и финансовых функций, а если в том же окне установить флажок напротив Analysis ToolPak — VBA, то эти функции станут доступны из Visual Basic на Application (только внутри Excel, в котором установлена эта надстройка).
Ниже приведены только универсальные функции VBA для работы с числовыми значениями. Эти функции доступны из любых приложений VBA.
· ABS() — эта функция возвращает абсолютное значение переданного ей числа (читайте, то же число, но без знака). Например, ABS(3) и ABS(-3) вернут одно и то же значение 3. Обычно используется тогда, когда нам нужно определить разницу между двумя числами, но при этом мы не знаем, какое число — первое или второе — больше. Результат вычитания может быть и положительным и отрицательным. Чтобы он был только положительным, используется эта функция.
· Int(), Fix() и Round() позволяют по разному округлять числа: Int возвращает ближайшее меньшее целое, Fix() отбрасывает дробную часть, Round() округляет до указанного количества знаков после запятой. При этом Round() работает не совсем правильно, в чем легко убедиться:
Поэтому на практике для округления лучше использовать Format():
· Rnd() и команда Randomize используются для получения случайных значений (очень удобно для генерации имен файлов и в других ситуациях). Обычный синтаксис при применении Rnd выглядит так:
Настоятельно рекомендуется перед вызовом функции Rnd() выполнить команду Randomize для инициализации генератора случайных чисел.
3.9.5 Функции для работы с датой и временем
Функции VBA для работы с датой/временем, функции Date(), Time(), DateAdd(), DateDiff(), DatePart(), DateSerial(), Timer()
Без функций даты и времени обычно обойтись просто невозможно. Самые важные функции VBA для работы с датой/временем:
· Date() — возвращает текущую системную дату. Установить ее можно при помощи одноименного оператора, например, так:
· Time() возвращает текущее системное время, а Now() — дату и время вместе.
· DateAdd() — возможность добавить к дате указанное количество лет, кварталов, месяцев и так далее — вплоть до секунд.
· DateDiff() — возможность получить разницу между датами (опять таки в единицах от лет до секунд).
· DatePart() — очень важная функция, которая возвращает указанную вами часть даты (например, только год, только месяц или только день недели).
· DateSerial() — возможность создать значение даты на основе передаваемых символьных значений. То же самое делает DateValue(), отличия — в формате принимаемых значений. Аналогичным образом (для времени) работают TimeSerial() и TimeValue().
· Day() (а также Year(), Month(), Weekday(), Hour(), Minute(), Second()) — специализированные заменители функции DatePart(), которые возвращают нужную вам часть даты.
· MonthName() — возвращает имя месяца словами по его номеру. Возвращаемое значение зависит от региональных настроек. Если они русские, то вернется русское название месяца.
· Timer() — возвращает количество секунд, прошедших с полуночи.
Если нужно получить дополнительные возможности работы с датой/временем, то в вашем распоряжении объектная модель Outlook. Например, при помощи нее можно получить информацию о праздниках и рабочих/нерабочих днях большинства стран мира. Подробнее — в соответствующем разделе.
3.9.7 Функции для организации взаимодействия с пользователем
Взаимодействие с пользователем в VBA, функции MsgBox() и InputBox()
Во многих программах VBA необходимо обеспечить взаимодействие с пользователем — проинформировать его о чем-то и (возможно) получить от него ответную реакцию. В принципе, для пользователя можно просто вывести текст в окне приложения (например, в текущем документе Word) или воспользоваться формой и элементами управления. Как это делается — мы узнаем в соответствующих главах. В этой части мы рассмотрим только применение для этой цели встроенных функций VBA.
Самой простой способ вывести информацию пользователю — воспользоваться встроенной функцией VBA MsgBox(). Примеров применения этой функции в нашей книге уже было множество, а полный ее синтаксис выглядит так:
MsgBox(Текст[,кнопки] [,заголовок окна] [, файл справки, метка в файле справки])
· можно показывать символы Critical, Warning, Question, Information,
· можно выбирать кнопку по умолчанию,
· можно делать окно модальным или обычным.
В зависимости от того, на какую пользователь кнопку нажал, такое значение возвращается приложению (всего 7 вариантов). Подробнее — в справке по VBA. Пример возврата значения от MsgBox():
Если значение nVar равно 1, то пользователь нажал OK, если 2, то Cancel.
Иногда (например, при пакетной обработке данных) хотелось бы, чтобы окно сообщения через некоторое время закрывалось само собой. Это можно сделать при помощи метода Popup() объекта Wscript. Shell. Для этого в проект через меню References нужно добавить ссылку на Windows Script Host Object Model (файл C:\WINNT\system32\wshom. ocx), а после этого использовать следующий код:
Dim oShell As New WshShell
oShell. Popup «Test», 5
Самый простой способ принять информацию от пользователя — воспользоваться функцией InputBox(). Все очень просто :
Input = InputBox(«Введите Ваше имя: «)
MsgBox (» Вы ввели: » & Input )
Для InputBox() можно указать текст приглашения, заголовок окна, значение по умолчанию, местонахождение окна и файл справки. Не забывайте, что все вводимое пользователем InputBox() автоматически переводит в тип данных String — может потребоваться выполнить преобразование.
Можно привлечь внимание пользователем звуковым сигналом. Для этой цели используется оператор Beep:
Если нужно обеспечить более сложное взаимодействие с пользователем, необходимо использовать форму, сам документ или помощника (Office Assistant). Очень мощные возможности обеспечивает и применение объектной модели Internet Explorer.
3.9.8 Функции — заменители синтаксических конструкций
В VBA предусмотрено несколько функций, которые позволяют заменять синтаксические конструкции условного перехода, например, IF…THEN…ELSE или SELECT…CASE. Каких-то преимуществ применение этих функций не дает (может быть, код станет на несколько строчек короче), но профессиональные программисты очень любят их использовать, когда только это возможно.
Начинающим программистам рекомендуется обычные синтаксические конструкции, чтобы не путаться. Однако для чтения чужого кода необходимо знать и эти функции. Вот их перечень:
· Choose() — принимает число (номер значения) и несколько значений. Возвращает значение, порядковый номер которого соответствует передаваемому числу. Например, Choose (2, «Первый», «Второй», «Третий») вернет «Второй».
· IIF() — расшифровывается как Immediate IF, то есть «Немедленный IF. Представляет из себя упрощенный вариант IF, когда проверяется условие и возвращается одно из двух значений. Пример:
IIf (n > 10, «Больше десяти», «Меньше или равно десяти»)
· Switch() — принимает неограниченное количество пар типа выражение/значение, проверяет каждое выражения на истинность и возвращает значение для первого выражения, которое оказалось истинным. Например:
Function Language (CityName As String)
Language = Switch(CityName = «Москва», «русский», CityName _
Функции VBA для работы с массивами, функции Array(), Filter(), LBound(), UBound(), Join(), Split()
Как уже говорилось, при программной работе с приложениями Microsoft Office массивы используются нечасто. Вместо них применяются коллекции. Однако в VBA предусмотрены и возможности для работы с массивами:
· Array() — позволяет автоматически создать массив нужного размера и типа и сразу загрузить в него переданные значения:
Dim myArray As Variant
· Filter() — позволяет на основе одного массива получить другой, отфильтровав в исходном массиве нужные нам элементы.
· LBound() — возвращает информацию о нижней границе массива (то есть номере первого имеющегося в нем значения), а UBound() — о верхней (номер последнего имеющегося значения).
· Join() — возможность слить множество строк из массива строк в одну строковую переменную. В качестве разделителя по умолчанию используется пробел, можно указать свой разделитель. Обратная функция, создающая массив из одной строки — Split(). Эти функции очень удобны, например, при обработке значений, полученных из базы данных, электронной таблицы, макетного файла и т. п.
В VBA предусмотрен набор встроенных функций для выполнения различных операций с файлами, каталогами, дисками и прочими объектами файловой системы. Информация об этих функциях приведена ниже. Но не забывайте, что помимо этих возможностей (общих для всех приложений, в которых используется VBA) у нас есть также, во-первых, возможности, специфические для данного приложения (например, открытие и сохранение документа Word средствами объектной модели Word). Во-вторых, на любом компьютере под управлением Windows есть объектная библиотека под названием Microsoft Scripting Runtime — очень простая и очень удобная для выполнения различных операций с файлами, каталогами и дисками. Можно добавить в проект VBA на нее ссылку и использовать все имеющиеся в ней возможности. Если, к примеру, мне нужно пройти по всем файлам в данном каталоге и что-нибудь с ними сделать (например, загрузить в Excel все файлы отчетов, которые пришли из филиалов), я использую именно эту библиотеку. Справку по ней можно найти на сайте Microsoft (www. /scripting).
А это — встроенные функции для работой с файловой системой, предусмотренные в VBA:
· CurDir() — функция, которая возвращает путь к текущему каталогу, в котором будут сохраняться файлы нашего приложения по умолчанию.
· Dir() — позволяет искать файл или каталог по указанному пути на диске.
· EOF() — при операции записи в файл на диске эту функция вернет True, если вы находитесь в конце файла. Используется при записи в файл своего собственного формата. При сохранении документов Word, книг Excel и т. п. лучше использовать стандартные методы объектов этих документов: Save и SaveAs().
· Error() — позволяет вернуть описание ошибки по ее номеру. Генерировать ошибку нужно при помощи метода RaiseError() специального объекта Err (см. главу 6, в которой рассказывается про перехват ошибок и отладку).
· FileAttr() — позволяет определить, как именно был открыт вами файл в файловой системе: на чтение, запись, добавление, в двоичном или текстовом режиме и т. п.
· FileDateTime() — позволяет получить информацию о последнем времени обращения к указанному вами файлу. Если к файлу после создания ни разу не обращались, то это будет время создания файла.
· FileLen() — позволяет определить длину указанного вами файла в байтах.
· FreeFile() — позволяет определить следующую свободную цифру, которую можно использовать как номер файла при его открытии.
· GetAttr() — возможность обратиться к файлу к файловой системе и получить информацию об его атрибутах (скрытый, доступен только для чтения, архивный и т. п.)
· Input() — позволяет считать информацию из открытого файла. Например, считать информацию из файла C:\text1.txt и вывести ее в окно Immediate можно так:
Open » c:\text1.txt» For Input As #1 ‘Открываем файл функцией Open() на чтение
Do While Not EOF(1) ‘пока файл не кончился
‘ Получаем по одному символу и добавляем его к предыдущим
MyChar = MyChar & Input(1, #1)
Close #1 ‘ Закрываем файл
Debug. Print MyChar ‘Выводим его содержание в окно Immediate
· Вариант этой функции — InputB() позволяет указывать количество байт, которые надо скачать из файла.
· Loc() — от Location, то есть местонахождение — возвращает число, которое определяет текущее место вставки или чтения в открытом файле. Похоже работает функция Seek(), но она возвращает информацию о позиции, с которой будет выполняться следующая операция чтения или вставки.
· LOF() — от length of file — позволяет определить длину открытого файла в байтах.
Диалекты языка программирования Бейсик
