Можно запустить данную функцию, введя ее предварительно в строку формул. В общем случае формула выглядит следующим образом:
=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