Статьи

Автоматизация офисных приложений с помощью макросов

21.08.2013

/

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



К счастью, есть возможность автоматизировать все эти действия, а помочь в этом могут макросы. Применимо к документам MS Office, макросы пишутся на VBA (Visual Basic for Applications) - языке достаточно простом, интуитивном и обладающем всеми прелестями объектно-ориентированного программирования.

Приведем конкретные примеры, решив три вполне классическихзадачи с помощью макросов на VBA.


1. Поиск дублей по строке


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

Ниже приведен код макроса, решающего эту задачу.

Sub Find_Dubles_In_Range()

      For i = 1 To Selection.Cells.Count

          If (Selection.Cells(i).Value <> "") Then

              For j = i + 1 To Selection.Cells.Count

                  If (Selection.Cells(i).Value = Selection.Cells(j).Value) Then

                      Selection.Cells(i).Interior.Color = vbYellow

                      Selection.Cells(j).Interior.Color = vbYellow

                  End If

              Next

          End If

     Next

End Sub


Применение достаточно простое - необходимо выделить диапазон ячеек в документе MS Excel и вызвать макрос. Одинаковые ячейки он отметит цветом.

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

Ключевое слово Selection описывает выделенный диапазон. Т.е. диапазон ячеек — это и есть Selection. В зависимости от выделенного диапазона, Selection приобретает те или иные свойства и методы. Если говорить о любом виде выделения любых ячеек в Excel, то Selection всегда будет иметь свойство Cells для обращения к ячейкам диапазона. Свойство Cells имеет, в свою очередь, индексацию Cells(i) — можно обратиться не ко всем ячейкам из Selection, а к конкретным с помощью номера i. Ячейки же имеют свойство Count — их число. Для того, чтобы определить количество итераций для перебора ячеек в циклах, мы и будем отталкиваться от этого числа.

Предполагается, что нам не нужно выделять цветом пустые ячейки - это описано в строках

If (Selection.Cells(i).Value <> "") Then

...

End If

Итак, используем вложенные циклы для перебора ячеек. Первый индекс итератор i нам нужен для того, чтобы мы относительно i-ой ячейки делали сравнение значений. Для непосредственно перебора используем итератор j.

If (Selection.Cells(i).Value = Selection.Cells(j).Value) Then

        Selection.Cells(i).Interior.Color = vbYellow

        Selection.Cells(j).Interior.Color = vbYellow

End If


дают нам сравнение ячеек диапазона. Также здесь мы видим новое свойство Selection.Cells(i).Interior.Color — это заливка ячеек. Таким образом, если значения i-ой и j-ой ячеек совпадают, заливаем их фон желтым цветом.


2. Поиск различий на листах

Примерно та же задача, но немного сложнее. Ее решение на VBA следующее.

Sub Diff()

   Dim sh1, sh2

   Set sh1 = ActiveWindow.SelectedSheets.Item(1)

   Set sh2 = ActiveWindow.SelectedSheets.Item(2)

   '================================================

   ' Определяем кол-во строк в каждом листе и

   ' максимальное кол-во строк вообще

   '================================================

   Dim lineCount1, lineCount2

   Dim maxLines As Integer

   Dim Diff As Boolean


   lineCount1 = sh1.Cells.SpecialCells(xlLastCell).Row

   lineCount2 = sh2.Cells.SpecialCells(xlLastCell).Row

   maxLines = Application.WorksheetFunction.Max(lineCount1, lineCount2)


   '================================================

   ' Определяем в цикле максимальное кол-во ячеек

   ' каждой строки в выбранных листах. Сравниваем

   ' ячейки построчно

   '================================================


   For j = 1 To maxLines

 k = Application.WorksheetFunction.Max(sh1.Rows(j).Cells(1, Columns.Count).End(xlToLeft).Column, sh2.Rows(j).Cells(1, Columns.Count).End(xlToLeft).Column)

   For m = 1 To k

      If (sh1.Rows(j).Cells(m).Value <> sh2.Rows(j).Cells(m).Value) Then

         sh2.Rows(j).Cells(m).Interior.Color = vbRed

         Diff = True

      End If


     Next

   Next


   '================================================

   ' Результат =)

   '================================================

   If (Diff) Then

 MsgBox "Листы " & ActiveWindow.SelectedSheets.Item(1).Name & " и " & ActiveWindow.SelectedSheets.Item(2).Name & " отличаются." & Chr(13) & "Отличия показаны цветом на " & ActiveWindow.SelectedSheets.Item(2).Name

   Else

      MsgBox "Выбранные листы одинаковы"

   End If


End Sub

Для сравнения листов рабочей книги, мы выделяем интересующие нас листы и вызываем обработчик. В объектной модели MS Office существует объект ActiveWindow — объект открытого окна того или иного приложения MS Office. Применительно к разным окнам и объектам этих окон, у него могут быть разные свойства. Мы обращаемся к свойству SelectedSheets, для того чтобы обработать выделенные листы.

Алгоритм сравнения листов заключается в следующем: мы оцениваем количество строк в каждом листе и далее построчно производим перебор ячеек. Несовпадающие ячейки заливаем цветом. При этом, один лист выбирается как эталон, а второй сравнивается с этим эталоном. Соответственно, все изменения и отмечаются цветом на втором листе. Первый лист — это тот, который левее в списке листов. Второй — правее. Причем здесь не действуют правила сортировки по именам. Если левый лист имеет имя «Лист2», а правый - «Лист1», то все равно левый — эталон, на правом производятся изменения. Поэтому, перед вызовом обработчика, нужно передвинуть листы или поменять их местами по необходимости.

Итак, листы выделили, вызвали обработчик. Получаем ссылки на листы

Set sh1 = ActiveWindow.SelectedSheets.Item(1)

Set sh2 = ActiveWindow.SelectedSheets.Item(2)

Оцениваем количество строк в каждом листе.

lineCount1 = sh1.Cells.SpecialCells(xlLastCell).Row

lineCount2 = sh2.Cells.SpecialCells(xlLastCell).Row

И максимальное число строк вообще.

maxLines = Application.WorksheetFunction.Max(lineCount1, lineCount2)

Мы будем перебирать ячейки построчно с использованием знакомых нам вложенных циклов.

Смысл состоит в следующем: допустим, что в первом листе содержится 10 строк, а во втором 6. Ясно, что листы отличаются, поэтому мы должны закрасить 4 пустых строки второго листа после 6 заполненных.

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

Производим перебор ячеек в каждой строке каждого рабочего листа

For j = 1 To maxLines

k = Application.WorksheetFunction.Max(sh1.Rows(j).Cells(1, Columns.Count).End(xlToLeft).Column, sh2.Rows(j).Cells(1, Columns.Count).End(xlToLeft).Column)

   'MsgBox "Строка " & j & ", максимальное число ячеек" & k

   For m = 1 To k

        If (sh1.Rows(j).Cells(m).Value <> sh2.Rows(j).Cells(m).Value) Then

             sh2.Rows(j).Cells(m).Interior.Color = vbRed

             Diff = True

        End If

   Next

Next


Переменная булевского типа Diff нужна нам, чтобы сообщить пользователю были ли отличия на выбранных листах или нет. Ее значение по умолчанию — FALSE, однако первое же несовпадение значений при переборе ячеек меняет значение Diff на TRUE. А дальше обычным If — Else выводим окошко с сообщением различаются листы или нет.

If (Diff) Then

 MsgBox "Листы " & ActiveWindow.SelectedSheets.Item(1).Name & " и " & ActiveWindow.SelectedSheets.Item(2).Name & " отличаются." & Chr(13) & "Отличия показаны цветом на " & ActiveWindow.SelectedSheets.Item(2).Name

   Else

      MsgBox "Выбранные листы одинаковы"

End If


3. Проверка наличия символов Кириллицы в выделенном диапазоне ячеек

Принцип работы тот же: выделяем диапазон ячеек, далее вызываем обработчик.

Sub Rus()

   For Each Cell In Selection

        If Cell.Value Like "*[А-я]*" Then

             Cell.Interior.Color = vbGreen

        End If

   Next

End Sub


Алгоритм крайне прост. Используем все тот же Selection для обращения к выделенному диапазону ячеек. Все ячейки диапазона можно перебрать простейшим циклом For Each.

Перебирая ячейки, проверяем по очереди содержит ли каждая из них символы кириллицы.


Добавление макроса в приложение MS OFFICE


Когда код написан, остается его применять. Первым возникает вопрос «Как его исполнять?» Можно сохранить его в отдельном файле и запускать из него же. Но это здравая мысль при том, что макрос имеет отношение к этому файлу и только к нему. Для того, чтобы макросы были доступны для всех файлов Excel, на этом компьютере их нужно сохранить в Личную книгу макросов.

Для начала, откроем приложение Excel и перейдем на вкладку «Вид». На этой вкладке будет выпадающее меню «Макросы». Щелкаем по нему и выбираем «Запись макроса».

Нужно указать имя макроса и выбрать сохранение в «Личную книгу макросов». Для того, чтобы макрос был доступен для всех документов Excel, нужно сохранить его именно в «Личную книгу макросов». Тут же, по желанию, можно назначить сочетание клавиш для его быстрого вызова. Останавливаем запись макроса в меню «Макросы», так ничего и не записав.

Снова открываем меню «Макросы», выбираем пункт «Макросы». Видим в нем наш записанный макрос.

Нажимаем кнопку «Изменить», откроется редактор. Нужно вставить в него наш код.

Есть вероятность, что сразу не получится так сделать. При нажатии на кнопку «Изменить» в списке макросов может появиться окошко с сообщением о невозможности редактирования.

Для того, чтобы решить эту проблему, нужно закрыть все документы Excel, которые открыты, но не закрывать само приложение. В окне приложения Excel перейти на вкладку «Вид». Она будет вся неактивна, кроме кнопки «Отобразить». Вот ее и нажимаем.

В появившемся окне нажимаем «ОК»

Теперь доступ к личной книге получен - можно редактировать код, как это было описано ранее.



Добавление макроса на панель быстрого доступа


Как уже было описано, с помощью меню «Макросы» можно установить сочетание клавиш для вызова макроса, однако это не всегда бывает удобно. Альтернативный вариант быстрого вызова макроса – назначить на него кнопку на панели.

Для этого, открываем меню «Файл», выбираем пункт «Параметры», далее выбираем «Параметры быстрого доступа», меню «Часто используемые команды» => «Макросы».

Выбираем интересующий нас макрос и добавляем его на панель кнопкой «Добавить».

Теперь наш макрос появился на панели быстрого доступа. Однако, если вы добавите несколько макросов, то вид их иконок будет одним и тем же - можно запутаться.

Для изменения иконки запускаемого макроса делаем следующее: вызываем на иконке макроса контекстное меню с помощью правой кнопки мыши, выбираем пункт «Настройка панели быстрого доступа».

Попадаем в знакомое нам окно, выбираем в списке команд быстрого доступа наш макрос и нажимаем на кнопку «Изменить».

В появившемся окне «Изменение кнопки» можно выбрать понравившуюся иконку для запуска макроса, а также изменить отображаемое название.

В итоге панель быстрого доступа выглядит так

Таким же образом можно добавить ссылку на макрос в отдельную вкладку в главном меню приложений MS OFFICE. Принцип тот же, что и ранее описанный.

Открываем меню «Файл» => «Параметры» => «Настроить ленту». В левой панели выбираем пункт меню «Макросы», в правой – «Основные вкладки». Нажимаем на кнопку «Создать вкладку». В только что созданной вкладке автоматически появится новая группа. И вкладку и группу здесь же можно переименовать по вашему усмотрению.

Остается только выбрать нашу группу и добавить в нее макрос с помощью кнопки «Добавить».

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

Нажимаем сохранить.