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

Get FREE Advanced Excel Exercises with Solutions!

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 Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011, Excel 2010, and Excel 2007.

## 4 Practical Examples to Use VBA Large Function in Excel

There will be four suitable examples for using the VBA Large function in Excel. Firstly, we will find the highest value from a range. Secondly, we will get the lowest value. Thirdly, 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. Now, let us go through the steps. Steps:

• Firstly, press Alt+F11 to bring up the VBA window.
• Secondly, from the Insert tab, select Module. • Thirdly, 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()``````
• Firstly, with the “Option Explicit” statement we are making it mandatory to declare all the variables. Secondly, we name the subroutine “Highest_Sales”.
``Worksheets("Highest").Activate``
• Thirdly, we activate the “Highest” sheet.
``````Range("C12") = WorksheetFunction.Large(Range("D5:D10"), 1)
Range("C12").NumberFormat = "\$#,##0"
End Sub``````
• Then, we 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.
• Consequently, 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:

``````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()``````
• Firstly, with the “Option Explicit” statement we are making it mandatory to declare all the variables. Secondly, we name the subroutine “Lowest_Sales”.
``Worksheets("Lowest").Activate``
• Thirdly, we activate the “Lowest” sheet.
``````Dim xs As Double
' Finding the total number of rows
xs = Range("D5:D10").Rows.Count``````
• Then, we 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, we 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. Lastly, we change 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 third 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:

``````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. Secondly, we name the subroutine “Top3_Sales”.
``Worksheets("Top 3").Activate``
• Thirdly, we activate the “Top 3” sheet.
``Dim rIndex As Integer``
• Then, we 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 the 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:

``````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()``````
• Firstly, with the “Option Explicit” statement we are making it mandatory to declare all the variables. Secondly, we name the subroutine “Highest_Sales_Employee_Name”.
``Worksheets("HS Employee").Activate``
• Thirdly, we activate the “HS Employee” sheet.
``Dim lValue As Long``
• Then, we 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 the cell range D5:D10 as the lookup_range, and B5:B10 as the return_range. Lastly, we change 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.

## Related Articles Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  