Создание трюков с помощью макросов
Как отмечалось выше, макрос – программа, написанная на встроенном в Excel языке программирования Visual Basic for Applications (VBA). Язык VBA используется для встраивания программ в документы Microsoft Office. С помощью макросов можно выполнять как хорошо знакомые пользователям Excel действия (выделение ячейки, вставку функции или формулы в ячейку и т. д.), так и программировать более сложные операции (вплоть до построения информационных систем разной сложности).
Для работы с макросами предназначены команды, которые можно выбрать из раскрывающегося списка кнопки Макросы, расположенной на вкладке Вид.
Подсчет количества открытий файла
С помощью несложного приема можно вести автоматический учет количества открытий текущего файла. Для этого достаточно написать любой из трех приведенных ниже макросов (листинги 3.1–3.3) и поместить его в VBA-модуль.
Sub Auto_Open()
Worksheets(1).Cells(1) = Worksheets(1).Cells(1) + 1
End Sub
Sub Auto_Open()
Worksheets(1).Cells(1, 1) = Worksheets(1).Cells(1, 1) + 1
End Sub
Sub Auto_Open()
Worksheets(1).Range(«A1») = Worksheets(1).Range(«A1») + 1
End Sub
Все эти макросы имеют имя, определенное разработчиками Microsoft Office, – AutoOpen. Если макрос с таким именем реализован, то он автоматически вызывается средой Microsoft Office при каждом открытии книги.
При использовании любого из перечисленных вариантов количество открытий текущего файла отобразится в ячейке А1. Если результат впоследствии корректировался вручную, то в дальнейшем это значение при каждом открытии будет увеличиваться на 1 (например, при обнулении результата подсчет начнется заново, а если в ячейку А1 введено значение 52, то при следующем открытии файла оно будет равным 53). Если же в ячейку А1 введено нечисловое значение, то при следующем открытии файла на экране отобразится сообщение об ошибке.
Получение «закрытой» информации
Возможности программы предусматривают извлечение данных из закрытого файла. Для этого нужно записать и выполнить следующий макрос (листинг 3.4).
Sub GetDataFromFile()
Range(«A1»).Formula = «='C:\[Example.xls]Лист1'!A1»
End Sub
В данном случае подразумевается, что необходимый нам файл находится по адресу С:\Example.xls, а из хранящейся в нем книги нам нужно содержимое ячейки А1.
Формулу, которую мы задавали с помощью макроса, можно вводить в таблицу вручную, если возникнет необходимость заносить в ячейки таблицы данные из другого файла. При этом следует учитывать, что полный путь нужно задавать только в том случае, если необходимый файл расположен в другой папке. Имя файла в квадратных скобках указывать обязательно. Также следует указывать имя листа, на котором находится нужная нам ячейка. Необходимо строго соблюдать синтаксис, который используется в листинге 3.4.
Для подкрепления сказанного рассмотрим пример формулы для получения значения ячейки А1 листа Лист2 книги, расположенной в файле F.xls (в той же папке, что и текущая книга):
=' [F.xls]Лист2'!A1
Произвольный текст в строке состояния
С помощью небольшого макроса можно включить отображение в строке состояния произвольного текста. Код макроса выглядит следующим образом (листинг 3.5).
Sub ChangeStatusBarText()
Application.StatusBar = «Как надоело работать!!!»
End Sub
Результат применения данного макроса представлен на рис. 3.1.
Рис. 3.1. Изменение текста в строке состояния
Для возврата к первоначальному состоянию можно использовать следующий макрос (листинг 3.6).
Sub ReturnStatusBarText()
Application.StatusBar = False
End Sub
После запуска данного макроса строка состояния примет прежний вид.
Текст, изображенный в строке состояния на рис. 3.1, можно заставить двигаться, то есть создать эффект бегущей строки. В этом нам поможет макрос, код которого приведен в листинге 3.7.
Sub MovingTextInStatusBar()
Dim intSpaces As Integer
' Изменение количества пробелов в начале строки (от 20 до
0) – _
строка бежит (скорее ползет) влево
For intSpaces = 20 To 0 Step -1
' Запись текста в строку состояния
Application.StatusBar = Space(intSpaces) & "Как надоело
работать!!!"
' Выдерживаем паузу
Application.Wait Now + TimeValue(«00:00:01»)
' Дадим Excel обработать пользовательский ввод
DoEvents
Next
Application.StatusBar = False
End Sub
После запуска макроса текст в строке состояния (он может быть произвольным) начнет двигаться справа налево.
Быстрое изменение заголовка окна
Аналогичным образом при необходимости можно изменить текст в заголовке окна программы. Для этого можно использовать, например, следующий макрос (листинг 3.8).
Sub NewTitle()
Application.Caption = «Какая хорошая погода»
End Sub
Результат выполнения макроса показан на рис. 3.2.
Рис. 3.2. Изменение заголовка окна
Однако на рис. 3.2 видно, что перед новым заголовком находится название текущего файла. Чтобы убрать это название, необходимо внести в макрос некоторые изменения (листинг 3.9).
Sub NewTitle()
Application.Caption = «Какая хорошая погода»
ActiveWindow.Caption = ""
End Sub
После запуска данного макроса заголовок примет следующий вид (рис. 3.3).
Рис. 3.3. Заголовок без названия файла
Можно сделать так, что после заголовка будет отображаться произвольный текст. Соответствующий макрос выглядит примерно так (листинг 3.10).
Sub NewTitle()
Application.Caption = «А завтра будет дождь»
ActiveWindow.Caption = «Какая хорошая погода»
End Sub
Результат выполнения этого макроса показан на рис. 3.4.
Рис. 3.4. Измененный заголовок с дополнительным текстом
Чтобы заголовок окна программы принял свой первоначальный вид, необходимо запустить следующий макрос (листинг 3.11).
Sub ReturnTitle()
' Возвращение заголовка приложения (то есть Excel)
Application.Caption = Empty
' Указание правильного названия открытого файла (книги)
ActiveWindow.Caption = ThisWorkbook.Name
End Sub
Этот макрос отменяет и изменения в заголовке, и ввод дополнительного текста после заголовка.
Ввод данных с помощью диалогового окна
В процессе работы могут возникать ситуации, когда для ввода данных целесообразно использовать вызываемое отдельно диалоговое окно. Решить эту задачу поможет следующий макрос (листинг 3.12).
Sub InputDialog()
Dim strInput As String
' Вызов стандартного диалогового окна ввода данных
strInput = InputBox(«Введите данные», «Ввод данных»)
End Sub
При запуске этого макроса на экране отобразится диалоговое окно (рис. 3.5).
Рис. 3.5. Диалоговое окно ввода данных
При необходимости можно задать текст, который будет отображаться в поле ввода диалогового окна по умолчанию (с помощью аргумента Default), а также определить параметры расположения окна на экране (аргументы XPos и YPos). Пример соответствующего макроса приведен в листинге 3.13.
Sub NewInputDialog()
Dim strInput As String
' Вызов стандартного диалогового окна ввода со значением _
по умолчанию
strInput = InputBox(«Введите данные», «Ввод данных», _
«Значение по умолчанию», 200, 200)
End Sub
Окно, выводимое на экран при выполнении данного макроса, представлено на рис. 3.6.
Рис. 3.6. Текст по умолчанию в окне ввода данных
Работа в данном окне ведется по обычным правилам Windows.
Применение функции без ввода ее в ячейку
При необходимости можно воспользоваться стандартной функцией Excel таким образом, что вводить ее в ячейку будет не обязательно. Для этого достаточно запустить следующий макрос (листинг 3.14).
Sub Func()
[a1] = Application.Sum([B5:B10])
End Sub
Результатом работы данного макроса будет сумма диапазона ячеек В5:В10, отображаемая в ячейке А1.
Скрытие строк и столбцов от посторонних
Существует несколько небольших макросов, с помощью которых можно скрывать строки и столбцы. С некоторыми из таких макросов мы познакомимся в данном разделе.
Чтобы скрыть строку под определенным номером, можно применить следующий макрос (листинг 3.15).