Электронные таблицы и судоку
2: С помощью поля имени. Поле имени обычно находится левее строки формул. И поле имени, и строка формул обычно расположены чуть выше заголовков столбцов Эксель (рисунок 2.8).
Рисунок 2.8.
Обычно в поле имени отображается адрес текущей ячейки. Если выделен диапазон ячеек, тогда в поле имени отображается левая верхняя ячейка этого диапазона.
Если выделенный диапазон или текущая ячейка уже имеют ранее присвоенное имя, то при выделении этого диапазона или этой ячейки в поле имени будет отображаться именно это имя. Если какой-то ячейке или диапазону одновременно присвоено несколько имен, тогда в поле имени будет отображаться самое первое имя по алфавиту. Если один и тот же диапазон ячеек содержит и те имена, что начинаются с латинских букв, и те имена, что начинаются с русских букв, то в поле имени будет изображено то имя, которое начинается с латинской буквы. Одно и то же имя может содержать одновременно и латинские, и русские буквы. Это можно использовать, в частности, при создании имен с префиксами — то есть с небольшими «примечаниями».
Хотелось бы еще несколько слов сказать о том, какие есть правила для создания имен. В имени не может быть пробелов. Имя должно начинаться с буквы или знака нижнего подчеркивания, но не может начинаться с цифры.
Начиная со второго знака имени можно использовать следующие символы: буквы, цифры, точки и знаки нижнего подчеркивания. Любые другие символы запрещено использовать, в том числе и пробел. В любом случае, при попытке ввести имя, которое будет создано с нарушением правил (например, при применении недопустимого для имени символа), мы не сможем это имя добавить в список имен, а получим сообщение, включающее в себя значок Warning Message, то есть сообщение с предупреждением, это сообщение обычно содержит большой желтый треугольник с восклицательным знаком. Кроме того, все попытки создать имя с ошибкой приведут не только к сообщению с предупреждением, но и к тому, что имя с ошибкой создано не будет, на данном этапе процесс создания имени будет завершен (и отменен), новое имя с ошибкой создано не будет.
Итак, второй способ присвоения имени в Эксель связан именно с полем имени.
Если сначала выделить нужную ячейку или диапазон ячеек, а затем щёлкнуть в поле имени и ввести то самое имя, которое хотим присвоить ячейке или диапазону ячеек, а затем нажать на клавишу Enter, то получим новое имя в Эксель. Это имя встанет на смену адреса левой верхней ячейки выделенного диапазона или просто на смену адреса выделенной (то есть текущей) ячейки.
Если мы выделим какой-то диапазон ячеек и попытаемся этим способом присвоить выделенному диапазону или ячейке такое имя, которое уже принадлежит какому-то другому диапазону или ячейке, то мы просто перейдем к тому диапазону или ячейке, которым это самое имя было присвоено ранее. Другими словами, диспетчер имен можно использовать не только для создания новых имен ячеек Эксель или их диапазонов, но и для перехода к ячейкам или диапазонам с известными именами, присвоенными ранее. Кроме того, нельзя присвоить какое-то имя еще раз, если это же имя уже имеется и принадлежит другой ячейке или другому диапазону ячеек. А еще в диспетчере имен можно набирать просто адрес ячейки, например: D1 или R40. Не важно, присвоено ли этой ячейке какое-то имя или не присвоено, все равно с помощью диспетчера имен можно попасть к любой ячейке Эксель, если ввести ее адрес. Поэтому, говоря о правилах создания имен для ячеек Эксель и их диапазонов, нужно добавить еще один пункт: эти имена не должны совпадать с уже существующими адресами ячеек и их диапазонов. Например, нельзя присвоить имя S17 или AAN14.
А применительно к нашей конкретной ситуации (напомню, речь идет о том, что надо присвоить имя стр_1 первой (верхней) строке судоку) нужно поступить так: мы уже заранее выделили нужный диапазон (это строка 1 судоку, она находится в интервале от C3 до K3 Эксель), и нам осталось только одно: ввести новое название интервала стр_1 в поле имени. После этого нажимаем на клавишу Enter. Вот что должно получиться после этого (рисунок 2.9):
Рисунок 2.9.
Тут стрелочкой показано поле имени, в котором уже есть новое имя. Единственное, что можно добавить: в случае присвоения имени с помощью поля имени мы не сможем сразу же при создании нового имени создавать и примечание к этому же имени. Но при необходимости это примечание к имени можно будет добавить через тот же диспетчер имен, о котором мы говорили чуть ранее.
Небольшое отступление от основной темы. Здесь (на рисунке 2.9) в ячейке B2 Эксель мы впервые встречаем цифру. Это цифра 39, она означает количество заполненных клеток в основном судоку. Чтобы добавить эту цифру к судоку, нужно ввести в ячейку B2 Эксель следующую формулу:
= СЧЁТ (C3:K11)
Напоминаю, что перед скобкой, как и после знака равенства, пробелы не нужны, они добавлены автозаменой издательства.
5) Выполним команду «Остановить запись», то есть остановим запись макроса. Эту команду можно найти на вкладке «Разработчик», в группе «Код» Во время записи макроса команда «Запись макроса» превращается в команду «Остановить запись».
Хотя мы создали целый макрос, присвоили ему имя (Имя_строкам_судоку), но у нас пока есть не окончательная, а черновая, предварительная версия этого макроса. Этот макрос пока присваивает имя только первой (верхней) строке судоку. Позже мы исправим этот макрос. Необходимо сделать его таким, чтобы была возможность присвоения имени не одной строке судоку, а каждой строке. Но основной текст макроса, его «каркас», мы уже создали.
Таким образом, имя для первой (верхней) строки судоку создано.
2.3.2. Имена для остальных строк судоку
Итак, только что было создано имя для первой (верхней) строки судоку. Но всего в судоку есть не одна строка, а 9 строк. Чтобы присвоить имена каждой строке судоку, нам нужно будет изменить тот макрос, который присваивал имя первой строке судоку. Что для этого надо сделать?
1) Войдем во вкладку (в меню) Разработчик, группу «Код», выберем там подменю (кнопку, команду) Макросы. Найдем (выделим) макрос под названием Имя_строкам_судоку, нажмем на кнопку Изменить.
Мы увидим текст самого макроса. Вот это текст:
↓ ↓ ↓ ↓
Sub Имя_строкам_судоку ()
′
′ Имя_строкам_судоку Макрос
′
′
Range(″C3:K3″).Select
ActiveWorkbook.Names.Add Name:=″стр_1″, RefersToR1C1:= _
″=Основное_судоку! R3C3:R3C11″
ActiveWorkbook.Names(″стр_1″).Comment =″″
End Sub
↑ ↑ ↑ ↑
Далее покажем скриншот этого макроса (рисунок 2.10):
Рисунок 2.10.
Итак, уточним, из чего обычно состоит любой макрос.
Первая строка макроса начинается с оператора Sub (это означает «начало макроса»); далее следует имя макроса, а также пустые скобки.
Любой макрос в Эксель можно назвать процедурой. Процедура в Excel на языке VBA — это последовательность инструкций, расположенных в модуле, доступ к которому осуществляется через VBA.
Пустые скобки после имени макроса в Excel обозначают, что процедуре не передаётся никаких аргументов. Чисто теоретически, могут существовать и такие макросы или процедуры, которым передаются какие-то аргументы с самого начала, в этом случае после оператора Sub и названия процедуры будут находиться не пустые скобки, а скобки с какой-то информацией внутри. В данной книге мы эти варианты подробно рассматривать не будем, здесь мы только объясняем, почему почти во всех макросах сразу после имени макроса можно встретить пустые скобки.
Затем идут несколько строк макроса, которые содержат знак апострофа (′). В макросах апостроф — это то же самое, что и оператор Rem, то есть начало ремарки, комментария.
В любом макросе, созданном с помощью команды «Запись макроса», всегда будут присутствовать несколько строк с ремарками (комментариями), причем одна из этих ремарок дублирует первоначальное имя, присвоенное этому макросу. И у макроса, который мы назвали Имя_строкам_судоку, тоже в самом начале должны присутствовать эти строчки. Эти строки с ремарками (комментариями к макросу) часто бывают не нужны совсем, поэтому мы можем эти строки с ремарками удалить. Хотелось бы сообщить, что в данной книге все макросы, созданные с помощью команды «Запись макроса», будут показаны без дополнительных ремарок, которые автоматически создаются в начале макроса. Правда, при желании какие-то свои ремарки можно добавлять, причем ремарки можно помещать в любые места программы (макроса). В принципе, можно и оставить те ремарки, что создаются автоматически в самом начале (другими словами, удаление этих самых ремарок — это ритуал не обязательный, и если они не мешают, то их можно оставить).
Таким образом, текст макроса после удаления строк с ремарками (примечаниями) будет следующим:
↓ ↓ ↓ ↓
Sub Имя_строкам_судоку ()
Range(″C3:K3″).Select
ActiveWorkbook.Names.Add Name:=″стр_1″, RefersToR1C1:= _
″=Основное_судоку! R3C3:R3C11″
ActiveWorkbook.Names(″стр_1″).Comment =″″
End Sub
↑ ↑ ↑ ↑
Дублируем этот текст макроса картинкой-скриншотом (рисунок 2.11):
Рисунок 2.11.
Итак, любые макросы обычно завершаются строкой «End Sub», что означает завершение макроса. Иногда можно запрограммировать выход из программы и в середине макроса тоже (например, при выполнении определенных условий, которые прописаны в операторах If...Then, что означает в переводе на русский язык Если… Тогда), но в этом случае выход из макроса будет не «End Sub», а «Exit Sub». В любом случае, в макросе может только один раз находиться команда «End Sub», и она может присутствовать исключительно в самом конце макроса.
Предпоследняя строчка этого макроса (та, что находится чуть выше строки End Sub, добавляет комментарии, но не к самому макросу, а к тем именам в Эксель, которые присоединяет к нашему файлу этот самый макрос). Две пустые кавычки в самом конце этой строки макроса означают, что в данном случае применен пустой комментарий, можно сказать, что никакого комментария к создаваемым именам нет вообще.
Конечно же, при желании можно добавить комментарий к именам, расположенным в Эксель. Например, предпоследнюю строчку макроса (ту самую строчку, что находится чуть выше строки End Sub), можно заменить следующим образом:
↓ ↓ ↓ ↓
ActiveWorkbook.Names(″стр_1″).Comment =″строка 1 судоку″
↑ ↑ ↑ ↑
Продублируем эту строчку макроса скриншотом (рисунок 2.12):
Рисунок 2.12.
Если комментарии к именам не нужны совсем, можно эту строчку макроса пропустить. А можно перед этой строчкой оставить апостроф — знак ремарки — и тогда можно будет позже в любое время снова вернуть строчку с комментарием, удалив знак ремарки. В любом случае, все эти комментарии к именам видны только в диспетчере имен. Он находится во вкладке «Формулы», в группе «Определенные имена». Там находятся все имена, и если у какого-то имени имеется примечание, то оно тоже будет видно в диспетчере имен. Если книга Эксель содержит много имен, то примечания к именам позволят сориентироваться и не запутаться в этом огромном массиве имен. Главное в том, чтобы создаваемое примечание было бы понятным. И сами имена, и примечания к ним можно добавлять как вручную, так и с помощью макросов. Если есть необходимость добавить, изменить или удалить примечание к имени, это можно сделать в любое время с помощью диспетчера имен.
Далее нужно изменить текст этого макроса таким образом, чтобы один макрос смог присвоить имена каждой строке судоку. У нас же имеется 9 строк судоку, а не одна. Можно, конечно, каждой строке присвоить имя отдельно и без макроса, но этот путь не самый быстрый и не самый лучший.
Вот каким получится измененный текст макроса:
↓ ↓ ↓ ↓
Sub Имя_строкам_судоку ()
For i = 3 To 11
Range (Cells (i, 3), Cells (i, 11)).Select
ActiveWorkbook.Names.Add Name:=″стр_″ & i — 2, RefersToR1C1:= _
″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″
ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″″
Next i
End Sub
↑ ↑ ↑ ↑
Скриншот этого макроса в редакторе VBA покажем на рисунке 2.13:
Рисунок 2.13.
Здесь хотелось бы сделать небольшое отступление от основной темы (от темы судоку) и рассказать про разбивку той информации, что в тексте макроса заключена внутри кавычек. Сравним макросы, скриншоты которых находятся на рисунках 2.11 и 2.13. Обратим внимание только на одну строчку — на ту, которая добавляет комментарии к именам. Вот как эта строчка выглядела в первом макросе:
↓ ↓ ↓ ↓
ActiveWorkbook.Names(″стр_1″).Comment =″″
↑ ↑ ↑ ↑
И вот как та же строчка выглядит уже во втором случае:
↓ ↓ ↓ ↓
ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″″
↑ ↑ ↑ ↑
Приведенные фрагменты разных вариаций одного и того же макроса очень похожи между собой. Разница только в маленьком фрагменте. В первом случае это (″стр_1″). А втором — (″стр_″ & i — 2).
Мы заменили цифру 1 на i-2, то есть на формулу, в которой есть и переменная (i), и действие вычитание. Эта замена произошла в соответствии со всеми правилами. Все замененные элементы мы освободили от кавычек, а также отделили от других частей формулы VBA знаком амперсанд (&), который со всех сторон окружен пробелами. Мы еще много раз будем встречать подобные ситуации, когда в формулах внутри ячеек Эксель или в фрагментах макроса в редакторе VBA придется разбивать текст, который находится внутри кавычек, заменять фрагменты этого текста. В других главах этой книги еще раз будут подробно описаны правила подобных замен. Это дублирование происходит специально, поскольку данная замена текста внутри кавычек имеет очень большое значение, она может пригодиться при решении разных задач программирования.
А теперь, продолжим. Мы видим, что в тексте этого макроса (Имя_строкам_судоку) несколько раз используется выражение «i-2». Это значит, что мы можем ввести другую переменную (например, m), которая будет равна этому самому значению i-2. Тогда макрос будет выглядеть так:
↓ ↓ ↓ ↓
Sub Имя_строкам_судоку ()
For i = 3 To 11
m = i — 2
Range (Cells (i, 3), Cells (i, 11)).Select
ActiveWorkbook.Names.Add Name:=″стр_″ & m, RefersToR1C1:= _
″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″
ActiveWorkbook.Names (″стр_″ & m).Comment = ″″
Next i
End Sub
↑ ↑ ↑ ↑
А вот и скриншот этой вариации макроса (рисунок 2.14):
Рисунок 2.14.
Если нужно добавить комментарии к каждому имени, которое присваивается макросом (не надо путать комментарий к имени для ячейки или диапазона ячеек, который виден только в диспетчере задач, с комментариями к макросу, которых в макросе может быть несколько штук, они обычно дают пояснения к той или иной строке макроса, или ко всему макросу в целом), тогда строка макроса перед строкой ″Next i″ будет такой:
↓ ↓ ↓ ↓
ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″
↑ ↑ ↑ ↑
Скриншот этой строки макроса приведем ниже (рисунок 2.15):
Рисунок 2.15.
Если мы используем новую переменную m, тогда эта же строка будет выглядеть так:
↓ ↓ ↓ ↓
ActiveWorkbook.Names (″стр_″ & m).Comment = ″строка ″ & m & ″ судоку″
↑ ↑ ↑ ↑
Скриншот этой строки макроса изобразим на рисунке 2.16:
Рисунок 2.16.
Макрос готов, но у него есть один существенный недостаток: этот макрос очень медленный.
Поэтому сейчас мы рассмотрим, как именно можно ускорить работу многих макросов (конечно же, речь идет об ускорении не сколько работы макросов, сколько об ускорении Эксель в целом).
Многие макросы замедляют свою работу по следующим причинам:
1. Часто срабатывает обновление экрана.
2. Постоянно происходит автоматический пересчет формул.
3. Постоянно происходит отслеживание событий.
4. Если включена разбивка на печатные страницы, то она тоже замедляет работу макросов.
Все эти перечисленные пункты замедляют не столько работу макросов, сколько работу самого Эксель в целом. Чтобы существенно увеличить скорость работы, нужно создать два макроса (точнее говоря, первый макрос будет отключать все то, что замедляет работу макроса (и Эксель), а второй макрос снова включит то, что было отключено первым макросом).
А вначале хотелось бы сказать о том, что макросы можно создавать не только с помощью команды «Запись макроса». Есть и еще один способ создания: непосредственно в редакторе VBA. В этом случае текст макроса будет набираться непосредственно с клавиатуры.
Каждый код VBA должен где-то храниться. Для хранения кодов в VBA используются модули, эти модули обычно расположены в книге, то есть в том самом файле, в котором и размещены все листы этой книги (этого файла). Книга может содержать достаточно много разных модулей. Кроме того, каждый модуль может содержать множество процедур (макросов).
Итак, расскажем подробно, как можно зайти в редактор VBA:
1. Найдем вкладку «Разработчик», выберем группу «Код». Затем выберем команду «Visual Basic» (рисунок 2.17):
Рисунок 2.17.
После этого мы войдем в этот самый редактор VBA. Главный его недостаток состоит в том, что эта часть Эксель обычно не переводится на разные языки мира, поэтому необходимо просто смириться с тем, что основной язык редактора VBA — английский.
Кстати, в этой книге несколько раз уже говорилось о том, что тексты разных макросов можно копировать из электронной версии этой книги (затем, при необходимости, исправить те «ошибки», которые могут присутствовать из-за автозамены символов, которая присутствует в издательстве, публикующем мою книгу, об этом уже говорилось ранее), а затем вставлять нужный текст макросов в редактор VBA. Именно команда (кнопка) «Visual Basic», о которой говорится сейчас, позволит перейти в этот самый редактор VBA. В этом редакторе тексты макросов можно не только вставлять, предварительно скопировав их из любых источников, но и просто набирать с клавиатуры.
Итак, как уже говорилось ранее, нам необходимо создать еще два макроса, и создавать мы их будем непосредственно в редакторе VBA, или просто в редакторе. Первый макрос будет включать ускорение Эксель, а второй макрос будет выключать ускорение, то есть возвращать всё на свои места. Между запуском этих двух макросов нужно запустить «основной макрос», то есть тот самый макрос, работу которого необходимо ускорить, но этот пункт мы рассмотрим достаточно подробно чуть позже, пока лишь рассмотрим только те макросы, которые включают и выключают ускорение.
Конечно же, работа «с ускорением» имеет и «плюсы», и «минусы». Главный «плюс» — ускоренная работа Эксель. Главный «минус» в том, что это ускорение применимо не ко всем макросам. Лучше перед использованием так называемого «ускорения» сделать копии нужных файлов, чтобы не потерять важные данные. В любом случае, если в данной книге приводятся какие-то макросы и содержится рекомендация использовать эти макросы с «ускорением» (либо приводится текст макросов, в котором видно, что этот макрос используется с «ускорением»), то это значит, что при применении «ускорения» к этим конкретным макросам мы получим только сокращение времени выполнения макроса, и при этом не будет наблюдаться никаких негативных последствий (например, «зависания» Эксель или искажения данных). К сожалению, всего этого не всегда можно будет наблюдать, если мы будем использовать так называемое «ускорение» для любых других макросов.
Во время так называемого «ускорения» нет пересчета формул, и это значит, что некоторые данные могут отражаться некорректно, это касается зависимых ячеек. Но все эти недостатки исправятся после запуска того макроса, который выключает ускорение. Итак, вот тексты этих макросов. Первый макрос включает ускорение.
↓ ↓ ↓ ↓
Sub Ускорение_включить ()
′Отключаем обновление экрана
Application.ScreenUpdating = False
′Отключаем автоматический пересчет формул
Application.Calculation = xlCalculationManual
′Отключаем отслеживание событий
Application. EnableEvents = False
′Отключаем разбивку на печатные страницы
ActiveWorkbook.ActiveSheet. DisplayPageBreaks = False
End Sub
↑ ↑ ↑ ↑
Скриншот этого макроса разместим на рисунке 2.18:
Рисунок 2.18.
И второй макрос, который выключает ускорение:
↓ ↓ ↓ ↓
Sub Ускорение_выключить ()
′Возвращаем обновление экрана
Application.ScreenUpdating = True
′Возвращаем автоматический пересчет формул
Application.Calculation = xlCalculationAutomatic
′Включаем отслеживание событий
Application. EnableEvents = True
End Sub
↑ ↑ ↑ ↑
Скриншот этого макроса — на рисунке 2.19:
Рисунок 2.19.
В том макросе, который выключает ускорение, нет ни слова о включении разбивки на печатные страницы. Это не ошибка, просто в этой опции (имеется ввиду разбивка на печатные страницы), действительно, обычно не бывает ни смысла, ни необходимости, тем более в тех случаях, если не надо распечатывать данные из файла. В общем, лучше эту опцию (то есть разбивку на печатные страницы) использовать только при самой крайней необходимости, а если ее нет, то не использовать вовсе.
Мы уже говорили о том, что само «ускорение», о котором говорится в данной книге, можно применить не ко всем макросам. Более подробно мы расскажем про то, где применять ускорение нельзя, или можно, но с определенными особенностями, мы расскажем позже, в последних главах этой книги. Пока лишь скажем одно: если в макросах совсем нет формул для ячеек Эксель, но есть присвоение (создание) имен для ячеек Эксель и их диапазонов, построение графиков, то можно смело применять это самое «ускорение».