Как оптимизировать функцию VBA в Excel. В vba функция

Математические функции (Visual Basic)

Рекомендуем использовать Visual Studio 2017

Эта документация перемещена в архив и не поддерживается.

Visual Studio 2013

Методы класса Math предоставляют тригонометрические, логарифмические и других общих математические функции.

В следующей таблице перечислены методы класса Math. Можно использовать их в программе Visual Basic.

Метод .NET Framework

Описание

Abs	Возвращает абсолютное значение числа.
Acos	Возвращает угол, косинус которого равен указанному числу.
Asin	Возвращает угол, синус которого равен указанному числу.
Atan	Возвращает угол, тангенс которого равен указанному числу.
Atan2	Возвращает угол, тангенс которого равен отношению двух указанных чисел.
BigMul	Возвращает полный продукт 2 32 разрядных чисел.
Ceiling	Возвращает наименьшее целое значение, которое меньше или равно указанному Decimal или Double.
Cos	Возвращает косинус указанного угла.
Cosh	Возвращает гиперболический косинус указанного угла.
DivRem	Возвращает частное 2 32 или 64 разрядного разрядных знаковых целых чисел, а также возвращает остаток в параметре вывода.
Exp	Возвращает e (основание натуральных логарифмов), возведенное в заданную степень.
Floor	Возвращает наибольшее целое число, которое меньше или равно числу указанного типа Decimal или Double.
IEEERemainder	Возвращает остаток от деления, результаты из указанного числа другим указанным количеством.
Log	Возвращает естественный ( e) базового логарифм заданного числа или логарифм заданного числа в определенной базе.
Log10	Возвращает логарифм с основанием 10 указанного числа.
Max	Возвращает большее 2 чисел.
Min	Возвращает меньшее из двух чисел.
Pow	Возвращает указанное число, возведенное в указанную степень.
Round	Возвращает значение Decimal или значение Double, округленное до разным значений или с указанным количеством цифр.
Sign	Возвращает значение типа Integer, показывающее знак числа.
Sin	Возвращает синус указанного угла.
Sinh	Возвращает гиперболический синус указанного угла.
Sqrt	Возвращает квадратный корень из указанного числа.
Tan	Возвращает тангенс указанного угла.
Tanh	Возвращает гиперболический тангенс указанного угла.
Truncate	Вычисляет неотъемлемую часть номера, определенных в Decimal или Double.

Для использования этих функций без уточнения импортировать пространство имен Math в проект, добавив следующий код в начало файла источника:

В этом примере метод Abs класса Math используется для вычисления абсолютного значения числа.

' Returns 50.3. Dim MyNumber1 As Double = Math.Abs(50.3) ' Returns 50.3. Dim MyNumber2 As Double = Math.Abs(-50.3)

В этом примере метод Atan класса Math используется для вычисления значения числа пи.

Public Function GetPi() As Double ' Calculate the value of pi. Return 4.0 * Math.Atan(1.0) End Function

В этом примере метод Cos класса Math используется для возврата косинуса угла.

Public Function Sec(ByVal angle As Double) As Double ' Calculate the secant of angle, in radians. Return 1.0 / Math.Cos(angle) End Function

В этом примере метод Exp класса Math используется для возврата числа e, возведенного в степень.

Public Function Sinh(ByVal angle As Double) As Double ' Calculate hyperbolic sine of an angle, in radians. Return (Math.Exp(angle) - Math.Exp(-angle)) / 2.0 End Function

В этом примере метод Log класса Math используется для возврата натурального логарифма числа.

Public Function Asinh(ByVal value As Double) As Double ' Calculate inverse hyperbolic sine, in radians. Return Math.Log(value + Math.Sqrt(value * value + 1.0)) End Function

В этом примере метод Round класса Math используется для округления числа до ближайшего целого числа.

' Returns 3. Dim MyVar2 As Double = Math.Round(2.8)

В этом примере метод Sign класса Math используется для определения знака числа.

' Returns 1. Dim MySign1 As Integer = Math.Sign(12) ' Returns -1. Dim MySign2 As Integer = Math.Sign(-2.4) ' Returns 0. Dim MySign3 As Integer = Math.Sign(0)

В этом примере метод Sin класса Math используется для возврата синуса угла.

Public Function Csc(ByVal angle As Double) As Double ' Calculate cosecant of an angle, in radians. Return 1.0 / Math.Sin(angle) End Function

В этом примере метод Sqrt класса Math используется для вычисления квадратного корня числа.

' Returns 2. Dim MySqr1 As Double = Math.Sqrt(4) ' Returns 4.79583152331272. Dim MySqr2 As Double = Math.Sqrt(23) ' Returns 0. Dim MySqr3 As Double = Math.Sqrt(0) ' Returns NaN (not a number). Dim MySqr4 As Double = Math.Sqrt(-4)

В этом примере метод Tan класса Math используется для возврата тангенса угла.

Public Function Ctan(ByVal angle As Double) As Double ' Calculate cotangent of an angle, in radians. Return 1.0 / Math.Tan(angle) End Function

Класс: Math

Пространство имен: System

Сборка: mscorlib (в mscorlib.dll)

Ссылки

Основные понятия

msdn.microsoft.com

Функция суммы в VBA | Programmerz.ru

У меня проблема с суммированием ячеек в vba. Мне нужно использовать ячейки (a, b):

Range("A1").function="=SUM(Range(Cells(2,1),Cells(3,2)))"

но это не сработает.

Функция не является свойством / методом из диапазона.

Если вы хотите суммировать значения, используйте следующее:

Range("A1").Value = Application.Sum(Range(Cells(2, 1), Cells(3, 2)))

РЕДАКТИРОВАТЬ:

если вы хотите, чтобы формула использовалась следующим образом:

Range("A1").Formula = "=SUM(" & Range(Cells(2, 1), Cells(3, 2)).Address(False, False) & ")" 'The two false after Adress is to define the address as relative (A2:B3). 'If you omit the parenthesis clause or write True instead, you can set the address 'as absolute ($A$2:$B$3).

Если вы всегда будете использовать один и тот же адрес диапазона, то вы можете использовать его как Rory:

Range("A1").Formula ="=Sum(A2:B3)"

Поместите функцию стоимость в клетку

Application.Sum часто не работает хорошо в моем опыте (или, по крайней мере, среда разработчика VBA не нравится по какой-либо причине).

Функция, которая работает лучше всего для меня, - это Excel.WorksheetFunction.Sum()

Пример:

Dim Report As Worksheet 'Set up your new worksheet variable. Set Report = Excel.ActiveSheet 'Assign the active sheet to the variable. Report.Cells(11, 1).Value = Excel.WorksheetFunction.Sum(Report.Range("A1:A10")) 'Add the function result.

Поместите функцию непосредственно в ячейку

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

Dim Report As Worksheet 'Set up your new worksheet variable. Set Report = Excel.ActiveSheet 'Assign the active sheet to the variable. Report.Cells(11, 1).Value = "=Sum(A1:A10)" 'Add the function. Range("A1").Function="=SUM(Range(Cells(2,1),Cells(3,2)))"

не будет работать, потому что функции рабочего листа (когда фактически используются на листе) не понимают Range или Cell

Пытаться

Range("A1").Formula="=SUM(" & Range(Cells(2,1),Cells(3,2)).Address(False,False) & ")" Range("A10") = WorksheetFunction.Sum(Worksheets("Sheet1").Range("A1", "A9"))

где

Range("A10") является ячейкой ответа

Range("A1", "A9") - это диапазон для вычисления

programmerz.ru

Использование функций Microsoft Excel в Visual Basic — howtoo.ru

Большинство функций рабочего листа Microsoft Excel можно использовать в коде Visual Basic (список этих функций: List of Worksheet Functions Available to Visual Basic)

Часть функций Excel в Visual Basic просто не нужны. Например, вместо функции Concatenate (СЦЕПИТЬ) в коде Visual Basic для объединения текстов используется оператор &.

Вызов функции рабочего листа из Visual Basic

В Visual Basic функции рабочего листа (worksheet ) Microsoft Excel доступны через объект WorksheetFunction.

Например, здесь используется функция Min для определения наименьшего значения в диапазоне ячеек:

Sub UseFunction() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:C10") answer = Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub

Здесь сначала объявляется переменная myRangeкак объект типа Range, и затем ей назначается диапазон ячеек A1:C10 на листе Sheet1. Переменной answer присваивается результат применения функции Min к myRange. Полученное значение answer отображается в окне сообщения.

Диапазон ячеек необходимо объявить как объект Range!

Например, в формуле в ячейке таблицы Excel=MATCH(9,A1:A10,0)а в коде Visual Basic —

Sub FindFirst() myVar = Application.WorksheetFunction.Match(9, Worksheets(1).Range("A1:A10"), 0) MsgBox myVar End Sub

Функции Visual Basic (без префикса WorksheetFunction) могут иметь такое же имя, как и функция Excel, но работать иначе. Например, Application.WorksheetFunction.Log и Log возвращают разные значения.

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

Чтобы вставить функцию в ячейку, нужно присвоить ее свойству Formula соответствующего объекта Range.

Например:

Sub InsertFormula() Worksheets("Sheet1").Range("A1:B3").Formula = "=RAND()" End Sub

результат функции RAND (она возвращает случайное число) присваивается свойству Formula диапазона ячеек A1:B3 на листе Sheet1в активной книге Excel.

источник (Using Microsoft Excel Worksheet Functions in Visual Basic [Excel 2003 VBA Language Reference] Office 2003)

howtoo.ru

Функции VBA и массивы значений

Создание функций с бесконечным количеством аргументов

В предыдущих примерах мы создавали функции, в которых в качестве аргументов выступало одно значение. Но есть функции, у которых в качестве переменной выступает массив значений. Примерами таких функций встроенных в Excel служат функции СУММ, ВПР, МАКС, СРЗНАЧ и так далее. Давайте теперь создадим свою похожую функцию.

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

Function СуммаЧетных(Диапазон As Range) As Double Dim r As Range Dim S As Double For Each r In Диапазон If r.Value Mod 2 = 0 Then S = S + r.Value Next СуммаЧетных = S End Function

В первой строке данной функции можно увидеть, что переменной Диапазон мы присвоили тип Range (Диапазон As Range). Таким образом, в качестве аргумента функции мы можем использовать диапазон ячеек. Применим эту функцию на рабочем листе Excel.

Как видно из рисунка, мы не перечисляли каждое значение, а использовали диапазон ячеек A2:A11.

Теперь немного разберем код. Мы использовали цикл For Each - Next. Его задача пройтись по каждой ячейке нашего диапазона. Предварительно мы объявили еще одну переменную r типа Range. Она будет хранить значение каждой ячейки.

For Each r In Диапазон

Далее идет проверка значения ячейки и если оно кратно 2, то мы суммируем результат во временную переменную S.

If r.Value Mod 2 = 0 Then S = S + r.Value

В конце, после цикла, мы присваиваем функции СуммаЧетных значение просуммированных элементов S.

Создание функций с неопределенным количеством аргументов

Предыдущая функция имеет недостаток - если вы попытаетесь использовать в качестве переменной несмежный диапазон ячеек, то получите ошибку. Иногда требуется более универсальная функция, так как на старте мы можем не знать количество аргументов, которые будут использоваться. Например, всем известная функция СУММ может принимать неограниченное количество аргументов =СУММ(A1;B1:B5;1;2;3), функция все рано вернет верный результат.

Вы можете создать свою аналогичную функцию в VBA, указав к последнему (или единственному) аргументу ключевое слово ParamArray.

ParamArray - данный модификатор применяется только к последнему аргументу. Аргумент с данным модификатором всегда должен иметь тип данных Variant и всегда является необязательным, ключевое слов Optional не указывается дополнительно

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

Function СуммаЧетных2(ParamArray Значения() As Variant) As Double Dim v As Variant Dim S As Double For Each v In Значения If v Mod 2 = 0 Then S = S + v Next СуммаЧетных2 = S End Function

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

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

Function СуммаЧетных3(ParamArray Диапазоны() As Variant) As Double Dim v As Variant Dim r As Variant Dim S As Double For Each v In Диапазоны For Each r In v If r Mod 2 = 0 Then S = S + r Next Next СуммаЧетных3 = S End Function

У нас добавился еще один цикл For Each - Next. Т.е. сначала мы проходим по всем аргументам (диапазонам ячеек), а после проходим по всем ячейкам этих диапазонов.

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

Создание функции, возвращающих массив значений

Надеюсь вы знаете, как использовать формулы массивов в Excel. Эти функции вводятся на рабочий лист Excel с помощью сочетания клавиш Ctrl + Shift + Enter. Сейчас мы создадим функцию, которая возвратит массив значений. Например, выведет нам список всех месяцев.

Function СписокМесяцев() СписокМесяцев = Array("Январь", "Февраль", "Март", "Апрель", _ "Май", "Июнь", "Июль", "Август", _ "Сентябрь", "Октябрь", "Ноябрь", "Декабрь") End Function

Если мы введем функцию в одну ячейку, то увидим, что результатом будет только Январь. Чтобы понять как работает функция необходимо выделить 12 ячеек по горизонтали, ввести =СписокМесяцев() и нажать сочетание клавиш Ctrl + Shift + Enter.

Если необходимо вывести список месяцев по вертикали, то можно дополнительно использовать функцию =ТРАНСП(СписокМесяцев()).

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

Скачать

micro-solution.ru

Как оптимизировать функцию VBA в Excel Bilee

Должна удвоить скорость

Public Function Yield(Name As String, Price As Double) Dim Lookup As Range, rw As Integer Set Lookup = Range("LookupRange") rw = Application.WorksheetFunction.Match(Name, Lookup.Resize(ColumnSize:=1), 0) Yield = 100 * Application.Run("otherCustomFunction", Lookup.Cells(rw, 3), Lookup.Cells(rw, 7), Price) End Function

Это потому, что вы только просматриваете диапазон с именем «LookupRange» один раз, а не дважды, и вы смотрите только правильную линию один раз, а не дважды.

Вероятно, в 4 раза быстрее

Если мы получим диапазон в коде, который использует функцию yield , нам нужно сделать это только один раз

Public Function Yield(Lookup As Range, Name As String, Price As Double) rw = Application.WorksheetFunction.Match(Name, Lookup.Resize(ColumnSize:=1), 0) Yield = 100 * Application.Run("otherCustomFunction", Lookup.Cells(rw, 3), Lookup.Cells(rw, 7), Price) End Function Public Sub CallingRoutine() Dim Lookup As Range, rw As Integer Set Lookup = Range("LookupRange") ' Some code For Each someItem In someSet Dim amount As Double, Name As String, Price As Double ' Some code to deter;ine name and price amount = Yield(Lookup, Name, Price) ' Some code that used the yield Next someThing End Sub

Существует вариант этой стратегии, в котором вы объявляете Lookup вне всех подпрограмм, например, со словарем ниже.

На порядок быстрее, если вы часто Yield Доходу.

Вы просматриваете названный диапазон
Вы запрашиваете все значения из excel сразу
Вы ищете Name s в словаре, что намного эффективнее, чем поиск в диапазоне

Это код:

Public Function Yield(Name As String, Price As Double) If LookDict Is Nothing Then Set LookDict = New Dictionary Dim LookVal As Variant, rw As Integer, ToUse As ToUseType LookVal = Range("LookupRange").Value For rw = LBound(LookVal, 1) To UBound(LookVal, 1) Set ToUse = New ToUseType ToUse.Row3Val = LookVal(rw, 3) ToUse.Row7Val = LookVal(rw, 7) LookDict.Add LookVal(rw, 1), ToUse Next rw End If Set ToUse = LookDict.Item(Name) Yield = 100 * Application.Run("otherCustomFunction", _ ToUse.Row3Val, ToUse.Row7Val, Price) End Function Public Sub CallingRoutine() ' Some code For Each someItem In someSet Dim amount As Double, Name As String, Price As Double ' Some code to deter;ine name and price amount = Yield(Name, Price) ' Some code that used the yield Next someThing End Sub

www.bilee.com

Функции преобразования типов данных в VBA

Часто в программировании возникает необходимость перевести один тип в другой (по доступным типам данных в VB читаем в этой статье). Например, число в строку или строку в дату. Для перевода (преобразования) типов в VBA есть множество функций позволяющих это сделать. Но есть один момент, преобразовать возможно только тот тип или значение, которое подходит по формату нового типа. К примеру, если преобразовать строку "356" в целый тип, то на выходе мы получим число, но если в этой строке будет находиться символ, не относящийся к числу "356р", то преобразование завершится ошибкой несовпадения типов (Type mismatch).

В таблице ниже приведен полный список функций преобразования и тип, в который происходит преобразование:

Функция	Возвращает тип	Действие
CBool	Boolean	Преобразует значение в булевый тип
CByte	Byte	Преобразует значение в тип Byte
CCur	Currency	Преобразует значение в тип Currency
CDate	Date	Преобразует значение в Дату и Время
CDbl	Double	Преобразует значение в тип Double
CDec	Decimal	Преобразует значение в подтип Decimal типа Variant
CInt	Integer	Преобразует значение в целый тип
CLng	Long	Преобразует значение в длинное целое
CSng	Single	Преобразует значение в тип Single
CStr	String	Преобразует значение в строку
CVar	Variant	Преобразует значение в тип Variant

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

Работать с функциями преобразования легко. К примеру, необходимо преобразовать число 4568 в строку:

Dim OutStr as StringOutStr = CStr(4568)

Преобразование строки или числа в булевый тип

Dim OutBool as Boolean'Функция вернет значение TrueOutBool = CBool(1)OutBool = CBool ("TRUE")

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

Dim OutDate as DateOutDate = CDate("25/06/03 23:35")OutDate = CDate("25.06.03")OutDate = CDate("37797,9826388889")

Примечание: Функции преобразования можно использовать непосредственно в выражении избегая создания лишних переменных. Например: x=y+CInt("456")+z