Полная версия
Excel и судоку
Итак, второй способ присвоения имени в Эксель связан именно с полем имени.
Если сначала выделить нужную ячейку или диапазон ячеек, а затем щёлкнуть в поле имени и ввести то самое имя, которое хотим присвоить ячейке или диапазону ячеек, а затем нажать на клавишу 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.
В том макросе, который выключает ускорение, нет ни слова о включении разбивки на печатные страницы. Это не ошибка, просто в этой опции (имеется ввиду разбивка на печатные страницы), действительно, обычно не бывает ни смысла, ни необходимости, тем более в тех случаях, если не надо распечатывать данные из файла. В общем, лучше эту опцию (то есть разбивку на печатные страницы) использовать только при самой крайней необходимости, а если ее нет, то не использовать вовсе.
Мы уже говорили о том, что само «ускорение», о котором говорится в данной книге, можно применить не ко всем макросам. Более подробно мы расскажем про то, где применять ускорение нельзя, или можно, но с определенными особенностями, мы расскажем позже, в последних главах этой книги. Пока лишь скажем одно: если в макросах совсем нет формул для ячеек Эксель, но есть присвоение (создание) имен для ячеек Эксель и их диапазонов, построение графиков, то можно смело применять это самое «ускорение».
Впрочем, есть и еще один вариант: во время исполнения одного основного макроса можно несколько раз включать и выключать так называемое «ускорение», при этом искажения информация не будет, как и не будет наблюдаться «зависания» Эксель, но и скорость и ускорение Эксель увеличатся не очень сильно. Это связано с тем, что на включение и выключение ускорения тоже выделяется какое-то время. В этой книге данный вариант мы рассмотрим позже, когда перейдем к максимальной автоматизации создания судоку с помощью Эксель.
Но теперь продолжим говорить про ускорение тех файлов, для которых это ускорение действительно приемлемо, то есть приведет только к ускорению Эксель и не будет иметь никаких негативных последствий или искажения данных.
Если уже создан какой-то конкретный макрос (назовем его «макрос икс»), и необходимо увеличить скорость его работы (скажем более точно: нужно увеличить скорость работы Эксель во время выполнения этого макроса), то для решения этой задачи имеется несколько вариантов, можно выбрать любой вариант, который больше всего нравится.
Вариант №1: С помощью создания одного нового (дополнительного) макроса. Этот вариант можно реализовать с помощью команды «Запись макроса».
Все это можно осуществить за несколько шагов:
1: Начинаем запись нового макроса. Нажимаем на кнопку «Запись макроса», новому макросу присвоим имя:
Имя_строкам_судоку_быстро.
2: Запускаем макрос, включающий так называемое «ускорение». Чтобы запустить этот макрос, нужно найти вкладку «Разработчик», выбрать в ней группу «Код». Затем запустить команду «Макросы» (рисунок 2.20):
Рисунок 2.20.
После нажатия на кнопку «Макросы» мы увидим список всех макросов, которые уже есть в нашем файле.
В нашем конкретном случае сначала нужно найти макрос под названием Ускорение_включить; затем нажать на кнопку «выполнить», после чего дождаться, когда этот макрос выполнится.
Кстати, именно так (с помощью команды/кнопки «Макросы») и выполняются (запускаются) все макросы.
Обычно во время выполнения макроса многие надписи на кнопках команд групп становятся тусклыми, а след от мыши меняет свою форму. Как только макрос завершает свое выполнение, всё снова возвращается к своим обычным параметрам.
3. Запускаем «основной макрос». В нашем конкретном случае речь идет о макросе под названием Имя_строкам_судоку.
4. Запускаем макрос, выключающий так называемое «ускорение». напомню, этот макрос называется Ускорение_выключить.
5. Подождем, когда макрос, выключающий ускорение, завершит свою работу, а затем остановим запись макроса. Напомню, что остановить запись макроса можно с помощью той самой команды во вкладке «Разработчик» и в группе «Код», с которой мы и начинали запись макроса. Во время записи макроса кнопка «Запись макроса» преобразуется в кнопку «Остановить запись». Останавливая запись макроса, мы тем самым завершаем запись макроса под названием Имя_строкам_судоку_быстро.
Если мы захотим посмотреть текст этого макроса, то мы увидим следующую информацию:
↓ ↓ ↓ ↓
Sub Имена_строкам_судоку_быстро ()
Application.Run ″Судоку_2020.xlsm! Ускорение_включить″
Application.Run ″Судоку_2020.xlsm! Имя_строкам_судоку″