Studhelper IT
Разработка приложений, переводы книг по программированию
Страницы
пятница, 4 сентября 2015 г.
Пользовательские функции Часть 2-1 Excel
2. Столбцы с первого по предпоследний заполнить произвольными данными (примерно 15 – 20 строк).
Последний столбец не заполняется, его данные рассчитываются в следующем задании.
Учесть, что одно и то же предприятие обычно выпускает разные виды продукции, одна и та же продукция может выпускаться на различных предприятиях. Поэтому и продукция, и предприятия повторяются в таблице.
3. В столбце I (последнем) для каждой строки вывести номера месяцев с максимальным выпуском (таких номеров может быть несколько). Если номеров несколько, то они должны разделяться пробелом.
Для вычисления создать и использовать пользовательскую функцию, возвращающую номера максимальных элементов числового массива.
Решение
Составим таблицу в Excel и заполним ее данными:
‘Функция возвращает номера месяцев с максимальным выпуском
Public Function MaxMonths(rng As Range) As String
Dim i As Integer ‘счетчик цикла
Dim str As String ‘строка с номерами месяцев
Dim max As Integer ‘максимальный выпуск
‘начинаем с первого месяца
str = «1»
‘устанавливаем максимум как объем 1 месяца
max = CInt(rng(1, 1))
‘цикл по всем месяцам диапазона
For i = 2 To rng.Columns.Count
‘если максимум меньше текущего значения, то устанавливаем новый
‘максимум и начинаем строку с номерами сначала
If max
В списке функций выберем нашу функцию:
Появится вот такое окно:
Предлагается выбрать диапазон ячеек. Выделим мышью шесть ячеек с выпуском в первой строке:
Sub InstallFunc()
Application.MacroOptions Macro:=»MaxMonths», _
Description:=»возвращает номера месяцев с максимальным выпуском»
End Sub
Добавим ее в модуль и выполним. После этого снова возвращаемся на рабочий лист и запускаем Мастер функций:
Видим, что у функции появилось описание. Понятно, что оно не обязано быть именно таким, можете сделать, какое надо.
Сейчас копируем эту функцию на все ячейки нашего диапазона месяцев и получаем результат:
Канал в Telegram
Вы здесь
Пользовательские функции в Excel на VBA (+ видео)
Все, кто работает с Excel, сталкивались со встроенными функциями, например ВПР, ЕСЛИ и т.д. Из этих функций в Excel строятся различные формулы позволяющие, посчитать, обработать или принять решение. Эти функции находятся в мастере функций и разделены по группам. Мастер же позволяет упростить ввод аргументов функции. Набор функций в Excel достаточно обширный и для большинства задач можно найти нужную функцию или составить формулу из нескольких вложенных функций. Но что, если для решения задачи требуются особые вычисления!? В этом нам поможет встроенный язык VB, который позволяет написать собственные процедуры и функции обработки данных, при этом функции могут быть добавлены в мастер функций и использоваться как обычные встроенные функции (пользовательские функции).
Итак, что такое функция в Excel и VBA?
Функция — это набор команд, которые обрабатывают данные заданным образом и возвращают результат. Функция имеет входные данные, используемые при расчетах (аргументы функции). По сути, функция это та же процедура, с которыми мы уже сталкивались неоднократно при написании макросов, только функция еще и возвращает результат. Функции могут использоваться в следующих ситуациях:
Область видимости функций аналогична области действия переменных т.е. Public, Private, Static. Описание функции начинается с ключевого слова Function и заканчивается End Function.Требования к именам функций такие же, как и к именам переменных в VBA.
Рассмотрим простейший пример функции:
Function Test (a as integer, b as integer) as integer
Test = a*b
end function
Остановимся теперь более подробно на создании пользовательских функций, которые будут использоваться в формулах для вычислений.
Мы уже создали функцию Test. Добавьте ее в созданный Module.
Переходим теперь на лист и вводим в ячейки A1 и B1 целые числа, выделяем ячейку C1 и жмем вставка функции. В мастере функций необходимо выбрать категорию «Определенные пользователем» и в списке функций найдите «Test«:
Далее, вставляйте как обычную функцию, указав аргументы.
Есть один момент, пользовательские функции, в отличие от встроенных, работают только при включенных макросах. Как включить поддержку макросов описано в этой статье.
Определение категории функции
По умолчанию функции находятся в категории «Определенные пользователем«. Каким образом можно пользовательской функции переназначить категорию!?
Данную команду достаточно выполнить всего один раз и в дальнейшем при открытии книги функция будет находится в определенной категории. Поэтому, напишем процедуру InstallFunc, которая определит категорию для нашей функции.
Sub InstallFunc()
Application.MacroOptions Macro:=»Test», Category:=10
End Sub
Описание пользовательской функции
Как Вы заметили, наша функция не имеет никакого описания, для пользователей это будет неудобно, а те, кто впервые увидят эту функцию, вообще не поймут для чего она. Поэтому добавим некоторое описание нашей функции. По аналогии с определением категории, нам необходимо один раз выполнить команду Application.MacroOptions. Расширим наш InstallFunc:
Sub InstallFunc()
Application.MacroOptions Macro:=»Test», Category:=10, _
Description:=»Находит произведение аргументов A и B»
End Sub
Собственно описание находится в Description. Выполните процедуру InstallFunc. Готово. Смотрим результат:
Для того чтобы функции были видны постоянно для всех книг, можно создать набор функций, сделать Install для размещения функций по категориям, добавить к ним описание и сохранить книгу как файл Надстройки xla с последующим подключением в Excel.
На этом пока все. Видео по созданию пользовательской функции и готовый пример Вы можете скачать ниже.
Как создавать пользовательские функции Excel с помощью VBA 2021
Microsoft Excel Pack поставляется с множеством заранее определенных функций, которые делают максимальную работу для нас. В большинстве случаев нам не нужны никакие другие функции, кроме встроенных функций. Но, если вам нужна какая-то функциональность, которая не была предоставлена какой-либо предварительно определенной функцией Excel?
Создание пользовательских функций Excel
Поскольку мы будем создавать пользовательскую функцию Excel с помощью VBA, нам нужно сначала включить вкладку «Разработчик». По умолчанию он не включен, и мы можем включить его. Откройте лист Excel и нажмите кнопку «Excel», а затем нажмите «Параметры Excel». Затем установите флажок « Показать вкладку разработчика в ленте ».
Теперь, чтобы открыть редактор Visual Basic, откройте вкладку «Разработчик» и нажмите значок «Visual Basic», чтобы запустить Visual Basic Editor.
Вы можете даже использовать сочетание клавиш « Alt + F11 », чтобы запустить редактор Visual Basic. Если вы используете эту комбинацию клавиш, тогда нет необходимости включать вкладку «Разработчик».
Теперь все настроено на создание функции пользовательского Excel. Щелкните правой кнопкой мыши на «Объекты Microsoft Excel», нажмите «Вставить», а затем нажмите «Модуль».
Открывает простое окно, которое является местом для написания кода.
Прежде чем писать код, вам нужно для понимания синтаксиса образца, который необходимо использовать для создания пользовательской функции Excel, и вот как это сделать,
Нет «Return `, как у нас с нормальными языками программирования.
Вставьте свой код в открытое простое окно. Например, я создам функцию «FeesCalculate», которая вычисляет «8%» значения, предоставляемого функции. Я использовал возвращаемый тип как «Двойной», так как значение может быть и в десятичных знаках. Вы можете видеть, что мой код следует за синтаксисом VBA.
Теперь настало время сохранить книгу Excel. Сохраните его с расширением «.xslm», чтобы использовать лист excel с макросом. Если вы не сохраняете его с помощью этого расширения, оно выдает ошибку.
Теперь вы можете использовать функцию User Defined на листе Excel как обычную функцию Excel с помощью «=». Когда вы начинаете вводить «=» в ячейке, он показывает созданную функцию вместе с другой встроенной функцией.
Вы можете увидеть пример ниже:
Пользовательские функции Excel не могут изменить среду Microsoft Excel и, таким образом, они имеют ограничения.
Ограничения пользовательских функций Excel
Пользовательские функции Excel не могут выполнять следующие операции,
Есть еще много таких ограничений и упомянуты некоторые из них.
Это простые шаги, которые необходимо выполнить для создания пользовательских Функции Excel.
Mozilla позволяет предприятиям создавать пользовательские браузеры для Firefox
Mozilla запустит программу, которая позволит компаниям создавать свои собственные настроенные браузеры на основе следующей версии Firefox.
Пакетная обработка датчиков, функция ReadingTransform, пользовательские функции сенсора
Microsoft представила три новых сенсорных функции в Windows 10, а именно: пакетное дозирование, ReadingTransform и пользовательские датчики, чтобы помочь разработчикам.
Как создавать графики с нуля с помощью MS Excel 2013
Хотите знать, как вы можете создавать графики в MS Excel с нуля? Читайте дальше, чтобы узнать больше и стать на путь инфографики ниндзя.
Пользовательские функции VBA
Ранее были рассмотрены процедуры VBA. В настоящей заметке рассмотрены функции VBА.[1] Функция — это процедура VBA, которая выполняет вычисления и возвращает значение. Функции можно использовать в коде VBA или в формулах Excel. Процедуру можно рассматривать как команду, которая выполняется пользователем или другой процедурой. С другой стороны, функция обычно возвращает отдельное значение (или массив) подобно функциям рабочих листов Excel и встроенным функциям VBA.
Рис. 1. Применение пользовательской функции в формуле рабочего листа
Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)
Excel содержит более 400 встроенных функций. Если этого количества недостаточно, можно создавать пользовательские функции с помощью VBA. Однако следует отметить, что функции VBA, используемые в формулах, обычно выполняются медленнее, чем встроенные функции Excel. Пользовательские функции отображаются в диалоговом окне Мастер функций наряду со встроенными функциями Excel.
Пример пользовательской функции
Начнем с примера – функции RemoveVowels (УдалитьГласные), которая принимает текстовый аргумент, удаляет все гласные буквы и возвращает текст, состоящий только из согласных.
Function RemoveVowels(txt) As String
‘ Удаляет все гласные звуки из аргумента txt
Dim i As Long
RemoveVowels = » »
For i = 1 To Len(txt)
If Not ucase(Mid(txt, i, 1)) Like » [AEIOUАЕИОУЮЭЯ] » Then
RemoveVowels = RemoveVowels & Mid(txt, i, 1)
End If
Next i
End Function
Код пользовательских функций, которые используются в формуле рабочего листа, вводите в обычном модуле VBA. Если вы поместите пользовательские функции в модуле Лист, в Пользовательской форме или в модуле ЭтаКнига, они не будут выполняться в формулах.
Функцию RemoveVowels можно использовать, например, в формуле в ячейке В1 (рис. 1) =RemoveVowels (А1). Вы также можете создавать вложенные пользовательские функции и сочетать их в формулах с обычными функциями Excel. Например, =ПРОПИСН(RemoveVowels(А1))
Пользовательские функции можно применять не только в формулах рабочего листа, но и в процедурах VBA. Например, процедура ZapTheVowels() сначала отображает окно для ввода текста пользователем, затем обрабатывает этот текст функцией RemoveVowels, и наконец использует встроенную функцию VBA MsgBox для отображения результатов (рис. 2). Первоначальные данные отображаются в заголовке окна сообщения.
Sub ZapTheVowels()
Dim UserInput As String
UserInput = InputBox( » Введите текст: » )
MsgBox RemoveVowels(UserInput), vbInformation, UserInput
End Sub
Рис. 2. Применение пользовательской функции в процедуре VBA
Помните, что функции, используемые в формулах рабочего листа, — «пассивные». Они не могут изменять содержимое рабочего листа. Например, нельзя написать функцию, которая будет изменять цвет текста в ячейке в зависимости от значения этой ячейки. Функция возвращает значение, но не может выполнять операции над объектами.
Из этого правила имеется одно исключение. Вы можете изменить текст комментария ячейки с помощью пользовательской функции VBA:
Function ModifyComment(Cell As Range, Cmt As String)
Cell.Comment.Text Cmt
End Function
Например, можно ввести в ячейку В1 формулу =ModifyComment(А1,»Комментарий был изменен»). Функция не работает, если в ячейке А1 отсутствует комментарий.
Рассмотрим код функции RemoveVowels подробнее. Функция начинается с ключевого слова Function, а не Sub, после которого указывается название функции (RemoveVowels). Эта специальная функция использует только один аргумент (Txt), заключенный в скобки. Ключевое слово As String определяет тип данных значения, которое возвращает функция. (Excel по умолчанию использует тип данных Variant, если тип данных не определен.)
Вторая строка — простой комментарий (необязательный), который описывает выполняемые функцией действия. После комментария приведен оператор Dim, который объявляет переменную (i), применяемую в функции. Тип этой переменной — Long. Далее в качестве переменной используется имя функции. Как только функция завершает свое выполнение, возвращается текущее значение переменной, которое соответствует названию функции.
Следующие пять инструкций образуют цикл For-Next. Процедура циклически просматривает каждый символ введенного текста, создавая на их основе строку. Первая инструкция в цикле использует функцию VBA Mid, которая возвращает единственный символ строки ввода, а также преобразует этот символ в символ верхнего регистра. Затем этот символ сравнивается со списком символов с помощью оператора VBA Like (подробнее см. Оператор Like). Другими словами, значение выражения If будет True, если символ отличен от символов А, Е, I, O, U, А, Е, И, О, У, Ы, Э, Ю и Я. В подобных случаях символ добавляется к переменной RemoveVowels.
По завершении цикла из строки ввода удаляются все гласные буквы. Эта строка и является значением, возвращаемым функцией RemoveVowels. Процедура завершается оператором End Function. (Альтернативный код – RemoveVowels2, выполняющий ту же задачу приведен в модуле VBA приложенного Excel-файла.)
Синтаксис функции
Для объявления функции применяется следующий синтаксис (элементы аналогичны обычной процедуре; подробнее см. Работа с процедурами VBA).
Значение всегда присваивается названию функции минимум один раз и, как правило, тогда, когда функция завершила выполнение. Создание пользовательской функции начните с создания модуля VBA (можно также использовать существующий модуль). Введите ключевое слово Function, после которого укажите название функции и список ее аргументов (если они есть) в скобках. Вы также можете объявить тип данных значения, которое возвращает функция, используя ключевое слово As (это делать необязательно, но рекомендуется). Вставьте код VBA, выполняющий требуемые действия, и убедитесь, что необходимое значение присваивается переменной процедуры, соответствующей названию функции, минимум один раз в теле функции. Функция заканчивается оператором End Function.
Имена функций подчиняются тем же правилам, что и имена переменных. Если вы планируете использовать функцию в формуле рабочего листа, убедитесь, что название не имеет форму адреса ячейки. Также не присваивайте функциям имена, которые соответствуют названиям встроенных функций Excel. Если область действия функции не задана, то по умолчанию подразумевается Public. Функции, объявленные как Private, не отображаются в диалоговом окне Мастер функций.
Функцию можно вызвать одним из следующих способов:
Рис. 3. Вызов функции в окне отладки
В отличие от процедур, функции не отображаются в диалоговом окне Макрос (меню Разработчик –> Код –> Макросы; или Alt+F8).
Аргументы функций
Аргументы могут представляться переменными (в том числе массивами), константами, символьными данными или выражениями. Некоторые функции не имеют аргументов. Функции имеют как обязательные, так и необязательные аргументы.
Функции без аргументов
В Excel есть несколько встроенных функций, не имеющих аргументов, например, СЛЧИС, СЕГОДНЯ, ТДАТА. Несложно создать аналогичные пользовательские функции. Например:
Function User()
‘ Возвращает имя пользователя
User = Application.UserName
End Function
При вводе формулы =User() ячейка возвращает имя текущего пользователя (рис. 4). Обратите внимание: при использовании функции без аргумента в формуле рабочего листа необходимо указать пустые скобки.
Рис. 4. Формула =User() возвращает имя текущего пользователя
Пользовательские функции ведут себя подобно встроенным функциям Excel. Обычно пользовательская функция пересчитывается тогда, когда это нужно, т.е. в случае изменения одного из аргументов функции. Однако вы можете выполнять пересчет функций чаще. Функция пересчитывается при изменении любой ячейки, если в процедуру добавлен оператор
Метод Volatile объекта Application имеет один аргумент (True или False). Если функция выделена как volatile (изменяемая), она пересчитывается всякий раз, когда изменяется любая ячейка листа. При использовании аргумента False метода Volatile функция пересчитывается только тогда, когда в результате пересчета изменяется один из ее аргументов.
В Excel есть встроенная функция СЛЧИС. Но мне не слишком понравилось, что случайные числа изменяются при каждом пересчете рабочего листа. Поэтому я разработал функцию, которая возвращает случайные числа, не изменяющиеся при пересчете формул. Для этого была использована встроенная функция VBA Rnd:
Function StaticRand()
‘ Возвращает случайное число, не изменяемое при пересчете формул
StaticRand = Rnd()
End Function
Функция с одним аргументом
Допустим вам нужно подсчитать комиссионные, зависящие от объема продаж. Вычисления основываются на следующей таблице значений:
Рис. 5. Таблица комиссионных
Существует несколько способов вычислить комиссионные. Например, с помощью следующей формулы (если объем продаж поместить в ячейку D1):
=ЕСЛИ(И(D1>=0;D1 =10000;D1 =20000;D1 =40000;D1*0,14))))
Эта формула неудачна по нескольким причинам. Во-первых, она сложна, ее нелегко набрать, и в дальнейшем редактировать. Во-вторых, значения строго определены в формуле, из-за чего ее сложно изменять. Гораздо лучше использовать ВПР (рис. 6).
Рис. 6. Использование функции ВПР для вычисления комиссионных
Еще лучше (тогда не нужно использовать таблицу соответствия) создать пользовательскую функцию:
Function Commission(Sales)
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
‘ Вычисление комиссионных с продаж
Select Case Sales
Case 0 To 9999.99: Commission = Sales * Tier1
Case 10000 To 19999.99: Commission = Sales * Tier2
Case 20000 To 39999.99: Commission = Sales * Tier3
Case Is >= 40000: Commission = Sales * Tier4
End Select
End Function
После ввода в модуль VBA эту функцию можно использовать в формуле на рабочем листе или вызвать из других процедур VBA. При вводе в ячейку следующей формулы будет получен результат 3000:
Используйте аргументы, а не ссылки на ячейки. Все применяемые в пользовательской функции диапазоны должны передаваться в качестве аргументов. Рассмотрим функцию, которая возвращает значение в ячейке А1, умноженное на 2.
Function DoubleCell()
DoubleCell = Range( » Al » ) * 2
End Function
Хотя эта функция работает, в некоторых случаях она выдает неправильный результат. Причина в том, что вычислительный механизм Excel не учитывает диапазоны, которые не передаются в качестве аргументов. Вследствие этого иногда перед возвратом функцией значения, не вычисляются все связанные величины. Следует также написать функцию DoubleCell, в качестве аргумента которой передается значение ячейки А1.
Function DoubleCell(cell)
DoubleCell = cell * 2
End Function
Функция с двумя аргументами
Представим, что менеджер, о котором речь шла выше, внедряет новую политику, разработанную для уменьшения текучести кадров: общая сумма комиссионных, подлежащих выплате, увеличивается на 1% за каждый год, который служащий проработал в компании. Изменим пользовательскую функцию Commission так, чтобы она принимала два аргумента. Новый аргумент представляет количество лет, отработанных сотрудником в компании. Назовем эту новую функцию Commission2:
Function Commission2(Sales, Years) As Single
‘ Вычисление комиссионных с продаж на основе
‘ длительности стажа
Commission2 = Commission(Sales) + _
(Commission(Sales) * Years / 100)
End Function
Функция с аргументом в виде массива
В качестве аргументов функции могут принимать один или несколько массивов, обрабатывать этот массив (массивы) и возвращать единственное значение. Функция, представленная ниже, принимает в качестве аргумента массив и возвращает сумму его элементов.
Function SumArray(List) As Double
Dim Item As Variant
SumArray = 0
For Each Item In List
If WorksheetFunction.IsNumber(Item) Then _
SumArray = SumArray + Item
Next Item
End Function
Функция Excel ЕЧИСЛО проверяет, является ли каждый элемент числом, прежде чем добавить его к общему целому. Добавление этого простого оператора проверки данных устраняет ошибки несоответствия типов при попытке выполнить арифметическую операцию над строкой.
Функция с необязательными аргументами
Многие встроенные функции Excel имеют необязательные аргументы. Пример — функция ЛЕВСИМВ, возвращающая символы с левого края строки. Она имеет следующий синтаксис:
Первый аргумент — обязательный, в отличие от второго. Если не указан второй аргумент, Excel предполагает значение 1.
Пользовательские функции, разработанные в VBA, также могут иметь необязательные аргументы. Необязательный аргумент вы зададите, если введете перед именем аргумента ключевое слово Optional. В списке аргументов необязательные аргументы определяются после всех обязательных. Например:
Function User2(Optional Uppercase As Variant)
If IsMissing(Uppercase) Then Uppercase = False
User2 = Application.UserName
If Uppercase Then User2 = UCase(User2)
End Function
Если аргумент равен False или опущен, то имя пользователя возвращается без каких-либо изменений. Если же аргумент функции True, то имя пользователя возвращается в символах верхнего регистра (с помощью VBA-функции Ucase). Обратите внимание на первый оператор функции — он содержит VBA-функцию IsMissing, которая определяет наличие аргумента. Если аргумент отсутствует, оператор присваивает переменной Uppercase значение False (задано по умолчанию).
Функция VBA, возвращающая массив
Функция MonthNames — простой пример применения функции Array в пользовательской функции.
Функция, возвращающая значение ошибки
В VBA содержатся встроенные константы для обозначения ошибок, которые должна возвращать пользовательская функция (эти значения — ошибки выполнения формул Excel, а не ошибки выполнения кода VBA):
Ниже приведена преобразованная функция RemoveVowels (см. пример в начале). Конструкция If-Then применяется для выполнения альтернативного действия в случае, когда аргумент не является текстовым. Эта функция вызывает функцию Excel ЕТЕКСТ, которая определяет, содержит ли аргумент текст. Если ячейка содержит текст, то функция возвращает нормальный результат. Если же ячейка содержит не текст (или пуста), то функция возвращает ошибку #ЗНАЧ!
Function RemoveVowels3(txt) As Variant
‘ Удаляет все гласные буквы из аргумента Txt
‘ Возвращает ошибку #ЗНАЧ!, если аргумент — не строка
Dim i As Long
RemoveVowels3 = » »
If Application.WorksheetFunction.IsText(txt) Then
For i = 1 To Len(txt)
If Not UCase(Mid(txt, i, 1)) Like » [AEIOUАЕИОУЮЭЯ] » Then
RemoveVowels3 = RemoveVowels3 & Mid(txt, i, 1)
End If
Next i
Else
RemoveVowels3 = CVErr(xlErrValue)
End If
End Function
Обратите внимание, что был изменен тип данных для возвращаемого функцией значения. Поскольку функция может возвращать что-то еще, кроме строки, тип данных был изменен на Variant.
Функция с неопределенным количеством аргументов
Существует возможность создавать пользовательские функции, имеющие неопределенное количество аргументов. Примените в качестве последнего (или единственного) аргумента массив и добавьте перед ним ключевое слово ParamArray (ParamArray относится только к последнему аргументу в списке аргументов процедуры. Он всегда имеет тип данных Variant и всегда является необязательным аргументом). Следующая функция возвращает сумму всех аргументов, в качестве которых может выступать, как одно значение (ячейка), так и диапазон.
Function SimpleSum(ParamArray arglist() As Variant) As Double
Dim cell As Range
Dim arg As Variant
For Each arg In arglist
For Each cell In arg
SimpleSum = SimpleSum + cell
Next cell
Next arg
End Function
Отладка функций
При использовании формулы на рабочем листе для тестирования функции происходящие в процессе выполнения ошибки не отображаются в знакомом диалоговом окне сообщений. Формула просто возвращает значение ошибки (#ЗНАЧ!). К счастью, это не представляет большой проблемы при отладке функций, так как всегда существует несколько обходных путей.
Рис. 7. Используйте окно отладки для отображения результатов при выполнении функции
В данном случае значения двух переменных, Ch и i, выводятся в окне отладки (Immediate) всякий раз, когда в программе встречается оператор Debug.Print. Встаньте курсором в любое место процедуры Test() и нажмите F5. На рис. 7 показан результат для случая, когда функция принимает аргумент TusconArizona.
Использование метода MacroOptions
Можно воспользоваться методом MacroOptions объекта Application, который позволяет включить в состав встроенных функций Excel разработанные вами функции. Этот метод позволяет:
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim FuncCat As Long
Dim Arg1Desc As String, Arg2Desc As String
FuncName = » Draw »
FuncDesc = » Содержимое случайной ячейки диапазона »
FuncCat = 5 ‘ Ссылки и массивы
Arg1Desc = » Диапазон, который содержит значения »
Arg2Desc = » (не обязательный) Если False или отсутствует, _
функция Rnd не пересчитывается. »
Arg2Desc = Arg2Desc & » Если True, функция Rnd пересчитывается »
Arg2Desc = Arg2Desc & » при любом изменении на листе. »
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=FuncCat, _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
End Sub
На рис. 8 показаны диалоговые окна Мастер функций и Аргументы функции после выполнения процедуры DescribeFunction().
Рис. 8. Вид диалоговых окон Мастер функций и Аргументы функции для пользовательской функции
Процедуру DescribeFunction()следует вызывать только один раз. После ее вызова информация, связанная с функцией, сохраняется в рабочей книге. Но если вы модифицировали процедуру, повторите ее вызов.
Если вы не укажете категорию функции с помощью метода MacroOptions, пользовательская функция рабочего листа появится в категории Определенные пользователем диалогового окна Мастер функций. В таблице (рис. 9) перечислены номера категорий, которые можно использовать в качестве значений аргумента Category метода MacroOptions. Обратите внимание, что некоторые из этих категорий (от 10 до 13) обычно не отображаются в диалоговом окне Мастер функций. Если же отнести одну из пользовательских функций в подобную категорию, она появится в диалоговом окне.
Рис. 9. Номера категорий функций
Использование надстроек для хранения пользовательских функций
При желании можно сохранить часто используемые пользовательские функции в файле надстройки. Основное преимущество такого подхода заключается в следующем: функции могут быть применены в формулах без спецификатора имени файла. Предположим, у вас есть пользовательская функция ZapSpaces; она хранится в файле Myfuncs.xlsm. Чтобы применить ее в формуле другой рабочей книги (отличной от Myfuncs.xlsm), необходимо ввести следующую формулу: =Myfuncs.xlsm!ZapSpaces(А1:С12).
Если вы создадите надстройку на основе файла Myfuncs.xlsm и эта надстройка будет загружена в текущем сеансе работы Excel, то ссылку на файл можно пропустить, введя следующую формулу: =ZapSpaces(А1:С12). Создание надстроек будет рассмотрено отдельно.
Потенциальная проблема, которая может возникнуть из-за использования надстроек для хранения пользовательских функций, связана с зависимостью рабочей книги от файла надстроек. Если вы передаете рабочую книгу сотруднику, не забудьте также передать копию надстройки, которая содержит требуемые функции.
Использование функций Windows API
VBA может заимствовать методы из других файлов, которые не имеют ничего общего с Excel или VBA, например, файлы DLL (Dynamic Link Library — динамически подключаемая библиотека), которые используются Windows и другими программами. В результате в VBA появляется возможность выполнять операции, которые без заимствованных методов находятся за пределами возможностей языка.
Windows API (Application Programming Interface — интерфейс прикладного программирования) представляет собой набор функций, доступных программистам в среде Windows. При вызове функции Windows из VBA вы обращаетесь к Windows API. Многие ресурсы Windows, используемые программистами Windows, можно получить из файлов DLL, в которых хранятся программы и функции, подсоединяемые в процессе выполнения программы, а не во время компиляции.
Прежде чем использовать функцию Windows API, ее необходимо объявить вверху программного модуля. Если программный модуль — это не стандартный модуль VBA (т.е. модуль для UserForm, Лист или ЭтаКнига), то API-функцию необходимо объявить, как Private.
Объявление API-функции имеет некоторую сложность — функция должна объявляться максимально точно. Оператор объявления указывает VBA следующее:
После объявления API-функцию можно использовать в программе VBA.
Рассмотрим пример API-функции, которая отображает имя папки Windows (с помощью стандартных операторов VBA эту задачу порой выполнить невозможно). Для начала объявим API-функцию:
Declare PtrSafe Function GetWindowsDirectoryA Lib » kernel32 » _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long
Эта функция, имеющая два аргумента, возвращает название папки, в которой установлена операционная система Windows. После вызова этой функции путь к папке Windows будет храниться в переменной lpBuffer, а длина строки пути — в переменной nSize.
Следующий пример отображает результат в окне сообщения:
Sub ShowWindowsDir()
Dim WinPath As String * 255
Dim WinDir As String
WinPath = Space(255)
WinDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
MsgBox WinDir, vbInformation, » Windows Directory »
End Sub
В процессе выполнения процедуры ShowWindowsDir отображается окно сообщения с указанием расположения папки Windows.
Иногда требуется создать оболочку (wrapper) для API-функций. Другими словами, вы создадите собственную функцию, использующую API-функцию. Такой подход существенно упрощает использование API-функции. Ниже приведен пример такой функции VBA:
Function WindowsDir() As String
‘ Название папки Windows
Dim WinPath As String * 255
WinPath = Space(255)
WindowsDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
End Function
После объявления этой функции можно вызвать ее из другой процедуры: MsgBox WindowsDir(). Можно также использовать эту функцию в формуле рабочего листа: =WindowsDir().
Внимание! Не удивляйтесь сбоям в системе при использовании в VBA функций Windows API. Заранее сохраните свою работу перед тестированием.
Определение состояния клавиши
Рис. 10. Проверка нажатия клавиш Shift, Ctrl и Alt
Код функции VBA можно найти в приложенном Excel-файле
Работа с функциями Windows API может быть довольно сложной. Во многих книгах по программированию перечислены операторы объявления API-функций с соответствующими примерами. Как правило, можно просто скопировать выражения объявления и использовать функции, не вникая в их суть. Большинство VBA-программистов в Excel рассматривают API-функции как панацею для решения большинства задач. В Интернете вы найдете сотни вполне надежных примеров, которые можно скопировать и вставить в собственную программу.
В текстовом файле содержатся объявления и константы Windows API. Можно открыть этот файл в текстовом редакторе и скопировать соответствующие объявления в модуль VBA.