Автоматизация рутины в 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».





Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:



Sub FormatPrice()

End Sub



Напишем Hello World:



Sub FormatPrice()
    MsgBox "Hello World!"
End Sub



И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.



Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.



Примеры синтаксиса

' Процедура. Ничего не возвращает
' Перегрузка в VBA отсутствует
Sub foo(a As String, b As String)
    ' Exit Sub ' Это значит "выйти из процедуры"
    MsgBox a + ";" + b
End Sub

' Функция. Вовращает Integer
Function LengthSqr(x As Integer, y As IntegerAs Integer
    ' Exit Function
    LengthSqr = x * x + y * y
End Function

Sub FormatPrice()
    Dim s1 As String, s2 As String
    s1 = "str1"
    s2 = "str2"
    If s1 <> s2 Then
        foo "123""456" ' Скобки при вызове процедур запрещены
    End If

    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 Resume Next ' Обработка ошибок - игнорировать все ошибки
    x = 5 / 0
    MsgBox x

    On Error GoTo Err ' При ошибке перейти к метке Err
    x = 5 / 0
    MsgBox "OK!"
    GoTo ne

Err:
    MsgBox "Err!"

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

    ' Двухмерный массив.
    ' Можно также менять размеры командой ReDim (Preserve) - см. google
    Dim arr(1 to 10, 5 to 6) As Integer
    arr(1, 6) = 8

    Dim coll As New Collection
    Dim coll2 As Collection
    coll.Add "item""key"
    Set coll2 = coll ' Все присваивания объектов должны производится командой Set
    MsgBox coll2("key")
    Set coll2 = New Collection
    MsgBox coll2.Count
End Sub


Грабли-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, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:



  1. Считали группы из очередной строки.
  2. Пробегаемся по всем группам в порядке приоритета (вначале более крупные)
    1. Если текущая группа не совпадает, вызываем процедуру AddGroup(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие.
  3. После отрисовки всех необходимых заголовков делаем еще одну строку и заполняем её данными.


Для упрощения работы рекомендую определить следующие функции-сокращения:



Function GetCol(Col As IntegerAs String
    GetCol = Chr(Asc("A") + Col)
End Function

Function GetCellS(Sheet As String, Col As Integer, Row As IntegerAs Range
    Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row))
End Function

Function GetCell(Col As Integer, Row As IntegerAs Range
    Set GetCell = Range(GetCol(Col) + CStr(Row))
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

    Sheets("data").Activate
    I = 2
    Do While True
        If GetCell(0, I).Value = "" Then Exit Do
        ' ...
        I = I + 1
    Loop
End Sub


Теперь надо заполнить массив Groups:



На месте многоточия

Dim I2 As Integer
For I2 = 1 To GroupsCount
    Groups(I2) = GetCell(I2, I)
Next I2
' ...
For I2 = 1 To GroupsCount ' VB не умеет копировать массивы
    PrGroups(I2) = Groups(I2)
Next I2
I =  I + 1


И создать заголовки:



На месте многоточия в предыдущем куске

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

Sub AddHeader(Ty As Integer, Name As String)
    GetCellS("result", 1, CurRow).Value = Name
    CurRow = CurRow + 1
End Sub


Теперь надо перенести всякую информацию в result



For I2 = 0 To DataCount - 1
    GetCellS("result", I2, CurRow).Value = GetCell(I2, I)
Next I2



Подогнать столбцы по ширине и выбрать лист result для показа результата



После цикла в конце FormatPrice

Sheets("Result").Activate
Columns.AutoFit


Всё. Можно любоваться первой версией.





Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:



Sub AddHeader(Ty As Integer, Name As String)
    Sheets("result").Range("A" + CStr(CurRow) + ":C" + CStr(CurRow)).Merge
    ' Чтобы не заводить переменную и не писать каждый раз длинный вызов
    ' можно воспользоваться блоком With
    With GetCellS("result", 0, CurRow)
        .Value = Name
        .Font.Italic = True
        .Font.Name = "Cambria"
        Select Case Ty
        Case 1 ' Тип
            .Font.Bold = True
            .Font.Size = 16
        Case 2 ' Производитель
            .Font.Size = 12
        End Select
        .HorizontalAlignment = xlCenter
    End With
    CurRow = CurRow + 1
End Sub



Уже лучше:





Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:





Поэтому чуть-чуть меняем код с добавлением стиля границ:



Sub AddHeader(Ty As Integer, Name As String)
    With Sheets("result").Range("A" + CStr(CurRow) + ":C" + CStr(CurRow))
        .Merge
        .Value = Name
        .Font.Italic = True
        .Font.Name = "Cambria"
        .HorizontalAlignment = xlCenter

        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) — тут скобки нужны постоянно.

Similar posts

AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 37

    +3
    Когда-то еще в школе мне VBA в ворде и экселе помогал форматировать рефераты и другие текста. Было очень захватывающе писать программу которая тут же на глазах обрабатывала данный тобой текст, тогда это было похоже на некую магию и чувствовалась власть над кремниевым разумом :)
      0
      Да, согласен — похоже на магию.
      Меня в свою очередь удивило когда-то когда после универа устроился на работу в весьма не последнуюю в Киеве компанию, а в отделе продаж про макросы и хоть какую-нить автоматизацию задач даже не слышали.
      +3
      Хорошо бы включить в статью немного о «записи макроса». Для новичков это действительно лёгкий и быстрый способ изучить VBA.
        +2
        Это точно, я такое использую для того чтобы узнать какими функциями можно сделать нужные операции
        0
        В статье почти не освещено как запускать VBA программы (в частности через макросы), способ через через отладчик хорош для тестирования, а готовое решение лучше всего через кнопку на панели…
          0
          Я знаю только два способа — вытащить кнопку на лист и добавить в Quick Access (на Ribbon).

          Первый я вроде рассказал.
            –1
            Ой, немного проглядел, в рамках данной статьи этого достаточно
              0
              Можно и свою вкладочку на Ribbon добавить, с кнопками и т.п. Только там надо файлик книги руками потрошить и добавлять туда схему в формате XML.
              0
              Вы это имели ввиду?
                0
                или через Alt+F8
                0
                Если кто-нибудь подскажет человеческих файлохостинг, залью туда.
                — dropbox погуглите

                  0
                  Я бы посоветовал min.us — там теперь не только картинки можно заливать просто перетаскиванием.

                  Dropbox на бесплатном аккаунте может забанить юзера на некоторое время, который вешает ссылки в публичный доступ, после какого-то количества переходов по ним. Да и к тому же, может быть, автору вовсе не хочется регаться где-то, тем более ради мелкого файла.

                  +2
                  Visual Studio Tools for Office позволяет писать макросы на C# и встраивать их в книгу Excel. Для 2003 офиса работает вполне адекватно.
                    0
                    goto
                      0
                      Несколько лет назад на тогдашней работе меня попросили «что-то придумать», чтобы извлекать тексты из технических спецификаций и чертежей AutoCAD и складывать их в таблицы Excel.
                      В Автокаде есть интерпретаторы Lisp (точнее диалекта AutoLisp) и VBA. Лисп привёл меня в ужас, несмотря на поверхностное знакомство с ним в университете — остался VBA.
                      Язык непривычный и поначалу какой-то некомфортный для тех, кто учился Паскалю и C++ — но, надо признать, по-своему мощный. Через пару дней Автокад у меня уже «дистанционно» рулил Экселем и экспортировал таблички.
                        0
                        AutoCAD и его возможности автоматизации — по-моему такой конструктор лего, что от одной просьбы «что-то придумать» можно надолго зависнуть в выборе способа реализации «чего-то» )))
                        +2
                        а что, PivotTable для такой задачи мало?
                          +1
                          Спасибо, первый раз слышу, если честно.
                          А вообще задача показалась мне достаточной, чтобы показать основные средства форматирования через VBA. Это было целью.
                          +3
                          А еще VBA, как и его старший брат VB, содержит костыль для вызова нативных DLL.
                          И отроки пишут нехитрые макровирусы, например.

                          Или вот такие, вполне себе легитимные вещи.
                          Private Declare Function mciSendString Lib "winmm.dll" Alias "mciSendStringA" (ByVal lpstrCommand As String, ByVal lpstrReturnString As String, ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long

                          Private Sub open_cdrom()
                            Dim a As Double
                            a = mciSendString("open cdaudio", 0&, 0&, 0&)
                            a = mciSendString("set cdaudio door open", 0&, 0&, 0&)
                            a = mciSendString("close cdaudio", 0&, 0&, 0&)
                          End Sub

                          Private Sub close_cdrom()
                            Dim a As Double
                            a = mciSendString("open cdaudio", 0&, 0&, 0&)
                            a = mciSendString("set cdaudio door closed", 0&, 0&, 0&)
                            a = mciSendString("close cdaudio", 0&, 0&, 0&)
                          End Sub

                          * This source code was highlighted with Source Code Highlighter.
                            –5
                            Я ваял скрипты для экселя (некоторые ещё используются), но на jscript, от vba меня воротит.
                              +1
                              а в 2007 и 2010 так же, как и в младших версиях, в редакторе кода не работает полоса прокрутки колесиком? :)
                              кстати, для обхода ячеек иногда удобно пользоваться конструкций For Each :)
                                –6
                                Без обид, но что тут хорошего? Ужасный язык с уродливым синтаксисом, который вообще предназначем для обучения основам компьютерной грамотности, а не для работы, закрытая платная проприетарная замкнутая среда. То ли дело, если бы использовали опенсурсный яваскрипт, чтоб работало в бесплатной программе, но при этом не тормозило как опенофис.
                                  0
                                  Эх когда то я макро вирусы клепал для Ворда и Экселя… Как же тогда это было интересно…
                                    +1
                                    На стыке веков, помнится, практически везде (в банках и госконторах — точно) с помощью Excel, VBA и такой-то матери писалось практически все.

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

                                    Делалось для нужд любого подразделения — программеско-эникейские, бухгалтерия, склад, экономисты, юристы, оперчасть…

                                    Один мой друг «автоматизировал» на Excel ведение зарплаты — получилась вполне сносная система по учету оной.

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

                                    А еще один — тетрис в экселе наваял (наверное, другие игрушки у него были деревянные и прибитые к полу).
                                    0
                                    foo «123», «456» ' Скобки при вызове процедур запрещены
                                      0
                                      foo «123», «456» ' Скобки при вызове процедур запрещены
                                      ......
                                      ' А вот при вызове функций, от которых хотим получить значение, скобки нужны.
                                      

                                        0
                                        Извиняюсь что-то FF глючит, комментарии сами уходят.
                                        Хотел сказать, что сам недавно столкнулся с VBA и был озадачен логикой наличия/отсутствия скобок для функций и процедур.
                                      +1
                                      Интересно, а можно писать макросы на C# для OpenOffice. На работе отказались от платных продуктов. Поэтому есть Visual Studio Express и OpenOffice. Хотелось бы попрактиковаться
                                        0
                                        А какие другие языки можно использовать в офисе для подобных задач?
                                          +1
                                          Выше FireStorm писал, что есть Visual Studio Tools for Office. Они позволяют писать на C#. Видимо и на всех .NET языках тоже.
                                          А если хочется не .NET язык — то заявлять ничего не буду. Но точно есть привязка через COM к любому языку, который это поддерживает.
                                          0
                                          а можно сделать так, чтобы с N количества страниц информация с 2 определенны столбцов собиралась на странице Х??
                                            0
                                            Да, конечно.
                                            Просто делаете цикл от 1 до N, пробегаетесь по всем строкам нужной страницы ( вызвав сначала Sheets(«page» + CStr(ID)).Activate ) и копируете нужные данные в result ( GetCellS(«result», ...).Value =… ).
                                            0
                                            Спасибо за очень нужный материал.
                                            А в googledocs можно ли программировать, никто не в курсе?
                                            0
                                            Я в экселе написал целую систему имитационного моделирования системы массового обслуживания :)

                                            А вот с ОпенОфис не смог разобраться. Там какой-то очень стрёмный хелп. VBA в целом выглядит как-то логичнее.
                                            Хотя я VBA специально никогда не учил, могу писать сложные штуки по хелпу, а в ОпенОфисе вообще ничего понять — хелп на сайте составлен крайне неудачно.
                                              +1
                                              Статья не смогла оттенить убогость сабжа, не смотря на оформление, язык и целостность. (спасибо. Если не приведи господи придется с этим встретится, буду ее искать ))) )

                                              P.S. Чет накатило
                                                –1
                                                Эта публикация вдохновила меня на написание нечто похожего. Здесь
                                                (http://habrahabr.ru/blogs/sandbox/112259/) можно посмотреть практическое применение макросов в качестве примера к Вашей статье.

                                                Only users with full accounts can post comments. Log in, please.