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.


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

Syntax of VBA Large function

  • 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.

Dataset for the first example

Steps:

  • Firstly, press Alt+F11 to bring up the VBA window.
  • Secondly, from the Insert tab, select Module.

Insert Module menu in VBA window

  • 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 Large function code for getting the highest value

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.

VBA Large function returns highest sales value

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:

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 Large function code for getting the lowest value

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.

VBA code returns the lowest sales value

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.

Dataset for finding top 3 values using VBA Large function

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 Large function code for getting the top 3 values

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.

Output of the third example

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.

Dataset for example 4

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 for example 4

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.

Final output of the example 4

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!


Related Articles

Rafiul Haq
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo