Функции рабочих листов Excel при программировании на VBA

Рубрика: 7. Полезняшки Excel

Настоящая заметка продолжает знакомство с VBA. В ней представлены некоторые пользовательские функции, которые можно применять в формулах рабочего листа.[1] Помните, что эти процедуры функций необходимо определить в модуле VBA, а не в модуле кода соответствующей рабочей книги, листа или пользовательской формы.

Рис. 1. Активная ячейка имеет полужирное начертание, выполненное с помощью условного форматирования

Скачать заметку в формате Word или pdf, примеры в формате Excel (файл содержит макросы)

Получение информации о форматировании ячейки

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

Предупреждение. Эти функции не всегда обновляются автоматически — изменение форматирования не приводит к пересчету формул Excel. Чтобы вызвать глобальный пересчет формул (и обновить все пользовательские функции), нажмите клавиши <Ctrl+Alt+F9>. В функцию можно также добавить следующий оператор: Application.Volatile. При наличии этого оператора пересчет функции производится после нажатия клавиши <F9>.

Следующая функция возвращает ИСТИНА, если аргумент, состоящий из одной ячейки, выделен полужирным шрифтом. Если диапазон передается в качестве аргумента, функция использует его верхнюю левую ячейку.

Эта функция работает только с явно заданным форматированием (не может применяться с условным форматированием). В Excel 2010 появился новый объект – DisplayFormat. Он учитывает наличие условного форматирования. Функция IsBold2 может обрабатывать полужирный формат, являющийся результатом условного форматирования. Свойство DisplayFormat возвращает ошибку #ЗНАЧ! при использовании в пользовательских функциях. Однако свойство работает в процедурах, вызываемых из VBA. Если выбрать в качестве активной ячейку А3, отформатированную с помощью условного форматирования, и вызвать процедуру CI, появится окно, подтверждающее, что активная ячейка имеет полужирное начертание (рис. 1).

Следующая функция возвращает ИСТИНА, если используемая в качестве аргумента ячейка выделена курсивом.

Обе предыдущие функции возвращают ошибку, если ячейка имеет смешанное форматирование. Функция, приведенная ниже, возвращает ИСТИНА только тогда, когда все символы в ячейке выделены полужирным шрифтом.

Следующая функция возвращает целое число, соответствующее индексу цвета заливки ячейки. Если ячейка не имеет заливки, то функция возвращает значение 4142. Эта функция не может использоваться для определения цветов заливки таблиц (которые создаются с помощью команды Ctrl+T) или сводных таблиц. В подобных случаях воспользуйтесь объектом DisplayFormat.

Беседа с рабочим листом

Функция Saylt применяет синтезатор речи Excel для озвучивания аргумента.

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

=ЕСЛИ(СУММ(А:А)>25000;Saylt("Цель достигнута "))

Если сумма значений в столбце А превышает 25 000, вы услышите синтезированный голос, сообщающий о том, что цель достигнута. Метод Speak можно также включить в конец длинной процедуры, и компьютер известит вас о том, что выполнение процедуры завершено.

Отображение даты сохранения файла или вывода файла на печать

Рабочая книга Excel содержит несколько встроенных свойств документа, к которым можно получить доступ с помощью свойства BuiltinDocumentProperties объекта Workbook. Следующая функция возвращает дату и время последнего сохранения рабочей книги.

К свойствам можно обратиться по номеру, например, BuiltinDocumentProperties(5), или по имени, как в примере выше. Получить перечень свойств можно с помощью процедуры BDPNames(), код которой приведен в приложенном Excel-файле.

Значения, возвращаемые этой функцией, совпадают со значениями даты и времени, которые отображаются в разделе Связанные даты после выбора команды Файл –> Сведения (рис. 2). Обратите внимание, что на значения даты и времени оказывает влияние свойство AutoSave. Поэтому время последнего сохранения необязательно имеет отношение ко времени сохранения файла пользователем.

Рис. 2. Сведения о рабочей книге; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Следующая функция возвращает дату и время последнего вывода рабочей книги на печать или предварительного просмотра рабочей книги. Если рабочая книга никогда не печаталась и не просматривалась, функция возвращает ошибку #ЗНАЧ. При использовании двух последних функций, чтобы получить текущие значения данных свойств (чтобы обновить значения в ячейках), нажмите F9.

Не ко всем свойствам можно получить доступ с помощью BuiltinDocumentProperties. Например, при попытке получить доступ к свойству Number of Bytes, указывающему размер файла, будет генерироваться ошибка.

Основы иерархии объектов

Объектная модель Excel представляет собой определенную структуру: одни объекты содержатся в других объектах. На вершине этой иерархии находится объект Application. Excel содержит другие объекты, в которые, в свою очередь, вложены более низкоуровневые объекты и т.д. Следующая иерархия показывает, как в этой структуре представлен объект Range.

Объект Application
    Объект Workbook
        Объект Worksheet
            Объект Range

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

Следующая функция возвращает название рабочей книги для конкретной ячейки. Обратите внимание, что эта функция использует свойство Parent дважды. Первое свойство Parent возвращает объект Worksheet, а второе свойство Parent возвращает объект Workbook.

Следующая функция переносит это упражнение на следующий логический уровень, обращаясь к свойству Parent трижды. Такая функция возвращает имя объекта Application для заданной ячейки. Указанная функция всегда будет возвращать значение Microsoft Excel.

Подсчет количества ячеек между двумя значениями

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

Обратите внимание, что эта функция вызывает функцию Excel СЧЁТЕСЛИ (COUNTIFS). По сути, функция CountBetween является «оболочкой», которая может упростить формулы. (Но, наверное, не в этом учебном примере, для которого вполне достаточно стандартной функции Excel =СЧЁТЕСЛИМН(A17:A27;"<=5";A17:A27;">=2") Поскольку функция СЧЁТЕСЛИ появилась в Excel 2007, приведенный код не будет работать с более ранними версиями Excel.

Определение последней непустой ячейки в столбце или в строке

Функция LastInColumn возвращает содержимое последней непустой ячейки в столбце, а LastlnRow – содержимое последней непустой ячейки в строке. В качестве единственного аргумента эти функции используют диапазон. Причем в качестве диапазона может использоваться, как весь столбец, так и вся строка. Если же в качестве аргумента не используется вся строка либо весь столбец, задействуется строка или столбец, в котором находится верхняя левая ячейка диапазона. Например, следующая формула возвращает последнее значение в столбце В: =LastInColumn(В5). Следующая формула возвращает последнее значение в строке 7: =LastInRow (С7 :D9).

Оператор Application.Volatile вызывает выполнение функции всякий раз, когда пересчитываются формулы на рабочем листе. Оператор Rows.Count возвращает количество строк на рабочем листе (используется именно он, а не жестко заданное значение, из соображений совместимости; новые версии Excel могут включать большее количество строк на рабочем листе). Ссылка rng.Column возвращает номер столбца левой верхней ячейки в аргументе rng. Благодаря ссылке rng.Parent функция работает корректно, даже если аргумент rng ссылается на другой лист или рабочую книгу. Метод End (с аргументом xlUp) эквивалентен переходу к последней ячейке столбца и нажатию <End> и <Т>. Функция IsEmpty проверяет, пуста ли ячейка. Если ячейка пуста, функция возвращает пустую строку. Без этого оператора пустой ячейке соответствовал бы результат 0.

Код функции LastlnRow можно найти в модуле VBA приложенного Excel-файла.

Соответствует ли строка шаблону

Следующая функция возвращает значение ИСТИНА, если строка соответствует заданному шаблону. Функция представляет собой «оболочку», позволяющую использовать в формулах оператор VBA Like.

Функция IsLike принимает два аргумента: text — текстовая строка или ссылка на ячейку, содержащую текстовую строку; pattern — строка, содержащая групповые символы согласно таблице (рис. 3).

Рис. 3. Групповые символы

Представленная ниже формула возвращает ИСТИНА, так как * соответствует любому количеству символов. Она возвращает ИСТИНА, если первый аргумент — любой текст, начинающийся с g.

=IsLike("guitar", "g*")

Следующая формула возвращает ИСТИНА, так как ? соответствует любому отдельному символу. Если бы первым аргументом функции был «Unit12», то функция возвращала бы ЛОЖЬ.

=IsLike("Unit1", "Unit?")

Следующая формула возвращает ИСТИНА, так как первый аргумент является одним из символов списка во втором аргументе.

=IsLike("а", "[aeiou]")

Следующая формула возвращает ИСТИНА, если ячейка А1 содержит один из символов: а, е, i, о, u, А, Е, I, О, U. При использовании функции ПРОПИСН (UPPER) в аргументе функция становится нечувствительной к регистру.

=IsLike(ПРОПИСН(А1), ПРОПИСН("[aeiou]"))

Следующая формула возвращает ИСТИНА, если в ячейке А1 находится значение, начинающееся с 1 и состоящее ровно из трех цифр (т.е. любое целое число от 100 до 199).

=IsLike(A1, "1##")

Возвращение из строки n-го элемента

ExtractElement — специальная функция рабочего листа, которая извлекает элемент из текстовой строки.

В этой процедуре используется VBA-функция Split, возвращающая массив констант, из которого состоит текстовая строка. Массив начинается с нулевого элемента (а не с первого), поэтому текущий элемент имеет индекс n – 1.

Например, если ячейка содержит следующий текст "123-456-789-0133-8844", вы можете использовать функцию ExtractElement для извлечения любых подстрок между дефисами. Следующая формула возвращает 0133, т.е. четвертый элемент в строке.

=ExtractElement("123-456-789-0133-8844",4,"-")

Функция ExtractElement принимает три аргумента:

  • txt — текстовая строка, из которой извлекается подстрока (символьная строка или ссылка на ячейку);
  • n — целое число, представляющее номер извлекаемого элемента;
  • Separator — отдельный символ, используемый как разделитель.

Если в качестве символа-разделителя задать пробел, то несколько пробелов подряд будут рассматриваться как один, что не всегда соответствует требованиям. Если n превышает количество элементов в строке, функция возвращает пустую строку.

Преобразование чисел в текст

Функция SpellDollars возвращает текст, в который преобразуется исходное число – сумма в долларах и центах. Например, формула =SpellDollars(23,45) возвращает строку "двадцать три и 45/100 доллара" (рис. 4). Обратите внимание, что отрицательные числа заключаются в круглые скобки. Код функции SpellDollars приведен в приложенном Excel-файле.

Рис. 4. Примеры использования функции SpellDollars

Универсальная функция

Можно сделать так, чтобы одна функция рабочего листа работала как несколько функций. StatFunction имеет два аргумента: диапазон (rng) и операция (ор). В зависимости от значения аргумента ор функция возвращает значение, вычисленное с помощью одной из следующих функций Excel: СУММ, СРЗНАЧ, МЕДИАНА, МОДА, СЧЁТ, МАКС, МИН, ДИСП, СТАНДОТКЛОН. Например, результат формулы =StatFunction($A$33:$A$37;C36) зависит от содержимого ячейки С36, в которой должна быть текстовая строка с именем одной из допустимых функций. В нашем случае – СЧЁТ. Код функции StatFunction приведен в модуле VBA приложенного Excel-файла.

Рис. 5. Примеры использования функции StatFunction

Функция SheetOffset

В Excel ограничена поддержка «трехмерных рабочих книг». Например, чтобы сослаться на другой рабочий лист в книге, включите в формулу имя рабочего листа. Данная проблема будет оставаться незначительной до тех пор, пока вы не попытаетесь скопировать формулу из одного листа в другой. Скопированные формулы продолжают ссылаться на первоначальное имя рабочего листа, и ссылки на листы не изменяются, как это происходит в реальной трехмерной рабочей книге.

Функция VBA SheetOffset обеспечивает установку относительных ссылок на рабочие листы. Например (рис. 6), можно сослаться на ячейку А1 следующего рабочего листа с помощью такой формулы: =SheetOffset(1; А1). Первый аргумент представляет лист и может быть положительным, отрицательным или нулевым. Второй аргумент должен быть ссылкой на одну ячейку. Можете скопировать эту формулу в другие листы, и в скопированных формулах будет использована относительная ссылка.

Рис. 6. Пример использования функции SheetOffset

Возвращение максимального значения всех рабочих листов

Если необходимо определить максимальное значение в ячейке В1 в нескольких рабочих листах, используется следующая формула: =МАКС(Лист1:Лист4!В1). Эта формула возвращает максимальное значение ячейки В1 для листов Лист1, Лист4 и всех листов между ними.

Что же произойдет, если добавить после листа Лист4 новый лист – Лист5? Формула не будет автоматически изменена, поэтому ее необходимо отредактировать, чтобы включить ссылку на новый лист: =МАКС(Лист1:Лист5!В1).

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

Оператор For Each использует для доступа к рабочей книге выражение: cell.Parent.Parent.Worksheets. «Родителем» ячейки является рабочий лист, «родителем» рабочего листа — рабочая книга. Следовательно, цикл For Each проходит по всем рабочим листам в книге. Первый оператор If внутри цикла проверяет, содержит ли ячейка, которая проверяется в данный момент, функцию. Если содержит, то ячейка игнорируется во избежание циклической ссылки.

Функцию можно изменить, чтобы она определяла минимальное, среднее значение, или сумму.

Возвращение массива случайных целых чисел без повторов

Функция Randomlntegers возвращает массив натуральных чисел без повторов. Она предназначена для применения в формуле массива в нескольких ячейках: {=RandomIntegers ()}. Формула вводится в диапазон с помощью комбинации клавиш <Ctrl+Shift+Enter>. Она возвращает массив натуральных чисел без повторов, упорядоченных произвольным образом. Если диапазон содержит 35 ячеек, числа принимают значения от 1 до 35 (рис. 7).

Рис. 7. Пример использования функции Randomlntegers

Расположение значений диапазона в произвольном порядке

Функция RangeRandomize получает в качестве аргумента диапазон и возвращает массив, содержащий этот диапазон с произвольно переставленными значениями (рис. 8). Код функции приведен во вложенном Excel-файле. В диапазон В2:В11 введена формула массива:

{=RangeRandomize (А2:А11)}

Эта формула возвращает содержимое диапазона А2:А11, но в случайном порядке.

Рис. 8. Функция RangeRandomize возвращает содержимое диапазона в случайном порядке

[1] По материалам книги Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA. – М: Диалектика, 2013. – С. 362–375.


Прокомментировать