Excel VBA. На старт

Последнее время на работе занималась приведением excel-файлов к виду, при котором пользователь может допустить наименьшее количество ошибок при заполнении. До этого первый и последний раз я сталкивалась с visual basic в школе, причем не применительно к Ms Excel, поэтому потребовалось некоторое время на то, чтобы понять, как делать простые и, и тем более не очень, вещи. Эта статейка может помочь людям, оказавшимся в похожей ситуации, быстрее войти в курс дела. Демо-экселька прилагается.

Основы

Самый первый вопрос, который у меня возник, после того как вкладка "Разработчик" была добавлена (подробнее тут, например) и открыт встроенный vba-редактор, это куда собственно писать код. По умолчанию в книге есть модули страниц, модуль книги и один общий модуль. Существуют также модули формы и классов, но сейчас речь не о них. Все вполне логично: обработку событий на объекты страницы пишем в соответствующий лист страницы, обработку событий для книги - в модуль книги, а общие функции для всех листов - в общий модуль. При этом имеет смысл создать еще модули для отделения функционала по смыслу.

Также сразу возникает вопрос, а как элементарно обратиться к ячейке, найти ее значение и перезаписать его. Очень просто:

' в ячейку А1 пишем "add note" + значение ячейки B1
Cells(1,1).Value = "add note " & Cells(1,2).Value

К массиву же ячеек обращаться можно так (полезная ссылка по увеличению производительности и ускорению работы макросов)

For Each oCell In Range("A1:A10").Cells
     'обработка 
     oCell.Value = oCell.Row ' присвоили номер строки   
Next 

Моим следующим вопросом было каким образом вернуть значение из функции. В vba есть два типа функций: sub и function. Разница в том, что function как раз и умеет возвращать значение, для этого в коде функции надо написать ИмяФункции = возвращаемое_значение:

Public Function insertRow()
    Dim position As Integer
    '...
    insertRow = position 'возвращаемое значение

End Function

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

If ActiveSheet.Index = 1 _
   Or ActiveSheet.Index = 4 Then
        ' действия
End If

Нарочно и не придумаешь.

Помимо массивов, в VBA следует обратить внимание на коллекции. Работать с ними просто:

Dim сoll As New Collection
сoll.Add ("элемент1")'добавляем элемент
сoll.Add ("элемент2")'размер увеличивается динамически
' по умолчанию индекс начинается с 1, можно изменить
MsgBox (сoll.Item(1)) 'элемент1
сoll.Remove (1) ' удаляем первый элемент
MsgBox (сoll.Count)'выводим размер коллекции

Чтобы когда мы творим беспредел с нашим excel-файликом посредством vba, он предательски не корчился и напоказ не дергал строками и столбцами перед пользователем (например, при удалении и скрытии интервалов), можно отключать прорисовку:

Application.ScreenUpdating = False ' значение True включает

Автоматическая нумерация строк таблицы

Одной из первых моих мелких подзадач стала нумерация строк таблицы. Можно, конечно, при добавлении строки вставлять номер функцией, но хотелось по возможности чтобы это происходило само собой. Как выяснилось, нумеровать ячейки можно формулой =СТРОКА(ячейка), которая возвращает номер строки ячейки-параметра. Например, формула =СТРОКА(А1), вставленная в произвольную ячейку, выведет 1, строка =СТРОКА(А3) - цифру 3. 

Таким образом, при копировании формулы в следующую строку нумерация таблицы продолжится:

'проставляем формулу нумерации

Range("A7").FormulaR1C1 = Range("A6").FormulaR1C1 

Если нужно также копировать формат ячейки, используется метод AutoFill.

Тут может возникнуть одна неприятность. Пусть наша последовательность ячеек с формулами начинается  не с первой строки, а например, с третьей. Тогда в третьей строке будет формула =Строка(А1), во второй - =Строка(А2), в 10 - =Строка(А10) и т.д. Тогда при удалении строки 6 формула в теперешней строке 9 выдаст ошибку "неверная ссылка". 

Это потому, что формула в строке А9 крайне смущена тем, что ссылается на удаленную ячейку. Поэтому если строки в таблице буду удаляться и добавляться, лучше сделать ссылку на ячейки другой страницы (=СТРОКА(Лист2!А1)).

Кнопки вставки и удаления строк

Также неочевидными оказались некоторые нюансы при вставке и удалении строк в таблице по нажатию на соответствующие кнопки (под таблицей будем понимать визуально выделенный  диапазон ячеек, содержащий связанные по смыслу данные). Например, у нас на листе есть две таблицы, у каждой есть кнопка "добавить строку". При добавлении для каждой строки появляется кнопка "удалить".

Само по себе добавление и удаление по имени кнопки сложности не представляет:

Private Sub InsertButton1_Click(position as Integer)
   'вставка строки перед строкой с номером position
   Rows(position).Insert
End Sub

Private Sub DeleteButton1_Click()
   Dim position as Integer
   ' ищем номер строки кнопки удаления
   position = _            ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row  
   ' удаление кнопки
   ActiveSheet.Shapes(Application.Caller).Delete 
   ' удаление строки с номером position
   Rows(position).Delete
End Sub

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

Но вот проблемка, которую по внимательности можно сразу и не разглядеть. Вот мы добавили некоторое количество строк с кнопками в обеих таблицах, потом поудаляли и снова добавили. Возникает ситуация, при которой у нас на листе появляются кнопки с одинаковым именем (т.к. они создавались на строке с одним и тем же номером). В связи с этим возникают проблемы при их удалении (удаляется последняя кнопка из всех с одинаковым именем).

Нам нужен уникальный идентификатор. Чтобы не заниматься генерацией уникальных хэшей, можно, например, использовать имяКнопки_номерСтроки_текущаяДата. 

newButtonId = "delButton" & insertedRowNumber & Now()

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

Проверка листа на правильное заполнение 

Задача состоит в том, чтобы при сохранении файла проверять содержимое ячеек на всех листах и если какие-то помеченные как обязательные остались незаполненными, выдавать предупреждение пользователю. Для визуального выделения в зависимости от результата проверки содержимого ячеек на соответствие требованиям в excel можно использовать условное форматирование. Казалось бы, все просто: если мы используем условное форматирование и незаполненные ячейки (или неправильно заполненные, которые мы не смогли проверить при вводе с помощью меню "проверка данных") подкрашиваем, например, красным, то при сохранении все что нам нужно сделать - пройтись по всем ячейкам и проверить их цвет.

Но проблема вот в чем - если мы берем colorIndex или color ячейки, нам возвращается исходный цвет, не тот, на который он изменился при помощи условного форматирования.

В голову пришло два слегка костыльных способа решения (и честно говоря, если судить по форумам с обсуждением подобных проблем, кажется, что применительно к Excel по-другому их и не решишь).

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

usedRows = ActiveSheet.UsedRange.Rows.Count
usedCols = ActiveSheet.UsedRange.Columns.Count
' переводим последний столбец из цифры в букву
usedColsInNumb = Split(Cells(1, usedCols).Address, "$")(1)

For Each cell In ActiveSheet.Range("A1:" & usedColsInNumb & usedRows).Cells

   ' если колонка с результатом формулы проверки заполнена

   If Len(cell.Value) > 0 Then 

       ' если строка неправильно заполнена

       If cell.Value = "ЛОЖЬ" Then 

            ' сохраняем в массив, отражающий правильность 
            ' заполнения каждой строки
            notFilled(cell.Row) = True 

       End If

   End If

Next

Второй способ: изначально ячейки, значения которых необходимо проверять, выделять цветом. Далее, проходясь по всем ячейкам, искать выделенные и проверять на соответствие условиям. Тут также при помощи условного форматирования проблемные ячейки могут подсвечиваться красным. Этот способ проще, если все ячейки проверяем на одни и те же условия, иначе надо вводить несколько цветов или как-то иначе метить ячейки. Например, при помощи именованных диапазонов для каждой области с одинаковой проверкой.

For Each cell In Sheets(i).Range("A2:" & usedColsInNumb & usedRows).Cells

    ' проверяем выделенные цветом ячейки на пустоту

    If cell.Interior.Color = Sheets(5).Cells(1, 6).Interior.Color   And Len(cell.Value) = 0 Then

        ' обработка незаполненной ячейки

    End If

Next

Тут надо учитывать, что при использовании объединенных ячеек Excel искренне полагает, что содержимое находится в первой из них. Поэтому проверять на пустоту нужно только первую  ячейку из диапазона.

' ищем смежные с текущей ячейки в виде интервала, eg "A1:B10"

mergedInterv = Cells(cell.Row, cell.Column).MergeArea.Address(RowAbsolute:=False, ColumnAbsolute:=False)

' разбиваем адрес на первую и последнюю из смежных

splitAr = Split(mergedInt, ":")

' если текущая ячейка входит в объединенные

If UBound(splitAr) = 1 Then 

      splitAr2 = Split(cell.Address, "$")

      ' если первая из диапазона смежных равна текущей ячейке

      If splitAr(0) = splitAr2(1) & splitAr2(2) Then

          ' обработка незаполненной смежной ячейки

      End If

Else 

    ' если текущая ячейка не является смежной

End If

При нахождении последней используемой строки или столбца следует учесть, что если вы каким-то образом успели "поиспользовать" интервал (пусть даже выделили и очистили формат), то он все равно считается использованным. Придется удалить эти строки и столбцы.

Прилагаю демо-эксельку. Тестировалась в русифицированном MS Excel 2007.