Можно запустить данную функцию, введя ее предварительно в строку формул. В общем случае формула выглядит следующим образом:
=dhReverseText(A4)
Здесь А4 – это адрес ячейки, текст которой требуется преобразовать. После нажатия Enter результат отобразится в активной ячейке.
После запуска макроса ReverseText (его следует выбрать в окне Макрос) откроется диалоговое окно, в котором с клавиатуры нужно ввести требуемый текст и нажать кнопку ОК или клавишу Enter. Результат преобразования текста отобразится в открывшемся информационном окне. Для удобства можно поместить в любое место интерфейса кнопку, к которой привязать макрос ReverseText.
Поиск максимального значения на всех листах книги
В данном подразделе мы рассмотрим, каким образом можно быстро найти максимальное значение указанной ячейки среди всех листов текущей рабочей книги.
Следует отметить, что для решения данной задачи можно воспользоваться штатными средствами программы, а именно – функцией МАКС. Например, с помощью формулы =МАКС (Лист2: Лист7! A3) осуществляется поиск максимального значения ячейки A3 среди рабочих листов с Л ист2 по Л ист7 включительно. Однако данный способ имеет следующий недостаток: при добавлении в книгу новых листов (после Лист7) формулу придется соответствующим образом корректировать.
Поэтому для решения подобных задач целесообразно создать и применять пользовательскую функцию, код которой приведен в листинге 2.70.
Листинг 2.70. Поиск максимального значения
Function dhMaxInBook(cell As Range) As Double
Dim sheet As Worksheet
Dim dblMax As Double
Dim dblResult As Double
Dim fFirst As Boolean
fFirst = True
' Расчет максимальных значений во всех листах рабочей книги _
и выбор наибольшего из них
For Each sheet In cell.Parent.Parent.Worksheets
' Расчет максимального значения на листе
dblResult = Application.WorksheetFunction.Max( _
sheet.Range(cell.Address))
If fFirst Then
' Найдено первое значение – его не с чем сравнивать
dblMax = dblResult
fFirst = False
End If
' Выбираем большее из dblMax и dblResult
If dblResult > dblMax Then
dblMax = dblResult
End If
Next sheet
' Возврат результата
dhMaxInBook = dblMax
End Function
Данная функция имеет один аргумент – адрес ячейки, максимальное значение которой следует выбрать из всех рабочих листов текущей книги. При добавлении (удалении) рабочих листов никаких корректировок кода либо формулы выполнять не требуется – в любом случае обрабатываются все доступные рабочие листы текущей книги.
Использование относительных ссылок
Как известно, в Excel ограничена поддержка "трехмерных рабочих книг". Например, если при написании формулы необходимо сослаться на другой рабочий лист в книге, то в формулу нужно включить имя соответствующего рабочего листа. Однако при попытке копирования этой формулы с одного листа на другой ссылка на лист не изменяется, как это происходит в реальной трехмерной рабочей книге. Для решения этой проблемы можно применить пользовательскую функцию dhSheetOf f set, код которой выглядит следующим образом (листинг 2.71).
Листинг 2.71. Функция dhSheetOffset
Function dhSheetOffset(offset As Integer, cell As Range) As
Variant
' Возврат корректного значения ячейки cell листа, смещение _
которого относительно текущего задано переменной offset
dhSheetOffset = Sheets(Application.Caller.Parent.Index _
+ offset).Range(cell.Address)
End Function
Данная функция имеет два аргумента. Первый аргумент – это ссылка на лист; он может быть положительным, нулевым или отрицательным (например, для ссылки на предыдущий лист нужно указать -1). Второй аргумент – это ссылка на конкретную ячейку. Для использования функции можно применять формулу:
=dhSheetOffset(-1;A9)
В данном случае в активной ячейке будет получено значение ячейки А9, расположенной на предыдущем рабочем листе (то есть если текущий лист – Лист2, то будет получено значение ячейки А9 листа Лист1).
При использовании данной функции необходимо учитывать следующее: если рабочий лист содержит листы диаграмм, то при ссылке на ячейку в листе диаграммы будет получено сообщение об ошибке.
При необходимости можно усовершенствовать данную функцию. Ниже приведен код функции dhSheetOf f set2, игнорирующей все листы рабочей книги, которые не являются рабочими (листинг 2.72).
Листинг 2.72. Функция dhSheetOffset2
Function dhSheetOffset2(offset As Integer, cell As Range) As
Variant
' Корректировка смещения (чтобы ссылка была на рабочий лист)
Do While TypeName(Sheets(cell.Parent.Index + offset)) _
<> "Worksheet"
If offset > 0 Then
' Пропускаем лист и проходим вперед по книге
offset = offset + 1
Else
' Пропускаем лист и проходим назад по книге
offset = offset – 1
End If
Loop
' Возврат корректного значения ячейки cell листа, смещение _
которого относительно текущего задано переменной offset _
с пропуском листов с диаграммами
dhSheetOffset2 = Sheets(cell.Parent.Index _
+ offset).Range(cell.Address)
End Function
У данной функции аргументы и порядок использования такие же, как и у рассмотренной выше функции dhSheetOffset.
Определение типа данных ячейки
С помощью небольшой пользовательской функции 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 strSepa\rator = " " 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 введено нечисловое значение, то при следующем открытии файла на экране отобразится сообщение об ошибке.