Текст книги "Excel. Трюки и эффекты"

С помощью небольшой пользовательской функции dhCellType можно быстро получить тип данных какой-либо ячейки либо левой верхней ячейки указанного диапазона. Код функции, который набирается в стандартном модуле редактора VBA, представлен в листинге 2.73.

Листинг 2.73. Тип данных, хранящихся в ячейке

Function dhCellType(rgRange As Range) As String

' Переходим к левой верхней ячейке, если rgRange – диапазон, _

а не одна ячейка

Set rgRange = rgRange.Range(«A1»)

' Определение типа значения в ячейке

Select Case True

Case IsEmpty(rgRange)

' Ячейка пуста

dhCellType = «Пусто»

Case Application.IsText(rgRange)

' В ячейке текст

dhCellType = «Текст»

Case Application.IsLogical(rgRange)

' В ячейке логическое значение (True или False)

dhCellType = «Булево выражение»

Case Application.IsErr(rgRange)

' При вычислении значения в ячейке произошла ошибка

dhCellType = «Ошибка»

Case IsDate(rgRange)

' В ячейке дата

dhCellType = «Дата»

Case InStr(1, rgRange.Text, ":") <> 0

' В ячейке время

dhCellType = «Время»

Case IsNumeric(rgRange)

' В ячейке числовое значение

dhCellType = «Число»

End Select

End Function

Данная функция имеет один аргумент – в его качестве указывается либо адрес конкретной ячейки, либо диапазон (в последнем случае будет определен тип данных левой верхней ячейки этого диапазона). Результат работы функции отображается в активной ячейке. Например, если диапазон или указанная ячейка не содержит данных, то в активной ячейке отобразится значение Пусто; если содержится текстовое выражение, то в активной ячейке появится значение Текст и т. д. в соответствии с кодом функции.

Рассмотрим трюк, с помощью которого можно быстро выделить из текстового содержимого ячейки определенный элемент (текстовый фрагмент, число и т. п.) и поместить его в активную ячейку.

Для реализации данной задачи нам потребуется создать пользовательскую функцию, код которой показан в листинге 2.74 (этот код записывается в стандартном модуле редактора VBA).

Листинг 2.74. Выделение элемента текста

Function dhGetTextItem(ByVal strTextIn As String, intItem As _

Integer, strSeparator As String) As String

Dim intStart As Integer ' Позиция начала текущего элемента

Dim intEnd As Integer ' Позиция конца текущего элемента

Dim i As Integer ' Номер текущего элемента

' Проверка корректности номера элемента

If intItem < 1 Then Exit Function

' Убираются лишние пробелы, если разделитель – пробел

If strSeparator = " " Then strTextIn =

Application.Trim(strTextIn)

' Разделитель добавляется в конец строки

If Right(strTextIn, Len(strTextIn)) <> strSeparator Then _

strTextIn = strTextIn & strSeparator

' Поиск всех элементов в строке до нужного

For i = 1 To intItem

' Начало элемента (перемещение вперед по строке)

intStart = intEnd + 1

' Конец элемента

intEnd = InStr(intStart, strTextIn, strSeparator)

If (intEnd = 0) Then

' Дошли до конца строки, но элемент не нашли

Exit Function

End If

Next i

' Выделение текста из входной строки

dhGetTextItem = Mid(strTextIn, intStart, intEnd – intStart)

End Function

Данная функция имеет три аргумента: strTextIn, intltemn strSeparator. Аргумент strTextIn – адрес ячейки, из содержимого которой нужно извлечь элемент; intltem – порядковый номер извлекаемого элемента; a strSeparator – символ разделителя между элементами. Соответствующие значения можно как ввести в окне настройки параметров функции, которое открывается после ее выбора в окне Мастер функций, так и использовать для этой цели строку формул. Синтаксис функции выглядит следующим образом (пример):

=dhGetTextItem(A9;3;"-")

В данном случае dhGetTextltem– это название функции, А9 – адрес ячейки с текстом, 3 – порядковый номер извлекаемого элемента, дефис (-) – символ разделителя.

Предположим, что из текста Ночь, улица, фонарь, аптека, который расположен в ячейке В2, нам нужно извлечь слово фонарь. Если воспользоваться строкой формул, то нужно ввести в нее следующую формулу:

=dhGetTextItem(В2;3;",")

В результате слово фонарь отобразится в активной ячейке.

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

Для выполнения данной операции нам потребуется создать пользовательскую функцию. Ее код представлен в листинге 2.75.

Листинг 2.75. Функция dhGetRandomValues

Function dhGetRandomValues() As Variant

Dim intRow As Integer ' Номер текущей строки

Dim intCol As Integer ' Номер текущего столбца

Dim aintOut() As Integer ' Выходной массив (двумерный)

Dim aintValues() As Integer ' Массив с возможными значениями

Dim intMax As Integer ' Последний доступный элемент

массива _ aintValues

Dim i As Integer

ReDim aintOut(1 To Application.Caller.Rows.Count, 1 To _

Application.Caller.Columns.Count)

' Всего нужно чисел...

intMax = Application.Caller.Rows.Count * _

Application.Caller.Columns.Count

ReDim aintValues(1 To intMax)

' Заполнение массива aintValues значениями от 1 до intMax

For i = 1 To intMax

aintValues(i) = i

Next i

' Занесение значений в выходной массив aintOut, в произвольном _

порядке выбирая их из aintValues

Randomize

For intRow = 1 To Application.Caller.Rows.Count

For intCol = 1 To Application.Caller.Columns.Count

' Определение номера элемента из aintValues

i = Rnd * intMax

If i = 0 Then i = 1

' Занесение этого элемента в выходной массив

aintOut(intRow, intCol) = aintValues(i)

' Уменьшение массива aintValues (то есть еще один его _

элемент выбран) – замена выбранного элемента последним _

в массиве

aintValues(i) = aintValues(intMax)

intMax = intMax – 1

Next intCol

Next intRow

' Возвращение массива значений

dhGetRandomValues = aintOut

End Function

Характерной особенностью данной функции является то, что она не имеет аргументов. Пример результата выполнения функции представлен на рис. 2.9.

Рис. 2.9. Случайные числа в диапазоне

Синтаксис созданной функции выглядит так:

=dhGetRandomValues()

Данную формулу нужно применить сразу ко всему предварительно выделенному диапазону – для этого после ввода ее в строку формул следует нажать комбинацию клавиш Ctrl+Shift+Enter. На рис. 2.9 в произвольном порядке разбросано 40 разных чисел, так как в диапазоне 40 ячеек (10 по высоте и 4 по ширине).

Трюк, который мы рассмотрим в данном подразделе, по своей функциональности несколько напоминает предыдущий. Различие заключается в том, что здесь в случайном порядке генерируются числа, содержащиеся в определенном диапазоне (этот диапазон является единственным аргументом рассматриваемой функции). Код функции выглядит следующим образом (листинг 2.76).

Листинг 2.76. Функция dhGetRandomValuesI

Function dhGetRandomValues1(rgSource As Range) As Variant

Dim intRow As Integer ' Номер текущей строки

Dim intCol As Integer ' Номер текущего столбца

Dim avarOut() As Variant ' Выходной массив (двумерный)

Dim avarValues() As Variant ' Массив с возможными значениями

Dim intValCount As Integer ' Количество возможных значений

Dim cell As Range

Dim i As Integer

ReDim avarOut(1 To Application.Caller.Rows.Count, 1 To _

Application.Caller.Columns.Count)

' Всего нужно чисел...

intValCount = rgSource.Rows.Count * rgSource.Columns.Count

ReDim avarValues(1 To intValCount)

' Заполнение массива avarValues значениями из указанного _

диапазона

For Each cell In rgSource

i = i + 1

avarValues(i) = cell.Value

Next cell

' Занесение значений в выходной массив avarOut, в произвольном _

порядке выбирая их из avarValues

Randomize

For intRow = 1 To Application.Caller.Rows.Count

For intCol = 1 To Application.Caller.Columns.Count

' Определение номера элемента из avarValues

i = Rnd * intValCount

If i = 0 Then i = 1

' Занесение этого элемента в выходной массив

avarOut(intRow, intCol) = avarValues(i)

Next intCol

Next intRow

' Возвращение массива значений

dhGetRandomValues1 = avarOut

End Function

Для применения данной функции необходимо иметь диапазон с исходными данными. Синтаксис функции выглядит следующим образом (пример):

=dhGetRandomValues1(A1:C5)

Здесь А1: С5 – диапазон с исходными данными. Как и в предыдущем примере, данная функция применяется ко всему предварительно выделенному диапазону—с помощью сочетания клавиш CtrL+Shift+Enter. После применения функции данные исходного диапазона будут в произвольном порядке разбросаны в новом диапазоне.

Глава 3
Создание трюков с помощью макросов

Как отмечалось выше, макрос – программа, написанная на встроенном в Excel языке программирования Visual Basic for Applications (VBA). Язык VBA используется для встраивания программ в документы Microsoft Office. С помощью макросов можно выполнять как хорошо знакомые пользователям Excel действия (выделение ячейки, вставку функции или формулы в ячейку и т. д.), так и программировать более сложные операции (вплоть до построения информационных систем разной сложности).

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

Подсчет количества открытий файла

С помощью несложного приема можно вести автоматический учет количества открытий текущего файла. Для этого достаточно написать любой из трех приведенных ниже макросов (листинги 3.1–3.3) и поместить его в VBA-модуль.

Листинг 3.1. Количество открытий файла (вариант 1)

Sub Auto_Open()

Worksheets(1).Cells(1) = Worksheets(1).Cells(1) + 1

End Sub

Листинг 3.2. Количество открытий файла (вариант 2)

Sub Auto_Open()

Worksheets(1).Cells(1, 1) = Worksheets(1).Cells(1, 1) + 1

End Sub

Листинг 3.3. Количество открытий файла (вариант 3)

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).

Листинг 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).

Листинг 3.5. Произвольный текст в строке состояния

Sub ChangeStatusBarText()

Application.StatusBar = «Как надоело работать!!!»

End Sub

Результат применения данного макроса представлен на рис. 3.1.

Рис. 3.1. Изменение текста в строке состояния

Для возврата к первоначальному состоянию можно использовать следующий макрос (листинг 3.6).

Листинг 3.6. Восстановление строки состояния

Sub ReturnStatusBarText()

Application.StatusBar = False

End Sub

После запуска данного макроса строка состояния примет прежний вид.

Текст, изображенный в строке состояния на рис. 3.1, можно заставить двигаться, то есть создать эффект бегущей строки. В этом нам поможет макрос, код которого приведен в листинге 3.7.

Листинг 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).

Листинг 3.8. Изменение заголовка окна

Sub NewTitle()

Application.Caption = «Какая хорошая погода»

End Sub

Результат выполнения макроса показан на рис. 3.2.

Рис. 3.2. Изменение заголовка окна

Однако на рис. 3.2 видно, что перед новым заголовком находится название текущего файла. Чтобы убрать это название, необходимо внести в макрос некоторые изменения (листинг 3.9).

Листинг 3.9. Изменение заголовка окна (со скрытием названия файла)

Sub NewTitle()

Application.Caption = «Какая хорошая погода»

ActiveWindow.Caption = ""

End Sub

После запуска данного макроса заголовок примет следующий вид (рис. 3.3).

Рис. 3.3. Заголовок без названия файла

Можно сделать так, что после заголовка будет отображаться произвольный текст. Соответствующий макрос выглядит примерно так (листинг 3.10).

Листинг 3.10. Еще один вариант изменения заголовка

Sub NewTitle()

Application.Caption = «А завтра будет дождь»

ActiveWindow.Caption = «Какая хорошая погода»

End Sub

Результат выполнения этого макроса показан на рис. 3.4.

Рис. 3.4. Измененный заголовок с дополнительным текстом

Чтобы заголовок окна программы принял свой первоначальный вид, необходимо запустить следующий макрос (листинг 3.11).

Листинг 3.11. Возврат к первоначальному заголовку

Sub ReturnTitle()

' Возвращение заголовка приложения (то есть Excel)

Application.Caption = Empty

' Указание правильного названия открытого файла (книги)

ActiveWindow.Caption = ThisWorkbook.Name

End Sub

Этот макрос отменяет и изменения в заголовке, и ввод дополнительного текста после заголовка.

Ввод данных с помощью диалогового окна

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

Листинг 3.12. Диалоговое окно ввода данных

Sub InputDialog()

Dim strInput As String

' Вызов стандартного диалогового окна ввода данных

strInput = InputBox(«Введите данные», «Ввод данных»)

End Sub

При запуске этого макроса на экране отобразится диалоговое окно (рис. 3.5).

Рис. 3.5. Диалоговое окно ввода данных

При необходимости можно задать текст, который будет отображаться в поле ввода диалогового окна по умолчанию (с помощью аргумента Default), а также определить параметры расположения окна на экране (аргументы XPos и YPos). Пример соответствующего макроса приведен в листинге 3.13.

Листинг 3.13. Значение по умолчанию

Sub NewInputDialog()

Dim strInput As String

' Вызов стандартного диалогового окна ввода со значением _

по умолчанию

strInput = InputBox(«Введите данные», «Ввод данных», _

«Значение по умолчанию», 200, 200)

End Sub

Окно, выводимое на экран при выполнении данного макроса, представлено на рис. 3.6.

Рис. 3.6. Текст по умолчанию в окне ввода данных

Работа в данном окне ведется по обычным правилам Windows.

Применение функции без ввода ее в ячейку

При необходимости можно воспользоваться стандартной функцией Excel таким образом, что вводить ее в ячейку будет не обязательно. Для этого достаточно запустить следующий макрос (листинг 3.14).

Листинг 3.14. Применение функции без ввода в ячейку

Sub Func()

[a1] = Application.Sum([B5:B10])

End Sub

Результатом работы данного макроса будет сумма диапазона ячеек В5:В10, отображаемая в ячейке А1.

Скрытие строк и столбцов от посторонних

Существует несколько небольших макросов, с помощью которых можно скрывать строки и столбцы. С некоторыми из таких макросов мы познакомимся в данном разделе.

Чтобы скрыть строку под определенным номером, можно применить следующий макрос (листинг 3.15).

Листинг 3.15. Скрытие строки

Sub HideString()

Rows(2).Hidden = True

End Sub

В результате применения этого макроса будет скрыта строка 2.

Можно использовать также такой макрос (листинг 3.16).

Листинг 3.16. Скрытие нескольких строк

Sub HideStrings()

Rows(«3:5»).Hidden = True

End Sub

После его выполнения будут скрыты строки 3, 4 и 5.

Чтобы вновь отобразить скрытую строку (или строки), необходимо в соответствующих макросах изменить значение True на False, после чего запустить их на выполнение.

Похожие макросы используются для управления отображением столбцов). Например, в результате выполнения макроса, код которого приведен в листинге 3.17, будет скрыт столбец В.

Листинг 3.17. Скрытие столбца

Sub HideCollumn()

Columns(2).Hidden = True

End Sub

Если в данном макросе заменить 2, например, на С, то будет скрыт столбец С (то есть в коде макроса можно указывать как порядковый номер столбца, так и соответствующую ему букву).

Для скрытия нескольких столбцов можно применить, например, такой макрос (листинг 3.18).

Листинг 3.18. Скрытие нескольких столбцов

Sub HideCollumns()

Columns(«E:F»).Hidden = True

End Sub

В результате выполнения данного макроса будут скрыты столбцы Е и F.

Чтобы включить отображение скрытых столбцов, необходимо в соответствующих макросах заменить значение True на False и запустить их на выполнение.

Можно также скрывать строки и столбцы, ориентируясь на адрес или имя ячейки. Например, используйте такой макрос (листинг 3.19).

Листинг 3.19. Скрытие строки по имени ячейки

Sub HideCell()

Range(«Секрет»).EntireRow.Hidden = True

End Sub

После его применения будет скрыта строка, в которой находится ячейка с именем Секрет. Этот трюк удобно использовать, например, когда нужно срочно скрыть какие-либо данные, но адрес ячейки вспомнить не удается, а известно только ее имя. Однако при необходимости вместо имени можно указывать конкретный адрес ячейки (например, если в приведенном макросе заменить Секрет на А2, то будет скрыта строка 2).

Можно также указывать диапазон ячеек, строки с которыми нужно скрыть. Пример такого макроса приведен в листинге 3.20.

Листинг 3.20. Скрытие нескольких строк по адресам ячеек

Sub HideCell()

Range(«B3:D4»).EntireRow.Hidden = True

End Sub

После применения данного макроса будут скрыты строки 3 и 4.

Чтобы вновь отобразить скрытую строку (или строки), необходимо в соответствующих макросах изменить значение True на False и запустить их на выполнение.

Управлять отображением столбцов также можно, исходя из адреса или имени ячейки. Вот пример такого макроса (листинг 3.21).

Листинг 3.21. Скрытие столбца по имени ячейки

Sub HideCell()

Range(«Секрет»).EntireColumn.Hidden = True

End Sub

В данном случае будет удален столбец, содержащий ячейку Секрет. Здесь также можно указывать как имя, так и адрес ячейки (например, если в приведенном макросе заменить Секрет на C3, то будет скрыт столбец С).

Можно использовать такой макрос (листинг 3.22).

Листинг 3.22. Скрытие нескольких столбцов по адресам ячеек

Sub HideCell()

Range(«C2:D5»).EntireColumn.Hidden = True

End Sub

После применения данного макроса будут скрыты столбцы С и D.

Чтобы вновь включить отображение скрытых столбцов, нужно в соответствующих макросах заменить значение True на False.

Быстрое выделение ячеек, расположенных через интервал

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

Листинг 3.23. Выделение ячеек через интервал (вариант 1)

Sub IntervalCellSelect()

Dim intFirstRow As Integer ' Первая строка для выделения

Dim intLastRow As Integer ' Последняя строка для выделения

Dim rgCells As Range ' Объединение выделяемых ячеек

Dim intRow As Integer

intFirstRow = 3

intLastRow = 300

' Формирование объединения ячеек в столбце "B" от строки _

intFirstRow до строки intLastRow с шагом 3

For intRow = intFirstRow To intLastRow Step 3

If rgCells Is Nothing Then

' Первая ячейка в объединении

Set rgCells = Cells(intRow, 1)

Else

' Добавление очередной ячейки в объединение

Set rgCells = Union(rgCells, Cells(intRow, 1))

End If

Next

' Выделение всех ячеек в объединении

rgCells.Select

End Sub

В результате выполнения данного макроса будет выделена каждая третья ячейка, начиная с 3 и заканчивая 300, в столбце А.

Точно такой же результат (быстрое выделение ячеек с 3 по 300 в столбце А) можно получить, применив такой макрос (листинг 3.24).

Листинг 3.24. Выделение ячеек через интервал (вариант 2)

Sub IntervalCellSelect()

Dim intFirstRow As Integer ' Первая строка для выделения

Dim intLastRow As Integer ' Последняя строка для выделения

Dim rgCells As Range ' Объединение выделяемых ячеек

Dim cell As Range ' Текущая ячейка

Dim intRow As Integer

intFirstRow = 3

intLastRow = 300

' Формирование объединения ячеек в столбце "B" от строки _

intFirstRow до строки intLastRow с шагом 3

For intRow = intFirstRow To intLastRow Step 3

Set cell = Cells(intRow, 1)

Set rgCells = Union(cell, _

IIf(intRow = intFirstRow, cell, rgCells))

Next

' Выделение всех ячеек в объединении

rgCells.Select

End Sub

Например, если есть таблица, в которой отражается выручка за каждый рабочий день недели (с понедельника по пятницу), а в каждой шестой ячейке – итоговая выручка за неделю, то с помощью этого трюка можно быстро выделить и вывести на печать итоговые данные о выручке за каждую неделю.

Для выделения различных диапазонов и ячеек можно применить следующий макрос (листинг 3.25).

Листинг 3.25. Выделение нескольких диапазонов

Sub SelectRange()

Range(«D3:D10, A3:A10 , F3»).Select

End Sub

В результате выполнения данного макроса будут выделены диапазоны D3:D10, АЗ:А10 и ячейка F3.