Rafiul Haq
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.
Download Practice Workbook
You can download the Excel file from the link below.
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.
- 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.
Read More: How to Use Excel Large Function with Criteria( 4 Suitable Ways)
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()- 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.
Read More: How to Use Excel Large Function in Multiple Ranges
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:
- 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. 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.
Read More: How to Use LARGE and SMALL Function in Excel
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()- 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.
Read More: How to Use Excel Large Function with Text (3 Easy Ways)
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.
Conclusion
We have shown you four easy examples of 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, ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
