How to Use VBA Large Function in Excel (4 Suitable Examples)

Excel functions can also be used in the VBA Macro. Many functions are available in the WorksheetFunction module. In this article, we will show you how to use the VBA Large function in Excel.

Introduction to VBA Large Function in Excel

Summary:

Returns the K-th largest value in a dataset, where K must be a positive integer.

Syntax:

Large(Arg1, Arg2 As Double) As Double

Arguments:

Argument	Requirement	Type	Explanation
Arg1	Required	Variant	The array from which you need to choose the kth largest value.
Arg2	Required	Double	Pass a double type of value that specifies the position in the array from the largest value.

Return Parameter:

This function returns a double type of data.

Available In:

Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011, Excel 2010, and Excel 2007.

How to Use VBA Large Function in Excel: 4 Suitable Examples

There will be four suitable examples for using the VBA Large function in Excel. First, we will find the highest value from a range. Then, we will get the lowest value. We will return the top three values. Finally, we will show you how to combine the Large function with the VBA XLookup function. It will be used to return the name of the employee who generated the highest sales.

1. Getting the Highest Value with VBA Large Function

We will use the Excel VBA Large function to return the highest sales value. Here is a snapshot of the dataset for this example. You can press a shortcut key or click Visual Basic from the Developer tab to start typing the VBA codes. To display the Developer tab on the ribbon, you need to use Excel Options. Now, let us go through the steps.

Steps:

First, press Alt+F11 to bring up the VBA window.
From the Insert tab, select Module.

Type the following code in that window.

Option Explicit
Sub Highest_Sales()
Worksheets("Highest").Activate
Range("C12") = WorksheetFunction.Large(Range("D5:D10"), 1)
Range("C12").NumberFormat = "$#,##0"
End Sub

VBA Code Breakdown

Option Explicit
Sub Highest_Sales()

With the Option Explicit statement, we are making it mandatory to declare all the variables. We named the subroutine as Highest_Sales.

Worksheets("Highest").Activate

Activate the sheet named Highest.

Range("C12") = WorksheetFunction.Large(Range("D5:D10"), 1)
Range("C12").NumberFormat = "$#,##0"
End Sub

Apply the VBA Large function in cell C12. Here, we want the highest value from the range D5:D10, so we input 1. Lastly, we change the number format.

Then, click inside the code and press F5. It will execute this code.
It will return the highest sales value in cell C12.

2. Getting the Lowest Value

In this section, we will use the VBA Large function to find the lowest sales value from the array. The last number of elements in an array will return the smallest value from the range. We can find the number of rows to determine this.

Steps:

To begin with, as shown in the first example, bring up the Module window and insert the following code.

Option Explicit
Sub Lowest_Sales()
Worksheets("Lowest").Activate
Dim xs As Double
' Finding the total number of rows
xs = Range("D5:D10").Rows.Count
Range("C12") = WorksheetFunction.Large(Range("D5:D10"), xs)
Range("C12").NumberFormat = "$#,##0"
End Sub

VBA Code Breakdown

Option Explicit
Sub Lowest_Sales()

Option Explicit statement we are making it mandatory to declare all the variables. Set Lowest_Sales as the name of the subroutine.

Worksheets("Lowest").Activate

Activate the Lowest sheet.

Dim xs As Double
' Finding the total number of rows
xs = Range("D5:D10").Rows.Count

Then, declare the variable type and find the number of rows in the dataset.

Range("C12") = WorksheetFunction.Large(Range("D5:D10"), xs)
Range("C12").NumberFormat = "$#,##0"
End Sub

Then, apply the VBA Large function in cell C12. Here, we want the lowest value from the range D5:D10, so we input xs, which is the total number of rows in the range. Finally, we changed the number format.

Then, click inside the code and press F5. It will execute this code.
So, it will return the lowest sales value in cell C12.

3. Finding Top 3 Values

In this example, we will use the VBA Large function to find the top three sales values. The For Next loop needs to be used to go through three cells. Moreover, we have changed the dataset a little bit for this example.

Steps:

To begin with, as shown in the first example, bring up the Module window and insert the following code.

Option Explicit
Sub Top3_Sales()
Worksheets("Top 3").Activate
Dim rIndex As Integer
For rIndex = 12 To 14
    Cells(rIndex, 3) = WorksheetFunction.Large(Range("D5:D10"), rIndex - 11)
    Cells(rIndex, 3).NumberFormat = "$#,##0"
Next rIndex
End Sub

VBA Code Breakdown

Option Explicit
Sub Top3_Sales()

Firstly, with the Option Explicit statement, we are making it mandatory to declare all the variables. Then, we name the subroutine as Top3_Sales.

Worksheets("Top 3").Activate

Activate the sheet named as Top 3.

Dim rIndex As Integer

Next, declare the variable type.

For rIndex = 12 To 14
    Cells(rIndex, 3) = WorksheetFunction.Large(Range("D5:D10"), rIndex - 11)
    Cells(rIndex, 3).NumberFormat = "$#,##0"
Next rIndex
End Sub

Then, we apply the VBA Large function in column C and rows 12 to 14. Here, we want the top three values from the range D5:D10, so we input rIndex-11, which returns the values 1, 2, and 3 for each loop. Here, we have used 12 to 14 because the output will be on rows 12 to 14.
Lastly, we change the number format.

Then, click inside the code and press F5. It will execute this code.
So, it will return the top three sales values.

4. Combining Large Function with XLookup to Lookup the Highest Value

In this last method, we will show you an example of how to use the VBA Large function with other functions. We will combine the VBA Large and XLookup functions to find the name of the employee who is responsible for the highest sales. Here is a snapshot of the dataset.

Steps:

To begin with, as shown in the first example, bring up the Module window and use the following code.

Option Explicit
Sub Highest_Sales_Employee_Name()
Worksheets("HS Employee").Activate
Dim lValue As Long
lValue = WorksheetFunction.Large(Range("D5:D10"), 1)
Range("C12") = WorksheetFunction.XLookup(lValue, Range("D5:D10"), Range("B5:B10"))
Range("C12").NumberFormat = "$#,##0"
End Sub

VBA Code Breakdown

Option Explicit
Sub Highest_Sales_Employee_Name()

With the Option Explicit statement, we are making it mandatory to declare all the variables. We name the subroutine Highest_Sales_Employee_Name.

Worksheets("HS Employee").Activate

Activate the HS Employee sheet.

Dim lValue As Long

Declare the variable type.

lValue = WorksheetFunction.Large(Range("D5:D10"), 1)

After that, we find the largest sales value and assign it to the variable.

Range("C12") = WorksheetFunction.XLookup(lValue, Range("D5:D10"), Range("B5:B10"))
Range("C12").NumberFormat = "$#,##0"
End Sub

Then, we input the previous value inside the VBA XLookup function. Here, the lookup_value is our variable. Then, we specify range D5:D10 as the lookup_range, and range B5:B10 as the return_range. Lastly, we changed the number format to currency.

Then, click inside the code and press F5. It will execute this code.
So, it will return the employee’s name for the highest sales value.

Things to Remember

Here the value of Arg2 should be greater than 0 (Arg2>0).
Large(array,1) returns the biggest value, and Large(array,n) returns the smallest value if n is the number of data points in a range.
The VBA Large function processes only numeric values. Blank cells, text, and logical values are ignored.

Download Practice Workbook

You can download the Excel file from the link below.

Use of VBA Large Function.xlsm

Conclusion

We have shown you four easy examples of the VBA Large function in Excel. Moreover, there is a practice section in the Excel file. You can use that to follow along with this article. Please leave a comment below if you have any questions or concerns about these techniques. However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, for more Excel-related articles. Thanks for reading.