Доработка макроса 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.

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *