I have an array that looks like this:
Dim values(1 To 3) As String
values(1) = Sheets("risk_cat_2").Cells(4, 6).Value
values(2) = Sheets("risk_cat_2").Cells(5, 6).Value
values(3) = Sheets("risk_cat_2").Cells(6, 6).Value
What I would like to do now is get the maximum value from all the values in string. Is there an easy way in VBA to fetch the max value from an array?
asked Jun 13, 2016 at 12:08
Frits VerstratenFrits Verstraten
2,0097 gold badges22 silver badges40 bronze badges
1
Is there an easy way in VBA to fetch the max value from an array?
Yes — if the values are numeric. You can use WorksheetFunction.Max
in VBA.
For strings — this won’t work.
Sub Test2()
Dim arr(1 To 3) As Long
arr(1) = 100
arr(2) = 200
arr(3) = 300
Debug.Print WorksheetFunction.Max(arr)
End Sub
answered Jun 13, 2016 at 12:26
Robin MackenzieRobin Mackenzie
18.3k7 gold badges38 silver badges54 bronze badges
1
Simple loop would do the trick
Dim Count As Integer, maxVal As Long
maxVal = Values(1)
For Count = 2 to UBound(values)
If Values(Count) > maxVal Then
maxVal = Values(Count)
End If
Next Count
answered Jun 13, 2016 at 12:11
The easiest way to retrieve the maximum (I can think of) is iterating through the array and comparing the values. The following two functions do just that:
Option Explicit
Public Sub InitialValues()
Dim strValues(1 To 3) As String
strValues(1) = 3
strValues(2) = "af"
strValues(3) = 6
Debug.Print GetMaxString(strValues)
Debug.Print GetMaxNumber(strValues)
End Sub
Public Function GetMaxString(ByRef strValues() As String) As String
Dim i As Long
For i = LBound(strValues) To UBound(strValues)
If GetMaxString < strValues(i) Then GetMaxString = strValues(i)
Next i
End Function
Public Function GetMaxNumber(ByRef strValues() As String) As Double
Dim i As Long
For i = LBound(strValues) To UBound(strValues)
If IsNumeric(strValues(i)) Then
If CDbl(strValues(i)) > GetMaxNumber Then GetMaxNumber = CDbl(strValues(i))
End If
Next i
End Function
Note, that each time a string (text) array is passed to the function. Yet, one function is comparing strings (text) while the other is comparing numbers. The outcome is quite different!
The first function (comparing text) will return (with the above sample data) af
as the maximum, while the second function will only consider numbers and therefore returns 6
as the maximum.
answered Jun 13, 2016 at 12:22
RalphRalph
9,2444 gold badges32 silver badges42 bronze badges
Solution for Collection.
Sub testColl()
Dim tempColl As Collection
Set tempColl = New Collection
tempColl.Add 57
tempColl.Add 10
tempColl.Add 15
tempColl.Add 100
tempColl.Add 8
Debug.Print largestNumber(tempColl, 2) 'prints 57
End Sub
Function largestNumber(inputColl As Collection, indexMax As Long)
Dim element As Variant
Dim result As Double
result = 0
Dim i As Long
Dim previousMax As Double
For i = 1 To indexMax
For Each element In inputColl
If i > 1 And element > result And element < previousMax Then
result = element
ElseIf i = 1 And element > result Then
result = element
End If
Next
previousMax = result
result = 0
Next
largestNumber = previousMax
End Function
answered Oct 27, 2017 at 12:21
AnsAns
1,2021 gold badge23 silver badges51 bronze badges
You can use the following basic syntax to calculate the max value in a range using VBA:
Sub MaxValue()
Range("D2") = WorksheetFunction.Max(Range("B2:B11"))
End Sub
This particular example calculates the max value in the range B2:B11 and assigns the result to cell D2.
If you would instead like to display the max value in a message box, you can use the following syntax:
Sub MaxValue()
'Create variable to store max value
Dim maxValue As Single
'Calculate max value in range
maxValue = WorksheetFunction.Max(Range("B2:B11"))
'Display the result
MsgBox "Max Value in Range: " & maxValue
End Sub
The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:
Example 1: Calculate Max Value of Range Using VBA and Display Results in Cell
Suppose we would like to calculate the max value in the points column and output the results in a specific cell.
We can create the following macro to do so:
Sub MaxValue()
Range("D2") = WorksheetFunction.Max(Range("B2:B11"))
End Sub
When we run this macro, we receive the following output:
Notice that cell D2 contains a value of 43.
This tells us that the max value in the points column is 43.
Example 2: Calculate Max Value of Range Using VBA and Display Results in Message Box
Suppose we would instead like to calculate the max value in the points column and output the results in a message box.
We can create the following macro to do so:
Sub MaxValue()
'Create variable to store max value
Dim maxValue As Single
'Calculate max value in range
maxValue = WorksheetFunction.Max(Range("B2:B11"))
'Display the result
MsgBox "Max Value in Range: " & maxValue
End Sub
When we run this macro, we receive the following output:
The message box tells us that the max value in the range B2:B11 is 43.
Note that in this example we calculated the max value in the range B2:B11.
However, if you’d like to instead calculate the max value in an entire column you could type B:B instead.
This will calculate the max value in all of column B.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Calculate Average Value of Range
VBA: How to Count Number of Rows in Range
VBA: How to Sum Values in Range
all_angarsk, Вы меня не поняли. Я имел ввиду, что не нужно усложнять. Любой модуль/процедуру Вы легко отправите в экспорт на флэшку в формате *.bas. И так точно вытянете его оттуда в любом месте, на любом компе, в любой документ. А с модулем кнопки — тяжелее. Ну и с самой кнопкой — нарисуйте встроенными инстр-ми фигуру (или обьект WordArt) что Вам нравится, и назначьте ей нужную процедуру (правая кнопка > Назначить макрос (или как там у Вас по локализации)). Всего пару кликов. И практично, и веселее, и проще, а не унылая серость.
А про «…регулярные выражения…«. Что Вы имели ввиду? Я их там не вижу.
Добавлено через 25 минут
Кажется, я понял про регулярку. Смотрите, у Тoiai грамотный и лаконичный код. Лично я бы все-таки сгенерированный массив выгрузил на лист, чтоб было видно. I.e., после next я бы добавил строку:
[a1].resize(1, ubound(a)).value=a
Дальше он вызывает окно сообщения MsgBox, в котором использует фукции не VBA, а Excel — Min и Max. Поэтому его тяжелая жизнь заставила вызывать их такими фразами Application.Max(a), Application.Min(a)…
Кстати, что б, если не нужно, не выкладывать массив на лист, его тоже можно одним движение загнать в этот же MsgBox.
What is Max Function in VBA?
Max Function is used to calculate the largest number. There are several numerical functions in excel which can be used to count the range, sum up the lot or to find the minimum or maximum value from the range of numbers. Max function is used to find the maximum value out of a range of values. It is an inbuilt function in Excel and categorized as the Max function. However, in VBA, there is no inbuilt function as Max to get the maximum value. Max function can be used in VBA Excel also. For the function argument (array, range, etc.), it can be either entered directly into the function or defined as variables to use instead.
Syntax:
=application.WorksheetFunction.max(arg1,arg2,arg3……………arg30)
Parameter or Arguments used in Max function are:
arg1……arg30: number 1 to number 30 from which the maximum number is to be inferred. It can be number, named ranges, arrays or reference to numbers.
Note:
- If the arguments contain no numbers, MAX returns 0 (zero).
- Arguments that have error values or text and cannot be translated into numbers will throw errors.
- Max function returns a numeric value.
How to Enable the Developers Tab?
Developer tab is mandatory on the Excel ribbon to start and write the VBA macro. Follow the below steps to enable the developer’s tab in Excel.
Step 1: Go to File.
Step 2: Click on Options.
Step 3: In a window opening up named Excel Options, click on Customize Ribbon to access the ribbon customization options.
Step 4: Here in the customization options, you can see the Developer(Custom) option. Checkmark it, so that it gets activated on the main ribbon of excel and can easily be accessed. Click OK after checking the Developer option.
Step 5: Click on the Developer tab and then click the Visual Basic (ALT +F11) icon.
VBA editor will appear.
How to Use Max Function in Excel VBA?
Below are the different examples to use Max function in Excel VBA:
You can download this VBA Max Excel Template here – VBA Max Excel Template
VBA Max – Example #1
Take four numbers 12, 25, 36, 45. Find out Max’s number by using the max function.
Code:
Sub AA() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim result As Integer A = 12 B = 25 C = 36 D = 45 result = WorksheetFunction.Max(A, B, C, D) MsgBox result End Sub
Note:
- Mention the data type of the variables through dim.
- Assign numbers to variables.
Run the code by pressing the F5 key or by clicking on the Play Button. The result will be displayed in the message box.
VBA Max – Example #2
Take four numbers 15, 34, 50, 62. Find out max number by using the Max function.
Code:
Sub AA1() A = 15 B = 34 C = 50 D = 62 result = WorksheetFunction.Max(A, B, C, D) MsgBox result End Sub
Note:
- Here, we have directly assigned numbers to four different variables without mentioning their data type. The program automatically decides the data type.
- Used those variables in the formula and got the result in the message box.
Run the code by pressing the F5 key or by clicking on the Play Button. The result will be displayed in the message box.
VBA Max – Example #3
Find the maximum value from the range by using the Max function.
Code:
Function getmaxvalue(Maximum_range As Range) Dim i As Double For Each cell In Maximum_range If cell.Value > i Then i = cell.Value End If Next getmaxvalue = i End Function
Note:
- A function procedure in VBA code performs calculations and returns the result.
- It can have an optional return statement. It is required to return a value from a function.
- Before using the function we need to define that particular function.
Syntax:
Function functionname(parameter_list)
Statement 1
Statement 2
Statement 3
:
End Function
Here, the function keyword is followed by a unique function name e.g. getmaxvalue(args_1,…args_n) and may or may not carry the list of parameters with datatype e.g. Maximum_range As Range. It ends with the End Function which indicates the end of the function. Mention the data type of the variables through dim.
Calling a function:
To invoke a function call the function by using the function name e.g getmaxvalue(args_1,…args_n).
The result will be as given below.
VBA Max – Example #4
Find the maximum value from the range by using the Max function.
Code:
Function getmaxvalue(Maximum_range As Range) Dim i As Double For Each cell In Maximum_range If cell.Value > i Then i = cell.Value End If Next getmaxvalue = i End Function
Note:
- Maximum_range represents a range of cells passed from the excel sheet as a parameter.
- The variable i is declared as Double.
- The For loop is iterated. With each iteration, the, if condition checks whether the value read from the corresponding cell, is greater than i. If the condition evaluates true then cell value is assigned to i.
- When all the cells in the Maximum_range have been iterated, the maximum among those will be assigned to i.
- Finally, i is assigned to getmaxvalue and returned to the calling cell.
The result will be as given below.
Conclusion
VBA max function is used to find the maximum value from a range of numbers. A function procedure is required to perform calculations. Dim is used to define variables. End function is used to end the function. It performs the task very fast and accurate. Though it is not an inbuilt function in VBA excel, however, by using function procedure we can perform the max function in VBA excel.
Recommended Article
This is a guide to VBA MAX. Here we discuss how to use MAX function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –
- VBA Solver
- VBA IF Statements
- VBA Sort
- VBA While Loop
- Что такое Max Function в VBA?
Что такое Max Function в VBA?
Макс функция используется для расчета наибольшего числа. В Excel есть несколько числовых функций, которые можно использовать для подсчета диапазона, суммирования лота или для поиска минимального или максимального значения из диапазона чисел. Функция Max используется для поиска максимального значения из диапазона значений. Это встроенная функция в Excel, которая относится к категории Max. Однако в VBA нет встроенной функции как Max, чтобы получить максимальное значение. Функция Max также может использоваться в VBA Excel. Для аргумента функции (массив, диапазон и т. Д.) Его можно либо ввести непосредственно в функцию, либо определить как переменные для использования вместо него.
Синтаксис:
=application.WorksheetFunction.max(arg1, arg2, arg3……………arg30)
Параметр или аргументы, используемые в функции Max:
arg1 …… arg30: число 1 — число 30, из которого следует вывести максимальное число. Это может быть число, именованные диапазоны, массивы или ссылки на числа.
Замечания:
- Если аргументы не содержат чисел, MAX возвращает 0 (ноль).
- Аргументы, которые имеют значения ошибок или текст и не могут быть переведены в числа, приведут к ошибкам.
- Функция Max возвращает числовое значение.
Как включить вкладку «Разработчики»?
Вкладка «Разработчик» обязательна на ленте Excel для запуска и записи макроса VBA. Выполните следующие шаги, чтобы включить вкладку разработчика в Excel.
Шаг 1: Перейти к файлу .
Шаг 2: Нажмите на Опции .
Шаг 3. В открывшемся окне с именем «Параметры Excel» нажмите «Настроить ленту», чтобы получить доступ к параметрам настройки ленты.
Шаг 4: Здесь в опциях настройки вы можете увидеть опцию Разработчик (Custom) . Отметьте его, чтобы он активировался на главной ленте Excel и был легко доступен. Нажмите OK после проверки опции Разработчик.
Шаг 5. Откройте вкладку « Разработчик » и щелкните значок Visual Basic (ALT + F11).
VBA редактор появится.
Как использовать функцию Max в Excel VBA?
Ниже приведены различные примеры использования функции Max в Excel VBA:
Вы можете скачать этот шаблон VBA Max Excel здесь — Шаблон VBA Max Excel
VBA Max — Пример № 1
Возьмите четыре числа 12, 25, 36, 45. Узнайте число Макса, используя функцию max.
Код:
Sub AA () Dim A как целое число Dim B как целое число Dim C как целое число Dim D как целое число Dim результат как целое число A = 12 B = 25 C = 36 D = 45 result = WorksheetFunction.Max (A, B, C, D) MsgBox результат End Sub
Замечания:
- Упомяните тип данных переменных через dim .
- Присвойте числа переменным.
Запустите код, нажав клавишу F5 или нажав кнопку воспроизведения. Результат будет отображен в окне сообщения.
VBA Max — Пример № 2
Возьмите четыре числа 15, 34, 50, 62. Узнайте максимальное число, используя функцию Max.
Код:
Sub AA1 () A = 15 B = 34 C = 50 D = 62 результат = WorksheetFunction.Max (A, B, C, D) MsgBox результат End Sub
Замечания:
- Здесь мы прямо присвоили номера четырем различным переменным, не упоминая их тип данных. Программа автоматически решает тип данных.
- Использовали эти переменные в формуле и получили результат в окне сообщения.
Запустите код, нажав клавишу F5 или нажав кнопку воспроизведения. Результат будет отображен в окне сообщения.
VBA Max — Пример № 3
Найдите максимальное значение из диапазона, используя функцию Max.
Код:
Функция getmaxvalue (Maximum_range As Range) Dim i As Double для каждой ячейки в Maximum_range If cell.Value> i Тогда i = cell.Value End If Next getmaxvalue = i End Function
Замечания:
- Функциональная процедура в коде VBA выполняет вычисления и возвращает результат.
- Он может иметь необязательный оператор возврата. Требуется вернуть значение из функции.
- Перед использованием функции нам нужно определить эту конкретную функцию.
Синтаксис:
Function functionname(parameter_list)
Statement 1
Statement 2
Statement 3
:
End Function
Здесь за ключевым словом функции следует уникальное имя функции, например, getmaxvalue (args_1, … args_n), и может содержать или не содержать список параметров с типом данных, например Maximum_range As Range. Он заканчивается функцией завершения, которая указывает конец функции. Упомяните тип данных переменных через dim .
Вызов функции:
Чтобы вызвать функцию, вызовите функцию, используя имя функции, например, getmaxvalue (args_1, … args_n) .
Результат будет таким, как указано ниже.
VBA Max — Пример № 4
Найдите максимальное значение из диапазона, используя функцию Max.
Код:
Функция getmaxvalue (Maximum_range As Range) Dim i As Double для каждой ячейки в Maximum_range If cell.Value> i Тогда i = cell.Value End If Next getmaxvalue = i End Function
Замечания:
- Maximum_range представляет диапазон ячеек, переданных из таблицы Excel в качестве параметра.
- Переменная i объявлена как Double.
- Цикл For повторяется. На каждой итерации условие if проверяет, является ли значение, считанное из соответствующей ячейки, больше, чем i. Если условие оценивается как истина, то значение ячейки присваивается i .
- Когда все ячейки в Maximum_range будут повторены, максимум из них будет назначен i .
- Наконец , я назначен getmaxvalue и возвращен в вызывающую ячейку.
Результат будет таким, как указано ниже.
Вывод
Функция VBA max используется для поиска максимального значения из диапазона чисел. Для выполнения расчетов требуется функциональная процедура. Dim используется для определения переменных. Функция завершения используется для завершения функции. Он выполняет задачу очень быстро и точно. Хотя это не встроенная функция в Excel VBA, однако, используя процедуру функции, мы можем выполнить функцию max в VBA Excel.
Рекомендуемая статья
Это руководство по VBA MAX. Здесь мы обсудим, как использовать функцию MAX в Excel VBA вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —
- Создание объекта коллекции в Excel VBA
- VBA IF Заявления | Шаблоны Excel
- Как использовать функцию сортировки Excel VBA?
- VBA While Loop (Примеры с шаблоном Excel)