Алексей Гладкий - Excel. Трюки и эффекты

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

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.

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