Vba для чего нужен
Введение. Зачем нужен VBA?
В качестве первоначального знакомства с VBA попытаемся решить следующую задачу. Допустим, вы решили вести учет своих расходов, и с этой целью в конце каждого месяца намерены составлять таблицу (рис. В.1) и строить диаграмму для более наглядного отображения доли каждой статьи расходов вашего бюджета. Составлять ежемесячно одну и ту же таблицу с одновременным построением диаграммы довольно непроизводительная трата времени. Более разумно один раз научить компьютер создавать таблицу, а потом по мере необходимости лишь отдавать команду подготовки таблицы, чтобы осталось только внести в нее данные.
Рис. В.1. Таблица ежемесячных расходов
Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись (Tools, Macro, Record New Macro). Появится диалоговое окно Запись макроса (Record Macro) (рис. В.2). Это диалоговое окно позволяет задать параметры макроса.
Поля Имя макроса (Macro Name) и Описание (Description) предназначены для задания имени макроса и его описания. Последнее важно для многократно используемых макросов. Наша память не долговечна и, не имея под сказки в виде описания, через некоторое время бывает трудно вспомнить, для чего тот или иной макрос создавался. По умолчанию макросам присваиваются имена макрос1, Макрос2 и т. д. Чтобы было легче разпознать макрос, лучше не оставлять стандартное, а присвоить ему какое-нибудь уникальное имя, поясняющее его назначение. В данном случае присвоим макросу, например, имя Расходы. Поле Сочетание клавиш (Shortcut Key) позволяет назначить макросу комбинацию клавиш, т. е. указать клавишу, которая в сочетании с клавишей будет служить для запуска его на выполнение. Назначать комбинацию клавиш макросу совсем не обязательно. Это стоит делать только для постоянно используемых макросов, для быстрого доступа к ним. Макрос всегда можно вызвать командой Сервис, Макрос (Tools, Macro). Раскрывающийся список Сохранить в книге (Store Macro in) предназначен для выбора книги, в которой будет сохранен макрос. Если выбрать Личная книга макросов (Personal Macro Workbook), то макрос будет сохранен в специальной скрытой книге. Эта книга хотя и скрыта, но является открытой, и записанные в ней макросы доступны для других рабочих книг. Команда Окно, Отобразить (Window, Unhide) позволяет отобразить личную книгу макросов. Если в раскрывающемся списке выбрать Эта книга (This Workbook) (этот выбор по умолчанию предлагает компьютер), то макрос сохранится на новом листе модуля в активной рабочей книге, а если выбрать Новая книга (New Workbook) — в новой рабочей книге.
Рис. В.2. Диалоговое окно Запись макроса
Итак, в диалоговом окне Запись макроса (Record Macro) в поле Имя макроса (Macro Name) введем Расходы, а в поле Описание (Description) — Расчет месячных расходов и нажмем кнопку ОК. Появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. Построим шаблон таблицы расходов по следующему алгоритму:
Рабочий лист теперь будет выглядеть так, как показано на рис. В.1. Остановим запись макроса, нажав кнопку Остановить запись (Stop Recording).
Заполним ячейки таблицы исходными данными, расчет суммарных расходов и построение диаграммы теперь будет происходить автоматически.
Для просмотра записанной процедуры необходимо выбрать команду Сервис, Макрос, Макросы (Tools, Macro, Macros), которая вызовет диалоговое окно Макрос (Macro) (рис. В.З).
В этом диалоговом окне в списке выделим макрос и нажмем кнопку Изменить (Edit). Это вызовет появление главного окна редактора VBA (рис. В.4). Ниже приведен полный текст записанного макроса.
Рис. В.З. Диалоговое окно Макрос
Рис. В.4. Главное окно редактора VBA
Итак, активизируем новый рабочей лист, временно присвоим ему имя лист1. Выберем команду Сервис, Макрос, Макросы (Tools, Macro, Macros), которая вызовет диалоговое окно Макрос (Macro). В этом окне в списке выделим исходный макрос и нажмем кнопку Выполнить (Run). Диалоговое окно закроется и выполнится процедура, создающая на активном рабочем листе шаблон таблицы. Теперь в нее остается ввести новые данные, а расчет суммарных расходов и построение диаграммы будет происходить автоматически.
Автоматизация рутины в 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 = 0 CurRow = 1.
Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.
Спасибо за внимание.
Буду рад конструктивной критике в комментариях.
UPD: Перезалил пример на Dropbox и min.us.
UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.
Расцвет и упадок Visual Basic
Мне нужно сделать признание. До того, как я стал респектабельным разработчиком, работающим с такими современными языками, как C# и Java (и тем бурлящим хаосом, который известен нам под именем JavaScript), я был увлечённым фанатом невероятно популярного и неуклюжего Visual Basic.
Моя влюблённость была искренней. Ещё в детстве я учился программировать на языке BASIC. Но не просто на BASIC. Я начинал с потрясающей среды Microsoft под названием QuickBASIC на древней операционной системе DOS. Я всё ещё помню, как набирал белый текст на её бодром голубом фоне.
Для программирования в 1988 году QuickBASIC был волшебной штукой. Можно было писать код без громоздких номеров строк, отлавливать синтаксические ошибки в процессе набора и запускать программы прямиком из среды разработки без необходимости возни с командной строкой. Закончив программу, можно было делиться ею со своими друзьями при помощи вот этих ребят:
В наши дни QuickBASIC остался лишь ещё одной диковинкой из далёкого прошлого. Хотя так ли это? Новаторский проект под названием QB64 создал современную реплику QuickBASIC. Он запускается на Windows, MacOS и Linux, и не требует эмулятора. При запуске программы в QB64 среда выполняет хитрый трюк — сначала она транслирует код на BASIC в C++, а затем компилирует его.
Классический VB и эра Visual
QuickBASIC был впечатляющей средой для далёких дней текстовых компьютерных систем. Но всё изменилось, когда Microsoft выпустила Windows и погрузила пользователей PC в графический мир кнопок и кликов. Одновременно с выпуском компанией Microsoft Windows 3.0 (первой версии, которая добилась истинного успеха), был выпущен Visual Basic 1.0.
Это было нечто совершенно новое. Пользователь мог создавать кнопки для программ, рисуя их на поверхности окна, как на холсте художника. Чтобы заставить кнопку выполнять какое-то действие, достаточно было дважды щёлкнуть на неё в среде разработки и написать код. И код писался не на загадочном C++ с кучами классов, сложным управлением памятью и непонятными вызовами Windows API. Вы как цивилизованный человек писали код на дружелюбно выглядящем VB.
Все эти графические примочки впечатляли, но настоящим секретом успеха VB была его практичность. Попросту не было ни одного другого инструмента, в котором разработчик мог бы набросать полный интерфейс и приступить к кодингу так же быстро, как в VB. И хотя историки любят порассуждать о визуальной части Visual Basic, его неподражаемое умение не имело ничего общего с графическими виджетами. VB стал знаменитым благодаря легендарной функции edit-and-continue, позволявшей разработчикам запускать программы, находить ошибки, исправлять их и продолжать работать с новым кодом. Это разительно отличалось от почти всех остальных известных человечеству сред программирования, вынуждавших разработчиков компилировать работу заново и после каждого изменения начинать снова.
Оригинальный Visual Basic процветал примерно десяток лет. Возникнув как дружественная среда для новичков, он превратился в инструмент, имеющий достаточно возможностей и для серьёзных программистов. К моменту выпуска VB 6 — последней версии классического Visual Basic, в мире по приблизительным подсчётам было в десять раз больше кодеров на VB, чем на суровом C++. И они не просто создавали макеты игрушечных приложений. Visual Basic проложил себе дорогу в офисы компаний и даже в Интернет при помощи ASP (Active Server Pages) — ещё одной чудовищно популярной технологии. Благодаря ей вы могли создавать веб-страницы, способные общаться с компонентами VB, связываться с базами данных и писать HTML на лету.
Всё это происходило практически без структуры, если только вы не решали создать её самостоятельно. Это было неписанное правило Visual Basic — он давал вам свободу, о которой вы потом пожалеете.
Проблема классического VB
Во многих кругах классический VB имел плохую репутацию. Фанатики ООП часто жаловались, что VB недостаёт поддержки наследования. (Это была странная жалоба, ведь наследование часто является отличным способом для неопытных разработчиков выстрелить себе в ногу — именно этой особенности VB не требовалось.) На самом деле, классический VB не имел склонности к объектно-ориентированности. К концу своей жизни он уже поддерживал интерфейсы, полиморфизм и библиотеки классов. Всё это было позаимствовано из COM — фундаментальной части технологии компонентов, «прошитой» в каждой версии Windows.
Настоящая проблема классического VB заключалась в том, что он был слишком успешным. Он настолько эффективно снижал барьеры для новых программистов, что в нём мог работать практически кто угодно. Беззаботные новички, усталые сотрудники компаний и студенты решали проблемы, которые на любой другой платформе представляли бы гораздо большую сложность, и повсюду развешивали спагетти-код.
Другими словами, поскольку VB был достаточно лёгок в использовании, даже если вы не учились на программиста, его применяло множество людей, которые не учились быть программистами. И VB не делал ничего, чтобы помешать укоренению их плохих привычек.
Перенесёмся вперёд, в 2002 год: Microsoft занимается тем, что лучше всего получается у Microsoft — разрушает собственную экосистему разработчиков, вводя совершенно новый способ работы.
На этот раз проектом было масштабное изменение архитектуры COM — компонентной технологии, служившей опорой Windows (а косвенно и классическому VB). Это была ставка «ва-банк» для компании-мегалита, уже совершавшего несколько раз такие рискованные перемены.
Единственная проблема заключалась в том, что для реализации всех этих функций Microsoft пришлось избавиться почти от всего классического VB.
Если сильно прищуриться, то новая версия под названием VB.NET выглядела почти такой же. Но в ней появилось множество крупных и мелких революционных изменений. Крупные: старые программы на VB остались заключёнными в мире старых программ, и удивительно слабый мастер миграции почти никак не мог это исправить. Мелкие: программистам на классическом VB пришлось привыкать к новому способу подсчёта элементов массивов. Они больше не могли начинать с 1, как обычные люди. Теперь они должны были начинать с 0, как официальные программисты.
Ещё одно важное изменение: больше никакой функции edit-and-continue.
Разработчики любят жаловаться, а разработчики на VB особенно этим известны. Почти сразу же группа влиятельных разработчиков на VB окрестила новую среду программирования Microsoft именем VB.Fred, чтобы подчеркнуть тот факт, что этот новый язык не является Visual Basic.
Что обрекло Visual Basic на гибель
Но превращение в ещё один респектабельный язык программирования создало и проблему. Пропал энтузиазм, сделавший VB любимым языком столь обширной группы людей. Не потому, что изменился VB, а потому, что изменился C#.
Другими словами, у C# теперь появилась «страховочная сетка», защищающая любителей, студентов и новичков, и не ослабляющая при этом его мощи. Внезапно VB перестал быть чем-то особенным. Он превратился в просто ещё один инструмент из набора умелого программиста.
Современное состояние Visual Basic
Одна из ниш, в которых Visual Basic должен чувствовать себя естественно — это образовательный рынок. Но даже там он остаётся чёрной овцой. Современные языки наподобие C# и Python просты и достаточно безопасны, чтобы их можно было выбирать в качестве первого изучаемого языка. Если вам нужно что-то попроще для детей то рынок переполнен инструментами для графического программирования наподобие Scratch. Своей притягательностью обладают и языки «с фигурными скобками»: C#, C, JavaScript и Java. Они имеют схожий синтаксис, и человек, выучивший один из них, будет чувствовать себя уверенно с другим.
В области веб-разработки Microsoft смотрит в пустоту упущенных возможностей. Кого бы не соблазнила версия VB, столь же простая в работе, как VB 6, но ещё и способная компилироваться в JavaScript и комбинироваться с дизайнером форм HTML? Да, новые Google Maps в таком инструменте не создашь, но он мог бы возродить привлекательность Visual Basic, позволив бизнес-разработчикам, студентам и любителям создавать простые онлайн-приложения без необходимости пользоваться громоздким JavaScript. Вместо этого Microsoft создала продукт на основе VB под названием LightSwitch, исчезнувший со смертью браузерного плагина Silverlight. Если Microsoft начнёт создавать ещё один продукт класса «кодинг для широких масс», то это скорее всего будет управляемый шаблонами инструмент с небольшим количеством кода наподобие PowerApps.
Visual Basic и раньше грозили разные опасности. Но на этот раз, похоже, всё по-другому. Кажется, солнце для одного из самых популярных в мире языков программирования на самом деле закатывается. Если это правда, то Visual Basic не будет постепенно исчезать на протяжении десятилетий. Он станет ещё одним легаси-продуктом, недооценённым инструментом без любви пользователей и будущего. Потеряли ли мы что-то особенное, или просто избавили от мучений устаревшую технологию — решать вам.