Как найти наибольшее число vba

As the name suggests, Max is used to finding the maximum value from a given data set or array. Although it is a worksheet function, one may use it with the worksheet method as a worksheet function. However, there is a limitation to this method as this function takes an array as an argument. Therefore, there can only be 30 values in the array.

Excel VBA Max Function

We have several numerical functions in Excel. We can count numerical values in the range and sum and find the minimum value and maximum value of the lot. To find the maximum value of the lot, we have an excel function called MAXThe MAX Formula in Excel is used to calculate the maximum value from a set of data/array. It counts numbers but ignores empty cells, text, the logical values TRUE and FALSE, and text values.read more, which will return the maximum value of the supplied range of numbers. In VBA, we do not have any built-in function called “MAX” to get the maximum number. We will see how to use this Excel VBA Max function.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA Max (wallstreetmojo.com)

Example of Max Function in Excel VBA

Unfortunately, we do not have the luxury of using MAX as the VBA built-in function, but we can access this function as a part of the Worksheet function class.

Now, look at the code below.

Code:

Sub MAX_Example1()

    Dim a As Integer
    Dim b As Integer
    Dim c As Integer

    Dim Result As Integer

    a = 50
    b = 25
    c = 60

    Result = WorksheetFunction.Max(a, b, c)

    MsgBox Result

End Sub

We have declared three variables to store the number in the above example.

Dim a As Integer

Dim b As Integer

Dim c As Integer

We have declared one more variable to show the results.

Dim Result As Integer.

For the first 3 three variables, we assigned values like 50, 25, and 60, respectively.

a = 50

b = 25

c = 60

In the next line, we have applied the MAX as a VBA worksheet functionThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more class to store the result to the variable “Result.”

Result = WorksheetFunction.Max(a, b, c)

So finally, we are showing the value in the message box in VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

MsgBox Result

We will run this code using F5 or manually and see the result in the message box.

So, the result is 60.

From all the supplied numbers: 50, 25, and 60, the maximum number is 60.

Advanced Example of Max in Excel VBA

Loops are crucial in VBA to run through all the cells and arrive at the result. We will see how to combine VBA MAX with loops to arrive at the maximum value from the list of numbers.

We have a list of items and the monthly sales performance of those items, as shown below.

Now for each item, we want to know the maximum sale number across four months, as shown in the picture.

By applying MAX to Excel, we can find this in a few seconds.

We will now see how to find the maximum value using the VBA code.

The below code will perform the task of finding the maximum number for each item.

Code:

Sub MAX_Example2()

    Dim k As Integer

    For k = 2 To 9
        Cells(k, 7).Value = WorksheetFunction.Max(Range("A" & k & ":" & "E" & k))
    Next k

End Sub

It will identify the maximum number easily.

Run the code manually or press the F5 key to see the result below.

To get the maximum values month name, use the below code.

Code:

Sub MAX_Example2()

    Dim k As Integer

    For k = 2 To 9
        Cells(k, 7).Value = WorksheetFunction.Max(Range("B" & k & ":" & "E" & k))
        Cells(k, 8).Value = WorksheetFunction.Index(Range("B1:E1"), WorksheetFunction.Match _
                        (Cells(k, 7).Value, Range("B" & k & ":" & "E" & k)))
    Next k

End Sub

Based on the value provided by the VBA max function, the INDEX functionThe INDEX function in Excel helps extract the value of a cell, which is within a specified array (range) and, at the intersection of the stated row and column numbers.read more & MATCH functionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position.
read more will return the associated month in the next line.

Things to Remember

• If their duplicate number is there, it will show only one number which comes first.
• It is the opposite formula of the MIN function in excelIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array.read more.
• The MAX function is not a VBA function. However, it is a built-in function in Excel, so use the worksheet function class.

You can download this Excel Template here – VBA Max Function Template.

Recommended Articles

This article has been a guide to VBA Max. Here, we learn how to use the Max function in VBA to find the maximum value from a supplied range of numbers, along with examples and downloadable codes. Below are some useful Excel articles related to VBA: –

• VBA FileCopy
• VBA Debug Print
• VBA FileSystemObject
• ByRef in VBA
• VBA Find and Replace

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