Доработка макроса I

Доработка макроса I в vba excelВ прошлой статье мы освоили макрорекордер.

Однако, к сожалению, он не всегда дает ожидаемый результат.

Вот и в этот раз, как я заметил позднее, после первого обновления таблица начала сбоить.

Поэтому, как ни крути, придется нам все-таки влезть в код, и поправить ситуацию. А, за одним, рассмотрим логику программирования VBA.

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

Для начала, нам необязательно даже знать всех имен и обозначений объектов, свойств и команд. Нужно лишь просмотреть записанный макрос, и понаблюдать за его выполнением. Впрочем, знающие английский язык окажутся в более выигрышном положении. Поскольку все элементы языка VBA это, фактически, английские слова, зная которые можно догадаться о каком объекте или о какой команде идет речь.

Итак — займемся разборкой записанного ранее макроса.

Изучение записанного макроса

Как вы помните, мы копировали прайс на все страницы файла, а, затем, удаляли лишние колонки. Это все, что делал макрос. Однако, несмотря на простоту алгоритма, не все в нем работает хорошо. Ошибки могут возникать самые различные, и, в итоге, нам придется каждый раз исправлять их.

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

Входим в закладку меню «Разработчик» и жмем на кнопку «Visual Basic«. Это мы открываем редактор для ручного ввода макросов на языке VBA. Вот что мы видим (с небольшими вариациями):

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

Вроде бы ничего сложного, правда?

Внесение исправлений в макрос

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

Но, собственно, нам нет необходимости выделять ненужные колонки.  Мы просто удалим их без выделения. А для этого нужно просто удалить команду выделения (select), и вместо нее вставить команду удаления обозначенных колонок. Таким образом строка будет выглядеть так: Columns(«F:J»).Delete Shift:=xlToLeft

К слову сказать — очистка буфера нам совсем не помешает. Зачем нам в оперативной памяти иметь скопированную таблицу прайса. Поэтому до команды удаления оставим команду очистки буфера.

Аналогичным образом исправим все прочие команды удаления колонок на остальных листах файла.  Весь блок, посвященный удалению лишних колонок, будет выглядеть так:

Как видите, я не вспоминал никаких команд, а использовал только те, которые уже имелись. Однако есть один момент, который мне не очень нравится — если мы начнем обновлять прайс и запустим макрос, то он начнет вставлять значения поверх старых записей. Этого лучше избегать, чтобы программа работала с чистого листа и не возникало даже подозрения на возможную ошибку наложения.

Команда очистки пишется так: Clear. Это единственное слово, которое отсутствовало в макросе, но  его не мешало бы и запомнить. Для очистки листа нужно его выделить (Sheets(«Прайс123»).Select), и дать команду на очистку всех ячеек (Cells.Clear).

Далее, для вставки скопированного диапазона, я не выделял весь лист, а просто активировал ячейку A1 (Range(«A1»).Select). Полностью исправленный макрос выглядит так:

Теперь он стал намного надежнее и будет формировать прайсы без ошибок.

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

Доработка макроса II

Доработка макроса II в vba excelСозданный в макрорекордере макрос, как мы уже убедились ранее, не всегда будет работать именно так, как бы нам хотелось.

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

Но как сориентироваться в том, что там написано? Ведь даже знающие английский язык не всегда поймут, какое действие зашифровано.

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

Запись макроса

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

Что меня здесь не устраивает?

  • Множество объединенных ячеек мешает воспринимать данные как таблицу. С точки зрения оформления это — неплохо, но как мы будем писать формулы без риска ошибиться? Кроме того, когда мы будем протягивать формулы на другие ячейки, они будут у нас сбиваться на ненужные диапазоны.
  • В каждой строке у нас расположены наименования должностей. А наименования подразделений и департаментов находятся в объединенных ячейках, а, значит, соответствуют только одной верхней строке того диапазона, который они объединяют. Нужно разбить ячейки и скопировать записи на каждую строку, чтобы программа однозначно понимала, что такая-то должность находится в таком-то подразделении такого-то департамента.
  • Чтобы таблица воспринималась программой целостно, в колонках и строках не должно быть разрывов. А это произойдет, когда мы отменим объединение ячеек.

В общем-то исправить нужно не так уж и много, но когда мы этим займемся, поймем, что манипуляций придется проделать немало, и лучше бы все автоматизировать. Для этого сохраним файл в формате с поддержкой макросов (с расширением .xlsm). Открываем закладку меню «Разработчик» и жмем на пункт «Запись макроса«. Я установил имя «Штатное«. Далее выполняем последовательно следующие операции:

  1. Выделить весь лист (нажать мышкой на пересечение адресных строк и колонок);
  2. Нажать на кнопку «Объединить и поместить в центре» в закладке меню «Главная» (таким образом мы отменили объединение ячеек);
  3. Вызвать контекстное меню (правая кнопка мыши) и выбрать «Формат ячеек«. Там:
  4. За закладке «Выравнивание» настроить «Ориентацию» 0 градусов;
  5. Установить начертание «Обычный«, а размер 10 пунктов;
  6. В закладке меню «Главная» найти и нажать пункт «Формат«. В открывшемся списке выбрать пункт «Ширина столбца» и установить ширину 5 (чтобы проще было удалять пустые колонки);
  7. Удалить все колонки без записей. При этом, не обращайте внимания на строки с 1 по 12. Их мы потом тоже будем удалять. У вас должны остаться все пронумерованные колонки с 1 по 10;
  8. Настроить ширину колонок как вам удобнее;
  9. Дать новые имена колонкам (чтобы наименование колонки располагалось в одной ячейке;
  10. Удалить строки с 1 по 15;
  11. Протянуть наименования департаментов, чтобы каждая должность соответствовала тому департаменту, к которому она относится;
  12. Так же протянуть наименования подразделений;
  13. Увеличить шрифт заголовков;
  14. Выделить заголовки жирным;
  15. Поправить ширину колонок;
  16. Остановить запись макроса.

Вот что должно примерно получиться:

Удаление лишних записей

Теперь давайте откроем вкладку меню «Разработчик«, и нажмем пункт Visual Basic. Я предлагаю свернуть его в оконный режим, и разместить в нижней части экрана, чтобы в верхней части мы видели нашу рабочую область в Excel (см.рис.).

В меню редактора VBA есть пункт Debug. В переводе он означает «Отлаживать«. То есть, другими словами, в этом меню находятся команды для отладки макроса. Открываем его и видим пункт, который нас интересует — Step Info — пошаговый режим. С его помощью мы будем проверять работу каждой команды макроса. Чтобы не лезть всякий раз в меню, проще запомнить и использовать «Горячую клавишу» F8. Каждое ее нажатие запустит одну команду макроса.

Итак, жмем клавишу F8 и… получаем сообщение об ошибке. Оно говорит нам, что процедура слишком большая. Это произошло из-за того, что команда по отмене объединения ячеек воспринялась программой своеобразно — она начала проверять каждую выделенную ячейку на предмет объединения и, кроме того, выполнять массу других команд, которых мы, собственно, не задавали. Жмем на кнопку остановки (квадратик во второй строке меню), и смотрим на текст макроса.

Видим повторяющиеся блоки, начинающиеся со слова With, и заканчивающиеся словами End With. Это блочная команда, которая назначает свойства объектам. Она повторяется много раз, и перегружает наш макрос. А нужно то всего лишь одно свойство — отмена объединения по отношению ко всем выделенным ячейкам сразу. То есть, нужна только одна строка — отменить объединение ячеек (MergeCells = False), которые выделены (Selection).

Чтобы понять, что означает каждая команда в блоке With … End With, я просто посмотрел их перевод в гугле и понял, что за объединение отвечает команда со словом Merge. А Cells, это ячейки.

Все вышесказанное означает, что нам необходимо удалить ВСЕ блоки следующего содержания:

Это, не удивляйтесь, более 95% всего содержания макроса! Для упрощения задачи зажмите курсор мыши на каретке перемещения справа, и сдвиньте ее вниз примерно на 95%. Если все еще будут эти повторяющиеся блоки, то сдвигайте курсор ниже. Установите курсор после слова End With последнего повторяющегося блока (будьте внимательны, там есть другие блоки With … End With, но они с другим содержанием. Не удалите случайно их.

Теперь с помощью каретки поднимитесь наверх, зажмите Shift и кликните курсором мыши перед словом With второго блока (первый мы будем править). Нажмите Delete.

Для отмены объединения ячеек нам не нужна блочная команда. Мы ведь прочие свойства не трогаем. Поэтому удаляем блок With … End With, оставляя команды в одну строку Selection.MergeCells = False.

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

Доработка макроса III

Доработка макроса III в vba excelВ прошлой статье мы записали макрос и начали его правку — удалили лишние записи, обеспечив, тем самым, сокращение текста программы более чем на 95%!

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

Параметры шрифта и колонок

Следующий блок With … End With устанавливает ориентацию надписей 0 градусов. Это я понял по слову .Orientation = 0. Здесь нам, собственно, тоже целый блок не нужен. Оставляем и здесь одну строку: Selection.Orientation = 0

Далее мы с вами устанавливали параметры шрифта. Шрифт по английски — Font. Тут у нас тоже целый блок, но его я не стал трогать, поскольку мы изменяли две настройки, а находить их в блоке мне уже лень.

Следующие команды, это серия манипуляций с колонками — назначение ширины, удаление лишних колонок. Можно, конечно, с помощью клавиши F8 просматривать выполнение этих команд, но тут уже все очевидно — выделить (Select) колонки (Columns), удалить (Delete) выделенное (Selection) со смещением влево (Shift:=xlToLeft). Здесь все оставляем без изменений.

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

Затем мы писали наименования колонок. Команда Range(«A16»).Select выделяет нужную ячейку, а команда  ActiveCell.FormulaR1C1 = «Департамент» вносит в эту ячейку слово «Департамент«. И так с каждой колонкой. Две следующие команды выделяют и удаляют лишние строки.

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

Ручная запись макроса

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

С помощью макрорекордера можем выяснить как обозначается свойство «Нижняя толстая граница«:

  1. Открываем новый файл;
  2. Запускаем макрорекордер;
  3. В закладке меню «Главная» находим и жмем пункт «Нижняя толстая граница»;
  4. Останавливаем макрорекордер.

Таким образом выясняем, что свойство обозначается «.LineStyle = xlContinuous«.

В VBA есть три вида циклов:

  • по счетчику;
  • по условию;
  • по виду обрабатываемых объектов.

Я выбираю цикл по условию, поскольку в качестве условия мы уже выбрали нижнюю толстую границу ячейки. Цикл по условию выглядит так:

Do

команды, которые необходимо повторять

Loop Until условие, при котором цикл должен прекратиться

И еще одно новшество, которое мы ранее не использовали — назначение переменных. Это что-то похожее на присвоение имен ячейкам. Только имена мы присваиваем не ячейкам, а значениям, которые не появляются в таблице, но используются для вычислений. В нашем случае будет одна переменная, которую мы назовем, например, rngX (от слова Range — диапазон).

Назначаем переменные с помощью команды Dim … As …, где после Dim указываем имя, которое мы придумали переменной, а после As указываем тип данных. Тип данных может иметь много  значений:

String  Используется для хранения строк. До миллиона символов.
Integer Целое число от -32768 до 32767
Long Целое число от -2 147 483 648 до 2 147 483 647
Single Дробное число одинарной точности (до 8-го знака. Целые числа и запятая тоже считаются за знаки. )
Double Дробное число двойной точности (до 16-го знака. Целые числа и запятая тоже считаются за знаки.)
Date Полная дата, включая время. Пишется между решетками — #06/15/2012 10:00:00# Сначала пишется месяц, потом день, потом год.
Boolean Хранить только два значения — True и False
VARIANT Объявление переменной без указания типа. Требует много памяти, поэтому не рекомендуется использовать часто. Кроме того, может неверно определить данные.
Range Адрес ячейки или ссылка на диапазон

Нам подходит тип Range.

Запись 1-го цикла

Итак, после обозначения переменной, присваиваем ей адрес первой ячейки обрабатываемого диапазона — А4 (Set rngX = Range(«A4»)). Затем объявляем начало цикла словом Do. В следующей строке, копируем значение, которое находится в переменной, который мы указывали ранее: rngX.Copy

Далее необходимо переместить переменную вниз. Для этого мы используем команду Set (которая закрепляет за переменной какой-либо объект) и команду Offset (которая смещает объект по горизонтали или вертикали). Вот как это выглядит: Set rngX = rngX.Offset(1, 0)

Следующий шаг — вставка скопированного значения. Мы могли бы ограничиться командой PasteSpecial, но она вставит полностью всю ячейку, и, в этом случае, мы потеряем толстую нижнюю границу в соответствующих ячейках. Поэтому используем ограничение, которое пишется так: Paste:=xlPasteValues. Полностью строка команды будет такой: rngX.PasteSpecial Paste:=xlPasteValues

И, последнее, установим условие прекращения цикла. Как мы помним, таким условием является толстая граница ячейки, обозначаемая LineStyle = xlContinuous. Но, прежде чем указывать свойство, нам нужно обозначить объект, которым является нижняя граница нашей переменной. А обозначается она rngX.Borders(xlEdgeBottom).

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

Мы записали цикл до первого жирного разделителя. А таблица то у нас продолжается! Поэтому этот первый алгоритм необходимо вложить в другой, который будет запускать его со следующей строки. Об этом мы поговорим в следующей статье.

Доработка макроса IV

Доработка макроса IV в vba excelВ прошлых статьях мы отредактировали записанный в макрорекордере макрос и начали писать часть этого макроса вручную.

Записали первый цикл, который протягивает наименование департамента до ячейки с нижней жирной границей.

Продолжим. Этой статьей мы завершим работу, а в конце вы увидите ссылку, по которой сможете скачать файл с макросом, который у меня получился. Все комментарии в тексте макроса я оставил, и вы можете в нем без труда разобраться.

Запись 2-го цикла

Первый алгоритм с протягиванием наименования департамента (о нем мы писали в прежней статье) необходимо вложить в другой, который будет запускать его со следующей строки. По-русски весь алгоритм будет выглядеть примерно так:

  • Начало 2-го цикла
    • Начало 1-го цикла
      • Скопировать значение переменной
      • Сместить переменную на 1 строку вниз
      • Вставить скопированное значение
    • Закончить цикл на ячейке с толстой нижней границей
    • Сместить переменную на 1 строку вниз
  • Закончить цикл если ячейка на две колонки справа от переменной пустая

Выглядеть дополненный макрос будет так:

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

Условие будет проверяться с помощью наиболее часто используемой логической команды «Если … То …». Выглядит она так: «If … Then … End If«. Вот так будет выглядеть окончательный вариант макроса, протягивающего наименования департаментов.

Завершение редактирования макроса

Но у нас есть еще отделы! Фактически, там те же самые циклы, только со смещением на одну колонку. Поэтому можно просто скопировать предыдущий макрос с момента, когда мы определяем адрес переменной, и сменить, во первых, этот адрес на B4, и там, где проверяется условие на пустые ячейки, делаем смещение не на две, а на одну колонку.

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

Это же условие вставляем в начало цикла, работающего с департаментами.

Вот что получилось у меня:

Теперь идем в старый макрос, удаляем оттуда все команды протягивания (они все типичные и выглядят как на картинке), и вставляем наш макрос без заголовка «Sub Протягивание()» и команды окончания макроса внизу «End Sub«. В конце макроса остаются команды изменения параметров колонок (их я удалять не стал) и команды скроллинга (их я удалил).

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

P.S.: К слову казать, нередко отчеты из 1С  тоже нуждаются в исправлениях. Можно, конечно, попросить об отладке программиста 1С, но не всегда такие есть под рукой. А заранее записанный макрос по типу этого вполне справится с исправлением любого выгруженного из 1С отчета.