Быстрый переход по рабочему листу
Если рабочий лист Excel содержит большой объем информации, то целесообразно присвоить имена его произвольным областям для быстрого перехода по ним. Данный процесс называется созданием закладок.
Чтобы создать закладку, нужно выделить произвольный диапазон (несколько ячеек) и выполнить команду контекстного меню Имя диапазона. В результате откроется окно, изображенное на рис. 2.1.
Рис. 2.1. Окно Создание имени
В данном окне в поле Имя с клавиатуры следует ввести имя создаваемой закладки и нажать кнопку ОК.
При создании закладки необходимо соблюдать следующие правила.
• В имени закладки должно содержаться не более 255 символов.
• Первый символ в имени закладки должен быть либо буквой, либо символом подчеркивания. После первого символа можно использовать буквы, цифры или символы подчеркивания.
• Если имя закладки состоит из нескольких слов, то они должны разделяться символом подчеркивания (не пробелом).
Чтобы проверить работоспособность созданной закладки, нужно установить курсор в любое место рабочего листа и нажать сочетание клавиш Ctrl+G. При этом откроется окно, показанное на рис. 2.2.
Рис. 2.2. Окно Переход
В данном окне нужно установить курсор на имя требуемой закладки (в нашем примере – Область!.) и нажать кнопку ОК. В результате Excel перейдет к соответствующей области рабочего листа.
Разные листы с общими данными
Достаточно распространенной является ситуация, когда необходимо связать между собой данные, хранящиеся на разных листах рабочей книги. При этом порядок действий будет примерно таким (все данные условны).
1. Открыть рабочую книгу.
2. Перейти на Лист2 и в ячейке С5 ввести значение 100.
3. Перейти на Лист1 и в ячейке А2 ввести формулу =Лист2! С5.
4. Нажать Enter.
После выполнения указанных действий в ячейке А2, расположеной на листе Лист1, отобразится значение 100 (то есть то же значение, что и в ячейке С5 на листе Лист2).
Рисование "правильных" фигур
При рисовании автофигур иногда возникает необходимость их привязки к сетке Excel. Чтобы использовать данный прием, нужно выбрать фигуру (Вставка → Иллюстрации → Фигуры) и рисовать ее при нажатой клавише Alt. Все стороны фигуры будут выравниваться только по границам ячеек.
Скрытие данных от посторонних
В Excel реализована возможность защиты данных от несанкционированного просмотра и редактирования. Для этого можно скрыть строки и столбцы, в которых хранится конфиденциальная информация. Поскольку строки и столбцы скрываются аналогичным образом, мы рассмотрим данный прием на примере скрытия строк.
На рабочем листе нужно найти последнюю строку, содержащую общедоступные данные, и выделить полностью строку, расположенную сразу под ней (для этого нужно нажать кнопку с номером строки). После этого, нажав сочетание клавиш Ctrl+Shift+I, выделить все остальные строки рабочего листа. Теперь после выполнения команды контекстного меню Скрыть выделенный диапазон будет скрыт с рабочего листа. При этом все имеющиеся в этом диапазоне данные сохраняются – чтобы они вновь отобразились, нужно выделить весь рабочий лист (Ctrl+A) и выполнить команду контекстного меню Отобразить.
Блокировка использования контекстного меню
При необходимости можно запретить использование контекстного меню текущего рабочего листа. Для этого можно воспользоваться таким макросом (листинг 2.10).
Листинг 2.10. Блокировка контекстного меню
Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Static intCount As Integer ' Счетчик нажатий кнопки мыши
Dim x As Integer, y As Integer
' Блокировать обработку щелчка правой кнопкой мыши
Cancel = True
' Отображение текстового поля с количеством щелчков правой _
кнопкой мыши
x = Target.Left
y = Target.Top
intCount = intCount + 1
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
x, y, 35, 20).TextFrame.Characters.Text = intCount
End Sub
Следует учитывать, что приведенный код должен быть помещен в модуль соответствующего рабочего листа.
Теперь при щелчке правой кнопкой мыши на любом месте текущего рабочего листа контекстное меню будет недоступно. Вместо него на экране будет появляться порядковый номер щелчка правой кнопкой мыши (например, при первой попытке вызова контекстного меню вместо него отобразится 1, при второй попытке – 2 и т. д.).
Однако при этом остается возможность вызова контекстного меню с помощью соответствующей клавиши.
Вставка колонтитула с именем книги, листа и текущей датой
Обычно колонтитулы вставляются с помощью комадны Вставка → Текст → Колонтитулы. Однако для этой цели можно также воспользоваться макросом, код которого приведен в листинге 2.11.
Листинг 2.11. Вставка колонтитула
Sub AddPageHeader()
Dim i As Integer
With ThisWorkbook
' Вставка колонтитулов на все листы рабочей книги
For i = 1 To .Worksheets.Count – 1
.Worksheets(i).PageSetup.LeftHeader = .FullName
.Worksheets(i).PageSetup.CenterHeader = Worksheets(i).Name
.Worksheets(i).PageSetup.RightHeader = Now()
Next
End With
End Sub
Следует учитывать, что приведенный код должен быть помещен в модуль ЭтаКнига.
В результате выполнения данного макроса в колонтитуле документа слева будет выведено имя рабочей книги (с указанием полного пути к файлу), в центре – имя текущего рабочего листа, а справа – текущая дата. Увидеть сформированный колонтитул можно в режиме Разметка страницы.
Проверка существования листа
При большом количестве листов в рабочей книге иногда бывает необходимо быстро узнать, есть ли в ней тот или иной лист. Для этого целесообразно применить пользовательскую функцию dhSheetExist, код которой приводится в листинге 2.12. В качестве аргумента данная функция принимает строку с именем искомого листа и возвращает значение ИСТИНА, если лист есть в книге, или ЛОЖЬ, если листа в книге нет.
Листинг 2.12. Проверка существования листа
Function dhSheetExist(strSheetName As String) As Boolean
Dim objSheet As Object
On Error GoTo HandleError ' При ошибке перейти на HandleError
' Пытаемся получить ссылку на заданный лист
Set objSheet = ActiveWorkbook.Sheets(strSheetName)
' Ошибки не возникло – лист существует
dhSheetExist = True
Exit Function
HandleError:
' При попытке получить доступ к листу с заданным именем _
возникла ошибка, значит, такого листа не существует
dhSheetExist = False
End Function
Работа приведенной функции основана на том, что если листа в книге нет, то при попытке обращения к нему (инструкция ActiveWorkbook. Sheets (strSheetName)) будет сгенерирована ошибка времени исполнения, которую перехватит обработчик ошибок, идущий в тексте программы после метки HandleError. Таким образом, при возникновении ошибки функция возвращает значение ЛОЖЬ. Если ошибка отсутствует, то при обращении к листу инструкции функции исполняются до конца (до оператора End Function) и функция возвращает значение ИСТИНА.
После написания данной функции она появится в окне Мастер функций (Формулы → Вставить функцию) в категории Определенные пользователем. После выбора функции нужно будет указать имя листа (это единственный аргумент данной функции), наличие которого в текущей книге нужно проверить. Результат выполнения функции отобразится в ячейке, в которой находится курсор: если указанный лист присутствует в текущей книге, то будет выведено значение ИСТИНА, при отсутствии листа – значение ЛОЖЬ.
Проверка, защищен ли рабочий лист
Перед началом работы с листом Excel иногда бывает полезно узнать, защищен он или нет (вспомним, что защита рабочего листа включается с помощью кнопки Защитить лист на вкладке Рецензирование в группе Изменения. Быстро получить ответ на этот вопрос можно с помощью макроса, код которого приведен в листинге 2.13.
Листинг 2.13. Проверка наличия защиты рабочего листа
Sub IsSheetProtected()
' Проверка, установлена ли защита на содержимое листа
If Worksheets(1).ProtectContents Then
MsgBox "Защита листа включена"
Else
MsgBox "Защита листа не включена"
End If
End Sub
Если текущий рабочий лист защищен, то после выполнения данного макроса появится окно с сообщением Защита листа включена. При отсутствии защиты в этом окне отобразится сообщение Защита листа не включена.