Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Использование функций excel в vba

How to dou

Программирование макросов на VBA в Excel

Table of Contents:

Хотя VBA предлагает достойный набор встроенных функций, вы не всегда можете найти именно то, что вам нужно. К счастью, вы также можете использовать большинство функций листа Excel в своих процедурах VBA. Единственными функциями рабочего листа, которые вы не можете использовать, являются те, которые имеют эквивалентную функцию VBA. Например, вы не можете использовать функцию RAND Excel (которая генерирует случайное число), потому что VBA имеет эквивалентную функцию: Rnd.

VBA делает функции листа Excel доступными через объект WorksheetFunction, который содержится в объекте Application. Вот пример того, как вы можете использовать функцию SUM Excel в выражении VBA:

Вы можете пропустить часть приложения или часть WorksheetFunction выражения. В любом случае VBA выясняет, что вы делаете. Другими словами, эти три выражения работают одинаково:

Мое личное предпочтение заключается в том, чтобы использовать часть WorksheetFunction, чтобы совершенно ясно понять, что код использует функцию Excel.

Примеры функций рабочего листа

Здесь вы узнаете, как использовать функции рабочего листа в выражениях VBA.

Поиск максимального значения в диапазоне

Вот пример, показывающий, как использовать функцию листа MAX Excel в процедуре VBA. Эта процедура отображает максимальное значение в столбце A активного листа:

Использование функции рабочего листа в вашем коде VBA.

Вы можете использовать функцию MIN, чтобы получить наименьшее значение в диапазоне. И, как и следовало ожидать, вы можете использовать другие функции рабочего листа аналогичным образом. Например, вы можете использовать функцию LARGE для определения наименьшего значения k th в диапазоне. Следующее выражение демонстрирует это:

Обратите внимание, что функция LARGE использует два аргумента. Второй аргумент представляет k th часть — 2, в этом случае (второе наибольшее значение).

Вычисление ипотечного платежа

В следующем примере используется функция листа PMT для расчета ипотечного платежа. Три переменных используются для хранения данных, переданных функции Pmt в качестве аргументов.В окне сообщения отображается расчетный платеж.

Как показано в следующем заявлении, вы также можете вставлять значения непосредственно в качестве аргументов функции:

Однако использование переменных для хранения параметров делает код более легким для чтения и изменения, если необходимо.

Использование функции поиска

В следующем примере используются функции ввода-вывода VBA и MsgBox, а также функция VLOOKUP в Excel. Он запрашивает номер детали, а затем получает цену из таблицы поиска. Ниже диапазон A1: B13 называется PriceList.

Диапазон, названный PriceList, содержит цены на запчасти.

Вот как работает процедура GetPrice:

Функция InputBox VBA запрашивает у пользователя номер детали.

Номер детали, которую вводит пользователь, присваивается переменной PartNum.

Следующий оператор активирует лист цен, на всякий случай, если он еще не является активным листом.

В коде используется функция VLOOKUP, чтобы найти номер детали в таблице.

Обратите внимание, что аргументы, используемые в этом утверждении, такие же, как те, которые вы использовали бы с помощью функции в формуле рабочего листа. Этот оператор присваивает результат функции переменной Price.

Код отображает цену для детали через функцию MsgBox.

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

Ввод функций рабочего листа

Вы не можете использовать диалоговое окно функции вставки Excel, чтобы вставить функцию листа в модуль VBA. Вместо этого вводите такие функции старомодным способом: вручную. Тем не менее, вы можете использовать диалоговое окно «Вставить функцию», чтобы идентифицировать функцию, которую вы хотите использовать, и узнать о ее аргументах.

Вы также можете воспользоваться опцией Auto List Members VBE, которая отображает раскрывающийся список всех функций рабочего листа. Просто введите Приложение. Рабочий лист , за которым следует период. Затем вы увидите список функций, которые вы можете использовать. Если эта функция не работает, выберите команду «Инструменты» → «Параметры» VBE, перейдите на вкладку «Редактор» и поместите проверку рядом с «Автосписок».

Получение списка функций рабочего листа, которые можно использовать в коде VBA.

Подробнее об использовании функций рабочего листа

Новички в VBA часто путают встроенные функции VBA и функции книги Excel. Хорошим правилом для запоминания является то, что VBA не пытается изобретать колесо.По большей части VBA не дублирует функции листа Excel.

Для большинства функций рабочего листа, которые недоступны в качестве методов объекта WorksheetFunction, вы можете использовать эквивалентный встроенный оператор или функцию VBA. Например, функция рабочего листа MOD недоступна в объекте WorksheetFunction, потому что VBA имеет эквивалент: встроенный оператор Mod.

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

Процедуры «Function» и «Sub» в VBA

Встроенные функции VBA

Перед тем, как приступить к созданию собственных функций VBA, полезно знать, что Excel VBA располагает обширной коллекцией готовых встроенных функций, которые можно использовать при написании кода.

Список этих функций можно посмотреть в редакторе VBA:

  • Откройте рабочую книгу Excel и запустите редактор VBA (нажмите для этого Alt+F11), и затем нажмите F2.
  • В выпадающем списке в верхней левой части экрана выберите библиотеку VBA.
  • Появится список встроенных классов и функций VBA. Кликните мышью по имени функции, чтобы внизу окна отобразилось её краткое описание. Нажатие F1 откроет страницу онлайн-справки по этой функции.

Кроме того, полный список встроенных функций VBA с примерами можно найти на сайте Visual Basic Developer Centre.

Пользовательские процедуры «Function» и «Sub» в VBA

В Excel Visual Basic набор команд, выполняющий определённую задачу, помещается в процедуру Function (Функция) или Sub (Подпрограмма). Главное отличие между процедурами Function и Sub состоит в том, что процедура Function возвращает результат, процедура Sub – нет.

Читать еще:  Vba excel cells свойства и методы

Поэтому, если требуется выполнить действия и получить какой-то результат (например, просуммировать несколько чисел), то обычно используется процедура Function, а для того, чтобы просто выполнить какие-то действия (например, изменить форматирование группы ячеек), нужно выбрать процедуру Sub.

Аргументы

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

Имейте в виду, что наличие аргументов для процедур Function и Sub в VBA не является обязательным. Для некоторых процедур аргументы не нужны.

Необязательные аргументы

Процедуры VBA могут иметь необязательные аргументы. Это такие аргументы, которые пользователь может указать, если захочет, а если они пропущены, то процедура использует для них заданные по умолчанию значения.

Возвращаясь к предыдущему примеру, чтобы сделать целочисленный аргумент функции необязательным, его нужно объявить вот так:

В таком случае целочисленный аргумент i по умолчанию будет равен 0.

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

Передача аргументов по значению и по ссылке

Аргументы в VBA могут быть переданы процедуре двумя способами:

  • ByVal – передача аргумента по значению. Это значит, что процедуре передаётся только значение (то есть, копия аргумента), и, следовательно, любые изменения, сделанные с аргументом внутри процедуры, будут потеряны при выходе из неё.
  • ByRef – передача аргумента по ссылке. То есть процедуре передаётся фактический адрес размещения аргумента в памяти. Любые изменения, сделанные с аргументом внутри процедуры, будут сохранены при выходе из процедуры.

При помощи ключевых слов ByVal или ByRef в объявлении процедуры можно задать, каким именно способом аргумент передаётся процедуре. Ниже это показано на примерах:

Помните, что аргументы в VBA по умолчанию передаются по ссылке. Иначе говоря, если не использованы ключевые слова ByVal или ByRef, то аргумент будет передан по ссылке.

Перед тем как продолжить изучение процедур Function и Sub более подробно, будет полезным ещё раз взглянуть на особенности и отличия этих двух типов процедур. Далее приведены краткие обсуждения процедур VBA Function и Sub и показаны простые примеры.

VBA процедура «Function»

Редактор VBA распознаёт процедуру Function, когда встречает группу команд, заключённую между вот такими открывающим и закрывающим операторами:

Как упоминалось ранее, процедура Function в VBA (в отличие от Sub), возвращает значение. Для возвращаемых значений действуют следующие правила:

  • Тип данных возвращаемого значения должен быть объявлен в заголовке процедуры Function.
  • Переменная, которая содержит возвращаемое значение, должна быть названа так же, как и процедура Function. Эту переменную не нужно объявлять отдельно, так как она всегда существует как неотъемлемая часть процедуры Function.

Это отлично проиллюстрировано в следующем примере.

Пример VBA процедуры «Function»: Выполняем математическую операцию с 3 числами

Ниже приведён пример кода VBA процедуры Function, которая получает три аргумента типа Double (числа с плавающей точкой двойной точности). В результате процедура возвращает ещё одно число типа Double, равное сумме первых двух аргументов минус третий аргумент:

Эта очень простая VBA процедура Function иллюстрирует, как данные передаются процедуре через аргументы. Можно увидеть, что тип данных, возвращаемых процедурой, определён как Double (об этом говорят слова As Double после списка аргументов). Также данный пример показывает, как результат процедуры Function сохраняется в переменной с именем, совпадающим с именем процедуры.

Вызов VBA процедуры «Function»

Если рассмотренная выше простая процедура Function вставлена в модуль в редакторе Visual Basic, то она может быть вызвана из других процедур VBA или использована на рабочем листе в книге Excel.

Вызов VBA процедуры «Function» из другой процедуры

Процедуру Function можно вызвать из другой VBA процедуры при помощи простого присваивания этой процедуры переменной. В следующем примере показано обращение к процедуре SumMinus, которая была определена выше.

Вызов VBA процедуры «Function» из рабочего листа

VBA процедуру Function можно вызвать из рабочего листа Excel таким же образом, как любую другую встроенную функцию Excel. Следовательно, созданную в предыдущем примере процедуру FunctionSumMinus можно вызвать, введя в ячейку рабочего листа вот такое выражение:

VBA процедура «Sub»

Редактор VBA понимает, что перед ним процедура Sub, когда встречает группу команд, заключённую между вот такими открывающим и закрывающим операторами:

VBA процедура «Sub»: Пример 1. Выравнивание по центру и изменение размера шрифта в выделенном диапазоне ячеек

Рассмотрим пример простой VBA процедуры Sub, задача которой – изменить форматирование выделенного диапазона ячеек. В ячейках устанавливается выравнивание по центру (и по вертикали, и по горизонтали) и размер шрифта изменяется на заданный пользователем:

Данная процедура Sub выполняет действия, но не возвращает результат.

В этом примере также использован необязательный (Optional) аргумент iFontSize. Если аргумент iFontSize не передан процедуре Sub, то его значение по умолчанию принимается равным 10. Однако же, если аргумент iFontSize передается процедуре Sub, то в выделенном диапазоне ячеек будет установлен размер шрифта, заданный пользователем.

VBA процедура «Sub»: Пример 2. Выравнивание по центру и применение полужирного начертания к шрифту в выделенном диапазоне ячеек

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

Вызов процедуры «Sub» в Excel VBA

Вызов VBA процедуры «Sub» из другой процедуры

Чтобы вызвать VBA процедуру Sub из другой VBA процедуры, нужно записать ключевое слово Call, имя процедуры Sub и далее в скобках аргументы процедуры. Это показано в примере ниже:

Если процедура Format_Centered_And_Sized имеет более одного аргумента, то они должны быть разделены запятыми. Вот так:

Вызов VBA процедуры «Sub» из рабочего листа

Процедура Sub не может быть введена непосредственно в ячейку листа Excel, как это может быть сделано с процедурой Function, потому что процедура Sub не возвращает значение. Однако, процедуры Sub, не имеющие аргументов и объявленные как Public (как будет показано далее), будут доступны для пользователей рабочего листа. Таким образом, если рассмотренные выше простые процедуры Sub вставлены в модуль в редакторе Visual Basic, то процедура Format_Centered_And_Bold будет доступна для использования на рабочем листе книги Excel, а процедура Format_Centered_And_Sized – не будет доступна, так как она имеет аргументы.

Читать еще:  Dateadd vba excel

Вот простой способ запустить (или выполнить) процедуру Sub, доступную из рабочего листа:

  • Нажмите Alt+F8 (нажмите клавишу Alt и, удерживая её нажатой, нажмите клавишу F8).
  • В появившемся списке макросов выберите тот, который хотите запустить.
  • Нажмите Выполнить (Run)

Чтобы выполнять процедуру Sub быстро и легко, можно назначить для неё комбинацию клавиш. Для этого:

  • Нажмите Alt+F8.
  • В появившемся списке макросов выберите тот, которому хотите назначить сочетание клавиш.
  • Нажмите Параметры (Options) и в появившемся диалоговом окне введите сочетание клавиш.
  • Нажмите ОК и закройте диалоговое окно Макрос (Macro).

Внимание: Назначая сочетание клавиш для макроса, убедитесь, что оно не используется, как стандартное в Excel (например, Ctrl+C). Если выбрать уже существующее сочетание клавиш, то оно будет переназначено макросу, и в результате пользователь может запустить выполнение макроса случайно.

Область действия процедуры VBA

В части 2 данного самоучителя обсуждалась тема области действия переменных и констант и роль ключевых слов Public и Private. Эти ключевые слова так же можно использовать применительно к VBA процедурам:

VBA Excel. Пользовательская функция (синтаксис, компоненты)

Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.

Объявление пользовательской функции

Синтаксис функции

Компоненты функции

  • Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
  • Имя — обязательный компонент, имя пользовательской функции.
  • СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
  • Операторы — необязательный компонент, блок операторов (инструкций).
  • Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
  • Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.

Видимость функции

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

Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.

Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private, не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.

Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.

Пример пользовательской функции

Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции – Делимое и Делитель – тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.

Можно ли использовать формулы Excel в коде VBA?

можно ли использовать формулы Excel в коде VBA?

например есть такой диапазон:

можно ли как то в коде vba подсчитать сумму в этом диапазоне?

Что то вроде СУММ(r1). ‘в vba так конечно не сработае

Можно конечно вставить соответствующую формулу в лист и полученное значение прочитать из ячейки и закинуть обратно в VBA. Или пройтись циклом по всему диапазону. Но интересует именно возможность использования формул Excel в коде VBA, возможно ли?

VBA vs Формулы Excel — разные результаты вычислений над числом Пи
Элемент задания имеет вид ctg(pi*a)^2, где рі=3,1415926. В VBA прописывал как 1/(tan(pi*a)^2).

Как сместить формулы в VBA при добавлении новых строк в Excel
Имеется очень много формул в таблице, что бы избежать их нечаянного удаления или при добавлении.

Можно ли создать подобие формулы в документе Word средствами VBA?
На скрине пример документа, где всегда значения в тексте должны быть равны значениям в таблице.

Здравствуйте, sysrepos, Почти все функции Excel хранятся в объекте Application.WorksheetFunction .

Например, посчитать сумму в заданном диапазоне можно так:

Еще можно воспользоваться функцией Evaluate:

Надо различать функции листа Excel и формулы, которые могут содержать функции листа, а могут и не содержать — например, состоять только из арифметических операторов.
В примере уважаемого Аксима первое — это функция, второе — формула.
Я запостил множество примеров использования формул. Не знаю, сработает ли это ссылка у вас:
https://www.cyberforum.ru/search.php?searchid=9300199
Это поиск по форуму с такими параметрами:

Ключевые слова: evaluate ; Сообщения от: Казанский ; Раздел: VBA и подразделы

Можно также и так :

спасибо, разобрался со всеми вариантами.
Но возник вопрос по поводу использования функции Evaluate

написал такой код

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Возможно ли средствами VBA использовать autofilter не открывая файл Excel?
Может кто сталкивался: возможно ли средствами VBA использовать autofilter не открывая файл Excel.

Читать еще:  Vba excel поиск подстроки в строке

Как использовать пользовательские функции при указании диапазонов допустимых значений. Excel+VBA
В общем, идея была простая. Для создания выпадающего списка из допустимых значений нужен источник.

ООП в VBA: Можно ли использовать методы класса внутри его самого
Интересует: является ли это хорошим стилем программирования, или это приведет к проблемам? .

Можно ли как определить как писать формулы в яцейки из VBA по русски или английски?
в общем проблема в том можно ли как определить как писать формулы в яцейки из VBA по русски или.

Создаем свою первую функцию в Excel

Что такое функция в VBA?

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

  1. В качестве формулы на листе, где она может принимать аргументы и возвращать значения.
  2. Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.
  3. В правилах условного форматирования.

Хотя Excel уже содержит более 450 встроенных функций, но их тоже периодически не хватает. Иногда встроенные функции не могут выполнить то, что вы хотите сделать. Иногда для достижения результата необходимо создать огромную и сложную формулу, которая не понятна окружающим. В этом случае вы можете создать пользовательскую функцию, которую легко читать и использовать.

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

В чем отличие процедуры (Sub) от функции (Function)?

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

Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.

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

Создание простой пользовательской функции в VBA

Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.

Ниже представлен код функции, которая из текста оставляет только цифры, отбрасывая буквенные значения.

Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.

Теперь посмотрим как функция работает, попробуем использовать ее на листе:

Прежде чем разбирать саму функцию отметим 2 приятных момента, которые появились после создания:

  • Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
  • Когда вы ввели знак «=» и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.

Разбираем функцию пошагово

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

Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.

  • Имя функции не может содержать пробелов. Кроме того, вы не можете назвать функцию, если она сталкивается с именем ссылки на ячейку. Например, вы не можете назвать функцию ABC123, так как это имя также относится к ячейке в листе Excel.
  • Вы не должны указывать на свою функцию то же имя, что и у существующей функции. Если вы это сделаете, Excel будет отдавать предпочтение встроенной функции.
  • Вы можете использовать символ подчеркивания, если хотите разделить слова. Например, Сумма_Прописью является допустимым именем.

После названия в круглых скобках описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст. После названия аргумента мы указали As String, это означает, что наш аргумент — текстовое значение или ссылка на ячейку, содержащее текстовое значение. Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).

Последняя часть первой строки As Long задает тип данных, которая возвращает функция. В нашем случае функция будет возвращать целые значения. Это также не обязательно.

Вторая и третья строка функции объявляет дополнительные внутренние переменные, которые мы будем использовать.

Переменную i мы буем использовать для перебора символов. А переменную result для хранения промежуточного результата функции.

Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.

Len — функция, которая определяет количество символов.

Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result

Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.

Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/

Функция Next — закрывает цикл For тут все понятно.

Этой строкой мы преобразовываем текстовую переменную result, которая содержит все цифры аргумента Текст, в числовое значение. И говорим какой результат должна вывести наша функция Цифры.

Последняя строка кода — End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.

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

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×